RepoDb.PostgreSql.BulkOperations 1.15.0

RepoDb.PostgreSql.BulkOperations

High-performance bulk operations for RepoDB on PostgreSQL. Uses PostgreSQL's native binary import protocol to transfer data in a single pass — up to 90% faster than row-by-row or batch operations.

Important Pages

• GitHub Home — core library and source code.
• Website — full documentation, API reference, and blog.

Core Features

• Special Arguments
• Async Methods
• BinaryBulkDelete
• BinaryBulkDeleteByKey
• BinaryBulkInsert
• BinaryBulkMerge
• BinaryBulkUpdate

Community

• GitHub Issues — bug reports and feature requests.
• StackOverflow — technical questions.
• Microsoft Teams — live Q&A.
• X / Twitter — news and updates.

License

Apache-2.0 — Copyright © 2020 Michael Camara Pendon

Installation

Install-Package RepoDb.PostgreSql.BulkOperations

Then initialize the bootstrapper once at application startup:

RepoDb.PostgreSqlBootstrap.Initialize();

Or visit the installation page for more options.

Special Arguments

qualifiers — defines the fields used in the matching criteria for delete, merge, and update operations. Defaults to the primary key column.

keepIdentity — when enabled, the identity property value on the entity is preserved during the operation.

identityBehavior — controls identity handling and whether newly generated identity values are returned and written back to the entities.

pseudoTableType — controls whether a physical or session-scoped temporary table is created internally during the operation.

mergeCommandType — controls whether ON CONFLICT DO UPDATE or separate UPDATE/INSERT SQL commands are used during merge.

Identity Setting Alignment

RepoDB adds an internal __RepoDb_OrderColumn to the pseudo-temporary table when identity fields are present. This column preserves the original index of each entity in the IEnumerable<T> collection, ensuring generated identity values are mapped back to the correct objects after the operation completes.

BatchSize

All operations accept a batchSize argument to control how many rows are sent to the server per round-trip. Defaults to null (all rows in one pass). Tune this based on column count, data size, and network characteristics.

Enum Types

Npgsql supports the following .NET enum mappings:

• .NET Enum → PostgreSQL text-based types (e.g. text, varchar)
• .NET Enum → PostgreSQL integer types (e.g. int4, int8)
• .NET Enum → Native PostgreSQL enum type, when mapped via NpgsqlDataSource.MapEnum()

These mappings work correctly with standard fluent operations such as Insert and InsertAll. However, bulk operations such as BinaryBulkInsert may fail with the following error when an enum property towards Native PostgreSQL enum type (item 3) is involved:

'RepoDb.PostgreSql.BulkOperations.IntegrationTests.Enumerations.Hands' is not supported for parameters having NpgsqlDbType 'Unknown'.

To fix this, you have to follow the steps below.

Use the NpgsqlDataSource.MapEnum() method to map the PostgreSQL enum type on the current data source. Use the NpgsqlDataSourceBuilder class and then use the connection object created from this builder.

var dataSource = new NpgsqlDataSourceBuilder(Database.ConnectionString)
	.MapEnum<Hands>("hand", new NpgsqlNullNameTranslator())
	.Build();
var connection = dataSource.CreateConnection();

// Do your bulk stuffs here

Then, map the column to the right data type as seen in the ColumnEnumHand property below.

var mappings = return new[]
{
    ...
    new NpgsqlBulkInsertMapItem(nameof(EnumTable.ColumnEnumInt), nameof(EnumTable.ColumnEnumInt), NpgsqlTypes.NpgsqlDbType.Integer),
    new NpgsqlBulkInsertMapItem(nameof(EnumTable.ColumnEnumHand), nameof(EnumTable.ColumnEnumHand), "hand")
}

// Pass to 'mappings' argument
var result = NpgsqlConnectionExtension.BinaryBulkInsert<EnumTable>(connection,
    tableName,
    entities: entities,
    mappings: Helper.GetEnumTableMappings());

Async Methods

Every synchronous operation has a corresponding Async overload.

BinaryBulkDelete

Deletes existing rows from the database in bulk. Returns the number of deleted rows.

BinaryBulkDelete via DataEntities

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var customers = GetCustomers();
	var deletedRows = connection.BinaryBulkDelete<Customer>(customers);
}

Or with qualifiers:

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var customers = GetCustomers();
	var deletedRows = connection.BinaryBulkDelete<Customer>(customers, qualifiers: e => new { e.LastName, e.DateOfBirth });
}

Or via table-name:

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var customers = GetCustomers();
	var deletedRows = connection.BinaryBulkDelete("Customer", customers);
}

Or via table-name with qualifiers:

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var customers = GetCustomers();
	var deletedRows = connection.BinaryBulkDelete("Customer", customers, qualifiers: Field.From("LastName", "DateOfBirth"));
}

BinaryBulkDelete via DataTable

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var table = GetCustomersAsDataTable();
	var deletedRows = connection.BinaryBulkDelete("Customer", table);
}

Or with qualifiers:

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var table = GetCustomersAsDataTable();
	var deletedRows = connection.BinaryBulkDelete("Customer", table, qualifiers: Field.From("LastName", "DateOfBirth"));
}

BinaryBulkDelete via DbDataReader

using (var connection = new NpgsqlConnection(ConnectionString))
{
	using (var reader = connection.ExecuteReader("SELECT * FROM \"Customer\";"))
	{
		var deletedRows = connection.BinaryBulkDelete("Customer", reader);
	}
}

Or with qualifiers:

using (var connection = new NpgsqlConnection(ConnectionString))
{
	using (var reader = connection.ExecuteReader("SELECT * FROM \"Customer\";"))
	{
		var deletedRows = connection.BinaryBulkDelete("Customer", reader, qualifiers: Field.From("LastName", "DateOfBirth"));
	}
}

BinaryBulkDeleteByKey

Deletes existing rows from the database in bulk via a list of primary keys. Returns the number of deleted rows.

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var primaryKeys = new [] { 1, 2, ..., 10045 };
	var deletedRows = connection.BinaryBulkDeleteByKey(primaryKeys);
}

BinaryBulkInsert

Inserts a list of entities into the database in bulk. Returns the number of inserted rows.

BinaryBulkInsert via DataEntities

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var customers = GetCustomers();
	var insertedRows = connection.BinaryBulkInsert<Customer>(customers);
}

Or via table-name:

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var customers = GetCustomers();
	var insertedRows = connection.BinaryBulkInsert("Customer", customers);
}

BinaryBulkInsert via DataTable

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var table = GetCustomersAsDataTable();
	var insertedRows = connection.BinaryBulkInsert("Customer", table);
}

BinaryBulkInsert via DbDataReader

using (var connection = new NpgsqlConnection(ConnectionString))
{
	using (var reader = connection.ExecuteReader("SELECT * FROM \"Customer\";"))
	{
		var insertedRows = connection.BinaryBulkInsert("Customer", reader);
	}
}

BinaryBulkMerge

Upserts a list of entities in bulk — inserts new rows and updates existing ones based on the defined qualifiers. Returns the number of affected rows.

BinaryBulkMerge via DataEntities

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var customers = GetCustomers();
	var mergedRows = connection.BinaryBulkMerge<Customer>(customers);
}

Or with qualifiers:

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var customers = GetCustomers();
	var mergedRows = connection.BinaryBulkMerge<Customer>(customers, qualifiers: e => new { e.LastName, e.DateOfBirth });
}

Or via table-name:

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var customers = GetCustomers();
	var mergedRows = connection.BinaryBulkMerge("Customer", customers);
}

Or via table-name with qualifiers:

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var customers = GetCustomers();
	var mergedRows = connection.BinaryBulkMerge("Customer", customers, qualifiers: Field.From("LastName", "DateOfBirth"));
}

BinaryBulkMerge via DataTable

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var table = GetCustomersAsDataTable();
	var mergedRows = connection.BinaryBulkMerge("Customer", table);
}

Or with qualifiers:

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var table = GetCustomersAsDataTable();
	var mergedRows = connection.BinaryBulkMerge("Customer", table, qualifiers: Field.From("LastName", "DateOfBirth"));
}

BinaryBulkMerge via DbDataReader

using (var connection = new NpgsqlConnection(ConnectionString))
{
	using (var reader = connection.ExecuteReader("SELECT * FROM \"Customer\";"))
	{
		var mergedRows = connection.BinaryBulkMerge("Customer", reader);
	}
}

Or with qualifiers:

using (var connection = new NpgsqlConnection(ConnectionString))
{
	using (var reader = connection.ExecuteReader("SELECT * FROM \"Customer\";"))
	{
		var mergedRows = connection.BinaryBulkMerge("Customer", reader, qualifiers: Field.From("LastName", "DateOfBirth"));
	}
}

BinaryBulkUpdate

Updates existing rows in the database in bulk. Returns the number of updated rows.

BinaryBulkUpdate via DataEntities

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var customers = GetCustomers();
	var rows = connection.BinaryBulkUpdate<Customer>(customers);
}

Or with qualifiers:

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var customers = GetCustomers();
	var rows = connection.BinaryBulkUpdate<Customer>(customers, qualifiers: e => new { e.LastName, e.DateOfBirth });
}

Or via table-name:

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var customers = GetCustomers();
	var rows = connection.BinaryBulkUpdate("Customer", customers);
}

Or via table-name with qualifiers:

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var customers = GetCustomers();
	var rows = connection.BinaryBulkUpdate("Customer", customers, qualifiers: Field.From("LastName", "DateOfBirth"));
}

BinaryBulkUpdate via DataTable

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var table = GetCustomersAsDataTable();
	var rows = connection.BinaryBulkUpdate("Customer", table);
}

Or with qualifiers:

using (var connection = new NpgsqlConnection(ConnectionString))
{
	var table = GetCustomersAsDataTable();
	var rows = connection.BinaryBulkUpdate("Customer", table, qualifiers: Field.From("LastName", "DateOfBirth"));
}

BinaryBulkUpdate via DbDataReader

using (var connection = new NpgsqlConnection(ConnectionString))
{
	using (var reader = connection.ExecuteReader("SELECT * FROM \"Customer\";"))
	{
		var rows = connection.BinaryBulkUpdate("Customer", reader);
	}
}

Or with qualifiers:

using (var connection = new NpgsqlConnection(ConnectionString))
{
	using (var reader = connection.ExecuteReader("SELECT * FROM \"Customer\";"))
	{
		var rows = connection.BinaryBulkUpdate("Customer", reader, qualifiers: Field.From("LastName", "DateOfBirth"));
	}
}