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); }