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...

Frequently used 15 LINQ 2 SQL statements

As a .Net developer,you would be very familiar with the SQL Statements. With Entity Framework 4, LINQ to SQL  is replacing the old SQL statements. Understanding LINQ to SQL  is becoming crucial for .NET developers. Here I am listing frequently used 15 SQL Statements and  corresponding LINQ to SQL statements.

Data Model

Consider an online payment system. It will have a list of clients and payments will be processed on behalf of these clients. Periodically they have to settle their payments with the clients. To store this data, we have 4 tables – Transactions,Payments,SettledTransactions and ClientMaster. Here the Transactions table will have the details of payment transactions made by the users. The Payments table will have the card specific details used in these payments. Once settled, the transactions will be saved into the SettledTransactions table with an SLID. ClientMaster will have the list of Clients

Here is the structure. Only the relevant fields are listed

Transactions		SettledTransactions		Payments		ClientMaster
tx_no		tx_no		tx_no		client_code
client_code		client_code		client_code		client_name
tx_date		slid		card_type

Statements

1	Simple Select
	SQL Statement	Select * from Transactions t Where t.Client_code='CL0011'
	LINQ Statement	Var selectlist = from t in contex.Transactions where t.client_code==”CL0011” select t
	LINQ with Lambda Expression	var selectlist = context.Transactions .Where(t => t.Client_Code == "CL0011") .Select(t => t)
2	Select Specific Columns
	SQL Statement	Select t.tx_no,t.tx_date from Transactions t Where t.Client_code='CL0011'
	LINQ Statement	var selectlist = from t in contex.Transactions where t.client_code==”CL0011” select new {t.tx_no,t.tx_date}
	LINQ with Lambda Expression	var selectlist = context.Transactions .Where(t => t.Client_Code == "CL0011") .Select(t => new {t.tx_no,t.tx_date})
3	Sort by Single Field
	SQL Statement	Select t.tx_no,t.tx_date from Transactions t Where t.Client_code='CL0011' order by t.tx_date
	LINQ Statement	var selectlist = from t in contex.Transactions where t.client_code==”CL0011” orderby t.tx_date select new {t.tx_no,t.tx_date}
	LINQ with Lambda Expression	var selectlist = context.Transactions .Where(t => t.Client_Code == "CL0011") .OrderBy(t => t.tx_date) .Select(t => new {t.tx_no,t.tx_date})
4	Sort by Multiple Fields
	SQL Statement	Select t.tx_no,t.tx_date from Transactions t Where t.Client_code='CL0011 order by t.tx_date desc,t.tx_no desc
	LINQ Statement	var selectlist = from t in contex.Transactions where t.client_code==”CL0011” orderby t.tx_date descending,t.tx_no descending select new {t.tx_no,t.tx_date}
	LINQ with Lambda Expression	var selectlist = context.Transactions .Where(t => t.Client_Code == "CL0011") .OrderByDescending(t => new { t.tx_date, t.tx_no }) .Select(t => new {t.tx_no,t.tx_date})
5	Simple Aggregate
	SQL Statement	select count(*) from Transactions t where t.client_code='CL0011'
	LINQ Statement	var cnt = (from t in context.Transactions where t.Client_Code == "CL0011" Select t).Count();
	LINQ with Lambda Expression	var cnt = context.Transactions .Where(t => t.Client_Code == "CL0011") .Count()
6	With GROUP BY Clause
	SQL Statement	select client_code,count(*) from Transactions t group by client_code
	LINQ Statement	var summary = from t in context.Transactions group t by t.Client_Code into c select new { client = c.Key, TxCount = c.Count() };
	LINQ with Lambda Expression	var summary = context.Transactions .GroupBy(t => t.Client_Code) .Select( g => new {client=g.Key,TxCount=g.Count()});
	Remarks
7	Multiple GROUP BY clause
	SQL Statement	select client_code,tx_date,count(*) from Transactions t group by client_code,tx_date order by client_code,tx_date
	LINQ Statement	var summary = from t in context.Transactions group t by new { t.Client_Code,t.tx_date } into c orderby c.Key.Client_Code,c.Key.tx_date Select new { client = c.Key.Client_Code, busdate=c.Key.tx_date, txcount = c.Count(), txAmount = (from t in c select t.total_amount).Sum() };
	LINQ with Lambda Expression	var summary = context.Transactions .GroupBy(t => new { t.Client_Code, t.tx_date }) .OrderBy(g => new { g.Key.Client_Code, g.Key.tx_date }) .Select(c => new { client = c.Key.Client_Code, busdate = c.Key.tx_date, txcount = c.Count(), txAmount =c.Sum(t => t.total_amount) });
8	With HAVING Clause
	SQL Statement	select client_code,tx_date,SUM(total_amount) from Transactions t group by client_code,tx_date having SUM(total_amount)<0 order by client_code,tx_date
	LINQ Statement	var summary = from t in context.Transactions group t by new { t.Client_Code, t.tx_date } into c where (from t in c select t.total_amount).Sum() < 0 orderby c.Key.Client_Code, c.Key.tx_date select new { client = c.Key.Client_Code, busdate = c.Key.tx_date, txcount = c.Count(), txAmount = (from t in c select t.total_amount).Sum() };
	LINQ with Lambda Expression	var summary = context.Transactions .GroupBy(t => new { t.Client_Code, t.tx_date }) .OrderBy(g => new { g.Key.Client_Code, g.Key.tx_date }) .Where(g => g.Sum(t => t.total_amount)<0) .Select(c => new { client = c.Key.Client_Code, busdate = c.Key.tx_date, txcount = c.Count(), txAmount = c.Sum(t => t.total_amount) });
9	Distinct Clause
	SQL Statment	select distinct client_code from Transactions
	LINQ Statement	var selectlist = (from t in context.Transactions select new { t.Client_Code }).Distinct();
	LINQ with Lambda Expression	var selectlist = context.Transactions .Select(t => new { t.Client_Code}).Distinct();
10	Top 10
	SQL Statement	select top 10 t.tx_no from Transactions t where t.Client_Code='CL0011' order by t.tx_no desc
	LINQ Statement	var selectlist = (from t in context.Transactions where t.Client_Code == "CL0011" orderby t.tx_no descending select new { t.tx_no }).Take(10);
	LINQ with Lambda Expression	var selectlist = context.Transactions .Where(t => t.Client_Code == "CL0011") .OrderByDescending(t => t.tx_no) .Select(t => new { t.tx_no }).Take(10);
11	Inner Join
	SQL Statement	select c.client_name,t.tx_no from Transactions t inner join ClientMaster c on t.Client_Code= c.client_code where t.Client_Code='CL0011'
	LINQ Statement	var selectlist = from t in context.Transactions join c in context.ClientMaster on t.Client_Code equals c.client_code where c.client_code=="CL0011" select new { t.tx_no, c.client_name };
	LINQ with Lambda Expression	var selectlist = context.Transactions .Join(context.ClientMaster, t => t.Client_Code, C => c.client_code,(t,c) => new {t,c} ) .Where(t => t.t.Client_Code=="CL0011") .Select(r => new {r.c.client_name,r.t.tx_no});
	Note	There is more advanced Navigational Properties in EF for getting values from the associated Entity Sets. Join statements are useful when there is no association between the Entity Sets
12	Multiple Inner Join
	SQL Statement	select t.tx_no,p.card_type,c.client_name from Transactions t inner join ClientMaster c on t.Client_Code= c.client_code inner join Payments p on t.Client_Code=p.Client_Code and t.tx_no=p.tx_no where t.Client_Code='CL0011'
	LINQ Statement	var selectlist=from t in context.Transactions join c in context.ClientMaster on t.Client_Code equals c.client_code join p in context.Payments on new {a = t.Client_Code,b = t.tx_no } equals new {a = p.Client_Code, b = p.tx_no} Where c.client_code=="CL0011" select new { t.tx_no,c.client_name,p.card_type};
	LINQ with Lambda Expression	var selectlist = context.Transactions .Join(context.ClientMaster.Where(c=>c.client_code=="CL0011"), t => t.Client_Code, c => c.client_code, (t, c) => new { t, c }) .Join(context.Payments, t => new { a = t.t.Client_Code, b = t.t.tx_no }, p => new { a = p.Client_Code, b = p.tx_no }, (t, p) => new { t, p }) .Select(r => new { r.t.t.tx_no, r.t.c.client_name, r.p.card_type });
13	Left Join
	SQL Statement	select t.tx_no,s.slid from Transactions t left join SettledTransactions s on t.Client_Code= s.client_code and t.tx_no=s.tx_no where t.Client_Code='CL0011'
	LINQ Statement	var selectlist = from t in context.Transactions where t.Client_Code=="CL0011" join s in context.SettledTransactions on new { a = t.Client_Code, b = t.tx_no } Equals new { a = s.client_code, b = s.tx_no.Value } into g from ts in g.DefaultIfEmpty() Select new { t.tx_no, slid= (int?)ts.slid };
	LINQ with Lambda Expression	var selectlist = context.Transactions.Where(t => t.Client_Code == "CL0011") .GroupJoin(context.SettledTransactions, t => new { a = t.Client_Code, b = t.tx_no }, s => new { a = s.client_code, b = s.tx_no.Value }, (t, s) => new { tx = t, sl = s }) .SelectMany(g => g.sl.DefaultIfEmpty(), (g, sl) => new { g.tx.tx_no, slid=(int?) sl.slid });
	Note	In LINQ statement,left join also uses the 'join' keyword. But it groups the data using the 'into' keyword and filter the group with 'DefaultIfEmpty()' method. 'Value' to be used to compare the 'Nullable' fields.
14	Left Join and selecting the records not in 2nd Table
	SQL Statement	select t.tx_no,s.slid from Transactions t left join SettledTransactions s on t.Client_Code= s.client_code and t.tx_no=s.tx_no where t.Client_Code='CL0011' and slid is null
	LINQ Statement	var selectlist = from t in context.Transactions where t.Client_Code == "CL0011" join s in context.SettledTransactions on new { a = t.Client_Code, b = t.tx_no } equals new { a = s.client_code, b = s.tx_no.Value } into g from ts in g.DefaultIfEmpty() where ts.slid.HasValue==false Select new { t.tx_no, slid = (int?)ts.slid };
	LINQ with Lambda Expression	var selectlist = context.Transactions.Where(t => t.Client_Code == "CL0011") .GroupJoin(context.SettledTransactions, t => new { a = t.Client_Code, b = t.tx_no }, s => new { a = s.client_code, b = s.tx_no.Value }, (t, s) => new { tx = t, sl = s }) .SelectMany(g => g.sl.DefaultIfEmpty(), (g, sl) => New { g.tx.tx_no, slid = (int?)sl.slid }) .Where(x => x.slid.HasValue==false);
	Note	Used 'HasValue' to check the slid is NULL
15	Inner + Left Join
	SQL Statement	select t.tx_no,s.slid,c.client_name from Transactions t inner join ClientMaster c on t.Client_Code=c.client_code left join SettledTransactions s on t.Client_Code= s.client_code and t.tx_no=s.tx_no where t.Client_Code='CL0011' and slid is null
	LINQ Statement	var selectlist = from t in context.Transactions join c in context.ClientMaster on t.Client_Code equals c.client_code where c.client_code == "CL0011" join s in context.SettledTransactions on new { a = t.Client_Code, b = t.tx_no } equals New { a = s.client_code, b = s.tx_no.Value } into g from ts in g.DefaultIfEmpty() select new { t.tx_no, c.client_name, slid = (int?)ts.slid };
	LINQ with Lambda Expression	var selectlist = context.Transactions .Join( context.ClientMaster.Where(x => x.client_code == "CL0011"), t => t.Client_Code, c => c.client_code, (t, c) => new { t, c } ) .GroupJoin( context.SettledTransactions, t => new { a = t.t.Client_Code, b = t.t.tx_no }, s => new { a = s.client_code, b = s.tx_no.Value }, (t, s) => new { t, s } ) .SelectMany( g => g.s.DefaultIfEmpty(), (g, s) => new { g.t.t.tx_no, g.t.c.client_name, slid = (int?)s.slid } );

**'context' is the Entity Framework context object