private static BulkMergeResult <T> InternalBulkMerge <T>(this DbContext context, IEnumerable <T> entities, BulkMergeOptions <T> options) { int rowsAffected = 0; var outputRows = new List <BulkMergeOutputRow <T> >(); var tableMapping = context.GetTableMapping(typeof(T)); int rowsInserted = 0; int rowsUpdated = 0; int rowsDeleted = 0; 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.MergeOnCondition == null) { throw new InvalidDataException("BulkMerge requires that the entity have a primary key or the Options.MergeOnCondition must be set."); } context.Database.CloneTable(destinationTableName, stagingTableName, null, Common.Constants.InternalId_ColumnName); var bulkInsertResult = BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, null, SqlBulkCopyOptions.KeepIdentity, true); IEnumerable <string> columnsToInsert = CommonUtil.FormatColumns(columnNames.Where(o => !options.GetIgnoreColumnsOnInsert().Contains(o))); IEnumerable <string> columnstoUpdate = CommonUtil.FormatColumns(columnNames.Where(o => !options.GetIgnoreColumnsOnUpdate().Contains(o))).Select(o => string.Format("t.{0}=s.{0}", o)); List <string> columnsToOutput = new List <string> { "$Action", string.Format("{0}.{1}", "s", Constants.InternalId_ColumnName) }; List <PropertyInfo> propertySetters = new List <PropertyInfo>(); Type entityType = typeof(T); foreach (var storeGeneratedColumnName in storeGeneratedColumnNames) { columnsToOutput.Add(string.Format("inserted.[{0}]", storeGeneratedColumnName)); columnsToOutput.Add(string.Format("deleted.[{0}]", storeGeneratedColumnName)); propertySetters.Add(entityType.GetProperty(storeGeneratedColumnName)); } string mergeSqlText = string.Format("MERGE {0} t USING {1} s ON ({2}) WHEN NOT MATCHED BY TARGET THEN INSERT ({3}) VALUES ({3}) WHEN MATCHED THEN UPDATE SET {4}{5}OUTPUT {6};", destinationTableName, stagingTableName, CommonUtil <T> .GetJoinConditionSql(options.MergeOnCondition, storeGeneratedColumnNames, "s", "t"), SqlUtil.ConvertToColumnString(columnsToInsert), SqlUtil.ConvertToColumnString(columnstoUpdate), options.DeleteIfNotMatched ? " WHEN NOT MATCHED BY SOURCE THEN DELETE " : " ", SqlUtil.ConvertToColumnString(columnsToOutput) ); var bulkQueryResult = context.BulkQuery(mergeSqlText, dbConnection, transaction, options); rowsAffected = bulkQueryResult.RowsAffected; foreach (var result in bulkQueryResult.Results) { string id = string.Empty; object entity = null; string action = (string)result[0]; if (action != SqlMergeAction.Delete) { int entityId = (int)result[1]; id = (storeGeneratedColumnNames.Length > 0 ? Convert.ToString(result[2]) : "PrimaryKeyMissing"); entity = bulkInsertResult.EntityMap[entityId]; if (options.AutoMapOutputIdentity && entity != null) { for (int i = 2; i < 2 + storeGeneratedColumnNames.Length; i++) { propertySetters[0].SetValue(entity, result[i]); } } } else { id = Convert.ToString(result[2 + storeGeneratedColumnNames.Length]); } outputRows.Add(new BulkMergeOutputRow <T>(action, id)); if (action == SqlMergeAction.Insert) { rowsInserted++; } else if (action == SqlMergeAction.Update) { rowsUpdated++; } else if (action == SqlMergeAction.Delete) { rowsDeleted++; } } context.Database.DropTable(stagingTableName); //ClearEntityStateToUnchanged(context, entities); dbTransactionContext.Commit(); } catch (Exception) { dbTransactionContext.Rollback(); throw; } return(new BulkMergeResult <T> { Output = outputRows, RowsAffected = rowsAffected, RowsDeleted = rowsDeleted, RowsInserted = rowsInserted, RowsUpdated = rowsUpdated, }); } }
public static BulkMergeResult <T> BulkMerge <T>(this DbContext context, IEnumerable <T> entities, BulkMergeOptions <T> options) { int rowsAffected = 0; var outputRows = new List <BulkMergeOutputRow <T> >(); var tableMapping = context.GetTableMapping(typeof(T)); var dbConnection = context.GetSqlConnection(); int rowsInserted = 0; int rowsUpdated = 0; int rowsDeleted = 0; 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, Common.Constants.Guid_ColumnName); var bulkInsertResult = BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, null, SqlBulkCopyOptions.KeepIdentity, true); IEnumerable <string> columnsToInsert = columnNames.Where(o => !options.GetIgnoreColumnsOnInsert().Contains(o)); IEnumerable <string> columnstoUpdate = columnNames.Where(o => !options.GetIgnoreColumnsOnUpdate().Contains(o)).Select(o => string.Format("t.{0}=s.{0}", o)); List <string> columnsToOutput = new List <string> { "$Action", string.Format("{0}.{1}", "s", Constants.Guid_ColumnName) }; List <PropertyInfo> propertySetters = new List <PropertyInfo>(); Type entityType = typeof(T); foreach (var storeGeneratedColumnName in storeGeneratedColumnNames) { //columnsToOutput.Add(string.Format("deleted.[{0}]", storeGeneratedColumnName)); Not Yet Supported columnsToOutput.Add(string.Format("inserted.[{0}]", storeGeneratedColumnName)); propertySetters.Add(entityType.GetProperty(storeGeneratedColumnName)); } string mergeSqlText = string.Format("MERGE {0} t USING {1} s ON ({2}) WHEN NOT MATCHED BY TARGET THEN INSERT ({3}) VALUES ({3}) WHEN MATCHED THEN UPDATE SET {4} OUTPUT {5};", destinationTableName, stagingTableName, options.MergeOnCondition.ToSqlPredicate("s", "t"), SqlUtil.ConvertToColumnString(columnsToInsert), SqlUtil.ConvertToColumnString(columnstoUpdate), SqlUtil.ConvertToColumnString(columnsToOutput) ); var bulkQueryResult = context.BulkQuery(mergeSqlText, dbConnection, transaction); rowsAffected = bulkQueryResult.RowsAffected; //var entitiesEnumerator = entities.GetEnumerator(); //entitiesEnumerator.MoveNext(); foreach (var result in bulkQueryResult.Results) { string action = (string)result[0]; int id = (int)result[1]; var entity = bulkInsertResult.EntityMap[id]; outputRows.Add(new BulkMergeOutputRow <T>(action, entity)); if (options.AutoMapOutputIdentity && entity != null) { for (int i = 2; i < result.Length; i++) { propertySetters[0].SetValue(entity, result[i]); } } if (action == SqlMergeAction.Insert) { rowsInserted++; } else if (action == SqlMergeAction.Update) { rowsUpdated++; } else if (action == SqlMergeAction.Detete) { rowsDeleted++; } } SqlUtil.DeleteTable(stagingTableName, dbConnection, transaction); //ClearEntityStateToUnchanged(context, entities); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw ex; } finally { dbConnection.Close(); } return(new BulkMergeResult <T> { Output = outputRows, RowsAffected = rowsAffected, RowsDeleted = rowsDeleted, RowsInserted = rowsInserted, RowsUpdated = rowsUpdated, }); } }
public static int BulkInsert <T>(this DbContext context, IEnumerable <T> entities, BulkInsertOptions <T> options) { int rowsAffected = 0; var tableMapping = context.GetTableMapping(typeof(T)); using (var dbTransactionContext = new DbTransactionContext(context)) { try { var dbConnection = dbTransactionContext.Connection; var transaction = dbTransactionContext.CurrentTransaction; string stagingTableName = CommonUtil.GetStagingTableName(tableMapping, options.UsePermanentTable, dbConnection); string destinationTableName = string.Format("[{0}].[{1}]", tableMapping.Schema, tableMapping.TableName); IEnumerable <string> columnNames = options.InputColumns != null?options.InputColumns.GetObjectProperties() : tableMapping.GetColumns(options.KeepIdentity); columnNames = columnNames.Where(o => !options.IgnoreColumns.GetObjectProperties().Contains(o)); string[] storeGeneratedColumnNames = tableMapping.GetPrimaryKeyColumns().ToArray(); IEnumerable <string> columnsToInsert = CommonUtil.FormatColumns(columnNames); columnNames = columnNames.Union(storeGeneratedColumnNames); context.Database.CloneTable(destinationTableName, stagingTableName, columnNames, Common.Constants.InternalId_ColumnName); var bulkInsertResult = BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, columnNames, SqlBulkCopyOptions.KeepIdentity, true); List <string> columnsToOutput = new List <string> { "$Action", string.Format("[{0}].[{1}]", "s", Constants.InternalId_ColumnName) }; List <PropertyInfo> propertySetters = new List <PropertyInfo>(); Type entityType = typeof(T); foreach (var storeGeneratedColumnName in storeGeneratedColumnNames) { columnsToOutput.Add(string.Format("[inserted].[{0}]", storeGeneratedColumnName)); propertySetters.Add(entityType.GetProperty(storeGeneratedColumnName)); } string insertSqlText = string.Format("MERGE {0} t USING {1} s ON {2} WHEN NOT MATCHED THEN INSERT ({3}) VALUES ({3}){4};", destinationTableName, stagingTableName, options.InsertIfNotExists ? CommonUtil <T> .GetJoinConditionSql(options.InsertOnCondition, storeGeneratedColumnNames, "t", "s") : "1=2", SqlUtil.ConvertToColumnString(columnsToInsert), columnsToOutput.Count > 0 ? " OUTPUT " + SqlUtil.ConvertToColumnString(columnsToOutput) : ""); if (options.KeepIdentity && storeGeneratedColumnNames.Length > 0) { context.Database.ToggleIdentityInsert(true, destinationTableName); } var bulkQueryResult = context.BulkQuery(insertSqlText, dbConnection, transaction, options); if (options.KeepIdentity && storeGeneratedColumnNames.Length > 0) { context.Database.ToggleIdentityInsert(false, destinationTableName); } rowsAffected = bulkQueryResult.RowsAffected; if (options.AutoMapOutputIdentity) { if (rowsAffected == entities.Count()) { foreach (var result in bulkQueryResult.Results) { int entityId = (int)result[1]; var entity = bulkInsertResult.EntityMap[entityId]; for (int i = 2; i < columnsToOutput.Count; i++) { propertySetters[2 - i].SetValue(entity, result[i]); } } } } context.Database.DropTable(stagingTableName); //ClearEntityStateToUnchanged(context, entities); dbTransactionContext.Commit(); return(rowsAffected); } catch (Exception ex) { dbTransactionContext.Rollback(); throw; } } }
public static int BulkInsert <T>(this DbContext context, IEnumerable <T> entities, BulkInsertOptions <T> options) { int rowsAffected = 0; 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.GetColumns(options.KeepIdentity); string[] storeGeneratedColumnNames = tableMapping.GetPrimaryKeyColumns().ToArray(); SqlUtil.CloneTable(destinationTableName, stagingTableName, null, dbConnection, transaction, Common.Constants.Guid_ColumnName); var bulkInsertResult = BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, null, SqlBulkCopyOptions.KeepIdentity, true); IEnumerable <string> columnsToInsert = columnNames; List <string> columnsToOutput = new List <string>(); List <PropertyInfo> propertySetters = new List <PropertyInfo>(); Type entityType = typeof(T); foreach (var storeGeneratedColumnName in storeGeneratedColumnNames) { columnsToOutput.Add(string.Format("inserted.[{0}]", storeGeneratedColumnName)); propertySetters.Add(entityType.GetProperty(storeGeneratedColumnName)); } string mergeSqlText = string.Format("INSERT INTO {0} ({1}) OUTPUT {2} SELECT {3} FROM {4};", destinationTableName, SqlUtil.ConvertToColumnString(columnsToInsert), SqlUtil.ConvertToColumnString(columnsToOutput), SqlUtil.ConvertToColumnString(columnsToInsert), stagingTableName); if (options.KeepIdentity) { SqlUtil.ToggleIdentiyInsert(true, destinationTableName, dbConnection, transaction); } var bulkQueryResult = context.BulkQuery(mergeSqlText, dbConnection, transaction); if (options.KeepIdentity) { SqlUtil.ToggleIdentiyInsert(false, destinationTableName, dbConnection, transaction); } rowsAffected = bulkQueryResult.RowsAffected; if (options.AutoMapOutputIdentity) { if (rowsAffected == entities.Count()) { var entityIndex = 1; foreach (var result in bulkQueryResult.Results) { var entity = bulkInsertResult.EntityMap[entityIndex]; propertySetters[0].SetValue(entity, result[0]); entityIndex++; } } } SqlUtil.DeleteTable(stagingTableName, dbConnection, transaction); //ClearEntityStateToUnchanged(context, entities); transaction.Commit(); return(rowsAffected); } catch (Exception ex) { transaction.Rollback(); throw ex; } finally { dbConnection.Close(); } } }