Bulk update in Entity Framework

In Entity Framework, updating records are very simple. But how it is working behind the screen?


Consider below example.

Genre gnr= context.Genres.First(g => g.Name=="Rock");
gnr.Description = "Really Rocking!";
context.SaveChanges();


It is updating single record. When 'context.SaveChanges()' executes, it will pass the following statement to sql server.

exec sp_executesql N'UPDATE [dbo].[Genres]
SET [Description] = @0
WHERE ([GenreID] = @1)',
N'@0 nvarchar(max) ,@1 int',@0=N'Really Rocking!',@1=17

Well, It is using the primary id field to update the record.


Consider a scenario like we want to update a bunch of records. For example, I am trying to update all the Null Descripton with 'Tested OK' in Genres table'.

context.Genres
    .Where(g => g.Description == null)
    .ToList<Genre>()
    .ForEach(g => g.Description = "Tested OK");
context.SaveChanges();

The 'SaveChanges' passed a list of statements to SQL Server.

exec sp_executesql N'UPDATE [dbo].[Genres]
SET [Description] = @0
WHERE ([GenreID] = @1)
',N'@0 nvarchar(max) ,@1 int',@0=N'Tested OK',@1=1
go
exec sp_executesql N'UPDATE [dbo].[Genres]
SET [Description] = @0
WHERE ([GenreID] = @1)
',N'@0 nvarchar(max) ,@1 int',@0=N'Tested OK',@1=2
go
exec sp_executesql N'UPDATE [dbo].[Genres]
SET [Description] = @0
WHERE ([GenreID] = @1)
',N'@0 nvarchar(max) ,@1 int',@0=N'Tested OK',@1=3
go
exec sp_executesql N'UPDATE [dbo].[Genres]
SET [Description] = @0
WHERE ([GenreID] = @1)
',N'@0 nvarchar(max) ,@1 int',@0=N'Tested OK',@1=4
go

Here also Entity Framework uses the same strategy. Updating the each record using its primary Id.

Consider deleting multiple records

context.Genres
    .Where(g => g.Description == null)
    .ToList<Genre>()
    .ForEach(g => context.Genres.Remove(g));
context.SaveChanges();

This will produce a list of delete statements also as below.

exec sp_executesql N'DELETE [dbo].[Genres]
WHERE ([GenreID] = @0)',N'@0 int',@0=19
go
exec sp_executesql N'DELETE [dbo].[Genres]
WHERE ([GenreID] = @0)',N'@0 int',@0=20
go
exec sp_executesql N'DELETE [dbo].[Genres]
WHERE ([GenreID] = @0)',N'@0 int',@0=21
go

The Entity Framework always execute SQL statements record-wise. It uses the filters to set the status inside the Entity Model and updates the modifications one by one.But this will create more traffic in network and will affect the performance. Entity Framework lacks the ability to update or delete multiple records using a single SQL statement.

To resolve this Mr.Paul Welter has designed Entity Framework Extended Library. It can be installed from NuGet package manager. It can be found by searching with 'EntityFramework.Extended'. After installation, EntityFramework.Extended dll will be added to the project reference. It should be refered in the page with the following statement

using EntityFramework.Extensions;

Now our 'context' object will have added 'Upadate<>' and 'Delete<>' methods.

Here is the update statement using the extended library

context.Genres.Update(g => g.Description == null, g => new Genre { Description = "Tested OK" });

It accepts two parameters. First one is the filter and the second one is a new object with modified values.

This will update all records with the null description with the single statement

exec sp_executesql N'UPDATE [dbo].[Genres] SET
[Description] = @p__update__0
FROM [dbo].[Genres] AS j0 INNER JOIN (
SELECT
    [Extent1].[GenreID] AS [GenreID]
    FROM [dbo].[Genres] AS [Extent1]
    WHERE [Extent1].[Description] IS NULL
) AS j1 ON (j0.[GenreID] = j1.[GenreID])',N'@p__update__0 nvarchar(9)',@p__update__0=N'Tested OK'


The Delete statement is also a kind of same.

context.Genres.Delete(g => g.Description==null);

It will delete multiple records with the help of the below statement.

DELETE [dbo].[Genres]
FROM [dbo].[Genres] AS j0 INNER JOIN (
SELECT
    [Extent1].[GenreID] AS [GenreID]
    FROM [dbo].[Genres] AS [Extent1]
    WHERE [Extent1].[Description] IS NULL
) AS j1 ON (j0.[GenreID] = j1.[GenreID])

Note that, we haven't used the 'SaveChanges' call to the context object. Both 'Update' and 'Delete' will execute directly.


Happy coding...