/// <summary>
/// Creates the update command set with the supplied context.
/// </summary>
/// <param name="context"></param>
/// <param name="logger"></param>
public EntityUpdateCommands(DbContext context, ILogger logger)
{
Context = context ?? throw new ArgumentNullException(nameof(context));
Logger = logger ?? throw new ArgumentNullException(nameof(logger));
var t = typeof(TEntity);
EntityType = Context.Model.FindEntityType(t)
?? throw new ArgumentException($"The entity type {t} does not exist in the {Context.GetType()} model.");
Key = EntityType.FindPrimaryKey()
?? throw new ArgumentException($"The entity type {t} does not have a primary key in the {Context.GetType()} model.");
var stamps = new[] { "timestamp", "rowversion" };
EntityProperties = EntityType
.GetProperties()
.Where(x => !stamps.Contains(x.GetColumnType().ToLower()))
.ToArray();
var conn = Context.Database.ProviderName;
Knowledge = SqlKnowledge.For(conn)
?? throw new ArgumentException($"The {conn} provider does not have registered SQL knowledge.");
}
/// <summary>
/// Quotes an object name.
/// </summary>
/// <param name="knowledge"></param>
/// <param name="objectName"></param>
/// <returns></returns>
public static string QuoteObjectName(this ISqlKnowledge knowledge, string objectName)
{
if (knowledge is null)
{
return(null);
}
if (string.IsNullOrEmpty(objectName))
{
return("");
}
StringBuilder ret;
if (knowledge.EscapeObjectName != null)
{
ret = new StringBuilder(knowledge.EscapeObjectName(objectName));
}
else
{
ret = new StringBuilder(objectName);
ret.Replace(
knowledge.ObjectCloseQuote,
knowledge.ObjectCloseQuote + knowledge.ObjectCloseQuote
);
}
ret.Insert(0, knowledge.ObjectOpenQuote);
ret.Append(knowledge.ObjectCloseQuote);
return(ret.ToString());
}
/// <summary>
/// Concatenates two or more string values.
/// </summary>
/// <param name="knowledge"></param>
/// <param name="values"></param>
/// <returns></returns>
/// <exception cref="ArgumentNullException"></exception>
/// <exception cref="ArgumentException"></exception>
public static string ConcatValues(this ISqlKnowledge knowledge, params string[] values)
{
if (knowledge is null)
{
throw new ArgumentNullException(nameof(knowledge));
}
if (values is null)
{
throw new ArgumentNullException(nameof(values));
}
if (values.Length < 1)
{
throw new ArgumentException("Cannot be empty.", nameof(values));
}
if (values.Length == 1)
{
return(values[0]);
}
var ret = new StringBuilder();
ret.Append(values[0]);
for (var i = 1; i < values.Length; i++)
{
if (string.IsNullOrEmpty(knowledge.ConcatStringBefore))
{
ret.Insert(0, '(');
}
else
{
ret.Insert(0, knowledge.ConcatStringBefore);
}
if (knowledge.ConcatStringMid == ",")
{
ret.Append(", ");
}
else
{
ret.Append(' ').Append(knowledge.ConcatStringMid).Append(' ');
}
ret.Append(values[i]);
if (string.IsNullOrEmpty(knowledge.ConcatStringBefore))
{
ret.Append(')');
}
else
{
ret.Append(knowledge.ConcatStringAfter);
}
}
return(ret.ToString());
}
/// <inheritdoc />
public string GetCreateTableCommand(ISqlKnowledge knowledge)
{
return(knowledge.GetCreateTemporaryTableCommand(
GetTableName(knowledge),
"(ListId INTEGER NOT NULL, EntryValue " +
GetValueTypeName(knowledge) +
"NOT NULL, PRIMARY KEY (ListId, EntryValue))"
));
}
/// <summary>
/// Registers SQL knowledge.
/// </summary>
/// <param name="knowledge"></param>
/// <remarks>
/// The EngineName property is used to uniquely identify a set of knowledge.
/// </remarks>
/// <exception cref="ArgumentNullException"></exception>
public static void Register(ISqlKnowledge knowledge)
{
if (knowledge is null)
{
throw new ArgumentNullException(nameof(knowledge));
}
lock (Known)
{
if (Known.All(x => x.EngineName != knowledge.EngineName))
{
Known.Insert(0, knowledge);
}
}
}
/// <summary>
/// Unquotes an object name.
/// </summary>
/// <param name="knowledge"></param>
/// <param name="objectName"></param>
/// <returns></returns>
public static string UnquoteObjectName(this ISqlKnowledge knowledge, string objectName)
{
var quoteLen = knowledge.ObjectOpenQuote.Length + knowledge.ObjectCloseQuote.Length;
if (objectName.Length >= quoteLen &&
objectName.StartsWith(knowledge.ObjectOpenQuote) &&
objectName.EndsWith(knowledge.ObjectCloseQuote))
{
objectName = objectName.Substring(knowledge.ObjectOpenQuote.Length, objectName.Length - quoteLen);
}
if (knowledge.UnescapeObjectName != null)
{
return(knowledge.UnescapeObjectName(objectName));
}
var q = Regex.Escape(knowledge.ObjectCloseQuote);
return(Regex.Replace(objectName, $"{q}{q}", knowledge.ObjectCloseQuote));
}
/// <inheritdoc />
public string GetInsertCommand(ISqlKnowledge knowledge, int listId, int count)
{
var ret = new StringBuilder();
ret.Append("INSERT INTO ")
.Append(GetTableName(knowledge))
.Append(" (ListId, EntryValue) ");
if (count < 1)
{
return(ret.ToString());
}
ret.Append("VALUES (").Append(listId).Append(", {0})");
for (var i = 1; i < count; i++)
{
ret.Append(", (").Append(listId).Append(", {").Append(i).Append("})");
}
return(ret.ToString());
}
private ParameterizedSql(
ParameterizedSql <TEntity> source,
string action,
string sql,
IReadOnlyDictionary <string, object> parameterValues
)
{
SqlText = sql.Trim();
_parameterValues = parameterValues.ToDictionary(x => x.Key, x => x.Value);
_tableAlias = source._tableAlias;
_tableName = source._tableName;
_fromClause = source._fromClause;
_whereClause = source._whereClause;
IsGrouped = source.IsGrouped;
HasWithExpression = source.HasWithExpression;
_action = action.ToUpper();
DbContext = source.DbContext;
_knowledge = source._knowledge;
_logger = source._logger;
_original = source._original;
_info = source._info;
ReadOnly = source.ReadOnly;
}
/// <inheritdoc />
public string GetPurgeCommand(ISqlKnowledge knowledge)
=> $"DELETE FROM {GetTableName(knowledge)}";
/// <inheritdoc />
public string GetClearCommand(ISqlKnowledge knowledge, int listId)
=> $"DELETE FROM {GetTableName(knowledge)} WHERE ListId={listId}";
/// <inheritdoc /> public string GetTableName(ISqlKnowledge knowledge) => knowledge.CreateTemporaryTableName(BaseTableName);
/// <inheritdoc /> public string GetValueTypeName(ISqlKnowledge knowledge) => string.IsNullOrEmpty(_valueTypeName) ? knowledge.GetValueTypeName(ValueType) : _valueTypeName;
/// <summary>
/// Creates a parameterized SQL string from the supplied query.
/// </summary>
/// <param name="query"></param>
/// <param name="logger"></param>
public ParameterizedSql(IQueryable <TEntity> query, ILogger logger = null)
{
_original = query;
_info = new QueryInfo(query);
DbContext = _info.Context.Context;
_logger = logger
?? ((IInfrastructure <IServiceProvider>)DbContext)
.Instance
.GetService(typeof(ILogger <ParameterizedSql <TEntity> >)) as ILogger <ParameterizedSql <TEntity> >;
if (DbContext.Model.FindEntityType(typeof(TEntity)) is null)
{
_logger?.LogInformation($"The type {typeof(TEntity)} is not part of the data model, creating read-only SQL.");
ReadOnly = true;
}
_knowledge = SqlKnowledge.For(DbContext.Database.ProviderName);
_logger?.LogDebug("Generating SQL...");
var cmd = _info.Command;
SqlText = cmd.CommandText;
var paramNames = cmd.Parameters.Select(x => x.InvariantName).ToArray();
_parameterValues = _info.Context
.ParameterValues
.Where(v => paramNames.Contains(v.Key))
.ToDictionary(
v => "@" + v.Key.TrimStart('@'),
v => v.Value
);
_logger?.LogDebug(SqlText);
_logger?.LogDebug("Params: " + string.Join(", ", _parameterValues.Select(x => x.Key)));
var rip = SelectRipper.Match(SqlText);
if (!rip.Success)
{
throw new ArgumentException(
"The generated SQL does not match the extraction regular expression.\n" + SqlText
);
}
_action = "SELECT";
_fromClause = rip.Groups["FROM"].Value.Trim();
_whereClause = rip.Groups["WHERE"].Value.Trim();
IsGrouped = !string.IsNullOrWhiteSpace(rip.Groups["GROUPBY"].Value);
HasWithExpression = !string.IsNullOrWhiteSpace(rip.Groups["WITH"].Value);
var sel = rip.Groups["SELECTION"].Value.Trim();
var aliasSplit = sel.IndexOf('.');
if (aliasSplit <= 0)
{
_tableAlias = "";
}
else
{
var tbl = sel.Substring(0, aliasSplit);
_tableAlias = _knowledge.UnquoteObjectName(tbl);
}
if (!ReadOnly)
{
if (string.IsNullOrEmpty(_tableAlias))
{
_tableAlias = _info.Expression.Tables.First().Alias;
}
var tex = _info.Expression.Tables.FirstOrDefault(x => x.Alias == _tableAlias);
if (tex is null)
{
ReadOnly = true;
_logger?.LogInformation($"Failed to locate record set named {_tableAlias}.");
}
else if (tex is TableExpression tt)
{
if (!string.IsNullOrEmpty(tt.Schema) &&
tt.Schema != "dbo")
{
_tableName = _knowledge.QuoteObjectName(tt.Schema) + '.' + _knowledge.QuoteObjectName(tt.Name);
}
else
{
_tableName = _knowledge.QuoteObjectName(tt.Name);
}
}
else
{
ReadOnly = true;
_logger?.LogInformation($"Failed to locate a table expression named {_tableAlias}.");
}
}
}
