public static int BulkUpdate <T>(this DbContext context, IEnumerable <T> entities, BulkUpdateOptions <T> options) { int rowsUpdated = 0; var outputRows = new List <BulkMergeOutputRow <T> >(); var tableMapping = context.GetTableMapping(typeof(T)); using (var dbTransactionContext = new DbTransactionContext(context)) { var dbConnection = dbTransactionContext.Connection; var transaction = dbTransactionContext.CurrentTransaction; try { string stagingTableName = CommonUtil.GetStagingTableName(tableMapping, options.UsePermanentTable, dbConnection); string destinationTableName = string.Format("[{0}].[{1}]", tableMapping.Schema, tableMapping.TableName); string[] columnNames = tableMapping.GetNonValueGeneratedColumns().ToArray(); string[] storeGeneratedColumnNames = tableMapping.GetPrimaryKeyColumns().ToArray(); if (storeGeneratedColumnNames.Length == 0 && options.UpdateOnCondition == null) { throw new InvalidDataException("BulkUpdate requires that the entity have a primary key or the Options.UpdateOnCondition must be set."); } context.Database.CloneTable(destinationTableName, stagingTableName, null); BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, null, SqlBulkCopyOptions.KeepIdentity); IEnumerable <string> columnstoUpdate = CommonUtil.FormatColumns(columnNames.Where(o => !options.IgnoreColumns.GetObjectProperties().Contains(o))); string updateSetExpression = string.Join(",", columnstoUpdate.Select(o => string.Format("t.{0}=s.{0}", o))); string updateSql = string.Format("UPDATE t SET {0} FROM {1} AS s JOIN {2} AS t ON {3}; SELECT @@RowCount;", updateSetExpression, stagingTableName, destinationTableName, CommonUtil <T> .GetJoinConditionSql(options.UpdateOnCondition, storeGeneratedColumnNames, "s", "t")); rowsUpdated = context.Database.ExecuteSql(updateSql, options.CommandTimeout); context.Database.DropTable(stagingTableName); //ClearEntityStateToUnchanged(context, entities); dbTransactionContext.Commit(); } catch (Exception) { dbTransactionContext.Rollback(); throw; } return(rowsUpdated); } }
public static int BulkUpdate <T>(this DbContext context, IEnumerable <T> entities, BulkUpdateOptions <T> options) { int rowsUpdated = 0; var outputRows = new List <BulkMergeOutputRow <T> >(); var tableMapping = context.GetTableMapping(typeof(T)); var dbConnection = context.GetSqlConnection(); if (dbConnection.State == ConnectionState.Closed) { dbConnection.Open(); } using (var transaction = dbConnection.BeginTransaction()) { try { string stagingTableName = GetStagingTableName(tableMapping, options.UsePermanentTable, dbConnection); string destinationTableName = string.Format("[{0}].[{1}]", tableMapping.Schema, tableMapping.TableName); string[] columnNames = tableMapping.GetNonValueGeneratedColumns().ToArray(); string[] storeGeneratedColumnNames = tableMapping.GetPrimaryKeyColumns().ToArray(); SqlUtil.CloneTable(destinationTableName, stagingTableName, null, dbConnection, transaction); BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, null, SqlBulkCopyOptions.KeepIdentity); IEnumerable <string> columnstoUpdate = columnNames.Where(o => !options.IgnoreColumnsOnUpdate.GetObjectProperties().Contains(o)); string updateSetExpression = string.Join(",", columnstoUpdate.Select(o => string.Format("t.{0}=s.{0}", o))); string updateOnExpression = string.Join(" AND ", storeGeneratedColumnNames.Select(o => string.Format("s.{0}=t.{0}", o))); string updateSql = string.Format("UPDATE t SET {0} FROM {1} AS s JOIN {2} AS t ON {3}; SELECT @@RowCount;", updateSetExpression, stagingTableName, destinationTableName, updateOnExpression); rowsUpdated = SqlUtil.ExecuteSql(updateSql, dbConnection, transaction); SqlUtil.DeleteTable(stagingTableName, dbConnection, transaction); //ClearEntityStateToUnchanged(context, entities); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } finally { dbConnection.Close(); } return(rowsUpdated); } }