/// <summary>
/// Performs a bulk update/insert/delete process for a given list of entities, utilizes EF change tracking to determine the state of entities.
/// Uses the SqlBulkCopy and temp tables to perform the action.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="context"></param>
/// <param name="bulkProvider">The bulk provider to use for direct DB insertion</param>
/// <param name="entities">The list of entities to update</param>
/// <param name="refreshMode">Controls which values are read back from DB, Can't be None</param>
public static void BulkUpdate <T>(this DbContext context, IBulkProvider bulkProvider, ICollection <T> entities, RefreshMode refreshMode = RefreshMode.All)
where T : class
{
if (!context.Configuration.AutoDetectChangesEnabled)
{
throw new NotSupportedException("You must enable EF change tracking to call this function");
}
//Identity refresh is mandatory when using EF tracking, otherwise we can't tell EF to AcceptChanges after insert.
if (refreshMode == RefreshMode.None)
{
refreshMode = RefreshMode.Identity;
}
//We restrict updates to the provided list
var insertList = new List <T>();
var updateList = new List <T>();
var deleteList = new List <T>();
foreach (var entity in entities)
{
switch (context.Entry(entity).State)
{
case EntityState.Detached:
throw new EntityException("Entities must be added to context before saving.");
case EntityState.Added:
insertList.Add(entity);
break;
case EntityState.Modified:
updateList.Add(entity);
break;
case EntityState.Deleted:
deleteList.Add(entity);
break;
}
}
BulkUpdate(context, bulkProvider, insertList, updateList, deleteList, refreshMode);
//Update entries state
foreach (var entity in updateList)
{
context.Entry(entity).State = EntityState.Unchanged;
}
foreach (var entity in deleteList)
{
context.Entry(entity).State = EntityState.Detached;
}
}
/// <summary>
/// Performs a bulk update/insert/delete process for a given list of entities, Takes in a combined update/insert list and a delete list.
/// <para/>
/// Infers inserts based on identity values (equals zero), Use the Inserts/Updates/Deletes overload if you already have separate lists.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="context"></param>
/// <param name="bulkProvider">The bulk provider to use for direct DB insertion</param>
/// <param name="updateList"></param>
/// <param name="deleteList"></param>
public static void BulkUpdate <T>(this DbContext context, IBulkProvider bulkProvider, ICollection <T> updateList, ICollection <T> deleteList)
where T : class
{
//split updateList to insert/update based on identity key value to maintain backward compability
var keyColName = context.GetComputedColumnNames <T>().First(x => x.Value).Key;
if (keyColName == null)
{
//There're no identity columns, hence can't infer entities state.
BulkUpdate(context, bulkProvider, null, updateList, deleteList);
return;
}
var keyCol = context.GetTableColumns <T>()[keyColName];
var insertList = new List <T>();
var newUpdateList = new List <T>();
if (updateList != null)
{
foreach (var item in updateList)
{
//Below should be safe, since identity columns are always integers, using 64 to account for large ids.
if (Convert.ToInt64(keyCol.GetValue(item, null)) == 0)
{
//If identity value is 0 it's assumed to be a new record
insertList.Add(item);
}
else
{
newUpdateList.Add(item);
}
}
}
else
{
newUpdateList = null;
}
BulkUpdate(context, bulkProvider, insertList, newUpdateList, deleteList);
}
/// <summary>
/// Performs a bulk update/insert/delete process for a given list of entities, Takes in an update/insert list and a delete list.
/// Uses the SqlBulkCopy and temp tables to perform the action.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="context"></param>
/// <param name="bulkProvider"></param>
/// <param name="inserts">The list of entities to insert</param>
/// <param name="updates">The list of entities to update</param>
/// <param name="deletes">The list of entities to delete</param>
/// <param name="refreshMode">Controls which values are read back from DB</param>
public static void BulkUpdate <T>(this DbContext context, IBulkProvider bulkProvider, ICollection <T> inserts, ICollection <T> updates,
ICollection <T> deletes, RefreshMode refreshMode = RefreshMode.None)
where T : class
{
/*
* 1. Create temp table for sql bulk update (inserts/updates)
* 2. Bulk insert
* 3. Execute merge statements
* 4. Create temp table for deletes
* 5. Bulk insert keys for deletion
* 6. Execute delete statement
*/
var columns = context.GetTableColumns <T>();
var tmpTableName = GetTempTableName <T>();
var tableName = context.GetTableName <T>();
var keys = context.GetTableKeyColumns <T>();
var hasInserts = inserts != null && inserts.Count > 0;
var hasUpdates = updates != null && updates.Count > 0;
//Tracking the original connection state to return it in the same state.
var connectionState = context.Database.Connection.State;
if (connectionState != ConnectionState.Open)
{
context.Database.Connection.Open();
}
var computedCols = context.GetComputedColumnNames <T>();
var outSettings = GetOutputColumns <T>(context, hasInserts, hasUpdates, refreshMode, columns, keys.Keys,
computedCols);
if (hasInserts || hasUpdates)
{
var allUpdates = new List <T>();
if (hasInserts)
{
allUpdates.AddRange(inserts);
}
if (hasUpdates)
{
allUpdates.AddRange(updates);
}
var sql = context.GetTableDdl(tmpTableName, columns);
//Create a temp table to insert modified/inserted rows.
context.Database.ExecuteSqlCommand(sql);
if (outSettings != null)
{
context.Database.ExecuteSqlCommand(outSettings.TableSql);
var identityProps = computedCols.Where(x => x.Value).Select(x => columns[x.Key]).ToList();
SetTempIdentity(inserts, identityProps);
}
var table = context.GetDatatable(allUpdates, columns);
//Use BulkCopy to bulk insert records to temp table.
bulkProvider.WriteToServer(context.Database.Connection, tmpTableName, table);
//Get computed columns because we can't insert/update them
sql = context.GetMergeSql(tmpTableName, tableName, columns.Keys.ToList(), keys.Keys.ToList(),
computedCols, outSettings?.TableName, outSettings?.AllColumns.Keys);
context.Database.ExecuteSqlCommand(sql);
if (outSettings != null)
{
RefreshEntities(context, allUpdates, outSettings);
sql = "drop table " + outSettings.TableName;
context.Database.ExecuteSqlCommand(sql);
}
//drop the temp table
sql = "drop table " + tmpTableName;
context.Database.ExecuteSqlCommand(sql);
}
if (deletes != null && deletes.Count > 0)
{
//Create a temp table to store deleted entities keys
var tmpDeleteTableName = tmpTableName + "DelKeys";
var sql = context.GetTableDdl(tmpDeleteTableName, keys);
context.Database.ExecuteSqlCommand(sql);
var table = context.GetDatatable(deletes, keys);
bulkProvider.WriteToServer(context.Database.Connection, tmpDeleteTableName, table);
sql = context.GetDeleteSql(tmpDeleteTableName, tableName, keys.Keys.ToList());
context.Database.ExecuteSqlCommand(sql);
//drop the temp table
sql = "drop table " + tmpDeleteTableName;
context.Database.ExecuteSqlCommand(sql);
}
if (connectionState == ConnectionState.Closed)
{
context.Database.Connection.Close();
}
}
