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