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