internal List <MappingInfo> ConvertFragmentToMapping(
DbContext context,
Type type,
MappingFragment mappingFragment,
EntityType entityType)
{
var tableEntitySet = mappingFragment.StoreEntitySet;
var tableName = (tableEntitySet.MetadataProperties["Table"].Value ?? tableEntitySet.Name).ToString();
var columnsInfo = GetColumnsInfo(context, tableName);
var innerList = mappingFragment.PropertyMappings
.OfType <ScalarPropertyMapping>()
.Select(x =>
{
var columnInfo = columnsInfo.FirstOrDefault(c => c.ColumnName == x.Column.Name);
if (columnInfo == null)
{
throw new InvalidOperationException(
$"Column '{x.Column.Name}' (mapped to: '{x.Property.DeclaringType.Name}.{x.Property.Name}') is not found");
}
var isDbGenerated = x.Column.IsStoreGeneratedComputed || x.Column.IsStoreGeneratedIdentity;
return(new MappingInfo()
{
TableName = tableName,
TableNameQualified = NpgsqlHelper.GetQualifiedName(tableName, tableEntitySet.Schema),
Property = type.GetProperty(x.Property.Name,
BindingFlags.NonPublic | BindingFlags.Public |
BindingFlags.GetProperty | BindingFlags.Instance),
ColumnInfo = columnInfo,
IsKey = entityType.KeyProperties.Any(y => y.Name == x.Property.Name),
DoUpdate = !isDbGenerated,
DoInsert = !isDbGenerated
});
}).ToList();
return(innerList);
}
internal static List <MappingInfo> GetMetadata(DbContext context, Type type)
{
var metadata = context.Model;
var entityType = metadata.GetEntityTypes().Single(x => x.ClrType == type);
var tableName = GetTableName(context, type);
var columnsInfo = GetColumnsInfo(context, tableName);
if (entityType.BaseType != null)
{
var baseTableName = GetTableName(context, entityType.BaseType.ClrType);
if (baseTableName != tableName)
{
var extraColumnsInfo = GetColumnsInfo(context, baseTableName);
columnsInfo.AddRange(extraColumnsInfo);
}
}
var innerList = entityType.GetProperties()
.Where(x => x.PropertyInfo != null)
.Select(x =>
{
var relational = x.DeclaringEntityType.Relational();
return(new MappingInfo()
{
TableName = relational.TableName,
TableNameQualified = NpgsqlHelper.GetQualifiedName(relational.TableName, relational.Schema),
Property = x.PropertyInfo,
ColumnInfo = columnsInfo.First(c => c.ColumnName == x.Relational().ColumnName),
IsDbGenerated = x.ValueGenerated != ValueGenerated.Never,
IsKey = x.IsKey(),
IsInheritanceUsed = entityType.BaseType != null
});
}).ToList();
return(innerList);
}
private List <MappingInfo> GetMappingInfo(Type type, string tableName)
{
var mappings = NpgsqlHelper.GetMetadata(context, type);
mappings.ForEach(x =>
{
var sourceAttribute = x.Property?.GetCustomAttribute <BulkMappingSourceAttribute>();
var modifiers = x.Property?.GetCustomAttributes <BulkOperationModifierAttribute>();
x.ModifierAttributes = modifiers?.ToList() ?? new List <BulkOperationModifierAttribute>();
x.OverrideSourceMethod = GetOverrideSouceFunc(type, sourceAttribute?.PropertyName);
x.NpgsqlType = GetNpgsqlType(x.ColumnInfo);
x.TempAliasedColumnName = $"{x.TableName}_{x.ColumnInfo.ColumnName}".ToLower();
x.QualifiedColumnName = $"{NpgsqlHelper.GetQualifiedName(x.TableName)}.{NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)}";
});
return(mappings);
}
private EntityInfo CreateEntityInfo <T>(
string tableName,
string tableNameQualified,
List <MappingInfo> mappingInfo,
NpgsqlBulkCodeBuilder <T> codeBuilderOuter = null)
{
var codeBuilder = codeBuilderOuter ?? new NpgsqlBulkCodeBuilder <T>();
var info = new EntityInfo()
{
TableNameQualified = tableNameQualified,
TableName = tableName,
CodeBuilder = codeBuilder,
MappingInfos = mappingInfo,
PropToMappingInfo = mappingInfo.Where(x => x.Property != null).ToDictionary(x => x.Property.Name),
TableNames = mappingInfo.Select(x => x.TableName).Distinct().ToArray(),
InsertClientDataInfos = mappingInfo.Where(x => x.DoInsert).ToArray(),
UpdateClientDataWithKeysInfos = mappingInfo.Where(x => x.DoUpdate || x.IsKey).ToArray(),
MaxIsOptionalFlag = mappingInfo.Max(x => x.IsSpecifiedFlag)
};
#if EFCore
info.PropertyToGenerators = mappingInfo
.Where(x => x.LocalGenerator != null)
.ToDictionary(x => x.DbProperty, x => x.LocalGenerator);
info.PropertyNameToGenerators = info.PropertyToGenerators.ToDictionary(x => x.Key.Name, x => x.Value);
#endif
var tableNames = mappingInfo.Select(x => x.TableNameQualified).Distinct().ToList();
//var grouppedByTables = mappingInfo.GroupBy(x => x.TableName)
// .Select(x => new
// {
// TableName = x.Key,
// x.First().TableNameQualified,
// KeyInfos = x.Where(y => y.IsKey).ToList(),
// ClientDataInfos = x.Where(y => y.DoInsert).ToList(),
// ReturningInfos = x.Where(y => y.ReadBack).ToList()
// })
// .ToList();
info.InsertQueryParts = new ConcurrentDictionary <long, List <InsertQueryParts> >();
info.InsertQueryParts[0] = GetInsertQueryParts(mappingInfo, 0);
//info.InsertQueryParts = grouppedByTables.Select(x =>
//{
// var others = grouppedByTables.Where(y => y.TableName != x.TableName)
// .SelectMany(y => y.ClientDataInfos)
// .Select(y => new
// {
// My = y,
// Others = x.ReturningInfos.FirstOrDefault(ri => ri.Property.Name == y.Property.Name)
// })
// .Where(y => y.Others != null)
// .ToList();
// return new InsertQueryParts()
// {
// TableName = x.TableName,
// TableNameQualified = x.TableNameQualified,
// TargetColumnNamesQueryPart = string.Join(", ", x.ClientDataInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName))),
// SourceColumnNamesQueryPart = string.Join(", ", x.ClientDataInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.TempAliasedColumnName))),
// Returning = string.Join(", ", x.ReturningInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName))),
// ReturningSetQueryPart = string.Join(", ", others.Select(y => $"{NpgsqlHelper.GetQualifiedName(y.My.TempAliasedColumnName)} " +
// $" = source.{NpgsqlHelper.GetQualifiedName(y.Others.ColumnInfo.ColumnName)}"))
// };
//}).ToList();
info.SelectSourceForInsertQuery = "SELECT " +
string.Join(", ", info.InsertClientDataInfos
.Select(x => $"{x.QualifiedColumnName} AS {x.TempAliasedColumnName}")) +
" FROM " + string.Join(", ", tableNames);
info.CopyColumnsForInsertQueryPart = string.Join(", ", info.InsertClientDataInfos
.Select(x => x.TempAliasedColumnName));
info.InsertDbGeneratedInfos = info.MappingInfos.Where(x => x.ReadBack && x.Property != null).ToArray();
// Now time for updates
var grouppedByTables = mappingInfo.GroupBy(x => x.TableName)
.Select(x => new
{
TableName = x.Key,
x.First().TableNameQualified,
KeyInfos = x.Where(y => y.IsKey).ToList(),
ClientDataInfos = x.Where(y => y.DoUpdate).ToList(),
ReturningInfos = x.Where(y => y.ReadBack).ToList()
})
.ToList();
info.UpdateQueryParts = grouppedByTables.Select(x =>
{
var updateableInfos = x.ClientDataInfos;
updateableInfos = updateableInfos
.Where(y => y.DoUpdate)
.Where(y => y.ModifierAttributes == null || y.ModifierAttributes.All(m => m.Modification != BulkOperationModification.IgnoreForUpdate))
.ToList();
return(new UpdateQueryParts()
{
TableName = x.TableName,
TableNameQualified = x.TableNameQualified,
SetClause = string.Join(", ", updateableInfos.Select(y =>
{
var colName = NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName);
return $"{colName} = source.{y.TempAliasedColumnName}";
})),
WhereClause = string.Join(" AND ", x.KeyInfos.Select(y =>
{
var colName = NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName);
var clause = $"{colName} = source.{y.TempAliasedColumnName}";
if (y.IsNullableInClr)
{
clause = $"({clause} OR ({colName} IS NULL AND source.{y.TempAliasedColumnName} IS NULL))";
}
return clause;
}))
});
})
.Where(x => !string.IsNullOrEmpty(x.SetClause))
.ToList();
info.SelectSourceForUpdateQuery = "SELECT " +
string.Join(", ", info.UpdateClientDataWithKeysInfos
.Select(x => $"{x.QualifiedColumnName} AS {x.TempAliasedColumnName}")) +
" FROM " + string.Join(", ", grouppedByTables.Select(x => x.TableNameQualified));
info.CopyColumnsForUpdateQueryPart = string.Join(", ", info.UpdateClientDataWithKeysInfos
.Select(x => x.TempAliasedColumnName));
info.UpdateDbGeneratedInfos = info.MappingInfos.Where(x => x.ReadBack && x.Property != null).ToArray();
// Rest info
info.KeyInfos = info.MappingInfos.Where(x => x.IsKey).ToArray();
info.KeyColumnNames = info.KeyInfos.Select(x => x.ColumnInfo.ColumnName).ToArray();
if (codeBuilderOuter == null)
{
codeBuilder.InitBuilder(info, ReadValue);
}
return(info);
}
private List <InsertQueryParts> GetInsertQueryParts(
List <MappingInfo> mappingInfo,
long optionalFlags)
{
var grouppedByTables = mappingInfo
.GroupBy(x => x.TableName)
.Select(x => new
{
TableName = x.Key,
x.First().TableNameQualified,
KeyInfos = x.Where(y => y.IsKey).ToList(),
ClientDataInfos = x.Where(y => y.DoInsert &&
(y.IsSpecifiedFlag == 0 || (y.IsSpecifiedFlag & optionalFlags) > 0)).ToList(),
ReturningInfos = x.Where(y => y.ReadBack).ToList()
})
.ToList();
return(grouppedByTables.Select(x =>
{
var others = grouppedByTables.Where(y => y.TableName != x.TableName)
.SelectMany(y => y.ClientDataInfos)
.Select(y => new
{
My = y,
Others = x.ReturningInfos.FirstOrDefault(ri => ri.Property.Name == y.Property.Name)
})
.Where(y => y.Others != null)
.ToList();
return new InsertQueryParts()
{
TableName = x.TableName,
TableNameQualified = x.TableNameQualified,
TargetColumnNamesQueryPart = string.Join(", ", x.ClientDataInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName))),
SourceColumnNamesQueryPart = string.Join(", ", x.ClientDataInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.TempAliasedColumnName))),
Returning = string.Join(", ", x.ReturningInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName))),
ReturningSetQueryPart = string.Join(", ", others.Select(y => $"{NpgsqlHelper.GetQualifiedName(y.My.TempAliasedColumnName)} " +
$" = source.{NpgsqlHelper.GetQualifiedName(y.Others.ColumnInfo.ColumnName)}"))
};
}).ToList());
}
internal static List <MappingInfo> GetMetadata(DbContext context, Type type)
{
var metadata = context.Model;
var entityType = metadata.GetEntityTypes().Single(x => x.ClrType == type);
var tableName = GetTableName(context, type);
var columnsInfo = NpgsqlBulkUploader.RelationalHelper.GetColumnsInfo(context, tableName);
if (entityType.BaseType != null)
{
var baseTableName = GetTableName(context, entityType.BaseType.ClrType);
if (baseTableName != tableName)
{
var extraColumnsInfo = NpgsqlBulkUploader.RelationalHelper.GetColumnsInfo(context, baseTableName);
columnsInfo.AddRange(extraColumnsInfo);
}
}
int optinalIndex = 0;
var innerList = entityType.GetProperties()
.Select(x =>
{
var relational = x.DeclaringEntityType;
ValueGenerator localGenerator = null;
bool isDbGenerated = false;
var generatorFactory = x.GetAnnotations().FirstOrDefault(a => a.Name == "ValueGeneratorFactory");
if (generatorFactory != null)
{
var valueGeneratorAccessor = generatorFactory.Value as Func <IProperty, IEntityType, ValueGenerator>;
localGenerator = valueGeneratorAccessor(x, x.DeclaringEntityType);
}
else if (x.GetAnnotations().Any(y => y.Name == "Relational:ComputedColumnSql"))
{
isDbGenerated = true;
}
else if (x.GetAnnotations().Any(y => y.Name == "Npgsql:ValueGenerationStrategy"))
{
isDbGenerated = true;
}
var indexes = ((Microsoft.EntityFrameworkCore.Metadata.Internal.Property)x).PropertyIndexes;
long optionalFlag = 0;
if (indexes.StoreGenerationIndex >= 0 && localGenerator == null)
{
optionalFlag = 1 << optinalIndex;
optinalIndex++;
}
return(new MappingInfo()
{
TableName = relational.GetTableName(),
TableNameQualified = NpgsqlHelper.GetQualifiedName(relational.GetTableName(), relational.GetSchema()),
Property = x.PropertyInfo,
ColumnInfo = columnsInfo.First(c => c.ColumnName == x.GetColumnName()),
LocalGenerator = localGenerator,
ValueConverter = x.GetValueConverter(),
IsKey = x.IsKey(),
IsInheritanceUsed = entityType.BaseType != null,
DbProperty = x,
DoUpdate = !isDbGenerated && x.PropertyInfo != null, // don't update shadow props
DoInsert = !isDbGenerated, // do insert of shadow props
ReadBack = indexes.StoreGenerationIndex >= 0,
IsSpecifiedFlag = optionalFlag
});
}).ToList();
return(innerList);
}
private List <ColumnInfo> GetTableSchema(string tableName)
{
return(NpgsqlHelper.GetColumnsInfo(context, tableName));
}
private async Task UpdateAsyncInternal <T>(IEnumerable <T> entities, EntityInfo mapping)
{
var conn = NpgsqlHelper.GetNpgsqlConnection(context);
var connOpenedHere = await EnsureConnectedAsync(conn);
var transaction = NpgsqlHelper.EnsureOrStartTransaction(context, DefaultIsolationLevel);
try
{
// 0. Prepare variables
var dataColumns = mapping.ClientDataWithKeysColumnNames;
var tableName = mapping.TableNameQualified;
var tempTableName = GetUniqueName("_temp_");
var codeBuilder = (NpgsqlBulkCodeBuilder <T>)mapping.CodeBuilder;
// 1. Create temp table
var sql = $"CREATE TEMP TABLE {tempTableName} ON COMMIT DROP AS {mapping.SelectSourceForUpdateQuery} LIMIT 0";
//var sql = $"CREATE TABLE {tempTableName} AS {mapping.SelectSourceForUpdateQuery} LIMIT 0";
#if NETSTANDARD2_1
await context.Database.ExecuteSqlRawAsync(sql);
#else
#pragma warning disable EF1000 // Possible SQL injection vulnerability.
await context.Database.ExecuteSqlCommandAsync(sql);
#pragma warning restore EF1000 // Possible SQL injection vulnerability.
#endif
// 2. Import into temp table
using (var importer = conn.BeginBinaryImport($"COPY {tempTableName} ({mapping.CopyColumnsForUpdateQueryPart}) FROM STDIN (FORMAT BINARY)"))
{
foreach (var item in entities)
{
importer.StartRow();
codeBuilder.ClientDataWithKeyWriterAction(item, importer);
}
importer.Complete();
}
#pragma warning disable EF1000 // Possible SQL injection vulnerability.
// 3. Insert into real table from temp one
foreach (var part in mapping.UpdateQueryParts)
{
// 3.a Needs to accure lock
if (LockLevelOnUpdate != TableLockLevel.NoLock)
{
sql = $"LOCK TABLE {part.TableNameQualified} IN {LockLevelToString(LockLevelOnUpdate)} MODE;";
#if NETSTANDARD2_1
await context.Database.ExecuteSqlRawAsync(sql);
#else
await context.Database.ExecuteSqlCommandAsync(sql);
#endif
}
sql = $"UPDATE {part.TableNameQualified} SET {part.SetClause} FROM {tempTableName} as source WHERE {part.WhereClause}";
#if NETSTANDARD2_1
await context.Database.ExecuteSqlRawAsync(sql);
#else
await context.Database.ExecuteSqlCommandAsync(sql);
#endif
}
#pragma warning restore EF1000 // Possible SQL injection vulnerability.
// 5. Commit
transaction?.Commit();
}
catch
{
try
{
transaction?.Rollback();
}
catch { }
throw;
}
finally
{
if (connOpenedHere)
{
conn.Close();
}
}
}
public async Task InsertAsync <T>(IEnumerable <T> entities, InsertConflictAction onConflict)
{
var conn = NpgsqlHelper.GetNpgsqlConnection(context);
var connOpenedHere = await EnsureConnectedAsync(conn);
var transaction = NpgsqlHelper.EnsureOrStartTransaction(context, DefaultIsolationLevel);
var mapping = GetEntityInfo <T>();
var ignoreDuplicatesStatement = onConflict?.GetSql(mapping);
try
{
// 0. Prepare variables
var tempTableName = GetUniqueName("_temp_");
var list = entities.ToList();
var codeBuilder = (NpgsqlBulkCodeBuilder <T>)mapping.CodeBuilder;
// 1. Create temp table
var sql = $"CREATE TEMP TABLE {tempTableName} ON COMMIT DROP AS {mapping.SelectSourceForInsertQuery} LIMIT 0";
//var sql = $"CREATE {tempTableName} AS {mapping.SelectSourceForInsertQuery} LIMIT 0";
#if NETSTANDARD2_1
await context.Database.ExecuteSqlRawAsync(sql);
sql = $"ALTER TABLE {tempTableName} ADD COLUMN __index integer";
await context.Database.ExecuteSqlRawAsync(sql);
#else
#pragma warning disable EF1000 // Possible SQL injection vulnerability.
await context.Database.ExecuteSqlCommandAsync(sql);
sql = $"ALTER TABLE {tempTableName} ADD COLUMN __index integer";
await context.Database.ExecuteSqlCommandAsync(sql);
#pragma warning restore EF1000 // Possible SQL injection vulnerability.
#endif
#if NETSTANDARD1_5 || NETSTANDARD2_0 || NETSTANDARD2_1
SetAutoGeneratedFields(list, mapping, codeBuilder);
#endif
// 2. Import into temp table
using (var importer = conn.BeginBinaryImport($"COPY {tempTableName} ({mapping.CopyColumnsForInsertQueryPart}, __index) FROM STDIN (FORMAT BINARY)"))
{
var index = 1;
foreach (var item in list)
{
importer.StartRow();
codeBuilder.ClientDataWriterAction(item, importer);
importer.Write(index, NpgsqlDbType.Integer);
index++;
}
importer.Complete();
}
// 3. Insert into real table from temp one
foreach (var insertPart in mapping.InsertQueryParts)
{
using (var cmd = conn.CreateCommand())
{
var baseInsertCmd = $"INSERT INTO {insertPart.TableNameQualified} ({insertPart.TargetColumnNamesQueryPart}) " +
$"SELECT {insertPart.SourceColumnNamesQueryPart} FROM {tempTableName} ORDER BY __index {ignoreDuplicatesStatement}";
if (string.IsNullOrEmpty(insertPart.ReturningSetQueryPart))
{
cmd.CommandText = baseInsertCmd;
if (!string.IsNullOrEmpty(insertPart.Returning))
{
cmd.CommandText = $"WITH inserted as (\n {baseInsertCmd} RETURNING {insertPart.Returning} \n ), \n";
cmd.CommandText += $"source as (\n SELECT *, ROW_NUMBER() OVER (ORDER BY {insertPart.Returning}) as __index FROM inserted \n ) \n";
cmd.CommandText += $"SELECT * FROM source ORDER BY __index";
}
}
else
{
cmd.CommandText = $"WITH inserted as (\n {baseInsertCmd} RETURNING {insertPart.Returning} \n ), \n";
cmd.CommandText += $"source as (\n SELECT *, ROW_NUMBER() OVER (ORDER BY {insertPart.Returning}) as __index FROM inserted \n ) \n";
cmd.CommandText += $"UPDATE {tempTableName} SET {insertPart.ReturningSetQueryPart} FROM source WHERE {tempTableName}.__index = source.__index\n";
cmd.CommandText += $"RETURNING {insertPart.Returning}";
}
using (var reader = (NpgsqlDataReader)(await cmd.ExecuteReaderAsync()))
{
// 4. Propagate computed value
if (!string.IsNullOrEmpty(insertPart.Returning))
{
if (onConflict == null)
{
var readAction = codeBuilder.IdentityValuesWriterActions[insertPart.TableName];
foreach (var item in list)
{
await reader.ReadAsync();
readAction(item, reader);
}
}
else
{
while (await reader.ReadAsync())
{
// do nothing, for now...
}
}
}
}
}
}
// 5. Commit
transaction?.Commit();
}
catch
{
try
{
transaction?.Rollback();
}
catch { }
throw;
}
finally
{
if (connOpenedHere)
{
conn.Close();
}
}
}
private EntityInfo CreateEntityInfo <T>(
string tableName,
string tableNameQualified,
List <MappingInfo> mappingInfo,
NpgsqlBulkCodeBuilder <T> codeBuilderOuter = null)
{
var codeBuilder = codeBuilderOuter ?? new NpgsqlBulkCodeBuilder <T>();
var info = new EntityInfo()
{
TableNameQualified = tableNameQualified,
TableName = tableName,
CodeBuilder = codeBuilder,
MappingInfos = mappingInfo,
PropToMappingInfo = mappingInfo.ToDictionary(x => x.Property),
TableNames = mappingInfo.Select(x => x.TableName).Distinct().ToArray(),
ClientDataInfos = mappingInfo.Where(x => !x.IsDbGenerated).ToArray(),
ClientDataWithKeysInfos = mappingInfo.Where(x => !x.IsDbGenerated || x.IsKey).ToArray()
};
#if NETSTANDARD1_5 || NETSTANDARD2_0 || NETSTANDARD2_1
info.PropertyToGenerators = mappingInfo.Where(x => x.LocalGenerator != null).ToDictionary(x => x.Property, x => x.LocalGenerator);
info.PropertyNameToGenerators = info.PropertyToGenerators.ToDictionary(x => x.Key.Name, x => x.Value);
#endif
var grouppedByTables = mappingInfo.GroupBy(x => x.TableName)
.Select(x => new
{
TableName = x.Key,
x.First().TableNameQualified,
KeyInfos = x.Where(y => y.IsKey).ToList(),
ClientDataInfos = x.Where(y => !y.IsDbGenerated).ToList(),
ReturningInfos = x.Where(y => y.IsDbGenerated).ToList()
})
.ToList();
info.InsertQueryParts = grouppedByTables.Select(x =>
{
var others = grouppedByTables.Where(y => y.TableName != x.TableName)
.SelectMany(y => y.ClientDataInfos)
.Select(y => new
{
My = y,
Others = x.ReturningInfos.FirstOrDefault(ri => ri.Property.Name == y.Property.Name)
})
.Where(y => y.Others != null)
.ToList();
return(new InsertQueryParts()
{
TableName = x.TableName,
TableNameQualified = x.TableNameQualified,
TargetColumnNamesQueryPart = string.Join(", ", x.ClientDataInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName))),
SourceColumnNamesQueryPart = string.Join(", ", x.ClientDataInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.TempAliasedColumnName))),
Returning = string.Join(", ", x.ReturningInfos.Select(y => NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName))),
ReturningSetQueryPart = string.Join(", ", others.Select(y => $"{NpgsqlHelper.GetQualifiedName(y.My.TempAliasedColumnName)} " +
$" = source.{NpgsqlHelper.GetQualifiedName(y.Others.ColumnInfo.ColumnName)}"))
});
}).ToList();
info.SelectSourceForInsertQuery = "SELECT " +
string.Join(", ", info.ClientDataInfos
.Select(x => $"{x.QualifiedColumnName} AS {x.TempAliasedColumnName}")) +
" FROM " + string.Join(", ", grouppedByTables.Select(x => x.TableNameQualified));
info.CopyColumnsForInsertQueryPart = string.Join(", ", info.ClientDataInfos
.Select(x => x.TempAliasedColumnName));
info.UpdateQueryParts = grouppedByTables.Select(x =>
{
var updateableInfos = x.ClientDataInfos;
updateableInfos = updateableInfos.Where(
y => y.ModifierAttributes == null ||
y.ModifierAttributes.All(
m => m.Modification != BulkOperationModification.IgnoreForUpdate)
).ToList();
return(new UpdateQueryParts()
{
TableName = x.TableName,
TableNameQualified = x.TableNameQualified,
SetClause = string.Join(", ", updateableInfos.Select(y =>
{
var colName = NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName);
return $"{colName} = source.{y.TempAliasedColumnName}";
})),
WhereClause = string.Join(" AND ", x.KeyInfos.Select(y =>
{
var colName = NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName);
var clause = $"{colName} = source.{y.TempAliasedColumnName}";
if (y.IsNullableInClr)
{
clause = $"({clause} OR ({colName} IS NULL AND source.{y.TempAliasedColumnName} IS NULL))";
}
return clause;
}))
});
})
.Where(x => !string.IsNullOrEmpty(x.SetClause))
.ToList();
info.SelectSourceForUpdateQuery = "SELECT " +
string.Join(", ", info.ClientDataWithKeysInfos
.Select(x => $"{x.QualifiedColumnName} AS {x.TempAliasedColumnName}")) +
" FROM " + string.Join(", ", grouppedByTables.Select(x => x.TableNameQualified));
info.CopyColumnsForUpdateQueryPart = string.Join(", ", info.ClientDataWithKeysInfos
.Select(x => x.TempAliasedColumnName));
info.ClientDataColumnNames = string.Join(", ",
info.ClientDataInfos.Select(x => NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)));
info.KeyInfos = info.MappingInfos.Where(x => x.IsKey).ToArray();
info.KeyColumnNames = info.KeyInfos.Select(x => x.ColumnInfo.ColumnName).ToArray();
info.ClientDataWithKeysColumnNames = string.Join(", ",
info.ClientDataWithKeysInfos.Select(x => NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)));
info.DbGeneratedInfos = info.MappingInfos.Where(x => x.IsDbGenerated).ToArray();
info.DbGeneratedColumnNames = string.Join(", ",
info.DbGeneratedInfos.Select(x => NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)));
if (codeBuilderOuter == null)
{
codeBuilder.InitBuilder(info, ReadValue);
}
return(info);
}
private string GetTableNameQualified(Type t)
{
var tableAttr = t.GetCustomAttribute <TableAttribute>();
return(NpgsqlHelper.GetQualifiedName(tableAttr.Name, tableAttr.Schema));
}
private EntityInfo CreateEntityInfo <T>()
{
var t = typeof(T);
var tableName = GetTableName(t);
var mappingInfo = GetMappingInfo(t, tableName);
var codeBuilder = new NpgsqlBulkCodeBuilder <T>();
var info = new EntityInfo()
{
TableNameQualified = GetTableNameQualified(t),
TableName = tableName,
CodeBuilder = codeBuilder,
MappingInfos = mappingInfo,
TableNames = mappingInfo.Select(x => x.TableName).Distinct().ToArray(),
ClientDataInfos = mappingInfo.Where(x => !x.IsDbGenerated).ToArray(),
ClientDataWithKeysInfos = mappingInfo.Where(x => !x.IsDbGenerated || x.IsKey).ToArray()
};
var grouppedByTables = mappingInfo.GroupBy(x => x.TableName)
.Select(x => new
{
TableName = x.Key,
KeyInfos = x.Where(y => y.IsKey).ToList(),
ClientDataInfos = x.Where(y => !y.IsDbGenerated).ToList(),
ReturningInfos = x.Where(y => y.IsDbGenerated).ToList()
}).ToList();
info.InsertQueryParts = grouppedByTables.Select(x =>
{
var others = grouppedByTables.Where(y => y.TableName != x.TableName)
.SelectMany(y => y.ClientDataInfos)
.Select(y => new
{
My = y,
Others = x.ReturningInfos.FirstOrDefault(ri => ri.Property.Name == y.Property.Name)
})
.Where(y => y.Others != null)
.ToList();
return(new InsertQueryParts()
{
TableName = x.TableName,
TargetColumnNamesQueryPart = string.Join(", ", x.ClientDataInfos.Select(y => y.ColumnInfo.ColumnName)),
SourceColumnNamesQueryPart = string.Join(", ", x.ClientDataInfos.Select(y => y.TempAliasedColumnName)),
Returning = string.Join(", ", x.ReturningInfos.Select(y => y.ColumnInfo.ColumnName)),
ReturningSetQueryPart = string.Join(", ", others.Select(y => $"{y.My.TempAliasedColumnName} = source.{y.Others.ColumnInfo.ColumnName}"))
});
}).ToList();
info.SelectSourceForInsertQuery = "SELECT " +
string.Join(", ", info.ClientDataInfos
.Select(x => $"{x.QualifiedColumnName} AS {x.TempAliasedColumnName}")) +
" FROM " + string.Join(", ", grouppedByTables.Select(x => x.TableName));
info.CopyColumnsForInsertQueryPart = string.Join(", ", info.ClientDataInfos
.Select(x => x.TempAliasedColumnName));
info.UpdateQueryParts = grouppedByTables.Select(x =>
{
var updateableInfos = x.ClientDataInfos;
updateableInfos = updateableInfos.Where(
y => y.ModifierAttributes == null ||
y.ModifierAttributes.All(
m => m.Modification != BulkOperationModification.IgnoreForUpdate)
).ToList();
return(new UpdateQueryParts()
{
TableName = x.TableName,
SetClause = string.Join(", ", updateableInfos.Select(y =>
{
var colName = NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName);
return $"{colName} = source.{y.TempAliasedColumnName}";
})),
WhereClause = string.Join(", ", x.KeyInfos.Select(y =>
{
var colName = NpgsqlHelper.GetQualifiedName(y.ColumnInfo.ColumnName);
return $"{colName} = source.{y.TempAliasedColumnName}";
}))
});
}).ToList();
info.SelectSourceForUpdateQuery = "SELECT " +
string.Join(", ", info.ClientDataWithKeysInfos
.Select(x => $"{x.QualifiedColumnName} AS {x.TempAliasedColumnName}")) +
" FROM " + string.Join(", ", grouppedByTables.Select(x => x.TableName));
info.CopyColumnsForUpdateQueryPart = string.Join(", ", info.ClientDataWithKeysInfos
.Select(x => x.TempAliasedColumnName));
info.ClientDataColumnNames = string.Join(", ",
info.ClientDataInfos.Select(x => NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)));
info.KeyInfos = info.MappingInfos.Where(x => x.IsKey).ToArray();
info.KeyColumnNames = info.KeyInfos.Select(x => x.ColumnInfo.ColumnName).ToArray();
info.ClientDataWithKeysColumnNames = string.Join(", ",
info.ClientDataWithKeysInfos.Select(x => NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)));
info.DbGeneratedInfos = info.MappingInfos.Where(x => x.IsDbGenerated).ToArray();
info.DbGeneratedColumnNames = string.Join(", ",
info.DbGeneratedInfos.Select(x => NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)));
codeBuilder.InitBuilder(info.ClientDataInfos,
info.ClientDataWithKeysInfos,
info.DbGeneratedInfos,
ReadValue);
return(info);
}
public void Insert <T>(IEnumerable <T> entities)
{
var conn = NpgsqlHelper.GetNpgsqlConnection(context);
EnsureConnected(conn);
var transaction = NpgsqlHelper.EnsureOrStartTransaction(context);
var mapping = GetEntityInfo <T>();
try
{
// 0. Prepare variables
var tempTableName = "_temp_" + DateTime.Now.Ticks;
var list = entities.ToList();
var codeBuilder = (NpgsqlBulkCodeBuilder <T>)mapping.CodeBuilder;
// 1. Create temp table
var sql = $"CREATE TEMP TABLE {tempTableName} ON COMMIT DROP AS {mapping.SelectSourceForInsertQuery} LIMIT 0";
//var sql = $"CREATE {tempTableName} AS {mapping.SelectSourceForInsertQuery} LIMIT 0";
context.Database.ExecuteSqlCommand(sql);
context.Database.ExecuteSqlCommand($"ALTER TABLE {tempTableName} ADD COLUMN __index integer");
// 2. Import into temp table
using (var importer = conn.BeginBinaryImport($"COPY {tempTableName} ({mapping.CopyColumnsForInsertQueryPart}, __index) FROM STDIN (FORMAT BINARY)"))
{
var index = 1;
foreach (var item in list)
{
importer.StartRow();
codeBuilder.ClientDataWriterAction(item, importer);
importer.Write(index, NpgsqlDbType.Integer);
index++;
}
}
// 3. Insert into real table from temp one
foreach (var insertPart in mapping.InsertQueryParts)
{
using (var cmd = conn.CreateCommand())
{
var baseInsertCmd = $"INSERT INTO {insertPart.TableName} ({insertPart.TargetColumnNamesQueryPart}) SELECT {insertPart.SourceColumnNamesQueryPart} FROM {tempTableName}";
if (string.IsNullOrEmpty(insertPart.ReturningSetQueryPart))
{
cmd.CommandText = baseInsertCmd;
if (!string.IsNullOrEmpty(insertPart.Returning))
{
cmd.CommandText += $" RETURNING {insertPart.Returning}";
}
}
else
{
cmd.CommandText = $"WITH inserted as (\n {baseInsertCmd} RETURNING {insertPart.Returning} \n ), \n";
cmd.CommandText += $"source as (\n SELECT *, ROW_NUMBER() OVER (ORDER BY {insertPart.Returning}) as __index FROM inserted \n ) \n";
cmd.CommandText += $"UPDATE {tempTableName} SET {insertPart.ReturningSetQueryPart} FROM source WHERE {tempTableName}.__index = source.__index\n";
cmd.CommandText += $" RETURNING {insertPart.Returning}";
}
using (var reader = cmd.ExecuteReader())
{
// 4. Propagate computed value
if (!string.IsNullOrEmpty(insertPart.Returning))
{
var readAction = codeBuilder.IdentityValuesWriterActions[insertPart.TableName];
foreach (var item in list)
{
reader.Read();
readAction(item, reader);
}
}
}
}
}
// 5. Commit
transaction?.Commit();
}
catch
{
transaction?.Rollback();
throw;
}
}
public static List <T> BulkSelect <T, TKey>(
this IQueryable <T> source,
Expression <Func <T, TKey> > keyExpression,
IEnumerable <TKey> keyData)
{
EnsureNoNavigationProperties(keyExpression);
BulkSelectInterceptor.StartInterception();
var keyDataTable = $"_schema_{DateTime.Now.Ticks}";
var schemaQuery = source.Select(keyExpression);
var schemaSql = $"CREATE TEMP TABLE {keyDataTable} ON COMMIT DROP AS ({schemaQuery} LIMIT 0)";
var context = NpgsqlHelper.GetContextFromQuery(source);
var conn = NpgsqlHelper.GetNpgsqlConnection(context);
var localTr = NpgsqlHelper.EnsureOrStartTransaction(context);
try
{
context.Database.ExecuteSqlCommand(schemaSql);
var columnsInfo = NpgsqlHelper.GetColumnsInfo(context, keyDataTable);
var propsMap = GetPropertiesMap(
((IObjectContextAdapter)context).ObjectContext,
schemaQuery.Expression,
typeof(TKey));
var mapsInfo = new List <MappingInfo>();
foreach (var propMap in propsMap)
{
var cinfo = columnsInfo[propMap.Item2];
mapsInfo.Add(new MappingInfo()
{
Property = propMap.Item1,
ColumnInfo = cinfo,
NpgsqlType = NpgsqlBulkUploader.GetNpgsqlType(cinfo)
});
}
var columnsCsv = string.Join(", ",
mapsInfo.Select(x => NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)));
var copySql = $"COPY {keyDataTable} ({columnsCsv}) FROM STDIN (FORMAT BINARY)";
using (var importer = conn.BeginBinaryImport(copySql))
{
foreach (var kd in keyData)
{
importer.StartRow();
foreach (var kp in mapsInfo)
{
importer.Write(kp.Property.GetValue(kd), kp.NpgsqlType);
}
}
}
var whereSql = string.Join(" AND ",
mapsInfo.Select(x => $"source.{NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)}" +
$" = {keyDataTable}.{NpgsqlHelper.GetQualifiedName(x.ColumnInfo.ColumnName)}"));
var selectSql = $"SELECT source.* FROM ({source}) as source\n" +
$"JOIN {keyDataTable} ON {whereSql}";
BulkSelectInterceptor.SetReplaceQuery(source.ToString(), selectSql);
var result = source.ToList();
localTr?.Commit();
return(result);
}
catch
{
localTr?.Rollback();
throw;
}
finally
{
BulkSelectInterceptor.StopInterception();
}
}
public void Update <T>(IEnumerable <T> entities)
{
var conn = NpgsqlHelper.GetNpgsqlConnection(context);
var connOpenedHere = EnsureConnected(conn);
var transaction = NpgsqlHelper.EnsureOrStartTransaction(context, DefaultIsolationLevel);
var mapping = GetEntityInfo <T>();
try
{
// 0. Prepare variables
var dataColumns = mapping.ClientDataWithKeysColumnNames;
var tableName = mapping.TableNameQualified;
var tempTableName = "_temp_" + DateTime.Now.Ticks;
var codeBuilder = (NpgsqlBulkCodeBuilder <T>)mapping.CodeBuilder;
// 1. Create temp table
var sql = $"CREATE TEMP TABLE {tempTableName} ON COMMIT DROP AS {mapping.SelectSourceForUpdateQuery} LIMIT 0";
//var sql = $"CREATE TABLE {tempTableName} AS {mapping.SelectSourceForUpdateQuery} LIMIT 0";
context.Database.ExecuteSqlCommand(sql);
// 2. Import into temp table
using (var importer = conn.BeginBinaryImport($"COPY {tempTableName} ({mapping.CopyColumnsForUpdateQueryPart}) FROM STDIN (FORMAT BINARY)"))
{
foreach (var item in entities)
{
importer.StartRow();
codeBuilder.ClientDataWithKeyWriterAction(item, importer);
}
importer.Complete();
}
// 3. Insert into real table from temp one
foreach (var part in mapping.UpdateQueryParts)
{
// 3.a Needs to accure lock
if (LockLevelOnUpdate != TableLockLevel.NoLock)
{
sql = $"LOCK TABLE {part.TableNameQualified} IN {LockLevelToString(LockLevelOnUpdate)} MODE;";
context.Database.ExecuteSqlCommand(sql);
}
sql = $"UPDATE {part.TableNameQualified} SET {part.SetClause} FROM {tempTableName} as source WHERE {part.WhereClause}";
context.Database.ExecuteSqlCommand(sql);
}
// 5. Commit
transaction?.Commit();
}
catch
{
try
{
transaction?.Rollback();
}
catch { }
throw;
}
finally
{
if (connOpenedHere)
{
conn.Close();
}
}
}
internal static List <MappingInfo> GetMetadata(DbContext context, Type type)
{
var metadata = context.Model;
var entityType = metadata.GetEntityTypes().Single(x => x.ClrType == type);
var tableName = GetTableName(context, type);
var columnsInfo = NpgsqlBulkUploader.RelationalHelper.GetColumnsInfo(context, tableName);
if (entityType.BaseType != null)
{
var baseTableName = GetTableName(context, entityType.BaseType.ClrType);
if (baseTableName != tableName)
{
var extraColumnsInfo = NpgsqlBulkUploader.RelationalHelper.GetColumnsInfo(context, baseTableName);
columnsInfo.AddRange(extraColumnsInfo);
}
}
var valueGenSelector = ((IInfrastructure <IServiceProvider>)context).Instance.GetService <IValueGeneratorSelector>();
int optinalIndex = 0;
var innerList = entityType
.GetProperties()
.Where(x => x.GetColumnName() != "xmin") // For now we don't support xmin
.Select(x =>
{
var relational = x.DeclaringEntityType;
ValueGenerator localGenerator = null;
bool isDbGenerated = false;
var generatorFactory = x.GetAnnotations().FirstOrDefault(a => a.Name == "ValueGeneratorFactory");
if (generatorFactory != null)
{
var valueGeneratorAccessor = generatorFactory.Value as Func <IProperty, IEntityType, ValueGenerator>;
localGenerator = valueGeneratorAccessor(x, x.DeclaringEntityType);
}
else if (x.GetAnnotations().Any(y => y.Name == "Relational:ComputedColumnSql"))
{
isDbGenerated = true;
}
else
{
var autoGenStrategy = x.GetAnnotations().FirstOrDefault(y => y.Name == "Npgsql:ValueGenerationStrategy");
if (autoGenStrategy != null)
{
var npgsqlStartegy = (NpgsqlValueGenerationStrategy)autoGenStrategy.Value;
isDbGenerated = npgsqlStartegy != NpgsqlValueGenerationStrategy.SequenceHiLo &&
npgsqlStartegy != NpgsqlValueGenerationStrategy.None;
}
}
if (!isDbGenerated && localGenerator == null)
{
try
{
localGenerator = valueGenSelector.Select(x, entityType);
}
catch
{
// ignore
}
}
#if DotNet6
var readBack = x.GetStoreGeneratedIndex() >= 0;
#else
var indexes = ((Microsoft.EntityFrameworkCore.Metadata.Internal.Property)x).PropertyIndexes;
var readBack = indexes.StoreGenerationIndex >= 0;
#endif
long optionalFlag = 0;
// We don't support genertion based on Foreign Keys.
if (readBack && !x.IsForeignKey() && localGenerator == null)
{
optionalFlag = 1 << optinalIndex;
optinalIndex++;
}
return(new MappingInfo()
{
TableName = relational.GetTableName(),
TableNameQualified = NpgsqlHelper.GetQualifiedName(relational.GetTableName(), relational.GetSchema()),
Property = x.PropertyInfo,
ColumnInfo = columnsInfo.First(c => c.ColumnName == x.GetColumnName()),
LocalGenerator = localGenerator,
ValueConverter = x.GetValueConverter(),
IsKey = x.IsKey(),
IsInheritanceUsed = entityType.BaseType != null,
DbProperty = x,
DoUpdate = !isDbGenerated && x.PropertyInfo != null, // don't update shadow props
DoInsert = !isDbGenerated, // do insert of shadow props
ReadBack = readBack,
IsSpecifiedFlag = optionalFlag
});
}).ToList();
return(innerList);
}
