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);
}
}
// 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
{
transaction?.Rollback();
throw;
}
finally
{
if (connOpenedHere)
{
conn.Close();
}
}
}
/// <summary>
/// Simplified version of Insert which works better for huge sets (not calling ToList internally).
/// Note: it imports directly to target table, doesn't use RETURING, doesn't support inheritance
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entities"></param>
/// <param name="onConflict"></param>
public async Task ImportAsync <T>(IEnumerable <T> entities)
{
var mapping = GetEntityInfo <T>();
if (mapping.InsertQueryParts.Count > 1)
{
throw new NotSupportedException($"Import doesn't support entities with inheritance for now");
}
var conn = NpgsqlHelper.GetNpgsqlConnection(context);
var connOpenedHere = await EnsureConnectedAsync(conn);
var transaction = NpgsqlHelper.EnsureOrStartTransaction(context, DefaultIsolationLevel);
try
{
// Prepare variables
var codeBuilder = (NpgsqlBulkCodeBuilder <T>)mapping.CodeBuilder;
// Import
using (var importer = conn.BeginBinaryImport($"COPY {mapping.TableNameQualified} ({mapping.InsertQueryParts[0].TargetColumnNamesQueryPart}) FROM STDIN (FORMAT BINARY)"))
{
foreach (var item in entities)
{
importer.StartRow();
codeBuilder.ClientDataWriterAction(item, importer);
}
importer.Complete();
}
// Commit
transaction?.Commit();
}
catch
{
try
{
transaction?.Rollback();
}
catch { }
throw;
}
finally
{
if (connOpenedHere)
{
conn.Close();
}
}
}
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();
}
}
}
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();
}
}