public static (string, string, string, string, string, IEnumerable <object>) GetBatchSql(IQueryable query, DbContext context, bool isUpdate)
{
var sqlQueryBuilder = SqlAdaptersMapping.GetAdapterDialect(context);
var(fullSqlQuery, innerParameters) = query.ToParametrizedSql();
DbServer databaseType = SqlAdaptersMapping.GetDatabaseType(context);
var(leadingComments, sqlQuery) = SplitLeadingCommentsAndMainSqlQuery(fullSqlQuery);
string tableAlias = string.Empty;
string tableAliasSufixAs = string.Empty;
string topStatement = string.Empty;
(tableAlias, topStatement) = sqlQueryBuilder.GetBatchSqlReformatTableAliasAndTopStatement(sqlQuery);
int indexFROM = sqlQuery.IndexOf(Environment.NewLine);
string sql = sqlQuery.Substring(indexFROM, sqlQuery.Length - indexFROM);
sql = sql.Contains("{") ? sql.Replace("{", "{{") : sql; // Curly brackets have to be escaped:
sql = sql.Contains("}") ? sql.Replace("}", "}}") : sql; // https://github.com/aspnet/EntityFrameworkCore/issues/8820
if (isUpdate)
{
var extracted = sqlQueryBuilder.GetBatchSqlExtractTableAliasFromQuery(
sql, tableAlias, tableAliasSufixAs
);
tableAlias = extracted.TableAlias;
tableAliasSufixAs = extracted.TableAliasSuffixAs;
sql = extracted.Sql;
}
return(sql, tableAlias, tableAliasSufixAs, topStatement, leadingComments, innerParameters);
}
/// <summary>
/// Recursive analytic expression
/// </summary>
/// <param name="tableAlias"></param>
/// <param name="expression"></param>
/// <param name="sqlColumns"></param>
/// <param name="sqlParameters"></param>
/// <summary>
/// Recursive analytic expression
/// </summary>
/// <param name="tableAlias"></param>
/// <param name="expression"></param>
/// <param name="sqlColumns"></param>
/// <param name="sqlParameters"></param>
public static void CreateUpdateBody(BatchUpdateCreateBodyData createBodyData, Expression expression)
{
var rootTypeTableInfo = createBodyData.GetTableInfoForType(createBodyData.RootType);
var columnNameValueDict = rootTypeTableInfo.PropertyColumnNamesDict;
var tableAlias = createBodyData.TableAlias;
var sqlColumns = createBodyData.UpdateColumnsSql;
var sqlParameters = createBodyData.SqlParameters;
if (expression is MemberInitExpression memberInitExpression)
{
foreach (var item in memberInitExpression.Bindings)
{
if (item is MemberAssignment assignment)
{
if (columnNameValueDict.TryGetValue(assignment.Member.Name, out string value))
{
sqlColumns.Append($" [{tableAlias}].[{value}]");
}
else
{
sqlColumns.Append($" [{tableAlias}].[{assignment.Member.Name}]");
}
sqlColumns.Append(" =");
if (!TryCreateUpdateBodyNestedQuery(createBodyData, assignment.Expression, assignment))
{
CreateUpdateBody(createBodyData, assignment.Expression);
}
if (memberInitExpression.Bindings.IndexOf(item) < (memberInitExpression.Bindings.Count - 1))
{
sqlColumns.Append(" ,");
}
}
}
return;
}
if (expression is MemberExpression memberExpression &&
memberExpression.Expression is ParameterExpression parameterExpression &&
parameterExpression.Name == createBodyData.RootInstanceParameterName)
{
if (columnNameValueDict.TryGetValue(memberExpression.Member.Name, out string value))
{
sqlColumns.Append($" [{tableAlias}].[{value}]");
}
else
{
sqlColumns.Append($" [{tableAlias}].[{memberExpression.Member.Name}]");
}
return;
}
if (expression is ConstantExpression constantExpression)
{
var constantParamName = $"param_{sqlParameters.Count}";
// will rely on SqlClientHelper.CorrectParameterType to fix the type before executing
sqlParameters.Add(new Microsoft.Data.SqlClient.SqlParameter(constantParamName, constantExpression.Value ?? DBNull.Value));
sqlColumns.Append($" @{constantParamName}");
return;
}
if (expression is UnaryExpression unaryExpression)
{
switch (unaryExpression.NodeType)
{
case ExpressionType.Convert:
CreateUpdateBody(createBodyData, unaryExpression.Operand);
break;
case ExpressionType.Not:
sqlColumns.Append(" ~"); //this way only for SQL Server
CreateUpdateBody(createBodyData, unaryExpression.Operand);
break;
default: break;
}
return;
}
if (expression is BinaryExpression binaryExpression)
{
switch (binaryExpression.NodeType)
{
case ExpressionType.Add:
CreateUpdateBody(createBodyData, binaryExpression.Left);
var sqlOperator = SqlAdaptersMapping.GetAdapterDialect(createBodyData.DatabaseType)
.GetBinaryExpressionAddOperation(binaryExpression);
sqlColumns.Append(" " + sqlOperator);
CreateUpdateBody(createBodyData, binaryExpression.Right);
break;
case ExpressionType.Divide:
CreateUpdateBody(createBodyData, binaryExpression.Left);
sqlColumns.Append(" /");
CreateUpdateBody(createBodyData, binaryExpression.Right);
break;
case ExpressionType.Multiply:
CreateUpdateBody(createBodyData, binaryExpression.Left);
sqlColumns.Append(" *");
CreateUpdateBody(createBodyData, binaryExpression.Right);
break;
case ExpressionType.Subtract:
CreateUpdateBody(createBodyData, binaryExpression.Left);
sqlColumns.Append(" -");
CreateUpdateBody(createBodyData, binaryExpression.Right);
break;
case ExpressionType.And:
CreateUpdateBody(createBodyData, binaryExpression.Left);
sqlColumns.Append(" &");
CreateUpdateBody(createBodyData, binaryExpression.Right);
break;
case ExpressionType.Or:
CreateUpdateBody(createBodyData, binaryExpression.Left);
sqlColumns.Append(" |");
CreateUpdateBody(createBodyData, binaryExpression.Right);
break;
case ExpressionType.ExclusiveOr:
CreateUpdateBody(createBodyData, binaryExpression.Left);
sqlColumns.Append(" ^");
CreateUpdateBody(createBodyData, binaryExpression.Right);
break;
case ExpressionType.Coalesce:
sqlColumns.Append("COALESCE(");
CreateUpdateBody(createBodyData, binaryExpression.Left);
sqlColumns.Append(", ");
CreateUpdateBody(createBodyData, binaryExpression.Right);
break;
default:
throw new NotSupportedException($"{nameof(BatchUtil)}.{nameof(CreateUpdateBody)}(..) is not supported for a binary exression of type {binaryExpression.NodeType}");
}
return;
}
// For any other case fallback on compiling and executing the expression
var compiledExpressionValue = Expression.Lambda(expression).Compile().DynamicInvoke();
var parmName = $"param_{sqlParameters.Count}";
// will rely on SqlClientHelper.CorrectParameterType to fix the type before executing
sqlParameters.Add(new Microsoft.Data.SqlClient.SqlParameter(parmName, compiledExpressionValue ?? DBNull.Value));
sqlColumns.Append($" @{parmName}");
}
public static List <object> ReloadSqlParameters(DbContext context, List <object> sqlParameters)
{
return(SqlAdaptersMapping.GetAdapterDialect(context).ReloadSqlParameters(context, sqlParameters));
}
/// <summary>
/// Recursive analytic expression
/// </summary>
/// <param name="tableAlias"></param>
/// <param name="expression"></param>
/// <param name="sqlColumns"></param>
/// <param name="sqlParameters"></param>
public static void CreateUpdateBody(BatchUpdateCreateBodyData createBodyData, Expression expression, string columnName = null)
{
var rootTypeTableInfo = createBodyData.GetTableInfoForType(createBodyData.RootType);
var columnNameValueDict = rootTypeTableInfo.PropertyColumnNamesDict;
var tableAlias = createBodyData.TableAlias;
var sqlColumns = createBodyData.UpdateColumnsSql;
var sqlParameters = createBodyData.SqlParameters;
if (expression is MemberInitExpression memberInitExpression)
{
foreach (var item in memberInitExpression.Bindings)
{
if (item is MemberAssignment assignment)
{
string currentColumnName;
if (columnNameValueDict.TryGetValue(assignment.Member.Name, out string value))
{
currentColumnName = value;
}
else
{
currentColumnName = assignment.Member.Name;
}
sqlColumns.Append($" [{tableAlias}].[{currentColumnName}]");
sqlColumns.Append(" =");
if (!TryCreateUpdateBodyNestedQuery(createBodyData, assignment.Expression, assignment))
{
CreateUpdateBody(createBodyData, assignment.Expression, currentColumnName);
}
if (memberInitExpression.Bindings.IndexOf(item) < (memberInitExpression.Bindings.Count - 1))
{
sqlColumns.Append(" ,");
}
}
}
return;
}
if (expression is MemberExpression memberExpression &&
memberExpression.Expression is ParameterExpression parameterExpression &&
parameterExpression.Name == createBodyData.RootInstanceParameterName)
{
if (columnNameValueDict.TryGetValue(memberExpression.Member.Name, out string value))
{
sqlColumns.Append($" [{tableAlias}].[{value}]");
}
else
{
sqlColumns.Append($" [{tableAlias}].[{memberExpression.Member.Name}]");
}
return;
}
if (expression is ConstantExpression constantExpression)
{
// TODO: I believe the EF query builder inserts constant expressions directly into the SQL.
// This should probably match that behavior for the update body
AddSqlParameter(sqlColumns, sqlParameters, rootTypeTableInfo, columnName, constantExpression.Value);
return;
}
if (expression is UnaryExpression unaryExpression)
{
switch (unaryExpression.NodeType)
{
case ExpressionType.Convert:
CreateUpdateBody(createBodyData, unaryExpression.Operand, columnName);
break;
case ExpressionType.Not:
sqlColumns.Append(" ~"); //this way only for SQL Server
CreateUpdateBody(createBodyData, unaryExpression.Operand, columnName);
break;
default: break;
}
return;
}
if (expression is BinaryExpression binaryExpression)
{
switch (binaryExpression.NodeType)
{
case ExpressionType.Add:
CreateUpdateBody(createBodyData, binaryExpression.Left, columnName);
var sqlOperator = SqlAdaptersMapping.GetAdapterDialect(createBodyData.DatabaseType)
.GetBinaryExpressionAddOperation(binaryExpression);
sqlColumns.Append(" " + sqlOperator);
CreateUpdateBody(createBodyData, binaryExpression.Right, columnName);
break;
case ExpressionType.Divide:
CreateUpdateBody(createBodyData, binaryExpression.Left, columnName);
sqlColumns.Append(" /");
CreateUpdateBody(createBodyData, binaryExpression.Right, columnName);
break;
case ExpressionType.Multiply:
CreateUpdateBody(createBodyData, binaryExpression.Left, columnName);
sqlColumns.Append(" *");
CreateUpdateBody(createBodyData, binaryExpression.Right, columnName);
break;
case ExpressionType.Subtract:
CreateUpdateBody(createBodyData, binaryExpression.Left, columnName);
sqlColumns.Append(" -");
CreateUpdateBody(createBodyData, binaryExpression.Right, columnName);
break;
case ExpressionType.And:
CreateUpdateBody(createBodyData, binaryExpression.Left, columnName);
sqlColumns.Append(" &");
CreateUpdateBody(createBodyData, binaryExpression.Right, columnName);
break;
case ExpressionType.Or:
CreateUpdateBody(createBodyData, binaryExpression.Left, columnName);
sqlColumns.Append(" |");
CreateUpdateBody(createBodyData, binaryExpression.Right, columnName);
break;
case ExpressionType.ExclusiveOr:
CreateUpdateBody(createBodyData, binaryExpression.Left, columnName);
sqlColumns.Append(" ^");
CreateUpdateBody(createBodyData, binaryExpression.Right, columnName);
break;
case ExpressionType.Coalesce:
sqlColumns.Append("COALESCE(");
CreateUpdateBody(createBodyData, binaryExpression.Left, columnName);
sqlColumns.Append(", ");
CreateUpdateBody(createBodyData, binaryExpression.Right, columnName);
break;
default:
throw new NotSupportedException($"{nameof(BatchUtil)}.{nameof(CreateUpdateBody)}(..) is not supported for a binary exression of type {binaryExpression.NodeType}");
}
return;
}
// For any other case fallback on compiling and executing the expression
var compiledExpressionValue = Expression.Lambda(expression).Compile().DynamicInvoke();
AddSqlParameter(sqlColumns, sqlParameters, rootTypeTableInfo, columnName, compiledExpressionValue);
}
