public void Merge <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
{
tableInfo.InsertToTempTable = true;
var dropTempTableIfExists = tableInfo.BulkConfig.UseTempDB;
if (dropTempTableIfExists)
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB));
}
context.Database.ExecuteSqlRaw(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));
if (tableInfo.CreatedOutputTable)
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true));
if (tableInfo.TimeStampColumnName != null)
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.AddColumn(tableInfo.FullTempOutputTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType));
}
context.Database.ExecuteSqlRaw(SqlQueryBuilder.AlterTableColumnsToNullable(tableInfo.FullTempOutputTableName, tableInfo));
}
bool keepIdentity = tableInfo.BulkConfig.SqlBulkCopyOptions.HasFlag(Microsoft.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity);
try
{
Insert(context, type, entities, tableInfo, progress);
if (keepIdentity && tableInfo.HasIdentity)
{
context.Database.OpenConnection();
context.Database.ExecuteSqlRaw(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, true));
}
context.Database.ExecuteSqlRaw(SqlQueryBuilder.MergeTable(tableInfo, operationType));
if (tableInfo.CreatedOutputTable)
{
tableInfo.LoadOutputData(context, type, entities);
}
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
if (tableInfo.CreatedOutputTable)
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName, tableInfo.BulkConfig.UseTempDB));
}
context.Database.ExecuteSqlRaw(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB));
}
if (keepIdentity && tableInfo.HasIdentity)
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, false));
context.Database.CloseConnection();
}
}
}
public async Task ReadAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress,
CancellationToken cancellationToken) where T : class
{
Dictionary <string, string> previousPropertyColumnNamesDict = tableInfo.ConfigureBulkReadTableInfo(context);
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo), cancellationToken).ConfigureAwait(false);
try
{
await InsertAsync(context, type, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
tableInfo.PropertyColumnNamesDict = tableInfo.OutputPropertyColumnNamesDict;
var sqlQuery = SqlQueryBuilder.SelectJoinTable(tableInfo);
tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict;
List <T> existingEntities;
if (typeof(T) == type)
{
Expression <Func <DbContext, IQueryable <T> > > expression = tableInfo.GetQueryExpression <T>(sqlQuery, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).ToList();
}
else
{
Expression <Func <DbContext, IEnumerable> > expression = tableInfo.GetQueryExpression(type, sqlQuery, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).Cast <T>().ToList();
}
tableInfo.UpdateReadEntities(type, entities, existingEntities);
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB), cancellationToken).ConfigureAwait(false);
}
}
}
public static void Merge <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
{
tableInfo.InsertToTempTable = true;
tableInfo.CheckHasIdentity(context);
context.Database.CommandTimeout = 960;
context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName));
if (tableInfo.BulkConfig.SetOutputIdentity)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName));
}
try
{
Insert(context, entities, tableInfo, progress);
context.Database.ExecuteSqlCommand(SqlQueryBuilder.MergeTable(tableInfo, operationType));
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
if (!tableInfo.BulkConfig.SetOutputIdentity || !tableInfo.HasSinglePrimaryKey)
{
return;
}
try
{
tableInfo.UpdateOutputIdentity(context, entities);
var dp = SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName);
context.Database.ExecuteSqlCommand(dp);
}
catch (Exception)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
throw;
}
}
catch (Exception)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
throw;
}
}
public static async Task MergeAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
{
tableInfo.InsertToTempTable = true;
await tableInfo.CheckHasIdentityAsync(context).ConfigureAwait(false);
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName)).ConfigureAwait(false);
if (tableInfo.BulkConfig.SetOutputIdentity && tableInfo.HasIdentity)
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName)).ConfigureAwait(false);
}
try
{
await InsertAsync(context, entities, tableInfo, progress).ConfigureAwait(false);
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.MergeTable(tableInfo, operationType)).ConfigureAwait(false);
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName)).ConfigureAwait(false);
if (tableInfo.BulkConfig.SetOutputIdentity && tableInfo.HasIdentity)
{
await tableInfo.UpdateOutputIdentityAsync(context, entities).ConfigureAwait(false);
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName)).ConfigureAwait(false);
}
}
catch (Exception)
{
if (tableInfo.BulkConfig.SetOutputIdentity && tableInfo.HasIdentity)
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName)).ConfigureAwait(false);
}
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName)).ConfigureAwait(false);
throw;
}
}
// Publish Async and NonAsync are merged into single operation flow with protected method using arg: bool isAsync (keeps code DRY)
// https://docs.microsoft.com/en-us/archive/msdn-magazine/2015/july/async-programming-brownfield-async-development#the-flag-argument-hack
protected async Task InsertAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken, bool isAsync)
{
tableInfo.CheckToSetIdentityForPreserveOrder(entities);
if (isAsync)
{
await context.Database.OpenConnectionAsync(cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.OpenConnection();
}
var connection = context.GetUnderlyingConnection(tableInfo.BulkConfig);
try
{
var transaction = context.Database.CurrentTransaction;
using var sqlBulkCopy = GetSqlBulkCopy((SqlConnection)connection, transaction, tableInfo.BulkConfig);
bool setColumnMapping = false;
tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
try
{
var dataTable = GetDataTable(context, type, entities, sqlBulkCopy, tableInfo);
if (isAsync)
{
await sqlBulkCopy.WriteToServerAsync(dataTable, cancellationToken).ConfigureAwait(false);
}
else
{
sqlBulkCopy.WriteToServer(dataTable);
}
}
catch (InvalidOperationException ex)
{
if (ex.Message.Contains(BulkExceptionMessage.ColumnMappingNotMatch))
{
bool tableExist = isAsync ? await tableInfo.CheckTableExistAsync(context, tableInfo, cancellationToken, isAsync : true).ConfigureAwait(false)
: tableInfo.CheckTableExistAsync(context, tableInfo, cancellationToken, isAsync: false).GetAwaiter().GetResult();
if (!tableExist)
{
var sqlCreateTableCopy = SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo);
var sqlDropTable = SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlCreateTableCopy, cancellationToken).ConfigureAwait(false);
await context.Database.ExecuteSqlRawAsync(sqlDropTable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlCreateTableCopy);
context.Database.ExecuteSqlRaw(sqlDropTable);
}
}
}
throw;
}
}
finally
{
if (isAsync)
{
await context.Database.CloseConnectionAsync().ConfigureAwait(false);
}
else
{
context.Database.CloseConnection();
}
}
if (!tableInfo.CreatedOutputTable)
{
tableInfo.CheckToSetIdentityForPreserveOrder(entities, reset: true);
}
}
protected async Task ReadAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken, bool isAsync) where T : class
{
Dictionary <string, string> previousPropertyColumnNamesDict = tableInfo.ConfigureBulkReadTableInfo();
var sqlCreateTableCopy = SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlCreateTableCopy, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlCreateTableCopy);
}
try
{
if (isAsync)
{
await InsertAsync(context, type, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
}
else
{
InsertAsync(context, type, entities, tableInfo, progress, cancellationToken, isAsync: false).GetAwaiter().GetResult();
}
tableInfo.PropertyColumnNamesDict = tableInfo.OutputPropertyColumnNamesDict;
var sqlSelectJoinTable = SqlQueryBuilder.SelectJoinTable(tableInfo);
tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict; // TODO Consider refactor and integrate with TimeStampPropertyName, also check for Calculated props.
// Output only PropertisToInclude and for getting Id with SetOutputIdentity
if (tableInfo.TimeStampPropertyName != null && !tableInfo.PropertyColumnNamesDict.ContainsKey(tableInfo.TimeStampPropertyName))
{
tableInfo.PropertyColumnNamesDict.Add(tableInfo.TimeStampPropertyName, tableInfo.TimeStampColumnName);
}
List <T> existingEntities;
if (typeof(T) == type)
{
Expression <Func <DbContext, IQueryable <T> > > expression = tableInfo.GetQueryExpression <T>(sqlSelectJoinTable, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).ToList();
}
else // TODO: Consider removing
{
Expression <Func <DbContext, IEnumerable> > expression = tableInfo.GetQueryExpression(type, sqlSelectJoinTable, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).Cast <T>().ToList();
}
tableInfo.UpdateReadEntities(type, entities, existingEntities);
if (tableInfo.TimeStampPropertyName != null && !tableInfo.PropertyColumnNamesDict.ContainsKey(tableInfo.TimeStampPropertyName))
{
tableInfo.PropertyColumnNamesDict.Remove(tableInfo.TimeStampPropertyName);
}
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
var sqlDropTable = SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlDropTable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlDropTable);
}
}
}
}
protected async Task MergeAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress, CancellationToken cancellationToken, bool isAsync) where T : class
{
tableInfo.InsertToTempTable = true;
var dropTempTableIfExists = tableInfo.BulkConfig.UseTempDB;
if (dropTempTableIfExists)
{
var sqlDropTable = SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlDropTable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlDropTable);
}
}
var sqlCreateTableCopy = SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlCreateTableCopy, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlCreateTableCopy);
}
if (tableInfo.TimeStampColumnName != null)
{
var sqlAddColumn = SqlQueryBuilder.AddColumn(tableInfo.FullTempTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlAddColumn, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlAddColumn);
}
}
if (tableInfo.CreatedOutputTable)
{
var sqlCreateOutputTableCopy = SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlCreateOutputTableCopy, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlCreateOutputTableCopy);
}
if (tableInfo.TimeStampColumnName != null)
{
var sqlAddColumn = SqlQueryBuilder.AddColumn(tableInfo.FullTempOutputTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlAddColumn, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlAddColumn);
}
}
var sqlAlterTableColumnsToNullable = SqlQueryBuilder.AlterTableColumnsToNullable(tableInfo.FullTempOutputTableName, tableInfo);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlAlterTableColumnsToNullable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlAlterTableColumnsToNullable);
}
}
bool keepIdentity = tableInfo.BulkConfig.SqlBulkCopyOptions.HasFlag(SqlBulkCopyOptions.KeepIdentity);
try
{
if (isAsync)
{
await InsertAsync(context, type, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
}
else
{
Insert(context, type, entities, tableInfo, progress);
}
if (keepIdentity && tableInfo.HasIdentity)
{
var sqlSetIdentityInsertTrue = SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, true);
if (isAsync)
{
await context.Database.OpenConnectionAsync(cancellationToken).ConfigureAwait(false);
await context.Database.ExecuteSqlRawAsync(sqlSetIdentityInsertTrue, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.OpenConnection();
context.Database.ExecuteSqlRaw(sqlSetIdentityInsertTrue);
}
}
var sqlMergeTable = SqlQueryBuilder.MergeTable(tableInfo, operationType);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlMergeTable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlMergeTable);
}
if (tableInfo.CreatedOutputTable)
{
if (isAsync)
{
await tableInfo.LoadOutputDataAsync(context, type, entities, tableInfo, cancellationToken, isAsync : true).ConfigureAwait(false);
}
else
{
tableInfo.LoadOutputDataAsync(context, type, entities, tableInfo, cancellationToken, isAsync: false).GetAwaiter().GetResult();
}
}
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
if (tableInfo.CreatedOutputTable)
{
var sqlDropOutputTable = SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName, tableInfo.BulkConfig.UseTempDB);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlDropOutputTable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlDropOutputTable);
}
}
var sqlDropTable = SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlDropTable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlDropTable);
}
}
if (keepIdentity && tableInfo.HasIdentity)
{
var sqlSetIdentityInsertFalse = SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, false);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlSetIdentityInsertFalse, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlSetIdentityInsertFalse);
}
context.Database.CloseConnection();
}
}
}
public async Task InsertAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken)
{
tableInfo.CheckToSetIdentityForPreserveOrder(entities);
var connection = await OpenAndGetSqlConnectionAsync(context, tableInfo.BulkConfig, cancellationToken).ConfigureAwait(false);
try
{
var transaction = context.Database.CurrentTransaction;
// separate logic for System.Data.SqlClient and Microsoft.Data.SqlClient
if (SqlClientHelper.IsSystemConnection(connection))
{
using (var sqlBulkCopy = GetSqlBulkCopy((System.Data.SqlClient.SqlConnection)connection, transaction, tableInfo.BulkConfig))
{
bool setColumnMapping = false;
tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
try
{
var dataTable = GetDataTable(context, type, entities, sqlBulkCopy, tableInfo);
await sqlBulkCopy.WriteToServerAsync(dataTable, cancellationToken).ConfigureAwait(false);
}
catch (InvalidOperationException ex)
{
if (ex.Message.Contains(ColumnMappingExceptionMessage))
{
if (!await tableInfo.CheckTableExistAsync(context, tableInfo, cancellationToken).ConfigureAwait(false))
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo), cancellationToken).ConfigureAwait(false);
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB), cancellationToken).ConfigureAwait(false);
}
}
throw;
}
}
}
else
{
using (var sqlBulkCopy = GetSqlBulkCopy((Microsoft.Data.SqlClient.SqlConnection)connection, transaction, tableInfo.BulkConfig))
{
bool setColumnMapping = false;
tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
try
{
var dataTable = GetDataTable(context, type, entities, sqlBulkCopy, tableInfo);
await sqlBulkCopy.WriteToServerAsync(dataTable, cancellationToken).ConfigureAwait(false);
}
catch (InvalidOperationException ex)
{
if (ex.Message.Contains(ColumnMappingExceptionMessage))
{
if (!await tableInfo.CheckTableExistAsync(context, tableInfo, cancellationToken).ConfigureAwait(false))
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo), cancellationToken).ConfigureAwait(false);
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB), cancellationToken).ConfigureAwait(false);
}
}
throw;
}
}
}
}
finally
{
await context.Database.CloseConnectionAsync().ConfigureAwait(false);
}
if (!tableInfo.CreatedOutputTable)
{
tableInfo.CheckToSetIdentityForPreserveOrder(entities, reset: true);
}
}
public void Insert <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress)
{
tableInfo.CheckToSetIdentityForPreserveOrder(entities);
var connection = OpenAndGetSqlConnection(context, tableInfo.BulkConfig);
try
{
var transaction = context.Database.CurrentTransaction;
// separate logic for System.Data.SqlClient and Microsoft.Data.SqlClient
if (SqlClientHelper.IsSystemConnection(connection))
{
using (var sqlBulkCopy = GetSqlBulkCopy((System.Data.SqlClient.SqlConnection)connection, transaction, tableInfo.BulkConfig))
{
bool setColumnMapping = false;
tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
try
{
var dataTable = GetDataTable(context, type, entities, sqlBulkCopy, tableInfo);
sqlBulkCopy.WriteToServer(dataTable);
}
catch (InvalidOperationException ex)
{
if (ex.Message.Contains(ColumnMappingExceptionMessage))
{
if (!tableInfo.CheckTableExist(context, tableInfo))
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo)); // Will throw Exception specify missing db column: Invalid column name ''
context.Database.ExecuteSqlRaw(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB));
}
}
throw;
}
}
}
else
{
using (var sqlBulkCopy = GetSqlBulkCopy((Microsoft.Data.SqlClient.SqlConnection)connection, transaction, tableInfo.BulkConfig))
{
bool setColumnMapping = false;
tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
try
{
var dataTable = GetDataTable(context, type, entities, sqlBulkCopy, tableInfo);
sqlBulkCopy.WriteToServer(dataTable);
}
catch (InvalidOperationException ex)
{
if (ex.Message.Contains(ColumnMappingExceptionMessage))
{
if (!tableInfo.CheckTableExist(context, tableInfo))
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo)); // Will throw Exception specify missing db column: Invalid column name ''
context.Database.ExecuteSqlRaw(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB));
}
}
throw;
}
}
}
}
finally
{
context.Database.CloseConnection();
}
if (!tableInfo.CreatedOutputTable)
{
tableInfo.CheckToSetIdentityForPreserveOrder(entities, reset: true);
}
}
public async Task MergeAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, OperationType operationType,
Action <decimal> progress, CancellationToken cancellationToken) where T : class
{
tableInfo.InsertToTempTable = true;
var dropTempTableIfExists = tableInfo.BulkConfig.UseTempDB;
if (dropTempTableIfExists)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB), cancellationToken).ConfigureAwait(false);
}
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo), cancellationToken).ConfigureAwait(false);
if (tableInfo.CreatedOutputTable)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true), cancellationToken).ConfigureAwait(false);
if (tableInfo.TimeStampColumnName != null)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.AddColumn(tableInfo.FullTempOutputTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType), cancellationToken).ConfigureAwait(false);
}
}
bool keepIdentity = tableInfo.BulkConfig.SqlBulkCopyOptions.HasFlag(Microsoft.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity);
try
{
await InsertAsync(context, type, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
if (keepIdentity && tableInfo.HasIdentity)
{
await context.Database.OpenConnectionAsync(cancellationToken).ConfigureAwait(false);
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, true), cancellationToken).ConfigureAwait(false);
}
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.MergeTable(tableInfo, operationType), cancellationToken).ConfigureAwait(false);
if (tableInfo.CreatedOutputTable)
{
await tableInfo.LoadOutputDataAsync(context, type, entities, cancellationToken).ConfigureAwait(false);
}
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
if (tableInfo.CreatedOutputTable)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName, tableInfo.BulkConfig.UseTempDB), cancellationToken).ConfigureAwait(false);
}
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB), cancellationToken).ConfigureAwait(false);
}
if (keepIdentity && tableInfo.HasIdentity)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, false), cancellationToken).ConfigureAwait(false);
context.Database.CloseConnection();
}
}
}
protected async Task MergeAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress, CancellationToken cancellationToken, bool isAsync) where T : class
{
var entityPropertyWithDefaultValue = entities.GetPropertiesWithDefaultValue(type);
if (tableInfo.BulkConfig.CustomSourceTableName == null)
{
tableInfo.InsertToTempTable = true;
var dropTempTableIfExists = tableInfo.BulkConfig.UseTempDB;
if (dropTempTableIfExists)
{
var sqlDropTable = SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlDropTable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlDropTable);
}
}
var sqlCreateTableCopy = SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlCreateTableCopy, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlCreateTableCopy);
}
if (tableInfo.TimeStampColumnName != null)
{
var sqlAddColumn = SqlQueryBuilder.AddColumn(tableInfo.FullTempTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlAddColumn, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlAddColumn);
}
}
}
if (tableInfo.CreatedOutputTable)
{
var sqlCreateOutputTableCopy = SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlCreateOutputTableCopy, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlCreateOutputTableCopy);
}
if (tableInfo.TimeStampColumnName != null)
{
var sqlAddColumn = SqlQueryBuilder.AddColumn(tableInfo.FullTempOutputTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlAddColumn, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlAddColumn);
}
}
if (operationType == OperationType.InsertOrUpdateOrDelete)
{
// Output returns all changes including Deleted rows with all NULL values, so if TempOutput.Id col not Nullable it breaks
var sqlAlterTableColumnsToNullable = SqlQueryBuilder.AlterTableColumnsToNullable(tableInfo.FullTempOutputTableName, tableInfo);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlAlterTableColumnsToNullable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlAlterTableColumnsToNullable);
}
}
}
bool keepIdentity = tableInfo.BulkConfig.SqlBulkCopyOptions.HasFlag(SqlBulkCopyOptions.KeepIdentity);
try
{
if (tableInfo.BulkConfig.CustomSourceTableName == null)
{
if (isAsync)
{
await InsertAsync(context, type, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
}
else
{
Insert(context, type, entities, tableInfo, progress);
}
}
if (keepIdentity && tableInfo.HasIdentity)
{
var sqlSetIdentityInsertTrue = SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, true);
if (isAsync)
{
await context.Database.OpenConnectionAsync(cancellationToken).ConfigureAwait(false);
await context.Database.ExecuteSqlRawAsync(sqlSetIdentityInsertTrue, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.OpenConnection();
context.Database.ExecuteSqlRaw(sqlSetIdentityInsertTrue);
}
}
var sqlMergeTable = SqlQueryBuilder.MergeTable <T>(context, tableInfo, operationType, entityPropertyWithDefaultValue);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlMergeTable.sql, sqlMergeTable.parameters, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlMergeTable.sql, sqlMergeTable.parameters);
}
if (tableInfo.CreatedOutputTable)
{
if (isAsync)
{
await tableInfo.LoadOutputDataAsync(context, type, entities, tableInfo, cancellationToken, isAsync : true).ConfigureAwait(false);
}
else
{
tableInfo.LoadOutputDataAsync(context, type, entities, tableInfo, cancellationToken, isAsync: false).GetAwaiter().GetResult();
}
}
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
if (tableInfo.CreatedOutputTable)
{
var sqlDropOutputTable = SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName, tableInfo.BulkConfig.UseTempDB);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlDropOutputTable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlDropOutputTable);
}
}
if (tableInfo.BulkConfig.CustomSourceTableName == null)
{
var sqlDropTable = SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlDropTable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlDropTable);
}
}
}
if (keepIdentity && tableInfo.HasIdentity)
{
var sqlSetIdentityInsertFalse = SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, false);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlSetIdentityInsertFalse, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlSetIdentityInsertFalse);
}
context.Database.CloseConnection();
}
}
}