Location>code7788 >text

NET Open Source EF Core Batch Extension Tool, really useful!

Popularity：687 ℃/2024-09-30 10:51:27

preamble

Entity Framework Core (EF Core), a popular object-relational mapper (ORM) in the .NET ecosystem, is favored for its lightweight, scalability, and support for multiple database engines.

In this article, we will introduce an open source EF Core batch processing extension for .NET that greatly improves the efficiency and performance of data processing. See how easy it is to integrate into our existing EF Core project.

The Entity Framework Core (EF Core) Bulk Extension Library provides tools to support bulk copy functionality for insert, update, delete, read (CRUD), empty table (Truncate), and save changes (SaveChanges) operations on the .

The library supports SQL Server, PostgreSQL, MySQL and SQLite databases.

The library is lightweight and efficient, covers common CRUD operations, and was named one of the top 20 recommended EF Core extensions by Microsoft.

Imprint

Internal mechanisms

SQL Server: SqlBulkCopy is used for insert operations, while update and delete operations combine BulkInsert with native SQL's MERGE.

SQLite: Since there is no BulkCopy, the library uses pure SQL combined with UPSERT.

caveat

Batch testing: You cannot use UseInMemoryDb because InMemoryProvider does not support specific relational database methods.

How is it used?

First use Nuget to install .

Install-Package

Batch operation example

(entities);                   
(entities);           
(entities);   
(entities);                  
(entities);                  
(entities);                    
();

asynchronous version

(entities);  
(entities);    //Upsert  
(entiti);//Sync  
(entities);  
(entities);  
(entities);  
();

Use with EF Core

// removing
(a =>  >  500).BatchDelete();  
(a =>  >  500).BatchDeleteAsync();  
  
// update
(a =>  <= 500).BatchUpdate(a => new Item { Quantity =  + 100});  
(a =>  <= 500).BatchUpdateAsync(a => new Item {Quantity=+100});  
  // can be as value '+100' or as variable '+incrementStep' (int incrementStep = 100;)  
    
// update
(a =>  <= 500).BatchUpdate(new Item { Description = "Updated" });  
(a =>  <= 500).BatchUpdateAsync(new Item { Description = "Updated" });   
  
// Truncate  
<Entity>();  
<Entity>();

batch operation

Connection String Configuration

If Windows authentication is used, Trusted_Connection=True should be included in the connection string because SQL authentication information needs to be retained in the connection.

transaction management

Each batch operation is processed as a separate transaction by default and is committed automatically. If multiple operations need to be performed in a single process, transactions should be used explicitly.

For example, since child tables are not automatically inserted with the parent table, a second call needs to be made explicitly:

using (var transaction = ())
{
    (entitiesList);
    (subEntitiesList);
    ();
}

Batch insert or update

The BulkInsertOrUpdate method is used in scenarios where you need to perform an insert or update operation on the same database connection. An update is performed when the Primary Key matches, otherwise an insert is performed.

Batch insert, update or delete

The BulkInsertOrUpdateOrDelete method effectively synchronizes the rows in the table with the input data. Database records that are not in the input list are deleted.

batch read

The BulkRead method performs SELECT and JOIN operations based on one or more unique columns, as specified in the configured UpdateByProperties.

typical example

using (var transaction = ())
{
    // Insert or update the master table
    (mainEntitiesList);
    
    // Inserting or updating a sub-table
    (subEntitiesList);
    
    // Submission of transactions
    ();
}

// Synchronize table rows with input data
(allEntitiesList);

// Reading data based on unique columns
(uniqueColumnsConfig);

Performance Test Results

The following are the results of the performance test (in seconds) on SQL Server 2019:

Test Configuration

Hardware configuration: Intel i7-10510U CPU @ 2.30GHz, DDR3 16GB, SSD Samsung 512GB

Test Table Structure: The test table TestTable contains 6 columns (Guid, string x2, int, decimal?, DateTime), all columns are inserted and 2 columns are updated.

performance comparison

caveat

Small dataset overhead: For smaller datasets (less than 1000 rows), there is some overhead since most batch operations require creating temporary tables and deleting them upon completion.

Recommendation: It is recommended to use batch operations for datasets larger than 1000 rows for best performance.

Project Address

/borisdj/

summarize

I hope this article will help you in developing with EF Core. You are welcome to share your experience and suggestions in the comments section.

ultimate

If you found this article helpful, why not support it with a like? Your support is what keeps me motivated to continue sharing my knowledge. Feel free to leave a comment if you have any questions or need further help.

You can also join WeChat[DotNet Technician] community to share ideas and grow with other tech-loving peers!Excellence is a habit, so feel free to leave a comment and learn!

<Previous Next>