Increase EF Core Efficiency: with Bulk Updates

 ExecuteUpdate was introduced in EF Core 6.0 to improve data manipulation capabilities. 

To demonstrate the use of ExecuteUpdate,  let's consider examples below. 

using (var context = new DbContext())

var items= context.Items.ToList();

foreach (var item in items)

{

    item .Name = "XXX";

    item .LastUpdateDateTime = DateTime.UtcNow;

}

context.SaveChanges();

The first example uses the traditional approach of iterating over a list of items and updating each one.

context.Items.ExecuteUpdate(i => i.SetProperty(p => p.Name, "XXX")

                                 .SetProperty(g => p.LastUpdateDateTime, DateTime.UtcNow))

The second example uses ExecuteUpdate.

When consider a bulk update, EFCore will generate the following query for each line. As a result for 1000 lines, 1000 SQL queries will be executed.

 --Sigle query for bulk updates

UPDATE "Items" AS "i"

SET "LastUpdateDateTime" = rtrim(rtrim(strftime('%Y-%m-%d %H:%M:%f', 'now'), '0'), '.'),

    "Name" = 'XXX'

 The ExecuteUpdate method translates the update operation into a single SQ query executing without loading entities into memory. This results in significant performance gains, particularly noticeable in large-scale operations.

You can read more about ExecuteUpdate here.

Regards,

Samitha

:

EF Core AsSplitQuery

 AsSplitQuery will perform separate queries instead of complex joins when returning data from multiple tables. 

When using a single query result with data from multiple tables,  there is a probability of   “cartesian explosion” of duplicate data across many columns and rows.

e.g

  var result =dbContext.Student.Where(s=> s.Id == id)

      .Include(x => x.Books)

      .ThenInclude(x => x.Overdues)

      .AsSplitQuery();

Cheers,

Samitha