DOCSEARCHbyAlgolia
Submit Docs

EntityFrameworkCore.SqlServer.SimpleBulks

repository·master·Indexed 42 days ago

https://github.com/phongnguyend/entityframeworkcore.sqlserver.simplebulks

A high-performance .NET library for SQL Server that enables bulk synchronization of in-memory records using SqlBulkCopy. It supports Entity Framework Core DbContexts via DbContextExtensions and direct SqlConnection instances via ConnectionContextExtensions. The library provides optimized methods for BulkInsertAsync, BulkUpdateAsync, BulkMergeAsync, and BulkDeleteAsync, offering significant performance and memory efficiency improvements over standard EF Core operations.

Tokens
3.2K
Snippets
14
Records
15
Agent score
3%

What's inside EntityFrameworkCore.SqlServer.SimpleBulks

  1. Overview of EntityFrameworkCore.SqlServer.SimpleBulks

    master

    EntityFrameworkCore.SqlServer.SimpleBulks is a .NET library designed to synchronize large numbers of in-memory records into a database using the SqlBulkCopy class.

    It provides two primary ways to interact with the database:

    1. DbContextExtensions: Extension methods for your Entity Framework Core DbContext instance.
    2. ConnectionContextExtensions: Methods to work directly with a SqlConnection instance, allowing you to perform bulk operations without requiring Entity Framework Core.

  3. Use ConnectionContextExtensions for bulk operations

    master

    If you are not using Entity Framework Core, you can use ConnectionContext to perform bulk operations directly via a SqlConnection.

    When using ConnectionContext, you must first configure the table mapping globally using TableMapper to define table names and primary keys.

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

// 2. Use ConnectionContext
var connection = new ConnectionContext(new SqlConnection(connectionString), null);
await connection.BulkInsertAsync(rows, row => new { row.Column1, row.Column2 });

  4. Use DbContextExtensions for bulk operations

    master

    You can perform bulk operations directly on your DbContext instance. The library supports three main styles of operation:

    1. Lambda Expressions: Type-safe selection of columns.
    2. Dynamic Strings: Selecting columns using string arrays.
    3. Builder Approach: A fluent API for complex scenarios combining both styles.

    Supported operations include BulkInsertAsync, BulkUpdateAsync, BulkMergeAsync, and BulkDeleteAsync.

    // Using Lambda Expression
await dbct.BulkInsertAsync(rows, row => new { row.Column1, row.Column2 });
await dbct.BulkUpdateAsync(rows, row => new { row.Column3, row.Column2 });
await dbct.BulkMergeAsync(rows, 
    row => row.Id, 
    row => new { row.Column1 }, 
    row => new { row.Column1, row.Column2 });
await dbct.BulkDeleteAsync(rows);

// Using Dynamic String
await dbct.BulkUpdateAsync(rows, [ "Column3", "Column2" ]);
await dbct.BulkMergeAsync(rows, ["Id"], ["Column1"], ["Id", "Column1"]);

// Using Builder Approach
await dbct.CreateBulkInsertBuilder<Row>()
    .WithColumns(row => new { row.Column1, row.Column2 })
    .ToTable(dbContext.GetTableInfor<Row>())
    .ExecuteAsync(rows);

  5. Benchmark CreateTempTable performance

    master

    The time taken to CreateTempTable scales linearly with the number of rows. Creating a temp table for 1,000,000 rows takes approximately 6.2 seconds.

    ### CreateTempTable Performance
| Method | RowsCount | Mean | Allocated |
|---------------- |-------------:|------:|--------------:|
| **CreateTempTable** | 1000000 | 6,198.206 ms | 340282.7 KB |

  6. Compare BulkMatch performance (Single Column)

    master

    BulkMatch is used for matching existing records. When matching against a single column, BulkMatch is comparable to or slightly faster than EFCoreBatchSelect. For 100,000 rows, BulkMatch is faster than the standard EF Core batch select.

    ### BulkMatch Performance (Single Column)
| Method | RowsCount | Mean | Allocated |
|------------------ |-------------:|------:|--------------:|
| **EFCoreBatchSelect** | 100000 | 920.412 ms | 918085.6 KB |
| BulkMatch | 100000 | 742.030 ms | 82419.43 KB |

  7. Compare BulkMerge with ReturnDbGeneratedId enabled

    master

    When using BulkMerge with the option to return database-generated IDs (ReturnDbGeneratedId), there is a slight performance overhead compared to not returning them. For 1,000,000 rows, returning IDs adds approximately 4 seconds to the operation.

    ### BulkMerge (ReturnDbGeneratedId) Performance
| Method | RowsCount | Mean | Allocated |
|----------------------- |-------------:|------:|--------------:|
| **ReturnDbGeneratedId** | 1000000 | 32.449 s | 1003.67 MB |
| NotReturnDbGeneratedId | 1000000 | 28.253 s | 710.22 MB |

  8. Compare BulkMerge (Upsert) performance with EF Core

    master

    BulkMerge (used for Upsert operations) is significantly faster than standard EF Core upsert patterns. At 100,000 rows, BulkMerge is approximately 2.5x faster than EFCoreUpsert.

    ### BulkMerge Performance
| Method | RowsCount | Mean | Allocated |
|------------- |-------------:|------:|--------------:|
| **EFCoreUpsert** | 100000 | 13,902.06 ms | 1762696.52 KB |
| BulkMerge | 100000 | 3,415.31 ms | 115233.2 KB |

  9. Compare BulkUpdate performance with EF Core

    master

    BulkUpdate provides substantial speedups over standard EF Core updates. For 100,000 rows, BulkUpdate completes in ~2 seconds, whereas EFCoreUpdate takes ~8.5 seconds. The memory allocation is also drastically lower.

    ### BulkUpdate Performance
| Method | RowsCount | Mean | Allocated |
|------------- |-------------:|------:|--------------:|
| **EFCoreUpdate** | 100000 | 8,495.18 ms | 810248.07 KB |
| BulkUpdate | 100000 | 2,091.42 ms | 33819.46 KB |

  10. Compare BulkInsert performance with EF Core

    master

    The BulkInsert method is significantly faster and more memory-efficient than standard EF Core Insert operations, especially as the row count increases. For example, at 100,000 rows, BulkInsert takes approximately 998ms compared to 7,107ms for standard EF Core, with a massive reduction in allocated memory.

    ### BulkInsert Performance (Single Table)
| Method | RowsCount | Mean | Allocated |
|------------- |-------------:|------:|--------------:|
| **EFCoreInsert** | 100000 | 7,107.29 ms | 950162.56 KB |
| BulkInsert | 100000 | 998.42 ms | 51730.81 KB |

  11. Compare BulkMatch performance (Multiple Columns)

    master

    BulkMatch excels when matching against multiple columns. For 100,000 rows, BulkMatch is significantly faster (2.6s) than standard EFCoreSelect (89.2s).

    ### BulkMatch Performance (Multiple Columns)
| Method | RowsCount | Mean | Allocated |
|------------------ |-------------:|------:|--------------:|
| **EFCoreSelect** | 100000 | 89,204.16 ms | 1237424.23 KB |
| BulkMatch | 100000 | 2,612.00 ms | 138686.52 KB |

  12. Compare BulkInsert performance for multiple tables

    master

    When performing inserts involving a parent table and multiple child tables (e.g., 1 parent + 5 children), BulkInsert maintains a significant performance advantage over standard EF Core operations. At 100,000 rows, BulkInsert is roughly 6x faster than EFCoreInsert.

    ### BulkInsert Performance (Multiple Tables)
| Method | RowsCount | Mean | Allocated |
|------------- |-------------:|------:|--------------:|
| **EFCoreInsert** | 100000 | 59,389.89 ms | 7282561.93 KB |
| BulkInsert | 100000 | 9,504.12 ms | 327100.08 KB |