/// <summary>
/// Bulk update entities of specified properties
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entities"></param>
/// <param name="propertiesToUpdate"></param>
public Task UpdateAsync<T>(IEnumerable<T> entities,
params Expression<Func<T, Object>>[] propertiesToUpdate)
{
var mapping = CreateEntityInfo<T>(
propertiesToUpdate.Select(x => InsertConflictAction.UnwrapProperty<T>(x)).ToArray());
return UpdateAsyncInternal<T>(entities, mapping);
}
static void TestViaInterfaceCase <T>(IEnumerable <T> data, DbContext context) where T : IHasId
{
var uploader = new NpgsqlBulkUploader(context);
var properties = data
.First()
.GetType()
.GetProperties()
.ToArray();
uploader.Insert(data, InsertConflictAction.UpdateProperty <T>(x => x.AddressId, properties));
}
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();
}
}
}
private async Task InsertPortionAsync <T>(
IEnumerable <T> list,
List <InsertQueryParts> insertParts,
NpgsqlConnection conn,
NpgsqlBulkCodeBuilder <T> codeBuilder,
string tempTableName,
object ignoreDuplicatesStatement,
InsertConflictAction onConflict)
{
// 3. Insert into real table from temp one
foreach (var insertPart in insertParts)
{
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.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 = 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();
}
}
}
static void TestPlainCase()
{
var streets = new[] { "First", "Second", "Third" };
var codes = new[] { "001001", "002002", "003003", "004004" };
var extraNumbers = new int?[] { null, 1, 2, 3, 5, 8, 13, 21, 34 };
var addressTypes = new AddressType?[] { null, AddressType.Type1, AddressType.Type2 };
var dates = new DateTime?[] { null, DateTime.Now };
var guids = new Guid?[] { null, Guid.Empty };
var decimals = new decimal?[] { null, decimal.Zero };
var context = new BulkContext("DefaultConnection");
context.Database.ExecuteSqlCommand("TRUNCATE addresses cascade");
var data = Enumerable.Range(0, 100000)
.Select((x, i) => new Address()
{
StreetName = streets[i % streets.Length],
HouseNumber = i + 1,
PostalCode = codes[i % codes.Length],
ExtraHouseNumber = extraNumbers[i % extraNumbers.Length],
Type = addressTypes[i % addressTypes.Length],
Date = dates[i % dates.Length],
Guid = guids[i % guids.Length],
Dec = decimals[i % decimals.Length]
}).ToList();
var uploader = new NpgsqlBulkUploader(context);
context.Database.ExecuteSqlCommand("DELETE FROM addresses");
var sw = Stopwatch.StartNew();
HardcodedInsert(data, context);
sw.Stop();
Console.WriteLine($"Hardcoded solution inserted {data.Count} records for {sw.Elapsed }");
context.Database.ExecuteSqlCommand("DELETE FROM addresses");
sw = Stopwatch.StartNew();
uploader.Insert(data, InsertConflictAction.UpdateProperty <Address>(x => x.AddressId, x => x.Dec));
uploader.Insert(data, InsertConflictAction.DoNothing());
sw.Stop();
Console.WriteLine($"Dynamic solution inserted {data.Count} records for {sw.Elapsed }");
data.ForEach(x => x.HouseNumber += 1);
sw = Stopwatch.StartNew();
uploader.Update(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed }");
TestViaInterfaceCase(data, context);
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
sw = Stopwatch.StartNew();
uploader.Import(data);
sw.Stop();
Console.WriteLine($"Dynamic solution imported {data.Count} records for {sw.Elapsed }");
// With transaction
context.Database.ExecuteSqlCommand("TRUNCATE addresses CASCADE");
using (var transaction = new TransactionScope())
{
uploader.Insert(data);
}
Trace.Assert(context.Addresses.Count() == 0);
sw = Stopwatch.StartNew();
uploader.Update(data);
sw.Stop();
Console.WriteLine($"Dynamic solution updated {data.Count} records for {sw.Elapsed } (after transaction scope)");
TestAsync(context, uploader, data).Wait();
}
