private static void Merge <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
{
var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context);
adapter.Merge(context, type, entities, tableInfo, operationType, progress);
}
public static string SelectCountIsUpdateFromOutputTable(TableInfo tableInfo)
{
var q = $"SELECT COUNT(*) FROM {tableInfo.FullTempOutputTableName} WHERE [IsUpdate] = 1";
return(q);
}
protected void CheckAndSetParametesForConvertibles(IEnumerable <object> innerParameters, TableInfo tableInfo) // fix for enum 'int' Conversion to nvarchar
{
foreach (var innerParameter in innerParameters)
{
string parameterColumnName = ((Microsoft.Data.SqlClient.SqlParameter)innerParameter).ParameterName.Replace("@__", ""); // @__column_N..
parameterColumnName = parameterColumnName.Contains("_") ? parameterColumnName.Substring(0, parameterColumnName.IndexOf("_")) : parameterColumnName; // column
parameterColumnName = parameterColumnName.ToLower();
foreach (var convertibleProperty in tableInfo.ConvertibleProperties)
{
if (convertibleProperty.Key.ToLower() == parameterColumnName)
{
if (convertibleProperty.Value.ProviderClrType.Name == nameof(String))
{
((Microsoft.Data.SqlClient.SqlParameter)innerParameter).DbType = System.Data.DbType.String;
}
}
}
}
}
public static string GetSqlSetSegment <T>(DbContext context, T updateValues, List <string> updateColumns, List <object> parameters) where T : class, new()
{
var tableInfo = TableInfo.CreateInstance <T>(context, new List <T>(), OperationType.Read, new BulkConfig());
return(GetSqlSetSegment(context, tableInfo, typeof(T), updateValues, new T(), updateColumns, parameters));
}
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.BulkConfig.SetOutputIdentity && tableInfo.HasIdentity)
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempOutputTableName, tableInfo, true)).ConfigureAwait(false);
}
try
{
await InsertAsync(context, entities, tableInfo, progress).ConfigureAwait(false);
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.MergeTable(tableInfo, operationType)).ConfigureAwait(false);
if (tableInfo.BulkConfig.SetOutputIdentity && tableInfo.HasIdentity)
{
try
{
await tableInfo.UpdateOutputIdentityAsync(context, entities).ConfigureAwait(false);
}
finally
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName)).ConfigureAwait(false);
}
}
}
finally
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName)).ConfigureAwait(false);
}
}
private static async Task MergeAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress, CancellationToken cancellationToken) where T : class
{
var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context);
await adapter.MergeAsync(context, type, entities, tableInfo, operationType, progress, cancellationToken);
}
public static void Read(DbContext context, Type type, IList <object> entities, TableInfo tableInfo, Action <decimal> progress)
{
Read <object>(context, type, entities, tableInfo, progress);
}
public static void Truncate(DbContext context, TableInfo tableInfo)
{
var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context);
adapter.Truncate(context, tableInfo);
}
public static async Task TruncateAsync(DbContext context, TableInfo tableInfo, CancellationToken cancellationToken)
{
var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context);
await adapter.TruncateAsync(context, tableInfo);
}
private static void Read <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) where T : class
{
var dropTempTableIfExists = tableInfo.BulkConfig.UseTempDB;
if (dropTempTableIfExists)
{
context.Database.ExecuteSqlRaw(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB));
}
var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context);
adapter.Read(context, type, entities, tableInfo, progress);
}
private static async Task ReadAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken) where T : class
{
var dropTempTableIfExists = tableInfo.BulkConfig.UseTempDB;
if (dropTempTableIfExists)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB), cancellationToken).ConfigureAwait(false);
}
var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context);
await adapter.ReadAsync(context, type, entities, tableInfo, progress, cancellationToken);
}
public static async Task InsertAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress)
{
var sqlConnection = await OpenAndGetSqlConnectionAsync(context);
var transaction = context.Database.CurrentTransaction;
try
{
using (var sqlBulkCopy = GetSqlBulkCopy(sqlConnection, transaction, tableInfo.BulkConfig.SqlBulkCopyOptions))
{
bool setColumnMapping = !tableInfo.HasOwnedTypes;
tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
try
{
if (!tableInfo.HasOwnedTypes)
{
using (var reader = ObjectReaderEx.Create(entities, tableInfo.ShadowProperties, tableInfo.ConvertibleProperties, context, tableInfo.PropertyColumnNamesDict.Keys.ToArray()))
{
await sqlBulkCopy.WriteToServerAsync(reader).ConfigureAwait(false);
}
}
else
{
var dataTable = GetDataTable <T>(context, entities, sqlBulkCopy);
await sqlBulkCopy.WriteToServerAsync(dataTable);
}
}
catch (InvalidOperationException ex)
{
if (!tableInfo.BulkConfig.UseTempDB)
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempOutputTableName));
}
if (ex.Message.Contains(ColumnMappingExceptionMessage))
{
await context.Database.ExecuteSqlCommandAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo));
}
throw ex;
}
}
}
finally
{
if (transaction == null)
{
sqlConnection.Close();
}
}
}
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 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 async Task MergeAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress, CancellationToken cancellationToken) where T : class
{
await MergeAsync <T>(context, typeof(T), entities, tableInfo, operationType, progress, cancellationToken).ConfigureAwait(false);
}
public static void Insert <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress)
{
Insert <T>(context, typeof(T), entities, tableInfo, progress);
}
public static async Task MergeAsync(DbContext context, Type type, IList <object> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress, CancellationToken cancellationToken)
{
await MergeAsync <object>(context, type, entities, tableInfo, operationType, progress, cancellationToken).ConfigureAwait(false);
}
private static void Insert <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress)
{
var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context);
adapter.Insert(context, type, entities, tableInfo, progress);
}
public static void Read <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress) where T : class
{
Read <T>(context, typeof(T), entities, tableInfo, progress);
}
public static async Task InsertAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken)
{
await InsertAsync <T>(context, typeof(T), entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
}
private static void AddSqlParameter(StringBuilder sqlColumns, List <object> sqlParameters, TableInfo tableInfo, string columnName, object value)
{
var parmName = $"param_{sqlParameters.Count}";
if (columnName != null && tableInfo.ConvertibleProperties.TryGetValue(columnName, out var valueConverter))
{
value = valueConverter.ConvertToProvider.Invoke(value);
}
// will rely on SqlClientHelper.CorrectParameterType to fix the type before executing
sqlParameters.Add(new Microsoft.Data.SqlClient.SqlParameter(parmName, value ?? DBNull.Value));
sqlColumns.Append($" @{parmName}");
}
private static async Task InsertAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken)
{
var adapter = SqlAdaptersMapping.CreateBulkOperationsAdapter(context);
await adapter.InsertAsync(context, type, entities, tableInfo, progress, cancellationToken);
}
public static string GetSqlSetSegment(DbContext context, Type updateValuesType, object updateValues, List <string> updateColumns, List <object> parameters)
{
var tableInfo = TableInfo.CreateInstance(context, updateValuesType, new List <object>(), OperationType.Read, new BulkConfig());
return(GetSqlSetSegment(context, tableInfo, updateValuesType, updateValues, Activator.CreateInstance(updateValuesType), updateColumns, parameters));
}
public static void Merge <T>(DbContext context, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress) where T : class
{
Merge <T>(context, typeof(T), entities, tableInfo, operationType, progress);
}
public static async Task InsertAsync <T>(DbContext context, IList <T> entities, TableInfo tableInfo, Action <decimal> progress)
{
var sqlConnection = await OpenAndGetSqlConnectionAsync(context);
var transaction = context.Database.CurrentTransaction;
try
{
using (var sqlBulkCopy = GetSqlBulkCopy(sqlConnection, transaction, tableInfo.BulkConfig.SqlBulkCopyOptions))
{
bool setColumnMapping = !tableInfo.HasOwnedTypes;
tableInfo.SetSqlBulkCopyConfig(sqlBulkCopy, entities, setColumnMapping, progress);
if (!tableInfo.HasOwnedTypes)
{
using (var reader = ObjectReaderEx.Create(entities, tableInfo.ShadowProperties, tableInfo.ConvertibleProperties, context, tableInfo.PropertyColumnNamesDict.Keys.ToArray()))
{
await sqlBulkCopy.WriteToServerAsync(reader).ConfigureAwait(false);
}
}
else
{
var dataTable = GetDataTable <T>(context, entities);
await sqlBulkCopy.WriteToServerAsync(dataTable);
}
}
}
finally
{
if (transaction == null)
{
sqlConnection.Close();
}
}
}
public static void Merge(DbContext context, Type type, IList <object> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress)
{
Merge <object>(context, type, entities, tableInfo, operationType, progress);
}
public static string CreateTableCopy(string existingTableName, string newTableName, TableInfo tableInfo, bool isOutputTable = false)
{
// TODO: (optionaly) if CalculateStats = True but SetOutputIdentity = False then Columns could be ommited from Create and from MergeOutput
List <string> columnsNames = (isOutputTable ? tableInfo.OutputPropertyColumnNamesDict : tableInfo.PropertyColumnNamesDict).Values.ToList();
if (tableInfo.TimeStampColumnName != null)
{
columnsNames.Remove(tableInfo.TimeStampColumnName);
}
string isUpdateStatsColumn = (tableInfo.BulkConfig.CalculateStats && isOutputTable) ? ",[IsUpdate] = CAST(0 AS bit)" : "";
var q = $"SELECT TOP 0 {GetCommaSeparatedColumns(columnsNames, "T")} " + isUpdateStatsColumn +
$"INTO {newTableName} FROM {existingTableName} AS T " +
$"LEFT JOIN {existingTableName} AS Source ON 1 = 0;"; // removes Identity constrain
return(q);
}
internal static DataTable GetDataTable <T>(DbContext context, IList <T> entities, SqlBulkCopy sqlBulkCopy, TableInfo tableInfo)
{
var dataTable = new DataTable();
var columnsDict = new Dictionary <string, object>();
var ownedEntitiesMappedProperties = new HashSet <string>();
var type = typeof(T);
var entityType = context.Model.FindEntityType(type);
var entityPropertiesDict = entityType.GetProperties().Where(a => tableInfo.PropertyColumnNamesDict.ContainsKey(a.Name)).ToDictionary(a => a.Name, a => a);
var entityNavigationOwnedDict = entityType.GetNavigations().Where(a => a.GetTargetType().IsOwned()).ToDictionary(a => a.Name, a => a);
var properties = type.GetProperties();
var discriminatorColumn = tableInfo.ShadowProperties.Count == 0 ? null : tableInfo.ShadowProperties.ElementAt(0);
foreach (var property in properties)
{
if (entityPropertiesDict.ContainsKey(property.Name))
{
var relational = entityPropertiesDict[property.Name].Relational();
string columnName = relational.ColumnName;
var propertyType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
if (propertyType.IsEnum && relational.ColumnType.StartsWith("nvarchar")) // temporary fix for EnumToStringConverter when DataTables are used
{ // TODO replace with checking somehow for DataConverters (not found in Relational)
propertyType = typeof(string);
}
dataTable.Columns.Add(columnName, propertyType);
columnsDict.Add(property.Name, null);
}
else if (entityNavigationOwnedDict.ContainsKey(property.Name)) // isOWned
{
Type navOwnedType = type.Assembly.GetType(property.PropertyType.FullName);
var ownedEntityType = context.Model.FindEntityType(property.PropertyType);
if (ownedEntityType == null)
{
ownedEntityType = context.Model.GetEntityTypes().SingleOrDefault(a => a.DefiningNavigationName == property.Name && a.DefiningEntityType.Name == entityType.Name);
}
var ownedEntityProperties = ownedEntityType.GetProperties().ToList();
var ownedEntityPropertyNameColumnNameDict = new Dictionary <string, string>();
foreach (var ownedEntityProperty in ownedEntityProperties)
{
if (!ownedEntityProperty.IsPrimaryKey())
{
string columnName = ownedEntityProperty.Relational().ColumnName;
ownedEntityPropertyNameColumnNameDict.Add(ownedEntityProperty.Name, columnName);
ownedEntitiesMappedProperties.Add(property.Name + "_" + ownedEntityProperty.Name);
}
}
var innerProperties = property.PropertyType.GetProperties();
foreach (var innerProperty in innerProperties)
{
if (ownedEntityPropertyNameColumnNameDict.ContainsKey(innerProperty.Name))
{
string columnName = ownedEntityPropertyNameColumnNameDict[innerProperty.Name];
var ownedPropertyType = Nullable.GetUnderlyingType(innerProperty.PropertyType) ?? innerProperty.PropertyType;
dataTable.Columns.Add(columnName, ownedPropertyType);
columnsDict.Add(property.Name + "_" + innerProperty.Name, null);
}
}
}
}
if (discriminatorColumn != null)
{
dataTable.Columns.Add(discriminatorColumn, typeof(string));
columnsDict.Add(discriminatorColumn, typeof(T).Name);
}
foreach (var entity in entities)
{
foreach (var property in properties)
{
var propertyValue = property.GetValue(entity, null);
if (entityPropertiesDict.ContainsKey(property.Name))
{
columnsDict[property.Name] = propertyValue;
}
else if (entityNavigationOwnedDict.ContainsKey(property.Name))
{
var ownedProperties = property.PropertyType.GetProperties().Where(a => ownedEntitiesMappedProperties.Contains(property.Name + "_" + a.Name));
foreach (var ownedProperty in ownedProperties)
{
columnsDict[property.Name + "_" + ownedProperty.Name] = propertyValue == null ? null : ownedProperty.GetValue(propertyValue, null);
}
}
}
var record = columnsDict.Values.ToArray();
dataTable.Rows.Add(record);
}
foreach (DataColumn item in dataTable.Columns) //Add mapping
{
sqlBulkCopy.ColumnMappings.Add(item.ColumnName, item.ColumnName);
}
return(dataTable);
}
