/// <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(); }