private async Task InsertPortionAsync <T>(
IEnumerable <T> list,
List <InsertQueryParts> insertParts,
NpgsqlConnection conn,
NpgsqlBulkCodeBuilder <T> codeBuilder,
string tempTableName,
EntityInfo entityInfo,
InsertConflictAction onConflict)
{
// 3. Insert into real table from temp one
foreach (var insertPart in insertParts)
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandTimeout = CommandTimeout ?? cmd.CommandTimeout;
var ignoreDuplicatesStatement = onConflict?.GetSql(entityInfo, insertPart.TableNameQualified);
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.InsertIdentityValuesWriterActions[insertPart.TableName];
foreach (var item in list)
{
await reader.ReadAsync();
readAction(item, reader);
}
}
else
{
while (await reader.ReadAsync())
{
// do nothing, for now...
}
}
}
}
}
}
}
public async Task InsertAsync <T>(IEnumerable <T> entities, InsertConflictAction onConflict)
{
var conn = RelationalHelper.GetNpgsqlConnection(context);
var connOpenedHere = await EnsureConnectedAsync(conn);
var transaction = RelationalHelper.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";
await context.Database.ExecuteSqlCommandAsync(sql);
sql = $"ALTER TABLE {tempTableName} ADD COLUMN __index integer";
await context.Database.ExecuteSqlCommandAsync(sql);
#if EFCore
SetAutoGeneratedFields(list, mapping, codeBuilder, EntityState.Added);
#endif
if (mapping.MaxIsOptionalFlag == 0)
{
WriteInsertPortion(list, mapping, conn, tempTableName, codeBuilder);
await InsertPortionAsync <T>(list, mapping.InsertQueryParts[0], conn, codeBuilder,
tempTableName, ignoreDuplicatesStatement, onConflict);
}
else
{
var classified = list.ToLookup(x => codeBuilder.ClassifyOptionals(x));
foreach (var bucket in classified)
{
await context.Database.ExecuteSqlCommandAsync($"TRUNCATE TABLE " + tempTableName);
WriteInsertPortion(bucket, mapping, conn, tempTableName, codeBuilder);
await InsertPortionAsync <T>(
bucket,
mapping.InsertQueryParts.GetOrAdd(bucket.Key, (key) => GetInsertQueryParts(mapping.MappingInfos, key)),
conn,
codeBuilder,
tempTableName,
ignoreDuplicatesStatement,
onConflict);
}
}
// 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();
}
}
}
