public BatchUpdateCreateBodyData(
string baseSql,
DbContext dbContext,
IEnumerable <object> innerParameters,
IQueryable query,
Type rootType,
string tableAlias,
LambdaExpression updateExpression)
{
BaseSql = baseSql;
DatabaseType = SqlAdaptersMapping.GetDatabaseType(dbContext);
DbContext = dbContext;
Query = query;
RootInstanceParameterName = updateExpression.Parameters?.First()?.Name;
RootType = rootType;
TableAlias = tableAlias;
TableAliasesInUse = new List <string>();
UpdateColumnsSql = new StringBuilder();
UpdateExpression = updateExpression;
_tableInfoBulkConfig = new BulkConfig();
_tableInfoLookup = new Dictionary <Type, TableInfo>();
var tableInfo = TableInfo.CreateInstance(dbContext, rootType, Array.Empty <object>(), OperationType.Read, _tableInfoBulkConfig);
_tableInfoLookup.Add(rootType, tableInfo);
SqlParameters = new List <object>(innerParameters);
foreach (Match match in BatchUtil.TableAliasPattern.Matches(baseSql))
{
TableAliasesInUse.Add(match.Groups[2].Value);
}
}
public static (string, string, string, string, string, IEnumerable <object>) GetBatchSql(IQueryable query, DbContext context, bool isUpdate)
{
var sqlQueryBuilder = SqlAdaptersMapping.GetAdapterDialect(context);
var(fullSqlQuery, innerParameters) = query.ToParametrizedSql();
DbServer databaseType = SqlAdaptersMapping.GetDatabaseType(context);
var(leadingComments, sqlQuery) = SplitLeadingCommentsAndMainSqlQuery(fullSqlQuery);
string tableAlias = string.Empty;
string tableAliasSufixAs = string.Empty;
string topStatement = string.Empty;
(tableAlias, topStatement) = sqlQueryBuilder.GetBatchSqlReformatTableAliasAndTopStatement(sqlQuery);
int indexFROM = sqlQuery.IndexOf(Environment.NewLine);
string sql = sqlQuery.Substring(indexFROM, sqlQuery.Length - indexFROM);
sql = sql.Contains("{") ? sql.Replace("{", "{{") : sql; // Curly brackets have to be escaped:
sql = sql.Contains("}") ? sql.Replace("}", "}}") : sql; // https://github.com/aspnet/EntityFrameworkCore/issues/8820
if (isUpdate)
{
var extracted = sqlQueryBuilder.GetBatchSqlExtractTableAliasFromQuery(
sql, tableAlias, tableAliasSufixAs
);
tableAlias = extracted.TableAlias;
tableAliasSufixAs = extracted.TableAliasSuffixAs;
sql = extracted.Sql;
}
return(sql, tableAlias, tableAliasSufixAs, topStatement, leadingComments, innerParameters);
}
// In comment are Examples of how SqlQuery is changed for Sql Batch
// SELECT [a].[Column1], [a].[Column2], .../r/n
// FROM [Table] AS [a]/r/n
// WHERE [a].[Column] = FilterValue
// --
// DELETE [a]
// FROM [Table] AS [a]
// WHERE [a].[Columns] = FilterValues
public static (string, List <object>) GetSqlDelete(IQueryable query, DbContext context)
{
var(sql, tableAlias, _, topStatement, leadingComments, innerParameters) = GetBatchSql(query, context, isUpdate: false);
innerParameters = ReloadSqlParameters(context, innerParameters.ToList()); // Sqlite requires SqliteParameters
var databaseType = SqlAdaptersMapping.GetDatabaseType(context);
string resultQuery;
if (databaseType == DbServer.SqlServer)
{
tableAlias = $"[{tableAlias}]";
int outerQueryOrderByIndex = -1;
var useUpdateableCte = false;
var lastOrderByIndex = sql.LastIndexOf(Environment.NewLine + $"ORDER BY ", StringComparison.OrdinalIgnoreCase);
if (lastOrderByIndex > -1)
{
var subQueryEnd = sql.LastIndexOf($") AS {tableAlias}" + Environment.NewLine, StringComparison.OrdinalIgnoreCase);
if (subQueryEnd == -1 || lastOrderByIndex > subQueryEnd)
{
outerQueryOrderByIndex = lastOrderByIndex;
if (topStatement.Length > 0)
{
useUpdateableCte = true;
}
else
{
int offSetIndex = sql.LastIndexOf(Environment.NewLine + "OFFSET ", StringComparison.OrdinalIgnoreCase);
if (offSetIndex > outerQueryOrderByIndex)
{
useUpdateableCte = true;
}
}
}
}
if (useUpdateableCte)
{
var cte = "cte" + Guid.NewGuid().ToString().Substring(0, 8); // 8 chars of Guid as tableNameSuffix to avoid same name collision with other tables
resultQuery = $"{leadingComments}WITH [{cte}] AS (SELECT {topStatement}* {sql}) DELETE FROM [{cte}]";
}
else
{
if (outerQueryOrderByIndex > -1)
{
// ORDER BY is not allowed without TOP or OFFSET.
sql = sql.Substring(0, outerQueryOrderByIndex);
}
resultQuery = $"{leadingComments}DELETE {topStatement}{tableAlias}{sql}";
}
}
else
{
resultQuery = $"{leadingComments}DELETE {topStatement}{tableAlias}{sql}";
}
return(resultQuery, new List <object>(innerParameters));
}
// In comment are Examples of how SqlQuery is changed for Sql Batch
// SELECT [a].[Column1], [a].[Column2], .../r/n
// FROM [Table] AS [a]/r/n
// WHERE [a].[Column] = FilterValue
// --
// DELETE [a]
// FROM [Table] AS [a]
// WHERE [a].[Columns] = FilterValues
public static (string, List <object>) GetSqlDelete(IQueryable query, DbContext context)
{
(string sql, string tableAlias, string tableAliasSufixAs, string topStatement, string leadingComments, IEnumerable <object> innerParameters) = GetBatchSql(query, context, isUpdate: false);
innerParameters = ReloadSqlParameters(context, innerParameters.ToList()); // Sqlite requires SqliteParameters
tableAlias = (SqlAdaptersMapping.GetDatabaseType(context) == DbServer.SqlServer) ? $"[{tableAlias}]" : tableAlias;
var resultQuery = $"{leadingComments}DELETE {topStatement}{tableAlias}{sql}";
return(resultQuery, new List <object>(innerParameters));
}
/// <summary>
/// get Update Sql
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query"></param>
/// <param name="expression"></param>
/// <returns></returns>
public static (string, List <object>) GetSqlUpdate <T>(IQueryable <T> query, DbContext context, Type type, Expression <Func <T, T> > expression) where T : class
{
(string sql, string tableAlias, string tableAliasSufixAs, string topStatement, string leadingComments, IEnumerable <object> innerParameters) = GetBatchSql(query, context, isUpdate: true);
var createUpdateBodyData = new BatchUpdateCreateBodyData(sql, context, innerParameters, query, type, tableAlias, expression);
CreateUpdateBody(createUpdateBodyData, expression.Body);
var sqlParameters = ReloadSqlParameters(context, createUpdateBodyData.SqlParameters); // Sqlite requires SqliteParameters
var sqlColumns = (createUpdateBodyData.DatabaseType == DbServer.SQLServer)
? createUpdateBodyData.UpdateColumnsSql
: createUpdateBodyData.UpdateColumnsSql.Replace($"[{tableAlias}].", "");
var resultQuery = $"{leadingComments}UPDATE {topStatement}{tableAlias}{tableAliasSufixAs} SET {sqlColumns} {sql}";
if (resultQuery.Contains("ORDER") && resultQuery.Contains("TOP"))
{
string tableAliasPrefix = "[" + tableAlias + "].";
resultQuery = $"WITH C AS (SELECT {topStatement}*{sql}) UPDATE C SET {sqlColumns.Replace(tableAliasPrefix, "")}";
}
if (resultQuery.Contains("ORDER") && !resultQuery.Contains("TOP")) // When query has ORDER only without TOP(Take) then it is removed since not required and to avoid invalid Sql
{
resultQuery = resultQuery.Split("ORDER", StringSplitOptions.None)[0];
}
var databaseType = SqlAdaptersMapping.GetDatabaseType(context);
if (databaseType == DbServer.PostgreSQL)
{
resultQuery = SqlQueryBuilderPostgreSql.RestructureForBatch(resultQuery);
var npgsqlParameters = new List <object>();
foreach (var param in sqlParameters)
{
var npgsqlParam = new Npgsql.NpgsqlParameter(((SqlParameter)param).ParameterName, ((SqlParameter)param).Value);
string paramName = npgsqlParam.ParameterName.Replace("@", "");
var propertyType = type.GetProperties().SingleOrDefault(a => a.Name == paramName)?.PropertyType;
if (propertyType == typeof(System.Text.Json.JsonElement) || propertyType == typeof(System.Text.Json.JsonElement?))
{
npgsqlParam.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Jsonb;
}
npgsqlParameters.Add(npgsqlParam);
}
sqlParameters = npgsqlParameters;
}
return(resultQuery, sqlParameters);
}
// SELECT [a].[Column1], [a].[Column2], .../r/n
// FROM [Table] AS [a]/r/n
// WHERE [a].[Column] = FilterValue
// --
// UPDATE [a] SET [UpdateColumns] = N'updateValues'
// FROM [Table] AS [a]
// WHERE [a].[Columns] = FilterValues
public static (string, List <object>) GetSqlUpdate(IQueryable query, DbContext context, Type type, object updateValues, List <string> updateColumns)
{
var(sql, tableAlias, tableAliasSufixAs, topStatement, leadingComments, innerParameters) = GetBatchSql(query, context, isUpdate: true);
var sqlParameters = new List <object>(innerParameters);
string sqlSET = GetSqlSetSegment(context, updateValues.GetType(), updateValues, updateColumns, sqlParameters);
sqlParameters = ReloadSqlParameters(context, sqlParameters); // Sqlite requires SqliteParameters
var resultQuery = $"{leadingComments}UPDATE {topStatement}{tableAlias}{tableAliasSufixAs} {sqlSET}{sql}";
if (resultQuery.Contains("ORDER") && resultQuery.Contains("TOP"))
{
resultQuery = $"WITH C AS (SELECT {topStatement}*{sql}) UPDATE C {sqlSET}";
}
if (resultQuery.Contains("ORDER") && !resultQuery.Contains("TOP")) // When query has ORDER only without TOP(Take) then it is removed since not required and to avoid invalid Sql
{
resultQuery = resultQuery.Split("ORDER", StringSplitOptions.None)[0];
}
var databaseType = SqlAdaptersMapping.GetDatabaseType(context);
if (databaseType == DbServer.PostgreSQL)
{
resultQuery = SqlQueryBuilderPostgreSql.RestructureForBatch(resultQuery);
var npgsqlParameters = new List <object>();
foreach (var param in sqlParameters)
{
var npgsqlParam = new Npgsql.NpgsqlParameter(((SqlParameter)param).ParameterName, ((SqlParameter)param).Value);
string paramName = npgsqlParam.ParameterName.Replace("@", "");
var propertyType = type.GetProperties().SingleOrDefault(a => a.Name == paramName)?.PropertyType;
if (propertyType == typeof(System.Text.Json.JsonElement) || propertyType == typeof(System.Text.Json.JsonElement?)) // for JsonDocument works without fix
{
npgsqlParam.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Jsonb;
}
npgsqlParameters.Add(npgsqlParam);
}
sqlParameters = npgsqlParameters;
}
return(resultQuery, sqlParameters);
}
private static async Task ExecuteWithGraphAsync_Impl(DbContext context, IEnumerable <object> entities, OperationType operationType, BulkConfig bulkConfig, Action <decimal> progress, CancellationToken?cancellationToken, bool isAsync = true)
{
if (operationType != OperationType.Insert &&
operationType != OperationType.InsertOrUpdate &&
operationType != OperationType.InsertOrUpdateDelete &&
operationType != OperationType.Update)
{
throw new InvalidBulkConfigException($"{nameof(BulkConfig)}.{nameof(BulkConfig.IncludeGraph)} only supports Insert or Update operations.");
}
// Sqlite bulk merge adapter does not support multiple objects of the same type with a zero value primary key
if (SqlAdaptersMapping.GetDatabaseType(context) == DbServer.Sqlite)
{
throw new NotSupportedException("Sqlite is not currently supported due to its BulkInsert implementation.");
}
// If this is set to false, won't be able to propogate new primary keys to the relationships
bulkConfig.SetOutputIdentity = true;
// If this is set to false, wont' be able to support some code first model types as EFCore uses shadow properties when a relationship's foreign keys arent explicitly defined
bulkConfig.EnableShadowProperties = true;
var rootGraphItems = GraphUtil.GetOrderedGraph(context, entities);
if (rootGraphItems == null)
{
return;
}
// Inserting an entity graph must be done within a transaction otherwise the database could end up in a bad state
var hasExistingTransaction = context.Database.CurrentTransaction != null;
var transaction = context.Database.CurrentTransaction ?? (isAsync ? await context.Database.BeginTransactionAsync() : context.Database.BeginTransaction());
try
{
foreach (var actionGraphItem in rootGraphItems)
{
var entitiesToAction = GetUniqueEntities(context, actionGraphItem).ToList();
var tableInfo = TableInfo.CreateInstance(context, actionGraphItem.EntityClrType, entitiesToAction, operationType, bulkConfig);
if (isAsync)
{
await SqlBulkOperation.MergeAsync(context, actionGraphItem.EntityClrType, entitiesToAction, tableInfo, operationType, progress, cancellationToken.Value);
}
else
{
SqlBulkOperation.Merge(context, actionGraphItem.EntityClrType, entitiesToAction, tableInfo, operationType, progress);
}
// Loop through the dependants and update their foreign keys with the PK values of the just inserted / merged entities
foreach (var graphEntity in actionGraphItem.Entities)
{
var entity = graphEntity.Entity;
var parentEntity = graphEntity.ParentEntity;
// If the parent entity is null its the root type of the object graph.
if (parentEntity is null)
{
foreach (var navigation in actionGraphItem.Relationships)
{
// If this relationship requires the parents value to exist
if (navigation.ParentNavigation.IsDependentToPrincipal() == false)
{
foreach (var navGraphEntity in navigation.Entities)
{
if (navGraphEntity.ParentEntity != entity)
{
continue;
}
SetForeignKeyForRelationship(context, navigation.ParentNavigation,
navGraphEntity.Entity, entity);
}
}
}
}
else
{
var navigation = actionGraphItem.ParentNavigation;
if (navigation.IsDependentToPrincipal())
{
SetForeignKeyForRelationship(context, navigation,
dependent: parentEntity,
principal: entity);
}
else
{
SetForeignKeyForRelationship(context, navigation,
dependent: entity,
principal: parentEntity);
}
}
}
}
if (hasExistingTransaction == false)
{
if (isAsync)
{
await transaction.CommitAsync();
}
else
{
transaction.Commit();
}
}
}
finally
{
if (hasExistingTransaction == false)
{
if (isAsync)
{
await transaction.DisposeAsync();
}
else
{
transaction.Dispose();
}
}
}
}
private static async Task ExecuteWithGraphAsync(DbContext context, IEnumerable <object> entities, OperationType operationType, BulkConfig bulkConfig, Action <decimal> progress, CancellationToken cancellationToken, bool isAsync)
{
if (operationType != OperationType.Insert &&
operationType != OperationType.InsertOrUpdate &&
operationType != OperationType.InsertOrUpdateDelete &&
operationType != OperationType.Update)
{
throw new InvalidBulkConfigException($"{nameof(BulkConfig)}.{nameof(BulkConfig.IncludeGraph)} only supports Insert or Update operations.");
}
// Sqlite bulk merge adapter does not support multiple objects of the same type with a zero value primary key
if (SqlAdaptersMapping.GetDatabaseType(context) == DbServer.Sqlite)
{
throw new NotSupportedException("Sqlite is not currently supported due to its BulkInsert implementation.");
}
bulkConfig.PreserveInsertOrder = true; // Required for SetOutputIdentity ('true' is default but here explicitly assigned again in case it was changed to 'false' in BulkConfing)
bulkConfig.SetOutputIdentity = true; // If this is set to false, won't be able to propogate new primary keys to the relationships
// If this is set to false, wont' be able to support some code first model types as EFCore uses shadow properties when a relationship's foreign keys arent explicitly defined
bulkConfig.EnableShadowProperties = true;
var graphNodes = GraphUtil.GetTopologicallySortedGraph(context, entities);
if (graphNodes == null)
{
return;
}
// Inserting an entity graph must be done within a transaction otherwise the database could end up in a bad state
var hasExistingTransaction = context.Database.CurrentTransaction != null;
var transaction = context.Database.CurrentTransaction ?? (isAsync ? await context.Database.BeginTransactionAsync() : context.Database.BeginTransaction());
try
{
// Group the graph nodes by entity type so we can merge them into the database in batches, in the correct order of dependency (topological order)
var graphNodesGroupedByType = graphNodes.GroupBy(y => y.Entity.GetType());
foreach (var graphNodeGroup in graphNodesGroupedByType)
{
// It is possible the object graph contains duplicate entities (by primary key) but the entities are different object instances in memory.
// This an happen when deserializing a nested JSON tree for example. So filter out the duplicates.
var entitiesToAction = GetUniqueEntities(context, graphNodeGroup.Select(y => y.Entity)).ToList();
var entityClrType = graphNodeGroup.Key;
var tableInfo = TableInfo.CreateInstance(context, entityClrType, entitiesToAction, operationType, bulkConfig);
if (isAsync)
{
await SqlBulkOperation.MergeAsync(context, entityClrType, entitiesToAction, tableInfo, operationType, progress, cancellationToken);
}
else
{
SqlBulkOperation.Merge(context, entityClrType, entitiesToAction, tableInfo, operationType, progress);
}
// Set the foreign keys for dependents so they may be inserted on the next loop
var dependentsOfSameType = SetForeignKeysForDependentsAndYieldSameTypeDependents(context, entityClrType, graphNodeGroup).ToList();
// If there are any dependents of the same type (parent child relationship), then save those dependent entities again to commit the fk values
if (dependentsOfSameType.Any())
{
var dependentTableInfo = TableInfo.CreateInstance(context, entityClrType, dependentsOfSameType, operationType, bulkConfig);
if (isAsync)
{
await SqlBulkOperation.MergeAsync(context, entityClrType, dependentsOfSameType, dependentTableInfo, operationType, progress, cancellationToken);
}
else
{
SqlBulkOperation.Merge(context, entityClrType, dependentsOfSameType, dependentTableInfo, operationType, progress);
}
}
}
if (hasExistingTransaction == false)
{
if (isAsync)
{
await transaction.CommitAsync();
}
else
{
transaction.Commit();
}
}
}
finally
{
if (hasExistingTransaction == false)
{
if (isAsync)
{
await transaction.DisposeAsync();
}
else
{
transaction.Dispose();
}
}
}
}
/// <summary>
/// Common logic for two versions of GetDataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="context"></param>
/// <param name="type"></param>
/// <param name="entities"></param>
/// <param name="tableInfo"></param>
/// <returns></returns>
private static DataTable InnerGetDataTable <T>(DbContext context, ref Type type, IList <T> entities, TableInfo tableInfo)
{
var dataTable = new DataTable();
var columnsDict = new Dictionary <string, object>();
var ownedEntitiesMappedProperties = new HashSet <string>();
var databaseType = SqlAdaptersMapping.GetDatabaseType(context);
var isSqlServer = databaseType == DbServer.SQLServer;
var sqlServerBytesWriter = new SqlServerBytesWriter();
var objectIdentifier = tableInfo.ObjectIdentifier;
type = tableInfo.HasAbstractList ? entities[0].GetType() : type;
var entityType = context.Model.FindEntityType(type);
var entityTypeProperties = entityType.GetProperties();
var entityPropertiesDict = entityTypeProperties.Where(a => tableInfo.PropertyColumnNamesDict.ContainsKey(a.Name) ||
(tableInfo.BulkConfig.OperationType != OperationType.Read && a.Name == tableInfo.TimeStampPropertyName))
.ToDictionary(a => a.Name, a => a);
var entityNavigationOwnedDict = entityType.GetNavigations().Where(a => a.TargetEntityType.IsOwned()).ToDictionary(a => a.Name, a => a);
var entityShadowFkPropertiesDict = entityTypeProperties.Where(a => a.IsShadowProperty() &&
a.IsForeignKey() &&
a.GetContainingForeignKeys().FirstOrDefault()?.DependentToPrincipal?.Name != null)
.ToDictionary(x => x.GetContainingForeignKeys().First().DependentToPrincipal.Name, a => a);
var entityShadowFkPropertyColumnNamesDict = entityShadowFkPropertiesDict.ToDictionary(a => a.Key, a => a.Value.GetColumnName(objectIdentifier));
var shadowPropertyColumnNamesDict = entityPropertiesDict.Where(a => a.Value.IsShadowProperty()).ToDictionary(a => a.Key, a => a.Value.GetColumnName(objectIdentifier));
var properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
var discriminatorColumn = GetDiscriminatorColumn(tableInfo);
foreach (var property in properties)
{
var hasDefaultVauleOnInsert = tableInfo.BulkConfig.OperationType == OperationType.Insert && !tableInfo.BulkConfig.SetOutputIdentity && tableInfo.DefaultValueProperties.Contains(property.Name);
if (entityPropertiesDict.ContainsKey(property.Name))
{
var propertyEntityType = entityPropertiesDict[property.Name];
string columnName = propertyEntityType.GetColumnName(objectIdentifier);
var isConvertible = tableInfo.ConvertibleColumnConverterDict.ContainsKey(columnName);
var propertyType = isConvertible ? tableInfo.ConvertibleColumnConverterDict[columnName].ProviderClrType : property.PropertyType;
var underlyingType = Nullable.GetUnderlyingType(propertyType);
if (underlyingType != null)
{
propertyType = underlyingType;
}
if (isSqlServer && (propertyType == typeof(Geometry) || propertyType.IsSubclassOf(typeof(Geometry))))
{
propertyType = typeof(byte[]);
tableInfo.HasSpatialType = true;
if (tableInfo.BulkConfig.PropertiesToIncludeOnCompare != null || tableInfo.BulkConfig.PropertiesToIncludeOnCompare != null)
{
throw new InvalidOperationException("OnCompare properties Config can not be set for Entity with Spatial types like 'Geometry'");
}
}
if (isSqlServer && (propertyType == typeof(HierarchyId) || propertyType.IsSubclassOf(typeof(HierarchyId))))
{
propertyType = typeof(byte[]);
}
if (!columnsDict.ContainsKey(property.Name) && !hasDefaultVauleOnInsert)
{
dataTable.Columns.Add(columnName, propertyType);
columnsDict.Add(property.Name, null);
}
}
else if (entityShadowFkPropertiesDict.ContainsKey(property.Name))
{
var fk = entityShadowFkPropertiesDict[property.Name];
entityPropertiesDict.TryGetValue(fk.GetColumnName(objectIdentifier), out var entityProperty);
if (entityProperty == null) // BulkRead
{
continue;
}
var columnName = entityProperty.GetColumnName(objectIdentifier);
var propertyType = entityProperty.ClrType;
var underlyingType = Nullable.GetUnderlyingType(propertyType);
if (underlyingType != null)
{
propertyType = underlyingType;
}
if (propertyType == typeof(Geometry) && isSqlServer)
{
propertyType = typeof(byte[]);
}
if (propertyType == typeof(HierarchyId) && isSqlServer)
{
propertyType = typeof(byte[]);
}
if (!columnsDict.ContainsKey(columnName) && !hasDefaultVauleOnInsert)
{
dataTable.Columns.Add(columnName, propertyType);
columnsDict.Add(columnName, 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(x => x.ClrType == property.PropertyType && x.Name.StartsWith(entityType.Name + "." + property.Name + "#"));
}
var ownedEntityProperties = ownedEntityType.GetProperties().ToList();
var ownedEntityPropertyNameColumnNameDict = new Dictionary <string, string>();
foreach (var ownedEntityProperty in ownedEntityProperties)
{
if (!ownedEntityProperty.IsPrimaryKey())
{
string columnName = ownedEntityProperty.GetColumnName(objectIdentifier);
if (tableInfo.PropertyColumnNamesDict.ContainsValue(columnName))
{
ownedEntityPropertyNameColumnNameDict.Add(ownedEntityProperty.Name, columnName);
ownedEntitiesMappedProperties.Add(property.Name + "_" + ownedEntityProperty.Name);
}
}
}
var innerProperties = property.PropertyType.GetProperties();
if (!tableInfo.LoadOnlyPKColumn)
{
foreach (var innerProperty in innerProperties)
{
if (ownedEntityPropertyNameColumnNameDict.ContainsKey(innerProperty.Name))
{
var columnName = ownedEntityPropertyNameColumnNameDict[innerProperty.Name];
var propertyName = $"{property.Name}_{innerProperty.Name}";
if (tableInfo.ConvertibleColumnConverterDict.ContainsKey(propertyName))
{
var convertor = tableInfo.ConvertibleColumnConverterDict[propertyName];
var underlyingType = Nullable.GetUnderlyingType(convertor.ProviderClrType) ?? convertor.ProviderClrType;
dataTable.Columns.Add(columnName, underlyingType);
}
else
{
var ownedPropertyType = Nullable.GetUnderlyingType(innerProperty.PropertyType) ?? innerProperty.PropertyType;
dataTable.Columns.Add(columnName, ownedPropertyType);
}
columnsDict.Add(property.Name + "_" + innerProperty.Name, null);
}
}
}
}
}
if (tableInfo.BulkConfig.EnableShadowProperties)
{
foreach (var shadowProperty in entityPropertiesDict.Values.Where(a => a.IsShadowProperty()))
{
var columnName = shadowProperty.GetColumnName(objectIdentifier);
// If a model has an entity which has a relationship without an explicity defined FK, the data table will already contain the foreign key shadow property
if (dataTable.Columns.Contains(columnName))
{
continue;
}
var isConvertible = tableInfo.ConvertibleColumnConverterDict.ContainsKey(columnName);
var propertyType = isConvertible ? tableInfo.ConvertibleColumnConverterDict[columnName].ProviderClrType : shadowProperty.ClrType;
var underlyingType = Nullable.GetUnderlyingType(propertyType);
if (underlyingType != null)
{
propertyType = underlyingType;
}
if (isSqlServer && (propertyType == typeof(Geometry) || propertyType.IsSubclassOf(typeof(Geometry))))
{
propertyType = typeof(byte[]);
}
if (isSqlServer && (propertyType == typeof(HierarchyId) || propertyType.IsSubclassOf(typeof(HierarchyId))))
{
propertyType = typeof(byte[]);
}
dataTable.Columns.Add(columnName, propertyType);
columnsDict.Add(shadowProperty.Name, null);
}
}
if (discriminatorColumn != null)
{
var discriminatorProperty = entityPropertiesDict[discriminatorColumn];
dataTable.Columns.Add(discriminatorColumn, discriminatorProperty.ClrType);
columnsDict.Add(discriminatorColumn, entityType.GetDiscriminatorValue());
}
bool hasConverterProperties = tableInfo.ConvertiblePropertyColumnDict.Count > 0;
foreach (var entity in entities)
{
var propertiesToLoad = properties.Where(a => !tableInfo.AllNavigationsDictionary.ContainsKey(a.Name) || entityShadowFkPropertiesDict.ContainsKey(a.Name) || tableInfo.OwnedTypesDict.ContainsKey(a.Name)); // omit virtual Navigation (except Owned and ShadowNavig.) since it's Getter can cause unwanted Select-s from Db
foreach (var property in propertiesToLoad)
{
var propertyValue = tableInfo.FastPropertyDict.ContainsKey(property.Name) ? tableInfo.FastPropertyDict[property.Name].Get(entity) : null;
var hasDefaultVauleOnInsert = tableInfo.BulkConfig.OperationType == OperationType.Insert && !tableInfo.BulkConfig.SetOutputIdentity && tableInfo.DefaultValueProperties.Contains(property.Name);
if (tableInfo.BulkConfig.DateTime2PrecisionForceRound && isSqlServer && tableInfo.DateTime2PropertiesPrecisionLessThen7Dict.ContainsKey(property.Name))
{
DateTime dateTimePropertyValue = (DateTime)propertyValue;
int precision = tableInfo.DateTime2PropertiesPrecisionLessThen7Dict[property.Name];
int digitsToRemove = 7 - precision;
int powerOf10 = (int)Math.Pow(10, digitsToRemove);
long subsecondTicks = dateTimePropertyValue.Ticks % 10000000;
long ticksToRound = subsecondTicks + (subsecondTicks % 10 == 0 ? 1 : 0); // if ends with 0 add 1 tick to make sure rounding of value .5_zeros is rounded to Upper like SqlServer is doing, not to Even as Math.Round works
int roundedTicks = Convert.ToInt32(Math.Round((decimal)ticksToRound / powerOf10, 0)) * powerOf10;
dateTimePropertyValue = dateTimePropertyValue.AddTicks(-subsecondTicks).AddTicks(roundedTicks);
propertyValue = dateTimePropertyValue;
}
if (hasConverterProperties && tableInfo.ConvertiblePropertyColumnDict.ContainsKey(property.Name))
{
string columnName = tableInfo.ConvertiblePropertyColumnDict[property.Name];
propertyValue = tableInfo.ConvertibleColumnConverterDict[columnName].ConvertToProvider.Invoke(propertyValue);
}
if (tableInfo.HasSpatialType && propertyValue is Geometry geometryValue)
{
geometryValue.SRID = tableInfo.BulkConfig.SRID;
if (tableInfo.PropertyColumnNamesDict.ContainsKey(property.Name))
{
sqlServerBytesWriter.IsGeography = tableInfo.ColumnNamesTypesDict[tableInfo.PropertyColumnNamesDict[property.Name]] == "geography"; // "geography" type is default, otherwise it's "geometry" type
}
propertyValue = sqlServerBytesWriter.Write(geometryValue);
}
if (propertyValue is HierarchyId hierarchyValue && isSqlServer)
{
using MemoryStream memStream = new MemoryStream();
using BinaryWriter binWriter = new BinaryWriter(memStream);
hierarchyValue.Write(binWriter);
propertyValue = memStream.ToArray();
}
if (entityPropertiesDict.ContainsKey(property.Name) && !hasDefaultVauleOnInsert)
{
columnsDict[property.Name] = propertyValue;
}
else if (entityShadowFkPropertiesDict.ContainsKey(property.Name))
{
var foreignKeyShadowProperty = entityShadowFkPropertiesDict[property.Name];
var columnName = entityShadowFkPropertyColumnNamesDict[property.Name];
entityPropertiesDict.TryGetValue(columnName, out var entityProperty);
if (entityProperty == null) // BulkRead
{
continue;
}
columnsDict[columnName] = propertyValue == null ? null : foreignKeyShadowProperty.FindFirstPrincipal().PropertyInfo.GetValue(propertyValue); // TODO Check if can be optimized
}
else if (entityNavigationOwnedDict.ContainsKey(property.Name) && !tableInfo.LoadOnlyPKColumn)
{
var ownedProperties = property.PropertyType.GetProperties().Where(a => ownedEntitiesMappedProperties.Contains(property.Name + "_" + a.Name));
foreach (var ownedProperty in ownedProperties)
{
var columnName = $"{property.Name}_{ownedProperty.Name}";
var ownedPropertyValue = propertyValue == null ? null : tableInfo.FastPropertyDict[columnName].Get(propertyValue);
if (tableInfo.ConvertibleColumnConverterDict.ContainsKey(columnName))
{
var converter = tableInfo.ConvertibleColumnConverterDict[columnName];
columnsDict[columnName] = ownedPropertyValue == null ? null : converter.ConvertToProvider.Invoke(ownedPropertyValue);
}
else
{
columnsDict[columnName] = ownedPropertyValue;
}
}
}
}
if (tableInfo.BulkConfig.EnableShadowProperties)
{
foreach (var shadowPropertyName in shadowPropertyColumnNamesDict.Keys)
{
var shadowProperty = entityPropertiesDict[shadowPropertyName];
var columnName = shadowPropertyColumnNamesDict[shadowPropertyName];
var propertyValue = default(object);
if (tableInfo.BulkConfig.ShadowPropertyValue == null)
{
propertyValue = context.Entry(entity).Property(shadowPropertyName).CurrentValue;
}
else
{
propertyValue = tableInfo.BulkConfig.ShadowPropertyValue(entity, shadowPropertyName);
}
if (tableInfo.ConvertibleColumnConverterDict.ContainsKey(columnName))
{
propertyValue = tableInfo.ConvertibleColumnConverterDict[columnName].ConvertToProvider.Invoke(propertyValue);
}
columnsDict[shadowPropertyName] = propertyValue;
}
}
var record = columnsDict.Values.ToArray();
dataTable.Rows.Add(record);
}
return(dataTable);
}
