public BatchUpdateCreateBodyData( string baseSql, DbContext dbContext, IEnumerable <object> innerParameters, IQueryable query, Type rootType, string tableAlias, LambdaExpression updateExpression) { BaseSql = baseSql; DatabaseType = SqlAdaptersMapping.GetDatabaseType(dbContext); DbContext = dbContext; Query = query; RootInstanceParameterName = updateExpression.Parameters?.First()?.Name; RootType = rootType; TableAlias = tableAlias; TableAliasesInUse = new List <string>(); UpdateColumnsSql = new StringBuilder(); UpdateExpression = updateExpression; _tableInfoBulkConfig = new BulkConfig(); _tableInfoLookup = new Dictionary <Type, TableInfo>(); var tableInfo = TableInfo.CreateInstance(dbContext, rootType, Array.Empty <object>(), OperationType.Read, _tableInfoBulkConfig); _tableInfoLookup.Add(rootType, tableInfo); SqlParameters = new List <object>(innerParameters); foreach (Match match in BatchUtil.TableAliasPattern.Matches(baseSql)) { TableAliasesInUse.Add(match.Groups[2].Value); } }
// In comment are Examples of how SqlQuery is changed for Sql Batch // SELECT [a].[Column1], [a].[Column2], .../r/n // FROM [Table] AS [a]/r/n // WHERE [a].[Column] = FilterValue // -- // DELETE [a] // FROM [Table] AS [a] // WHERE [a].[Columns] = FilterValues public static (string, List <object>) GetSqlDelete(IQueryable query, DbContext context) { var(sql, tableAlias, _, topStatement, leadingComments, innerParameters) = GetBatchSql(query, context, isUpdate: false); innerParameters = ReloadSqlParameters(context, innerParameters.ToList()); // Sqlite requires SqliteParameters var databaseType = SqlAdaptersMapping.GetDatabaseType(context); string resultQuery; if (databaseType == DbServer.SqlServer) { tableAlias = $"[{tableAlias}]"; int outerQueryOrderByIndex = -1; var useUpdateableCte = false; var lastOrderByIndex = sql.LastIndexOf(Environment.NewLine + $"ORDER BY ", StringComparison.OrdinalIgnoreCase); if (lastOrderByIndex > -1) { var subQueryEnd = sql.LastIndexOf($") AS {tableAlias}" + Environment.NewLine, StringComparison.OrdinalIgnoreCase); if (subQueryEnd == -1 || lastOrderByIndex > subQueryEnd) { outerQueryOrderByIndex = lastOrderByIndex; if (topStatement.Length > 0) { useUpdateableCte = true; } else { int offSetIndex = sql.LastIndexOf(Environment.NewLine + "OFFSET ", StringComparison.OrdinalIgnoreCase); if (offSetIndex > outerQueryOrderByIndex) { useUpdateableCte = true; } } } } if (useUpdateableCte) { var cte = "cte" + Guid.NewGuid().ToString().Substring(0, 8); // 8 chars of Guid as tableNameSuffix to avoid same name collision with other tables resultQuery = $"{leadingComments}WITH [{cte}] AS (SELECT {topStatement}* {sql}) DELETE FROM [{cte}]"; } else { if (outerQueryOrderByIndex > -1) { // ORDER BY is not allowed without TOP or OFFSET. sql = sql.Substring(0, outerQueryOrderByIndex); } resultQuery = $"{leadingComments}DELETE {topStatement}{tableAlias}{sql}"; } } else { resultQuery = $"{leadingComments}DELETE {topStatement}{tableAlias}{sql}"; } return(resultQuery, new List <object>(innerParameters)); }
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); }
public static async Task ReadAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken) where T : class { if (tableInfo.BulkConfig.UseTempDB) // dropTempTableIfExists { await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB), cancellationToken).ConfigureAwait(false); } var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context); await adapter.ReadAsync(context, type, entities, tableInfo, progress, cancellationToken); }
public static void Read <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) where T : class { if (tableInfo.BulkConfig.UseTempDB) // dropTempTableIfExists { context.Database.ExecuteSqlRaw(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB)); } var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context); adapter.Read(context, type, entities, tableInfo, progress); }
// In comment are Examples of how SqlQuery is changed for Sql Batch // SELECT [a].[Column1], [a].[Column2], .../r/n // FROM [Table] AS [a]/r/n // WHERE [a].[Column] = FilterValue // -- // DELETE [a] // FROM [Table] AS [a] // WHERE [a].[Columns] = FilterValues public static (string, List <object>) GetSqlDelete(IQueryable query, DbContext context) { (string sql, string tableAlias, string tableAliasSufixAs, string topStatement, string leadingComments, IEnumerable <object> innerParameters) = GetBatchSql(query, context, isUpdate: false); innerParameters = ReloadSqlParameters(context, innerParameters.ToList()); // Sqlite requires SqliteParameters tableAlias = (SqlAdaptersMapping.GetDatabaseType(context) == DbServer.SqlServer) ? $"[{tableAlias}]" : tableAlias; var resultQuery = $"{leadingComments}DELETE {topStatement}{tableAlias}{sql}"; return(resultQuery, new List <object>(innerParameters)); }
/// <summary> /// get Update Sql /// </summary> /// <typeparam name="T"></typeparam> /// <param name="query"></param> /// <param name="expression"></param> /// <returns></returns> public static (string, List <object>) GetSqlUpdate <T>(IQueryable <T> query, DbContext context, Type type, Expression <Func <T, T> > expression) where T : class { (string sql, string tableAlias, string tableAliasSufixAs, string topStatement, string leadingComments, IEnumerable <object> innerParameters) = GetBatchSql(query, context, isUpdate: true); var createUpdateBodyData = new BatchUpdateCreateBodyData(sql, context, innerParameters, query, type, tableAlias, expression); CreateUpdateBody(createUpdateBodyData, expression.Body); var sqlParameters = ReloadSqlParameters(context, createUpdateBodyData.SqlParameters); // Sqlite requires SqliteParameters var sqlColumns = (createUpdateBodyData.DatabaseType == DbServer.SQLServer) ? createUpdateBodyData.UpdateColumnsSql : createUpdateBodyData.UpdateColumnsSql.Replace($"[{tableAlias}].", ""); var resultQuery = $"{leadingComments}UPDATE {topStatement}{tableAlias}{tableAliasSufixAs} SET {sqlColumns} {sql}"; if (resultQuery.Contains("ORDER") && resultQuery.Contains("TOP")) { string tableAliasPrefix = "[" + tableAlias + "]."; resultQuery = $"WITH C AS (SELECT {topStatement}*{sql}) UPDATE C SET {sqlColumns.Replace(tableAliasPrefix, "")}"; } if (resultQuery.Contains("ORDER") && !resultQuery.Contains("TOP")) // When query has ORDER only without TOP(Take) then it is removed since not required and to avoid invalid Sql { resultQuery = resultQuery.Split("ORDER", StringSplitOptions.None)[0]; } var databaseType = SqlAdaptersMapping.GetDatabaseType(context); if (databaseType == DbServer.PostgreSQL) { resultQuery = SqlQueryBuilderPostgreSql.RestructureForBatch(resultQuery); var npgsqlParameters = new List <object>(); foreach (var param in sqlParameters) { var npgsqlParam = new Npgsql.NpgsqlParameter(((SqlParameter)param).ParameterName, ((SqlParameter)param).Value); string paramName = npgsqlParam.ParameterName.Replace("@", ""); var propertyType = type.GetProperties().SingleOrDefault(a => a.Name == paramName)?.PropertyType; if (propertyType == typeof(System.Text.Json.JsonElement) || propertyType == typeof(System.Text.Json.JsonElement?)) { npgsqlParam.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Jsonb; } npgsqlParameters.Add(npgsqlParam); } sqlParameters = npgsqlParameters; } return(resultQuery, sqlParameters); }
// SELECT [a].[Column1], [a].[Column2], .../r/n // FROM [Table] AS [a]/r/n // WHERE [a].[Column] = FilterValue // -- // UPDATE [a] SET [UpdateColumns] = N'updateValues' // FROM [Table] AS [a] // WHERE [a].[Columns] = FilterValues public static (string, List <object>) GetSqlUpdate(IQueryable query, DbContext context, Type type, object updateValues, List <string> updateColumns) { var(sql, tableAlias, tableAliasSufixAs, topStatement, leadingComments, innerParameters) = GetBatchSql(query, context, isUpdate: true); var sqlParameters = new List <object>(innerParameters); string sqlSET = GetSqlSetSegment(context, updateValues.GetType(), updateValues, updateColumns, sqlParameters); sqlParameters = ReloadSqlParameters(context, sqlParameters); // Sqlite requires SqliteParameters var resultQuery = $"{leadingComments}UPDATE {topStatement}{tableAlias}{tableAliasSufixAs} {sqlSET}{sql}"; if (resultQuery.Contains("ORDER") && resultQuery.Contains("TOP")) { resultQuery = $"WITH C AS (SELECT {topStatement}*{sql}) UPDATE C {sqlSET}"; } if (resultQuery.Contains("ORDER") && !resultQuery.Contains("TOP")) // When query has ORDER only without TOP(Take) then it is removed since not required and to avoid invalid Sql { resultQuery = resultQuery.Split("ORDER", StringSplitOptions.None)[0]; } var databaseType = SqlAdaptersMapping.GetDatabaseType(context); if (databaseType == DbServer.PostgreSQL) { resultQuery = SqlQueryBuilderPostgreSql.RestructureForBatch(resultQuery); var npgsqlParameters = new List <object>(); foreach (var param in sqlParameters) { var npgsqlParam = new Npgsql.NpgsqlParameter(((SqlParameter)param).ParameterName, ((SqlParameter)param).Value); string paramName = npgsqlParam.ParameterName.Replace("@", ""); var propertyType = type.GetProperties().SingleOrDefault(a => a.Name == paramName)?.PropertyType; if (propertyType == typeof(System.Text.Json.JsonElement) || propertyType == typeof(System.Text.Json.JsonElement?)) // for JsonDocument works without fix { npgsqlParam.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Jsonb; } npgsqlParameters.Add(npgsqlParam); } sqlParameters = npgsqlParameters; } return(resultQuery, sqlParameters); }
private static void Merge <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class { var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context); adapter.Merge(context, type, entities, tableInfo, (EFCore.BulkExtensions.OperationType)operationType, progress); }
/// <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}"); }
private static async Task ExecuteWithGraphAsync(DbContext context, IEnumerable <object> entities, OperationType operationType, BulkConfig bulkConfig, Action <decimal> progress, CancellationToken cancellationToken, bool isAsync) { if (operationType != OperationType.Insert && operationType != OperationType.InsertOrUpdate && operationType != OperationType.InsertOrUpdateDelete && operationType != OperationType.Update) { throw new InvalidBulkConfigException($"{nameof(BulkConfig)}.{nameof(BulkConfig.IncludeGraph)} only supports Insert or Update operations."); } // Sqlite bulk merge adapter does not support multiple objects of the same type with a zero value primary key if (SqlAdaptersMapping.GetDatabaseType(context) == DbServer.Sqlite) { throw new NotSupportedException("Sqlite is not currently supported due to its BulkInsert implementation."); } bulkConfig.PreserveInsertOrder = true; // Required for SetOutputIdentity ('true' is default but here explicitly assigned again in case it was changed to 'false' in BulkConfing) bulkConfig.SetOutputIdentity = true; // If this is set to false, won't be able to propogate new primary keys to the relationships // If this is set to false, wont' be able to support some code first model types as EFCore uses shadow properties when a relationship's foreign keys arent explicitly defined bulkConfig.EnableShadowProperties = true; var graphNodes = GraphUtil.GetTopologicallySortedGraph(context, entities); if (graphNodes == null) { return; } // Inserting an entity graph must be done within a transaction otherwise the database could end up in a bad state var hasExistingTransaction = context.Database.CurrentTransaction != null; var transaction = context.Database.CurrentTransaction ?? (isAsync ? await context.Database.BeginTransactionAsync() : context.Database.BeginTransaction()); try { // Group the graph nodes by entity type so we can merge them into the database in batches, in the correct order of dependency (topological order) var graphNodesGroupedByType = graphNodes.GroupBy(y => y.Entity.GetType()); foreach (var graphNodeGroup in graphNodesGroupedByType) { // It is possible the object graph contains duplicate entities (by primary key) but the entities are different object instances in memory. // This an happen when deserializing a nested JSON tree for example. So filter out the duplicates. var entitiesToAction = GetUniqueEntities(context, graphNodeGroup.Select(y => y.Entity)).ToList(); var entityClrType = graphNodeGroup.Key; var tableInfo = TableInfo.CreateInstance(context, entityClrType, entitiesToAction, operationType, bulkConfig); if (isAsync) { await SqlBulkOperation.MergeAsync(context, entityClrType, entitiesToAction, tableInfo, operationType, progress, cancellationToken); } else { SqlBulkOperation.Merge(context, entityClrType, entitiesToAction, tableInfo, operationType, progress); } // Set the foreign keys for dependents so they may be inserted on the next loop var dependentsOfSameType = SetForeignKeysForDependentsAndYieldSameTypeDependents(context, entityClrType, graphNodeGroup).ToList(); // If there are any dependents of the same type (parent child relationship), then save those dependent entities again to commit the fk values if (dependentsOfSameType.Any()) { var dependentTableInfo = TableInfo.CreateInstance(context, entityClrType, dependentsOfSameType, operationType, bulkConfig); if (isAsync) { await SqlBulkOperation.MergeAsync(context, entityClrType, dependentsOfSameType, dependentTableInfo, operationType, progress, cancellationToken); } else { SqlBulkOperation.Merge(context, entityClrType, dependentsOfSameType, dependentTableInfo, operationType, progress); } } } if (hasExistingTransaction == false) { if (isAsync) { await transaction.CommitAsync(); } else { transaction.Commit(); } } } finally { if (hasExistingTransaction == false) { if (isAsync) { await transaction.DisposeAsync(); } else { transaction.Dispose(); } } } }
/// <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); }
private static async Task ExecuteWithGraphAsync_Impl(DbContext context, IEnumerable <object> entities, OperationType operationType, BulkConfig bulkConfig, Action <decimal> progress, CancellationToken?cancellationToken, bool isAsync = true) { if (operationType != OperationType.Insert && operationType != OperationType.InsertOrUpdate && operationType != OperationType.InsertOrUpdateDelete && operationType != OperationType.Update) { throw new InvalidBulkConfigException($"{nameof(BulkConfig)}.{nameof(BulkConfig.IncludeGraph)} only supports Insert or Update operations."); } // Sqlite bulk merge adapter does not support multiple objects of the same type with a zero value primary key if (SqlAdaptersMapping.GetDatabaseType(context) == DbServer.Sqlite) { throw new NotSupportedException("Sqlite is not currently supported due to its BulkInsert implementation."); } // If this is set to false, won't be able to propogate new primary keys to the relationships bulkConfig.SetOutputIdentity = true; // If this is set to false, wont' be able to support some code first model types as EFCore uses shadow properties when a relationship's foreign keys arent explicitly defined bulkConfig.EnableShadowProperties = true; var rootGraphItems = GraphUtil.GetOrderedGraph(context, entities); if (rootGraphItems == null) { return; } // Inserting an entity graph must be done within a transaction otherwise the database could end up in a bad state var hasExistingTransaction = context.Database.CurrentTransaction != null; var transaction = context.Database.CurrentTransaction ?? (isAsync ? await context.Database.BeginTransactionAsync() : context.Database.BeginTransaction()); try { foreach (var actionGraphItem in rootGraphItems) { var entitiesToAction = GetUniqueEntities(context, actionGraphItem).ToList(); var tableInfo = TableInfo.CreateInstance(context, actionGraphItem.EntityClrType, entitiesToAction, operationType, bulkConfig); if (isAsync) { await SqlBulkOperation.MergeAsync(context, actionGraphItem.EntityClrType, entitiesToAction, tableInfo, operationType, progress, cancellationToken.Value); } else { SqlBulkOperation.Merge(context, actionGraphItem.EntityClrType, entitiesToAction, tableInfo, operationType, progress); } // Loop through the dependants and update their foreign keys with the PK values of the just inserted / merged entities foreach (var graphEntity in actionGraphItem.Entities) { var entity = graphEntity.Entity; var parentEntity = graphEntity.ParentEntity; // If the parent entity is null its the root type of the object graph. if (parentEntity is null) { foreach (var navigation in actionGraphItem.Relationships) { // If this relationship requires the parents value to exist if (navigation.ParentNavigation.IsDependentToPrincipal() == false) { foreach (var navGraphEntity in navigation.Entities) { if (navGraphEntity.ParentEntity != entity) { continue; } SetForeignKeyForRelationship(context, navigation.ParentNavigation, navGraphEntity.Entity, entity); } } } } else { var navigation = actionGraphItem.ParentNavigation; if (navigation.IsDependentToPrincipal()) { SetForeignKeyForRelationship(context, navigation, dependent: parentEntity, principal: entity); } else { SetForeignKeyForRelationship(context, navigation, dependent: entity, principal: parentEntity); } } } } if (hasExistingTransaction == false) { if (isAsync) { await transaction.CommitAsync(); } else { transaction.Commit(); } } } finally { if (hasExistingTransaction == false) { if (isAsync) { await transaction.DisposeAsync(); } else { transaction.Dispose(); } } } }
public static void Truncate(DbContext context, TableInfo tableInfo) { var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context); adapter.Truncate(context, tableInfo); }
public static List <object> ReloadSqlParameters(DbContext context, List <object> sqlParameters) { return(SqlAdaptersMapping.GetAdapterDialect(context).ReloadSqlParameters(context, sqlParameters)); }
private static async Task InsertAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken) { var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context); await adapter.InsertAsync(context, type, entities, tableInfo, progress, cancellationToken); }
private static void Insert <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) { var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context); adapter.Insert(context, type, entities, tableInfo, progress); }
public static async Task TruncateAsync(DbContext context, TableInfo tableInfo, CancellationToken cancellationToken) { var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context); await adapter.TruncateAsync(context, tableInfo, cancellationToken); }
private static async Task MergeAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress, CancellationToken cancellationToken) where T : class { var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context); await adapter.MergeAsync(context, type, entities, tableInfo, (EFCore.BulkExtensions.OperationType) operationType, progress, cancellationToken); }
/// <summary> /// Common logic for two versions of GetDataTable /// </summary> /// <typeparam name="T"></typeparam> /// <param name="context"></param> /// <param name="type"></param> /// <param name="entities"></param> /// <param name="tableInfo"></param> /// <returns></returns> private static DataTable InnerGetDataTable <T>(DbContext context, ref Type type, IList <T> entities, TableInfo tableInfo) { var dataTable = new DataTable(); var columnsDict = new Dictionary <string, object>(); var ownedEntitiesMappedProperties = new HashSet <string>(); var databaseType = SqlAdaptersMapping.GetDatabaseType(context); var isSqlServer = databaseType == DbServer.SQLServer; var sqlServerBytesWriter = new SqlServerBytesWriter(); var objectIdentifier = tableInfo.ObjectIdentifier; type = tableInfo.HasAbstractList ? entities[0].GetType() : type; var entityType = context.Model.FindEntityType(type); var entityTypeProperties = entityType.GetProperties(); var entityPropertiesDict = entityTypeProperties.Where(a => tableInfo.PropertyColumnNamesDict.ContainsKey(a.Name) || (tableInfo.BulkConfig.OperationType != OperationType.Read && a.Name == tableInfo.TimeStampPropertyName)) .ToDictionary(a => a.Name, a => a); var entityNavigationOwnedDict = entityType.GetNavigations().Where(a => a.TargetEntityType.IsOwned()).ToDictionary(a => a.Name, a => a); var entityShadowFkPropertiesDict = entityTypeProperties.Where(a => a.IsShadowProperty() && a.IsForeignKey() && a.GetContainingForeignKeys().FirstOrDefault()?.DependentToPrincipal?.Name != null) .ToDictionary(x => x.GetContainingForeignKeys().First().DependentToPrincipal.Name, a => a); var entityShadowFkPropertyColumnNamesDict = entityShadowFkPropertiesDict.ToDictionary(a => a.Key, a => a.Value.GetColumnName(objectIdentifier)); var shadowPropertyColumnNamesDict = entityPropertiesDict.Where(a => a.Value.IsShadowProperty()).ToDictionary(a => a.Key, a => a.Value.GetColumnName(objectIdentifier)); var properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic); var discriminatorColumn = GetDiscriminatorColumn(tableInfo); foreach (var property in properties) { var hasDefaultVauleOnInsert = tableInfo.BulkConfig.OperationType == OperationType.Insert && !tableInfo.BulkConfig.SetOutputIdentity && tableInfo.DefaultValueProperties.Contains(property.Name); if (entityPropertiesDict.ContainsKey(property.Name)) { var propertyEntityType = entityPropertiesDict[property.Name]; string columnName = propertyEntityType.GetColumnName(objectIdentifier); var isConvertible = tableInfo.ConvertibleColumnConverterDict.ContainsKey(columnName); var propertyType = isConvertible ? tableInfo.ConvertibleColumnConverterDict[columnName].ProviderClrType : property.PropertyType; var underlyingType = Nullable.GetUnderlyingType(propertyType); if (underlyingType != null) { propertyType = underlyingType; } if (isSqlServer && (propertyType == typeof(Geometry) || propertyType.IsSubclassOf(typeof(Geometry)))) { propertyType = typeof(byte[]); tableInfo.HasSpatialType = true; if (tableInfo.BulkConfig.PropertiesToIncludeOnCompare != null || tableInfo.BulkConfig.PropertiesToIncludeOnCompare != null) { throw new InvalidOperationException("OnCompare properties Config can not be set for Entity with Spatial types like 'Geometry'"); } } if (isSqlServer && (propertyType == typeof(HierarchyId) || propertyType.IsSubclassOf(typeof(HierarchyId)))) { propertyType = typeof(byte[]); } if (!columnsDict.ContainsKey(property.Name) && !hasDefaultVauleOnInsert) { dataTable.Columns.Add(columnName, propertyType); columnsDict.Add(property.Name, null); } } else if (entityShadowFkPropertiesDict.ContainsKey(property.Name)) { var fk = entityShadowFkPropertiesDict[property.Name]; entityPropertiesDict.TryGetValue(fk.GetColumnName(objectIdentifier), out var entityProperty); if (entityProperty == null) // BulkRead { continue; } var columnName = entityProperty.GetColumnName(objectIdentifier); var propertyType = entityProperty.ClrType; var underlyingType = Nullable.GetUnderlyingType(propertyType); if (underlyingType != null) { propertyType = underlyingType; } if (propertyType == typeof(Geometry) && isSqlServer) { propertyType = typeof(byte[]); } if (propertyType == typeof(HierarchyId) && isSqlServer) { propertyType = typeof(byte[]); } if (!columnsDict.ContainsKey(columnName) && !hasDefaultVauleOnInsert) { dataTable.Columns.Add(columnName, propertyType); columnsDict.Add(columnName, null); } } else if (entityNavigationOwnedDict.ContainsKey(property.Name)) // isOWned { Type navOwnedType = type.Assembly.GetType(property.PropertyType.FullName); var ownedEntityType = context.Model.FindEntityType(property.PropertyType); if (ownedEntityType == null) { ownedEntityType = context.Model.GetEntityTypes().SingleOrDefault(x => x.ClrType == property.PropertyType && x.Name.StartsWith(entityType.Name + "." + property.Name + "#")); } var ownedEntityProperties = ownedEntityType.GetProperties().ToList(); var ownedEntityPropertyNameColumnNameDict = new Dictionary <string, string>(); foreach (var ownedEntityProperty in ownedEntityProperties) { if (!ownedEntityProperty.IsPrimaryKey()) { string columnName = ownedEntityProperty.GetColumnName(objectIdentifier); if (tableInfo.PropertyColumnNamesDict.ContainsValue(columnName)) { ownedEntityPropertyNameColumnNameDict.Add(ownedEntityProperty.Name, columnName); ownedEntitiesMappedProperties.Add(property.Name + "_" + ownedEntityProperty.Name); } } } var innerProperties = property.PropertyType.GetProperties(); if (!tableInfo.LoadOnlyPKColumn) { foreach (var innerProperty in innerProperties) { if (ownedEntityPropertyNameColumnNameDict.ContainsKey(innerProperty.Name)) { var columnName = ownedEntityPropertyNameColumnNameDict[innerProperty.Name]; var propertyName = $"{property.Name}_{innerProperty.Name}"; if (tableInfo.ConvertibleColumnConverterDict.ContainsKey(propertyName)) { var convertor = tableInfo.ConvertibleColumnConverterDict[propertyName]; var underlyingType = Nullable.GetUnderlyingType(convertor.ProviderClrType) ?? convertor.ProviderClrType; dataTable.Columns.Add(columnName, underlyingType); } else { var ownedPropertyType = Nullable.GetUnderlyingType(innerProperty.PropertyType) ?? innerProperty.PropertyType; dataTable.Columns.Add(columnName, ownedPropertyType); } columnsDict.Add(property.Name + "_" + innerProperty.Name, null); } } } } } if (tableInfo.BulkConfig.EnableShadowProperties) { foreach (var shadowProperty in entityPropertiesDict.Values.Where(a => a.IsShadowProperty())) { var columnName = shadowProperty.GetColumnName(objectIdentifier); // If a model has an entity which has a relationship without an explicity defined FK, the data table will already contain the foreign key shadow property if (dataTable.Columns.Contains(columnName)) { continue; } var isConvertible = tableInfo.ConvertibleColumnConverterDict.ContainsKey(columnName); var propertyType = isConvertible ? tableInfo.ConvertibleColumnConverterDict[columnName].ProviderClrType : shadowProperty.ClrType; var underlyingType = Nullable.GetUnderlyingType(propertyType); if (underlyingType != null) { propertyType = underlyingType; } if (isSqlServer && (propertyType == typeof(Geometry) || propertyType.IsSubclassOf(typeof(Geometry)))) { propertyType = typeof(byte[]); } if (isSqlServer && (propertyType == typeof(HierarchyId) || propertyType.IsSubclassOf(typeof(HierarchyId)))) { propertyType = typeof(byte[]); } dataTable.Columns.Add(columnName, propertyType); columnsDict.Add(shadowProperty.Name, null); } } if (discriminatorColumn != null) { var discriminatorProperty = entityPropertiesDict[discriminatorColumn]; dataTable.Columns.Add(discriminatorColumn, discriminatorProperty.ClrType); columnsDict.Add(discriminatorColumn, entityType.GetDiscriminatorValue()); } bool hasConverterProperties = tableInfo.ConvertiblePropertyColumnDict.Count > 0; foreach (var entity in entities) { var propertiesToLoad = properties.Where(a => !tableInfo.AllNavigationsDictionary.ContainsKey(a.Name) || entityShadowFkPropertiesDict.ContainsKey(a.Name) || tableInfo.OwnedTypesDict.ContainsKey(a.Name)); // omit virtual Navigation (except Owned and ShadowNavig.) since it's Getter can cause unwanted Select-s from Db foreach (var property in propertiesToLoad) { var propertyValue = tableInfo.FastPropertyDict.ContainsKey(property.Name) ? tableInfo.FastPropertyDict[property.Name].Get(entity) : null; var hasDefaultVauleOnInsert = tableInfo.BulkConfig.OperationType == OperationType.Insert && !tableInfo.BulkConfig.SetOutputIdentity && tableInfo.DefaultValueProperties.Contains(property.Name); if (tableInfo.BulkConfig.DateTime2PrecisionForceRound && isSqlServer && tableInfo.DateTime2PropertiesPrecisionLessThen7Dict.ContainsKey(property.Name)) { DateTime dateTimePropertyValue = (DateTime)propertyValue; int precision = tableInfo.DateTime2PropertiesPrecisionLessThen7Dict[property.Name]; int digitsToRemove = 7 - precision; int powerOf10 = (int)Math.Pow(10, digitsToRemove); long subsecondTicks = dateTimePropertyValue.Ticks % 10000000; long ticksToRound = subsecondTicks + (subsecondTicks % 10 == 0 ? 1 : 0); // if ends with 0 add 1 tick to make sure rounding of value .5_zeros is rounded to Upper like SqlServer is doing, not to Even as Math.Round works int roundedTicks = Convert.ToInt32(Math.Round((decimal)ticksToRound / powerOf10, 0)) * powerOf10; dateTimePropertyValue = dateTimePropertyValue.AddTicks(-subsecondTicks).AddTicks(roundedTicks); propertyValue = dateTimePropertyValue; } if (hasConverterProperties && tableInfo.ConvertiblePropertyColumnDict.ContainsKey(property.Name)) { string columnName = tableInfo.ConvertiblePropertyColumnDict[property.Name]; propertyValue = tableInfo.ConvertibleColumnConverterDict[columnName].ConvertToProvider.Invoke(propertyValue); } if (tableInfo.HasSpatialType && propertyValue is Geometry geometryValue) { geometryValue.SRID = tableInfo.BulkConfig.SRID; if (tableInfo.PropertyColumnNamesDict.ContainsKey(property.Name)) { sqlServerBytesWriter.IsGeography = tableInfo.ColumnNamesTypesDict[tableInfo.PropertyColumnNamesDict[property.Name]] == "geography"; // "geography" type is default, otherwise it's "geometry" type } propertyValue = sqlServerBytesWriter.Write(geometryValue); } if (propertyValue is HierarchyId hierarchyValue && isSqlServer) { using MemoryStream memStream = new MemoryStream(); using BinaryWriter binWriter = new BinaryWriter(memStream); hierarchyValue.Write(binWriter); propertyValue = memStream.ToArray(); } if (entityPropertiesDict.ContainsKey(property.Name) && !hasDefaultVauleOnInsert) { columnsDict[property.Name] = propertyValue; } else if (entityShadowFkPropertiesDict.ContainsKey(property.Name)) { var foreignKeyShadowProperty = entityShadowFkPropertiesDict[property.Name]; var columnName = entityShadowFkPropertyColumnNamesDict[property.Name]; entityPropertiesDict.TryGetValue(columnName, out var entityProperty); if (entityProperty == null) // BulkRead { continue; } columnsDict[columnName] = propertyValue == null ? null : foreignKeyShadowProperty.FindFirstPrincipal().PropertyInfo.GetValue(propertyValue); // TODO Check if can be optimized } else if (entityNavigationOwnedDict.ContainsKey(property.Name) && !tableInfo.LoadOnlyPKColumn) { var ownedProperties = property.PropertyType.GetProperties().Where(a => ownedEntitiesMappedProperties.Contains(property.Name + "_" + a.Name)); foreach (var ownedProperty in ownedProperties) { var columnName = $"{property.Name}_{ownedProperty.Name}"; var ownedPropertyValue = propertyValue == null ? null : tableInfo.FastPropertyDict[columnName].Get(propertyValue); if (tableInfo.ConvertibleColumnConverterDict.ContainsKey(columnName)) { var converter = tableInfo.ConvertibleColumnConverterDict[columnName]; columnsDict[columnName] = ownedPropertyValue == null ? null : converter.ConvertToProvider.Invoke(ownedPropertyValue); } else { columnsDict[columnName] = ownedPropertyValue; } } } } if (tableInfo.BulkConfig.EnableShadowProperties) { foreach (var shadowPropertyName in shadowPropertyColumnNamesDict.Keys) { var shadowProperty = entityPropertiesDict[shadowPropertyName]; var columnName = shadowPropertyColumnNamesDict[shadowPropertyName]; var propertyValue = default(object); if (tableInfo.BulkConfig.ShadowPropertyValue == null) { propertyValue = context.Entry(entity).Property(shadowPropertyName).CurrentValue; } else { propertyValue = tableInfo.BulkConfig.ShadowPropertyValue(entity, shadowPropertyName); } if (tableInfo.ConvertibleColumnConverterDict.ContainsKey(columnName)) { propertyValue = tableInfo.ConvertibleColumnConverterDict[columnName].ConvertToProvider.Invoke(propertyValue); } columnsDict[shadowPropertyName] = propertyValue; } } var record = columnsDict.Values.ToArray(); dataTable.Rows.Add(record); } return(dataTable); }