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;
