public async Task CheckHasIdentityAsync(DbContext context)
{
int hasIdentity = 0;
if (HasSinglePrimaryKey)
{
var sqlConnection = context.Database.GetDbConnection();
var currentTransaction = context.Database.CurrentTransaction;
try
{
if (currentTransaction == null)
{
if (sqlConnection.State != ConnectionState.Open)
{
await sqlConnection.OpenAsync().ConfigureAwait(false);
}
}
using (var command = sqlConnection.CreateCommand())
{
if (currentTransaction != null)
{
command.Transaction = currentTransaction.GetDbTransaction();
}
command.CommandText = SqlQueryBuilder.SelectIsIdentity(FullTableName, PropertyColumnNamesDict[PrimaryKeys[0]]);
using (var reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
if (reader.HasRows)
{
while (await reader.ReadAsync().ConfigureAwait(false))
{
hasIdentity = (int)reader[0];
}
}
}
}
}
finally
{
if (currentTransaction == null)
{
sqlConnection.Close();
}
}
}
HasIdentity = hasIdentity == 1;
}
public void CheckHasIdentity(DbContext context)
{
int hasIdentity = 0;
if (HasSinglePrimaryKey)
{
var sqlConnection = context.Database.GetDbConnection();
var currentTransaction = context.Database.CurrentTransaction;
try
{
if (currentTransaction == null)
{
if (sqlConnection.State != ConnectionState.Open)
{
sqlConnection.Open();
}
}
using (var command = sqlConnection.CreateCommand())
{
if (currentTransaction != null)
{
command.Transaction = currentTransaction.GetDbTransaction();
}
command.CommandText = SqlQueryBuilder.SelectIsIdentity(FullTableName, PropertyColumnNamesDict[PrimaryKeys[0]]);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
hasIdentity = reader[0] == DBNull.Value ? 0 : (int)reader[0];
}
}
}
}
}
finally
{
if (currentTransaction == null)
{
sqlConnection.Close();
}
}
}
HasIdentity = hasIdentity == 1;
}
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, tableInfo)).ConfigureAwait(false);
if (tableInfo.CreatedOutputTable)
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true)).ConfigureAwait(false);
if (tableInfo.TimeStampColumnName != null)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.AddColumn(tableInfo.FullTempOutputTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType));
}
}
try
{
await InsertAsync(context, entities, tableInfo, progress).ConfigureAwait(false);
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.MergeTable(tableInfo, operationType)).ConfigureAwait(false);
if (tableInfo.CreatedOutputTable)
{
try
{
await tableInfo.LoadOutputDataAsync(context, entities).ConfigureAwait(false);
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName)).ConfigureAwait(false);
}
}
}
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName)).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;
if (tableInfo.BulkConfig.UpdateByProperties == null || tableInfo.BulkConfig.UpdateByProperties.Count() == 0)
{
tableInfo.CheckHasIdentity(context);
}
context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));
if (tableInfo.CreatedOutputTable)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true));
if (tableInfo.TimeStampColumnName != null)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.AddColumn(tableInfo.FullTempOutputTableName, tableInfo.TimeStampColumnName, tableInfo.TimeStampOutColumnType));
}
}
try
{
Insert(context, entities, tableInfo, progress);
context.Database.ExecuteSqlCommand(SqlQueryBuilder.MergeTable(tableInfo, operationType));
if (tableInfo.CreatedOutputTable)
{
try
{
tableInfo.LoadOutputData(context, entities);
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
}
}
}
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
}
}
}
public void UpdateOutputIdentity <T>(DbContext context, IList <T> entities) where T : class
{
var entitiesWithOutputIdentity = context.Set <T>().FromSql(SqlQueryBuilder.SelectFromTable(this.FullTempOutputTableName, this.PrimaryKeyFormated)).ToList();
if (this.BulkConfig.PreserveInsertOrder) // Updates PK in entityList
{
var accessor = TypeAccessor.Create(typeof(T));
for (int i = 0; i < this.NumberOfEntities; i++)
{
accessor[entities[i], this.PrimaryKey] = accessor[entitiesWithOutputIdentity[i], this.PrimaryKey];
}
}
else // Clears entityList and then refill it with loaded entites from Db
{
entities.Clear();
((List <T>)entities).AddRange(entitiesWithOutputIdentity);
}
}
public async Task <bool> CheckTableExistAsync(DbContext context, TableInfo tableInfo, CancellationToken cancellationToken)
{
bool tableExist = false;
var sqlConnection = context.Database.GetDbConnection();
var currentTransaction = context.Database.CurrentTransaction;
try
{
if (currentTransaction == null)
{
if (sqlConnection.State != ConnectionState.Open)
{
await sqlConnection.OpenAsync(cancellationToken).ConfigureAwait(false);
}
;
}
using (var command = sqlConnection.CreateCommand())
{
if (currentTransaction != null)
{
command.Transaction = currentTransaction.GetDbTransaction();
}
command.CommandText = SqlQueryBuilder.CheckTableExist(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB);
using (var reader = await command.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
{
if (reader.HasRows)
{
while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
tableExist = (int)reader[0] == 1;
}
}
}
}
}
finally
{
if (currentTransaction == null)
{
sqlConnection.Close();
}
}
return(tableExist);
}
public static async Task TruncateAsync(DbContext context, TableInfo tableInfo)
{
string providerName = context.Database.ProviderName;
// -- SQL Server --
if (providerName.EndsWith(DbServer.SqlServer.ToString()))
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.TruncateTable(tableInfo.FullTableName));
}
// -- Sqlite --
else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.DeleteTable(tableInfo.FullTableName));
}
else
{
throw new SqlProviderNotSupportedException(providerName);
}
}
public bool CheckTableExist(DbContext context, TableInfo tableInfo)
{
bool tableExist = false;
var sqlConnection = context.Database.GetDbConnection();
var currentTransaction = context.Database.CurrentTransaction;
try
{
if (currentTransaction == null)
{
if (sqlConnection.State != ConnectionState.Open)
{
sqlConnection.Open();
}
}
using (var command = sqlConnection.CreateCommand())
{
if (currentTransaction != null)
{
command.Transaction = currentTransaction.GetDbTransaction();
}
command.CommandText = SqlQueryBuilder.CheckTableExist(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
tableExist = (int)reader[0] == 1;
}
}
}
}
}
finally
{
if (currentTransaction == null)
{
sqlConnection.Close();
}
}
return(tableExist);
}
public async Task LoadOutputDataAsync <T>(DbContext context, IList <T> entities) where T : class
{
if (BulkConfig.SetOutputIdentity && HasSinglePrimaryKey)
{
string sqlQuery = SqlQueryBuilder.SelectFromOutputTable(this);
var entitiesWithOutputIdentity = await QueryOutputTableAsync <T>(context, sqlQuery).ToListAsync().ConfigureAwait(false);
UpdateEntitiesIdentity(entities, entitiesWithOutputIdentity);
}
if (BulkConfig.CalculateStats)
{
int numberUpdated = await GetNumberUpdatedAsync(context);
BulkConfig.StatsInfo = new StatsInfo
{
StatsNumberUpdated = numberUpdated,
StatsNumberInserted = entities.Count - numberUpdated
};
}
}
// Compiled queries created manually to avoid EF Memory leak bug when using EF with dynamic SQL:
// https://github.com/borisdj/EFCore.BulkExtensions/issues/73
// Once the following Issue gets fixed(expected in EF 3.0) this can be replaced with code segment: DirectQuery
// https://github.com/aspnet/EntityFrameworkCore/issues/12905
#region CompiledQuery
public void LoadOutputData <T>(DbContext context, IList <T> entities) where T : class
{
if (BulkConfig.SetOutputIdentity && HasSinglePrimaryKey)
{
string sqlQuery = SqlQueryBuilder.SelectFromOutputTable(this);
var entitiesWithOutputIdentity = QueryOutputTable <T>(context, sqlQuery).ToList();
UpdateEntitiesIdentity(entities, entitiesWithOutputIdentity);
}
if (BulkConfig.CalculateStats)
{
string sqlQueryCount = SqlQueryBuilder.SelectCountIsUpdateFromOutputTable(this);
int numberUpdated = GetNumberUpdated(context);
BulkConfig.StatsInfo = new StatsInfo
{
StatsNumberUpdated = numberUpdated,
StatsNumberInserted = entities.Count - numberUpdated
};
}
}
public void CheckHasIdentity(DbContext context)
{
var sqlConnection = context.Database.GetDbConnection();
var currentTransaction = context.Database.CurrentTransaction;
try
{
if (currentTransaction == null)
{
if (sqlConnection.State != ConnectionState.Open)
{
sqlConnection.Open();
}
}
using (var command = sqlConnection.CreateCommand())
{
if (currentTransaction != null)
{
command.Transaction = currentTransaction.GetDbTransaction();
}
command.CommandText = SqlQueryBuilder.SelectIdentityColumnName(TableName, Schema);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
IdentityColumnName = reader.GetString(0);
}
}
}
}
}
finally
{
if (currentTransaction == null)
{
sqlConnection.Close();
}
}
}
public async Task CheckHasIdentityAsync(DbContext context, CancellationToken cancellationToken)
{
var sqlConnection = context.Database.GetDbConnection();
var currentTransaction = context.Database.CurrentTransaction;
try
{
if (currentTransaction == null)
{
if (sqlConnection.State != ConnectionState.Open)
{
await sqlConnection.OpenAsync(cancellationToken).ConfigureAwait(false);
}
}
using (var command = sqlConnection.CreateCommand())
{
if (currentTransaction != null)
{
command.Transaction = currentTransaction.GetDbTransaction();
}
command.CommandText = SqlQueryBuilder.SelectIdentityColumnName(TableName, Schema);
using (var reader = await command.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
{
if (reader.HasRows)
{
while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
IdentityColumnName = reader.GetString(0);
}
}
}
}
}
finally
{
if (currentTransaction == null)
{
sqlConnection.Close();
}
}
}
internal async Task LoadOutputDataAsync<T>(DbContext context, Type type, IList<T> entities, CancellationToken cancellationToken) where T : class
{
bool hasIdentity = OutputPropertyColumnNamesDict.Any(a => a.Value == IdentityColumnName);
if (BulkConfig.SetOutputIdentity && hasIdentity)
{
string sqlQuery = SqlQueryBuilder.SelectFromOutputTable(this);
//var entitiesWithOutputIdentity = await QueryOutputTableAsync<T>(context, sqlQuery).ToListAsync(cancellationToken).ConfigureAwait(false); // TempFIX
var entitiesWithOutputIdentity = (typeof(T) == type) ? QueryOutputTable<T>(context, sqlQuery).ToList() :
QueryOutputTable(context, type, sqlQuery).Cast<T>().ToList();
UpdateEntitiesIdentity(type, entities, entitiesWithOutputIdentity);
}
if (BulkConfig.CalculateStats)
{
int numberUpdated = await GetNumberUpdatedAsync(context, cancellationToken).ConfigureAwait(false);
BulkConfig.StatsInfo = new StatsInfo
{
StatsNumberUpdated = numberUpdated,
StatsNumberInserted = entities.Count - numberUpdated
};
}
}
public static void Read <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) where T : class
{
Dictionary <string, string> previousPropertyColumnNamesDict = tableInfo.ConfigureBulkReadTableInfo(context);
context.Database.ExecuteSqlCommand(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));
try
{
Insert(context, entities, tableInfo, progress);
tableInfo.PropertyColumnNamesDict = tableInfo.OutputPropertyColumnNamesDict;
var sqlQuery = SqlQueryBuilder.SelectJoinTable(tableInfo);
tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict;
//var existingEntities = context.Set<T>().FromSql(q).AsNoTracking().ToList(); // Not used because of EF Memory leak bug
Expression <Func <DbContext, IQueryable <T> > > expression = null;
if (tableInfo.BulkConfig.TrackingEntities)
{
expression = (ctx) => ctx.Set <T>().FromSql(sqlQuery);
}
else
{
expression = (ctx) => ctx.Set <T>().FromSql(sqlQuery).AsNoTracking();
}
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
var existingEntities = compiled(context).ToList();
tableInfo.UpdateReadEntities(entities, existingEntities);
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
}
}
}
// Compiled queries created manually to avoid EF Memory leak bug when using EF with dynamic SQL:
// https://github.com/borisdj/EFCore.BulkExtensions/issues/73
// Once the following Issue gets fixed(expected in EF 3.0) this can be replaced with code segment: DirectQuery
// https://github.com/aspnet/EntityFrameworkCore/issues/12905
#region CompiledQuery
public void LoadOutputData <T>(DbContext context, IList <T> entities) where T : class
{
bool hasIdentity = PropertyColumnNamesDict.Any(a => a.Value == IdentityColumnName);
if (BulkConfig.SetOutputIdentity && hasIdentity)
{
string sqlQuery = SqlQueryBuilder.SelectFromOutputTable(this);
var entitiesWithOutputIdentity = QueryOutputTable <T>(context, sqlQuery).ToList();
UpdateEntitiesIdentity(entities, entitiesWithOutputIdentity);
}
if (BulkConfig.CalculateStats)
{
string sqlQueryCount = SqlQueryBuilder.SelectCountIsUpdateFromOutputTable(this);
int numberUpdated = GetNumberUpdated(context);
BulkConfig.StatsInfo = new StatsInfo
{
StatsNumberUpdated = numberUpdated,
StatsNumberInserted = entities.Count - numberUpdated
};
}
}
internal void LoadOutputData <T>(DbContext context, Type type, IList <T> entities) where T : class
{
bool hasIdentity = OutputPropertyColumnNamesDict.Any(a => a.Value == IdentityColumnName);
if (BulkConfig.SetOutputIdentity && hasIdentity)
{
string sqlQuery = SqlQueryBuilder.SelectFromOutputTable(this);
var entitiesWithOutputIdentity = (typeof(T) == type) ? QueryOutputTable <T>(context, sqlQuery).ToList() :
QueryOutputTable(context, type, sqlQuery).Cast <T>().ToList();
UpdateEntitiesIdentity(type, entities, entitiesWithOutputIdentity);
}
if (BulkConfig.CalculateStats)
{
int numberUpdated = GetNumberUpdated(context);
int numberDeleted = GetNumberDeleted(context);
BulkConfig.StatsInfo = new StatsInfo
{
StatsNumberUpdated = numberUpdated,
StatsNumberDeleted = numberDeleted,
StatsNumberInserted = entities.Count - numberUpdated - numberDeleted
};
}
}
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 ex)
{
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 ex;
}
}
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.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)
{
try
{
tableInfo.UpdateOutputIdentity(context, entities);
var dp = SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName);
context.Database.ExecuteSqlCommand(dp);
}
catch (Exception ex)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
throw ex;
}
}
}
catch (Exception ex)
{
context.Database.ExecuteSqlCommand(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
throw ex;
}
}
public static async Task ReadAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) where T : class
{
Dictionary <string, string> previousPropertyColumnNamesDict = tableInfo.ConfigureBulkReadTableInfo(context);
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));
try
{
await InsertAsync(context, entities, tableInfo, progress);
tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict;
var sqlQuery = SqlQueryBuilder.SelectJoinTable(tableInfo);
//var existingEntities = await context.Set<T>().FromSql(sqlQuery).ToListAsync();
Expression <Func <DbContext, IQueryable <T> > > expression = null;
if (tableInfo.BulkConfig.TrackingEntities)
{
expression = (ctx) => ctx.Set <T>().FromSql(sqlQuery);
}
else
{
expression = (ctx) => ctx.Set <T>().FromSql(sqlQuery).AsNoTracking();
}
var compiled = EF.CompileAsyncQuery(expression);
var existingEntities = (await compiled(context).ToListAsync().ConfigureAwait(false));
tableInfo.UpdateReadEntities(entities, existingEntities);
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
}
}
}
public static void Insert <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress)
{
string providerName = context.Database.ProviderName; // "Microsoft.EntityFrameworkCore.*****"
// -- SQL Server --
if (providerName.EndsWith(DbServer.SqlServer.ToString()))
{
var connection = OpenAndGetSqlConnection(context, tableInfo.BulkConfig);
var transaction = context.Database.CurrentTransaction;
try
{
using (var sqlBulkCopy = GetSqlBulkCopy((SqlConnection)connection, transaction, tableInfo.BulkConfig))
{
bool useFastMember = tableInfo.HasOwnedTypes == false && // With OwnedTypes DataTable is used since library FastMember can not (https://github.com/mgravell/fast-member/issues/21)
tableInfo.ColumnNameContainsSquareBracket == false && // FastMember does not support escaped columnNames ] -> ]]
tableInfo.ShadowProperties.Count == 0 && // With Shadow prop. Discriminator (TPH inheritance) also not used because FastMember is slow for Update (https://github.com/borisdj/EFCore.BulkExtensions/pull/17)
!tableInfo.ConvertibleProperties.Any() && // With ConvertibleProperties FastMember is slow as well
!tableInfo.HasAbstractList && // AbstractList not working with FastMember
!tableInfo.BulkConfig.UseOnlyDataTable;
bool setColumnMapping = useFastMember;
tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
try
{
if (useFastMember)
{
using (var reader = ObjectReaderEx.Create(entities, tableInfo.ShadowProperties, tableInfo.ConvertibleProperties, context, tableInfo.PropertyColumnNamesDict.Keys.ToArray()))
{
sqlBulkCopy.WriteToServer(reader);
}
}
else
{
var dataTable = GetDataTable <T>(context, 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));
}
}
throw ex;
}
}
}
finally
{
if (transaction == null)
{
connection.Close();
}
}
}
// -- SQLite --
else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
{
var connection = OpenAndGetSqliteConnection(context, tableInfo.BulkConfig);
var transaction = tableInfo.BulkConfig.SqliteTransaction ?? connection.BeginTransaction();
try
{
var command = GetSqliteCommand(context, entities, tableInfo, connection, transaction);
var typeAccessor = TypeAccessor.Create(typeof(T), true);
int rowsCopied = 0;
foreach (var item in entities)
{
LoadSqliteValues(tableInfo, typeAccessor, item, command);
command.ExecuteNonQuery();
SetProgress(ref rowsCopied, entities.Count, tableInfo.BulkConfig, progress);
}
}
finally
{
if (tableInfo.BulkConfig.SqliteTransaction == null)
{
transaction.Commit();
transaction.Dispose();
}
if (tableInfo.BulkConfig.SqliteConnection == null)
{
connection.Close();
}
}
}
else
{
throw new SqlProviderNotSupportedException(providerName);
}
}
private static void Merge <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
{
string providerName = context.Database.ProviderName;
// -- SQL Server --
if (providerName.EndsWith(DbServer.SqlServer.ToString()))
{
tableInfo.InsertToTempTable = true;
tableInfo.CheckHasIdentity(context);
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));
}
}
bool keepIdentity = tableInfo.BulkConfig.SqlBulkCopyOptions.HasFlag(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));
}
context.Database.ExecuteSqlRaw(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName));
}
if (keepIdentity && tableInfo.HasIdentity)
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, false));
context.Database.CloseConnection();
}
}
}
// -- SQLite --
else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
{
var connection = OpenAndGetSqliteConnection(context, tableInfo.BulkConfig);
var transaction = tableInfo.BulkConfig.SqliteTransaction ?? connection.BeginTransaction();
try
{
var command = GetSqliteCommand(context, type, entities, tableInfo, connection, transaction);
var typeAccessor = TypeAccessor.Create(type, true);
int rowsCopied = 0;
foreach (var item in entities)
{
LoadSqliteValues(tableInfo, typeAccessor, item, command);
command.ExecuteNonQuery();
SetProgress(ref rowsCopied, entities.Count, tableInfo.BulkConfig, progress);
}
if (operationType != OperationType.Delete && tableInfo.BulkConfig.SetOutputIdentity && tableInfo.IdentityColumnName != null)
{
command.CommandText = SqlQueryBuilderSqlite.SelectLastInsertRowId();
long lastRowIdScalar = (long)command.ExecuteScalar();
var identityPropertyInteger = false;
var accessor = TypeAccessor.Create(type, true);
string identityPropertyName = tableInfo.PropertyColumnNamesDict.SingleOrDefault(a => a.Value == tableInfo.IdentityColumnName).Key;
if (accessor.GetMembers().FirstOrDefault(x => x.Name == identityPropertyName)?.Type == typeof(int))
{
identityPropertyInteger = true;
}
for (int i = entities.Count - 1; i >= 0; i--)
{
if (identityPropertyInteger)
{
accessor[entities[i], identityPropertyName] = (int)lastRowIdScalar;
}
else
{
accessor[entities[i], identityPropertyName] = lastRowIdScalar;
}
lastRowIdScalar--;
}
}
}
finally
{
if (tableInfo.BulkConfig.SqliteTransaction == null)
{
transaction.Commit();
transaction.Dispose();
}
if (tableInfo.BulkConfig.SqliteConnection == null)
{
connection.Close();
}
}
}
else
{
throw new SqlProviderNotSupportedException(providerName);
}
}
public static async Task MergeAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress, CancellationToken cancellationToken) where T : class
{
string providerName = context.Database.ProviderName;
// -- SQL Server --
if (providerName.EndsWith(DbServer.SqlServer.ToString()))
{
tableInfo.InsertToTempTable = true;
await tableInfo.CheckHasIdentityAsync(context, 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(SqlBulkCopyOptions.KeepIdentity);
try
{
await InsertAsync(context, 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, entities, cancellationToken).ConfigureAwait(false);
}
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
if (tableInfo.CreatedOutputTable)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName), cancellationToken).ConfigureAwait(false);
}
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName), cancellationToken).ConfigureAwait(false);
}
if (keepIdentity && tableInfo.HasIdentity)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.SetIdentityInsert(tableInfo.FullTableName, false), cancellationToken).ConfigureAwait(false);
context.Database.CloseConnection();
}
}
}
// -- SQLite --
else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
{
var connection = await OpenAndGetSqliteConnectionAsync(context, tableInfo.BulkConfig, cancellationToken).ConfigureAwait(false);
var transaction = tableInfo.BulkConfig.SqliteTransaction ?? connection.BeginTransaction();
try
{
var command = GetSqliteCommand(context, entities, tableInfo, connection, transaction);
var typeAccessor = TypeAccessor.Create(typeof(T), true);
int rowsCopied = 0;
foreach (var item in entities)
{
LoadSqliteValues(tableInfo, typeAccessor, item, command);
await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
SetProgress(ref rowsCopied, entities.Count, tableInfo.BulkConfig, progress);
}
if (operationType != OperationType.Delete && tableInfo.BulkConfig.SetOutputIdentity && tableInfo.IdentityColumnName != null)
{
command.CommandText = SqlQueryBuilderSqlite.SelectLastInsertRowId();
long lastRowIdScalar = (long)await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false);
int lastRowId = (int)lastRowIdScalar;
var accessor = TypeAccessor.Create(typeof(T), true);
string identityPropertyName = tableInfo.PropertyColumnNamesDict.SingleOrDefault(a => a.Value == tableInfo.IdentityColumnName).Key;
for (int i = entities.Count - 1; i >= 0; i--)
{
accessor[entities[i], identityPropertyName] = lastRowId;
lastRowId--;
}
}
}
finally
{
if (tableInfo.BulkConfig.SqliteTransaction == null)
{
transaction.Commit();
transaction.Dispose();
}
if (tableInfo.BulkConfig.SqliteConnection == null)
{
connection.Close();
}
}
}
else
{
throw new SqlProviderNotSupportedException(providerName);
}
}
public static async Task InsertAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken)
{
string providerName = context.Database.ProviderName; // "Microsoft.EntityFrameworkCore.*****"
// -- SQL Server --
if (providerName.EndsWith(DbServer.SqlServer.ToString()))
{
var connection = await OpenAndGetSqlConnectionAsync(context, tableInfo.BulkConfig, cancellationToken).ConfigureAwait(false);
var transaction = context.Database.CurrentTransaction;
try
{
using (var sqlBulkCopy = GetSqlBulkCopy((SqlConnection)connection, transaction, tableInfo.BulkConfig))
{
bool useFastMember = tableInfo.HasOwnedTypes == false &&
tableInfo.ColumnNameContainsSquareBracket == false &&
tableInfo.ShadowProperties.Count == 0 &&
!tableInfo.ConvertibleProperties.Any() &&
!tableInfo.HasAbstractList &&
!tableInfo.BulkConfig.UseOnlyDataTable;
bool setColumnMapping = useFastMember;
tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
try
{
if (useFastMember)
{
using (var reader = ObjectReaderEx.Create(entities, tableInfo.ShadowProperties, tableInfo.ConvertibleProperties, context, tableInfo.PropertyColumnNamesDict.Keys.ToArray()))
{
await sqlBulkCopy.WriteToServerAsync(reader, cancellationToken).ConfigureAwait(false);
}
}
else
{
var dataTable = GetDataTable <T>(context, 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), cancellationToken).ConfigureAwait(false);
}
}
throw ex;
}
}
}
finally
{
if (transaction == null)
{
connection.Close();
}
}
}
// -- SQLite --
else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
{
var connection = await OpenAndGetSqliteConnectionAsync(context, tableInfo.BulkConfig, cancellationToken).ConfigureAwait(false);
var transaction = tableInfo.BulkConfig.SqliteTransaction ?? connection.BeginTransaction();
try
{
var command = GetSqliteCommand(context, entities, tableInfo, connection, transaction);
var typeAccessor = TypeAccessor.Create(typeof(T), true);
int rowsCopied = 0;
foreach (var item in entities)
{
LoadSqliteValues(tableInfo, typeAccessor, item, command);
await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
SetProgress(ref rowsCopied, entities.Count, tableInfo.BulkConfig, progress);
}
}
finally
{
if (tableInfo.BulkConfig.SqliteTransaction == null)
{
transaction.Commit();
transaction.Dispose();
}
if (tableInfo.BulkConfig.SqliteConnection == null)
{
connection.Close();
}
}
}
else
{
throw new SqlProviderNotSupportedException(providerName);
}
}
protected IQueryable <T> QueryOutputTable <T>(DbContext context) where T : class
{
var query = context.Set <T>().FromSql <T>(SqlQueryBuilder.SelectFromOutputTable(this));
return(OrderBy(query, this.PrimaryKeys[0]));
}
private static 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);
var dropTempTableIfExists = tableInfo.BulkConfig.UseTempDB;
if (dropTempTableIfExists)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB)).ConfigureAwait(false);
}
string providerName = context.Database.ProviderName;
// -- SQL Server --
if (providerName.EndsWith(DbServer.SqlServer.ToString()))
{
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 = previousPropertyColumnNamesDict;
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);
}
}
}
// -- Sqlite --
else if (providerName.EndsWith(DbServer.Sqlite.ToString()))
{
throw new NotImplementedException();
}
else
{
throw new SqlProviderNotSupportedException(providerName);
}
}
protected IQueryable <T> QueryOutputTable <T>(DbContext context) where T : class
{
return(context.Set <T>().FromSql(SqlQueryBuilder.SelectFromTable(this.FullTempOutputTableName, this.PrimaryKeys[0])));
}