EntityFrameworkCore.SqlServer.SimpleBulks

A very simple .net core library that can help to sync a large number of records in-memory into the database using the SqlBulkCopy class.  

Overview

This library provides extension methods so that you can use with your EntityFrameworkCore DbContext instance DbContextExtensions.cs or you can use ConnectionContextExtensions.cs to work directly with a SqlConnection instance without using EntityFrameworkCore.

Nuget

| Database | Package | GitHub | | -------- | ------- | ------ | | SQL Server| EntityFrameworkCore.SqlServer.SimpleBulks | EntityFrameworkCore.SqlServer.SimpleBulks | | PostgreSQL| EntityFrameworkCore.PostgreSQL.SimpleBulks | EntityFrameworkCore.PostgreSQL.SimpleBulks | | MySQL| EntityFrameworkCore.MySQL.SimpleBulks | EntityFrameworkCore.MySQL.SimpleBulks |

Features

• Bulk Insert
• Bulk Update
• Bulk Delete
• Bulk Merge
• Bulk Match
• Temp Table
• Direct Insert
• Direct Update
• Direct Delete
• Upsert

Examples

DbContextExtensionsExamples

• Update the connection string ConnectionStrings.SqlServerConnectionString.
• Build and run.

ConnectionExtensionsExamples

• Update the connection string ConnectionStrings.SqlServerConnectionString.
• Build and run.

DbContextExtensions

Using Lambda Expression

using EntityFrameworkCore.SqlServer.SimpleBulks.BulkDelete;
using EntityFrameworkCore.SqlServer.SimpleBulks.BulkInsert;
using EntityFrameworkCore.SqlServer.SimpleBulks.BulkMerge;
using EntityFrameworkCore.SqlServer.SimpleBulks.BulkUpdate;

// Insert all columns
await dbct.BulkInsertAsync(rows);
await dbct.BulkInsertAsync(compositeKeyRows);

// Insert selected columns only
await dbct.BulkInsertAsync(rows,
    row => new { row.Column1, row.Column2, row.Column3 });
await dbct.BulkInsertAsync(compositeKeyRows,
    row => new { row.Id1, row.Id2, row.Column1, row.Column2, row.Column3 });

await dbct.BulkUpdateAsync(rows,
    row => new { row.Column3, row.Column2 });
await dbct.BulkUpdateAsync(compositeKeyRows,
    row => new { row.Column3, row.Column2 });

await dbct.BulkMergeAsync(rows,
    row => row.Id,
    row => new { row.Column1, row.Column2 },
    row => new { row.Column1, row.Column2, row.Column3 });
await dbct.BulkMergeAsync(compositeKeyRows,
    row => new { row.Id1, row.Id2 },
    row => new { row.Column1, row.Column2, row.Column3 },
    row => new { row.Id1, row.Id2, row.Column1, row.Column2, row.Column3 });
                        
await dbct.BulkDeleteAsync(rows);
await dbct.BulkDeleteAsync(compositeKeyRows);

Using Dynamic String

using EntityFrameworkCore.SqlServer.SimpleBulks.BulkDelete;
using EntityFrameworkCore.SqlServer.SimpleBulks.BulkInsert;
using EntityFrameworkCore.SqlServer.SimpleBulks.BulkMerge;
using EntityFrameworkCore.SqlServer.SimpleBulks.BulkUpdate;

await dbct.BulkUpdateAsync(rows,
    [ "Column3", "Column2" ]);
await dbct.BulkUpdateAsync(compositeKeyRows,
    [ "Column3", "Column2" ]);

await dbct.BulkMergeAsync(rows,
    ["Id"],
    [ "Column1", "Column2" ],
    [ "Column1", "Column2", "Column3" ]);
await dbct.BulkMergeAsync(compositeKeyRows,
    [ "Id1", "Id2" ],
    [ "Column1", "Column2", "Column3" ],
    [ "Id1", "Id2", "Column1", "Column2", "Column3" ]);

Using Builder Approach in case you need both Dynamic & Lambda Expression

await dbct.CreateBulkInsertBuilder<Row>()
	.WithColumns(row => new { row.Column1, row.Column2, row.Column3 })
	// or .WithColumns([ "Column1", "Column2", "Column3" ])
	.ToTable(dbContext.GetTableInfor<Row>())
	.ExecuteAsync(rows);

ConnectionContextExtensions

Using Lambda Expression

using EntityFrameworkCore.SqlServer.SimpleBulks.BulkDelete;
using EntityFrameworkCore.SqlServer.SimpleBulks.BulkInsert;
using EntityFrameworkCore.SqlServer.SimpleBulks.BulkMerge;
using EntityFrameworkCore.SqlServer.SimpleBulks.BulkUpdate;

// Configure Mapping globaly

TableMapper.Configure<Row>(config =>
{
    config
    .TableName("Rows")
    .PrimaryKeys(x => x.Id)
    .OutputId(x => x.Id, OutputIdMode.ServerGenerated);
});

TableMapper.Configure<CompositeKeyRow>(config =>
{
    config
    .TableName("CompositeKeyRows")
    .PrimaryKeys(x => new { x.Id1, x.Id2 });
});

var connection = new ConnectionContext(new SqlConnection(connectionString), null);

// Insert all columns
await connection.BulkInsertAsync(rows);
await connection.BulkInsertAsync(compositeKeyRows);

// Insert selected columns only
await connection.BulkInsertAsync(rows,
    row => new { row.Column1, row.Column2, row.Column3 });
await connection.BulkInsertAsync(compositeKeyRows,
    row => new { row.Id1, row.Id2, row.Column1, row.Column2, row.Column3 });

await connection.BulkUpdateAsync(rows,
    row => new { row.Column3, row.Column2 });
await connection.BulkUpdateAsync(compositeKeyRows,
    row => new { row.Column3, row.Column2 });

await connection.BulkMergeAsync(rows,
    row => row.Id,
    row => new { row.Column1, row.Column2 },
    row => new { row.Column1, row.Column2, row.Column3 });
await connection.BulkMergeAsync(compositeKeyRows,
    row => new { row.Id1, row.Id2 },
    row => new { row.Column1, row.Column2, row.Column3 },
    row => new { row.Id1, row.Id2, row.Column1, row.Column2, row.Column3 });
                        
await connection.BulkDeleteAsync(rows);
await connection.BulkDeleteAsync(compositeKeyRows);

Using Dynamic String

using EntityFrameworkCore.SqlServer.SimpleBulks.BulkDelete;
using EntityFrameworkCore.SqlServer.SimpleBulks.BulkInsert;
using EntityFrameworkCore.SqlServer.SimpleBulks.BulkMerge;
using EntityFrameworkCore.SqlServer.SimpleBulks.BulkUpdate;

var connection = new ConnectionContext(new SqlConnection(connectionString), null);

await connection.BulkInsertAsync(rows,
    [ "Column1", "Column2", "Column3" ]);
await connection.BulkInsertAsync(compositeKeyRows,
    [ "Id1", "Id2", "Column1", "Column2", "Column3" ]);

await connection.BulkUpdateAsync(rows,
    [ "Column3", "Column2" ]);
await connection.BulkUpdateAsync(compositeKeyRows,
    [ "Column3", "Column2" ]);

await connection.BulkMergeAsync(rows,
    ["Id"],
    [ "Column1", "Column2" ],
    [ "Column1", "Column2", "Column3" ]);
await connection.BulkMergeAsync(compositeKeyRows,
    [ "Id1", "Id2" ],
    [ "Column1", "Column2", "Column3" ],
    [ "Id1", "Id2", "Column1", "Column2", "Column3" ]);

Using Builder Approach in case you need both Dynamic & Lambda Expression

await connection.CreateBulkInsertBuilder<Row>()
	.WithColumns(row => new { row.Column1, row.Column2, row.Column3 })
	// or .WithColumns([ "Column1", "Column2", "Column3" ])
	.ToTable(new SqlTableInfor<Row>("Rows"))
	.ExecuteAsync(rows);

Execution Options

BulkInsert

await _context.BulkInsertAsync(rows,
    row => new { row.Column1, row.Column2, row.Column3 },
    new BulkInsertOptions
    {
        KeepIdentity = false,
        BatchSize = 0,
        Timeout = 30,
        LogTo = Console.WriteLine
    });

BulkUpdate

await _context.BulkUpdateAsync(rows,
    row => new { row.Column3, row.Column2 },
    new BulkUpdateOptions
    {
        BatchSize = 0,
        Timeout = 30,
        LogTo = Console.WriteLine
    });

BulkDelete

await _context.BulkDeleteAsync(rows,
    new BulkDeleteOptions
    {
        BatchSize = 0,
        Timeout = 30,
        LogTo = Console.WriteLine
    });

BulkMerge

await _context.BulkMergeAsync(rows,
    row => row.Id,
    row => new { row.Column1, row.Column2 },
    row => new { row.Column1, row.Column2, row.Column3 },
    new BulkMergeOptions
    {
        BatchSize = 0,
        Timeout = 30,
        WithHoldLock = false,
        ReturnDbGeneratedId = true,
        LogTo = Console.WriteLine
    });

BulkMatch

var contactsFromDb = await _context.BulkMatchAsync(matchedContacts,
    x => new { x.CustomerId, x.CountryIsoCode },
    new BulkMatchOptions
    {
        BatchSize = 0,
        Timeout = 30,
        LogTo = Console.WriteLine
    });

TempTable

var customerTableName = await _context.CreateTempTableAsync(customers,
    x => new
    {
        x.IdNumber,
        x.FirstName,
        x.LastName,
        x.CurrentCountryIsoCode
    },
    new TempTableOptions
    {
        BatchSize = 0,
        Timeout = 30,
        LogTo = Console.WriteLine
    });

DirectInsert

await _context.DirectInsertAsync(row,
    row => new { row.Column1, row.Column2, row.Column3 },
    new BulkInsertOptions
    {
        Timeout = 30,
        LogTo = Console.WriteLine
    });

DirectUpdate

await _context.DirectUpdateAsync(row,
    row => new { row.Column3, row.Column2 },
    new BulkUpdateOptions
    {
        Timeout = 30,
        LogTo = Console.WriteLine
    });

DirectDelete

await _context.DirectDeleteAsync(row,
    new BulkDeleteOptions
    {
        Timeout = 30,
        LogTo = Console.WriteLine
    });

Upsert

await _context.UpsertAsync(row,
    row => row.Id,
    row => new { row.Column1, row.Column2 },
    row => new { row.Column1, row.Column2, row.Column3 },
    new BulkMergeOptions
    {
        Timeout = 30,
        WithHoldLock = false,
        ReturnDbGeneratedId = true,
        LogTo = Console.WriteLine
    });

Returned Result

BulkUpdate

var updateResult = await dbct.BulkUpdateAsync(rows, row => new { row.Column3, row.Column2 });

Console.WriteLine($"Updated: {updateResult.AffectedRows} row(s)");

BulkDelete