{{announcement.body}}
{{announcement.title}}

Efficient Data Modification With Entity Framework Core

DZone 's Guide to

Efficient Data Modification With Entity Framework Core

Learn how to efficiently insert multiple rows, do bulk inserts, and upsert data into Entity Framework Core, including syntax for MySQL, SQL Server, Postgres, and Oracle.

· Database Zone ·
Free Resource

The rule of dumb tells that for optimal performance we need to make as few database requests as possible. This is especially relevant for insert and update scenarios, where we sometimes need to work with thousands of objects. Sending those objects to the database one by one is usually significantly slower than in a batch. With tools like Entity LINQ we can easily write efficient data modification queries.

Let's analyze the possible cases:

  • INSERT 
  • UPDATE  (DELETE)
  • Upsert (UPDATE or INSERT)

INSERT Case Optimizations

  • Insert multiple rows in a single statement. Assuming you have a collection of promotions. The following snippet inserts them in a single query
C#
 




xxxxxxxxxx
1
16


 
1
public void InsertBatch(IEnumerable<Promotions> promos)
2
{
3
    var query = DbContext.Promotions.Query((Promotions promo) =>
4
    {
5
        var set = promo.@using((promo.PromotionName, promo.Discount, promo.StartDate, promo.ExpiredDate));
6
 
          
7
        INSERT().INTO(set);
8
        var r = OUTPUT(INSERTED<Promotions>());
9
        VALUES(set.RowsFrom(promos));
10
 
          
11
        return r;
12
    });
13
 
          
14
    foreach (var promo in query)
15
        Console.WriteLine((promo.PromotionId, promo.PromotionName, promo.Discount, promo.StartDate, promo.ExpiredDate));
16
}


  •  INSERT INTO ... SELECT ...  - so called bulk insert. In case the data is already in the database, it is much cheaper to avoid data pulling altogether. Potentially, the operation can be performed inside the database. The following snippet copies addresses from one table to another according to some criteria without a single byte to leave the database:
C#
 




x


1
var cities = new[] { "Santa Cruz", "Baldwin" };
2
 
          
3
DbContext.Database.Query((Stores stores, Addresses address) =>
4
{
5
    var set = address.@using((address.Street, address.City, address.State, address.ZipCode));
6
 
          
7
    INSERT().INTO(set);
8
    SELECT((stores.Street, stores.City, stores.State, stores.ZipCode));
9
    FROM(stores);
10
    WHERE(cities.Contains(stores.City));
11
});



UPDATE Case Optimizations

  • Bulk update works when there is a need to update multiple rows in the same table. There is a special SQL construct for this case - UPDATE ... WHERE, which performs the update in a single query. Some databases, like SQL server, also support a more powerful UPDATE ... JOIN construct. Below, we update all the tax configurations without pulling them to the application server:
C#
 




x


1
var one = 0.01M;
2
var two = 0.02M;
3
DbContext.Database.Query((Taxes taxes) =>
4
{
5
    UPDATE(taxes).SET(() =>
6
        {
7
            taxes.MaxLocalTaxRate += two;
8
            taxes.AvgLocalTaxRate += one;
9
        });
10
    WHERE(taxes.MaxLocalTaxRate == one);
11
});


C#
 




xxxxxxxxxx
1
10


1
private static void PrepareProductHistory(Products products)
2
{
3
    var productHistory = ToTable<Products>(PRODUCT_HISTORY);
4
 
          
5
    SELECT(products).INTO(productHistory);
6
    FROM(products);
7
 
          
8
    Semicolon();
9
}
10
 
          
11
...
12
 
          
13
var year = 2017;
14
DbContext.Database.Query((Products products) =>
15
{
16
    PrepareProductHistory(products);
17
    var productHistory = ToTable<Products>(PRODUCT_HISTORY);
18
 
          
19
    DELETE().FROM(productHistory);
20
    WHERE(productHistory.ModelYear == year);
21
});



UPSERT Optimization

Upsert means UPDATE  or  INSERT  in a single statement. In cases when a table has more than 1 unique constraint (in addition to PK), a plain  INSERT  can fail on duplicate keys. In those cases, we usually want to ignore, replace, or combine some existing fields with new values. Most vendors support this capability, but use different syntax and providing different features.


  •  MERGE  - SQL Server and Oracle. In fact this is the official standard. In its simplistic form, it lets to specify what to do  WHEN MATCHED , i.e. when there is a unique key collision; and what to do   WHEN NOT MATCHED, i.e. we can  INSERT safely:
C#
 




x


 
1
DbContext.Category.Query((Category category) =>
2
{
3
    var staging = ToTable<Category>(CATEGORY_STAGING);
4
 
          
5
    MERGE().INTO(category).USING(staging).ON(category == staging);
6
 
          
7
    WHEN_MATCHED().THEN(MERGE_UPDATE().SET(() =>
8
            {
9
                category.CategoryName = staging.CategoryName;
10
                category.Amount = staging.Amount;
11
            }));
12
 
          
13
    var set = category.@using((category.CategoryId, category.CategoryName, category.Amount));
14
    WHEN_NOT_MATCHED().THEN(MERGE_INSERT(set.ColumnNames(), VALUES(set.RowFrom(staging))));
15
 
          
16
    Semicolon();
17
 
          
18
    return SelectAll(category);
19
});


As the picture says, it's trickier than it should be. There are many tutorials and official documentation to help you, though.

  •  INSERT ... ON DUPLICATE ... - MySQL and Postgres. The syntax is much simpler but lets you handle only the most common cases (compared to the feature-packed  MERGE):
C#
 




x


1
// There is a store which might already exist in the database.
2
// Should we add it or update? (PK is not always the only UNIQUE KEY)
3
newOrExisting.LastUpdate = DateTime.Now;
4
 
          
5
DbContext.Database.Query((Store store) =>
6
{
7
    var view = store.@using((store.StoreId, store.AddressId, store.ManagerStaffId, store.LastUpdate));
8
    INSERT().INTO(view);
9
    VALUES(view.RowFrom(newOrExisting));
10
    ON_DUPLICATE_KEY_UPDATE(() => store.LastUpdate = INSERTED_VALUES(store.LastUpdate));
11
});



Summary

Batch/bulk updates are usually an order of a magnitude faster than working with entities one by one. Optimizing those scenarios is usually a low hanging fruit improvement.


Topics:
c# ,database ,database performance ,entity framework core ,insert ,sql ,tutorial ,update ,upsert

Published at DZone with permission of Konstantin Triger . See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}