internal static int Merge <TEntity>( DbContext context, IEnumerable <TEntity> entities, BulkTableInfo <TEntity> tableInfo) where TEntity : class { bool isTempTableCreated = false; bool isTempOutputTableCreated = false; bool keepIdentity = tableInfo.KeepIdentity; try { // Create temporary table to store values to insert context.Database.ExecuteSqlCommand( SqlQueryBuilder.CreateTableCopy( tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo.TempTableIncludedColumns)); isTempTableCreated = true; if (tableInfo.Config.SetOutputIdentity || tableInfo.Config.IsBulkResultEnabled) { // Create temporary output table to store result values context.Database.ExecuteSqlCommand( SqlQueryBuilder.CreateOutputTableCopy( tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo.TempOutputTableIncludedColumns)); isTempOutputTableCreated = true; } // Insert data into temp table Insert(context, tableInfo.FullTempTableName, entities, tableInfo.TempTableIncludedColumns, tableInfo.Config); if (keepIdentity && tableInfo.HasIdentity) { context.Database.ExecuteSqlCommand(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, true)); } context.Database.ExecuteSqlCommand(SqlQueryBuilder.MergeTable(tableInfo)); LoadOutputData(context, entities, tableInfo); } finally { if (isTempOutputTableCreated) { context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName)); } if (isTempTableCreated) { context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName)); } if (keepIdentity && tableInfo.HasIdentity) { context.Database.ExecuteSqlCommand(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, false)); } } return(0); }
protected override void ExecuteCommand <TEntity>( DbContext context, IEnumerable <TEntity> entities, BulkTableInfo <TEntity> tableInfo) { if (tableInfo.Config.SetOutputIdentity || tableInfo.Config.IsBulkResultEnabled) { SqlBulkOperation.Merge(context, entities, tableInfo); } else { SqlBulkOperation.Insert(context, entities, tableInfo); } }
public void Execute <TEntity>( DbContext context, IEnumerable <TEntity> entities, BulkMergeOperationType operationType, BulkConfig <TEntity> config) where TEntity : class { BulkTableInfo <TEntity> tableInfo = new BulkTableInfo <TEntity>(context, entities, config, operationType); // Creates inner transaction for the scope of the operation if the context doesn't have one. var transaction = context.InternalTransaction(); try { ExecuteCommand(context, entities, tableInfo); //Commit if internal transaction exists. transaction?.Commit(); } finally { transaction?.Dispose(); } }
private static void LoadOutputData <TEntity>(DbContext context, IEnumerable <TEntity> entities, BulkTableInfo <TEntity> tableInfo) where TEntity : class { if (tableInfo.Config.SetOutputIdentity || tableInfo.Config.IsBulkResultEnabled) { var mergeOutputResult = context.Database.SqlQuery( SqlQueryBuilder.SelectFromTable(tableInfo.FullTempOutputTableName, OrderByColumns: tableInfo.TempOutputTableIncludedColumns)); if (tableInfo.Config.SetOutputIdentity && tableInfo.HasIdentity) { var accessor = TypeAccessor.Create(typeof(TEntity)); string identityPropertyName = tableInfo.IdentityColumn.PropertyName; string identityColumnName = tableInfo.IdentityColumn.PropertyName; for (int i = 0; i < entities.Count(); i++) { var resultRow = mergeOutputResult.ElementAt(i); if ((string)resultRow[SqlQueryBuilder.OutputActionColumnAlias] == SqlQueryBuilder.OutputInsertActionValue) { var entity = entities.ElementAt(i); accessor[entity, identityPropertyName] = resultRow[identityColumnName]; } } } if (tableInfo.Config.IsBulkResultEnabled) { var mergeStats = SqlQueryBuilder.ExtractMergeStats(mergeOutputResult); if (tableInfo.OperationType == BulkMergeOperationType.Insert) { mergeStats.Updated = 0; } tableInfo.Config.BulkResult = mergeStats; } } }
internal static void Insert <TEntity>(DbContext context, IEnumerable <TEntity> entities, BulkTableInfo <TEntity> tableInfo) where TEntity : class { Insert(context, tableInfo.FullTableName, entities, tableInfo.OperationIncludedColumns, tableInfo.Config); }
protected override void ExecuteCommand <TEntity>( DbContext context, IEnumerable <TEntity> entities, BulkTableInfo <TEntity> tableInfo) { SqlBulkOperation.Merge(context, entities, tableInfo); }
internal static string MergeTable <TEntity>(BulkTableInfo <TEntity> tableInfo) where TEntity : class { var operationType = tableInfo.OperationType; var sourceTable = tableInfo.Config.SetOutputIdentity ? $"(SELECT TOP {tableInfo.EntityCount} * FROM {tableInfo.FullTempTableName} ORDER BY {JoinColumns(tableInfo.EntityMap.Pks)})" : tableInfo.FullTempTableName; var query = new StringBuilder(); query.AppendFormat("{0}DECLARE @dummy bit;{0}", Environment.NewLine); // dummy variable for fake update query.AppendFormat("{0}MERGE {1} {2} AS T", Environment.NewLine, tableInfo.FullTableName, tableInfo.Config.MergeWithHoldLock ? "WITH (HOLDLOCK)" : string.Empty); query.AppendFormat("{0}USING {1} AS S", Environment.NewLine, sourceTable); query.AppendFormat("{0}ON {1}", Environment.NewLine, JoinColumns(tableInfo.IdentifierColumns, " AND ", "T", "S")); if (operationType.HasFlag(BulkMergeOperationType.Insert)) { query.AppendFormat("{0}WHEN NOT MATCHED BY TARGET THEN INSERT ({1})", Environment.NewLine, JoinColumns(tableInfo.OperationIncludedColumns)); query.AppendFormat("{0}VALUES ({1})", Environment.NewLine, JoinColumns(tableInfo.OperationIncludedColumns, leftTableAlias: "S")); if (operationType == BulkMergeOperationType.Insert) { query.AppendFormat( "{0}WHEN MATCHED THEN UPDATE SET @dummy = @dummy", Environment.NewLine); } } if (operationType.HasFlag(BulkMergeOperationType.Update)) { var updateColumns = new List <string> { "@dummy = @dummy", JoinColumns(tableInfo.OperationIncludedColumns.Where(p => !p.IsIdentity), leftTableAlias: "T", rightTableAlias: "S") }.Where(c => !string.IsNullOrEmpty(c)); query.AppendFormat( "{0}WHEN MATCHED THEN UPDATE SET {1}", Environment.NewLine, string.Join(", ", updateColumns)); } if (operationType.HasFlag(BulkMergeOperationType.Update | BulkMergeOperationType.Delete)) { query.AppendFormat("{0}WHEN NOT MATCHED BY SOURCE THEN DELETE", Environment.NewLine); } else if (operationType.HasFlag(BulkMergeOperationType.Delete)) { query.AppendFormat("{0}WHEN MATCHED THEN DELETE", Environment.NewLine); } if (tableInfo.Config.SetOutputIdentity || tableInfo.Config.IsBulkResultEnabled) { var outputColumFormat = "CASE WHEN $action = 'INSERT' OR $action = 'UPDATE' THEN INSERTED.[{0}] WHEN $action = 'DELETE' THEN DELETED.[{0}] END AS [{0}]"; var outputColumns = new List <string> { OutputActionColumnName } .Concat(tableInfo.TempOutputTableIncludedColumns.Select(p => string.Format(outputColumFormat, p.ColumnName))) .Where(c => !string.IsNullOrEmpty(c)); query.AppendFormat("{0}OUTPUT {1}", Environment.NewLine, string.Join(", ", outputColumns)); query.AppendFormat("{0}INTO {1}", Environment.NewLine, tableInfo.FullTempOutputTableName); } query.Append(";"); return(query.ToString()); }
protected abstract void ExecuteCommand <TEntity>( DbContext context, IEnumerable <TEntity> entities, BulkTableInfo <TEntity> tableInfo) where TEntity : class;