Linq Queries Examples Using Method And Query Syntax






4.79/5 (18 votes)
Linq Queries Examples Using Method And Query Syntax
I am sure you are going to bookmark and share this.
Introduction
Basically, you can write LINQ queries using method syntax (sometimes called as Lambda Expressions) and query syntax. So, here I have tried to gather 36 T-SQL queries along with their equivalent LINQ queries in both method and query syntax.
Scenario
Let us consider a scenario where we have two tables in SQL Server database MyOrg
, i.e., tbl_Dept
and tbl_Emp
and say we have generated an entity data model with these two tables as MyOrg.edmx as shown below and we have created dev
as an object of MyOrgEntities
context object, i.e.,
Queries
MyOrgEntites dev=new MyOrgEntities();
-
Get all the records from tbl_Dept
Select * from dbo.tbl_Dept // T-Sql var res= dev.tbl_Dept.ToList(); //lambda var res=from res in dev.tbl_Dept select res; //query
-
Get all the records from tbl_Dept with column aliasing
Select Did as ‘Department Id’, _ DName as ‘Department Name’ from dbo.tbl_Dept // T-Sql var res = dev.tbl_Dept.Select(x => _ new { DepartmentId = x.Did, DepartmentName = x.DName }); //lambda var res=from re in dev.tbl_Dept _ select new{Department_Id=re.Did,Department_Name=re.DName}; //query
-
Get top two records from tbl_Dept
Select top(2) * from tbl_Dept // T-Sql var res = dev.tbl_Dept.Take(2).ToList(); //lambda var res = from re in dev.tbl_Dept.Take(2) select re; // query
-
Get all the records from tbl_Dept which are sorted by Did ascending
select * from tbl_Dept order by Did var res = dev.tbl_Dept.OrderBy(x => x.Did).ToList();//lambda var res = from re in dev.tbl_Dept orderby (re.Did) select re;//query
-
Get all the records from tbl_Dept which are sorted by Did descending
Select * from tbl_Dept order by Did desc var res = from re in dev.tbl_Dept orderby (re.Did) descendingselect re; //query var res = dev.tbl_Dept.OrderByDescending(x => x.Did).ToList(); //lambda
-
Get the record from tbl_Dept with highest Did
Select top(1) * from tbl_Dept order by Did desc //T-Sql var res = dev.tbl_Dept.OrderByDescending(x => x.Did).Take(1); //lambda var res = (from re in dev.tbl_Dept orderby _ (re.Did) descending select re).Take(1).ToList(); //query
-
Get all the records from tbl_Dept which are sorted by DName and then by Did ascending
Select * from tbl_Dept order by DName, Did //T-Sql var res =dev.tbl_Dept.OrderBy(X => X.DName).ThenBy(X => X.Did); //lambda var res = from re in dev.tbl_Dept orderby (re.DName) orderby (re.Did) select re; //query
-
Get all the records from tbl_Dept whose Did is less than or equal to 4
Select * from tbl_Dept Where Did <= 4 var res = dev.tbl_Dept.Where(x => x.Did <= 4); //lambda var res = from re in dev.tbl_Dept where (re.Did <= 4) select re; //query
-
Get all the records from tbl_Dept whose Did is either 4 or 7
Select * from tbl_Dept Where Did = 4 OR Did = 7 var res = dev.tbl_Dept.Where(x => x.Did == 4 || x.Did == 7).ToList(); //lambda var res = from re in dev.tbl_Dept where (re.Did == 4 || re.Did == 7) select re; //query
-
Get all the records from tbl_Dept whose Did is among 1, 5 and 6
select * from tbl_Dept Where Did IN (1, 5, 6) //T-Sql var res = from re in dev.tbl_Dept where (re.Did == 1 || _ re.Did == 5||re.Did==6) select re; //query var res = dev.tbl_Dept.Where(x => x.Did == 1 || x.Did == 5||x.Did==6).ToList(); //lambda
-
Get all the records from tbl_Dept whose Did is neither 3 nor 4
select * from tbl_Dept Where Did <> 3 and Did <> 4 var res = dev.tbl_Dept.Where(x => x.Did != 3 && x.Did != 4).ToList(); //lambda var res = from re in dev.tbl_Dept where (re.Did != 3 && re.Did != 4) select re; //query
-
Get all the records from tbl_Dept whose Did is not among 1, 5 and 6
select * from tbl_Dept Where Did NOT IN (1, 5, 6) var res = from re in dev.tbl_Dept _ where (re.Did != 1 && re.Did != 5&& re.Did!=6) select re; //query var res = dev.tbl_Dept.Where_ (x => x.Did != 1 && x.Did != 5 && x.Did!=6).ToList(); //lambda
-
Get all the records from tbl_Dept whose Did is greater than or equal to 2 and less than or equal to 4
select * from tbl_Dept Where Did >= 2 and Did <= 4 var res = dev.tbl_Dept.Where(x => x.Did>=2 && x.Did<=4).ToList(); //lambda var res = from re in dev.tbl_Dept where (re.Did >= 2 && re.Did <=4) select re; //query
-
Get all the records from tbl_Dept whose Did is between 2 and 4
select * from tbl_Dept Where Did between 2 and 4 var res = from re in dev.tbl_Dept where (re.Did > 2 && re.Did <4) select re;//query var res = dev.tbl_Dept.Where(x => x.Did>2 && x.Did<4).ToList();
-
Get all the records from tbl_Dept whose Did is not between 2 and 4
select * from tbl_Dept Where Did < 2 and Did > 4 var res = dev.tbl_Dept.Where(x => x.Did<2 || x.Did>4).ToList(); //lambda var res = from re in dev.tbl_Dept where (re.Did < 2 || re.Did >4) select re; //query
-
Get all the records from tbl_Dept whose Did is not between 2 and 4
select * from tbl_Dept Where Did not between 2 and 4 var res = dev.tbl_Dept.Where(x => x.Did <= 2 || x.Did >= 4).ToList(); //lambda var res = from re in dev.tbl_Dept where (re.Did <= 2 || re.Did >= 4) select re; //query
-
Get all the records from tbl_Dept whose Description is null
select * from tbl_Dept Where [Description] IS NULL var res = from re in dev.tbl_Dept where (re.Description ==“”) select re; //query var res = dev.tbl_Dept.Where(x => x.Description == “”); //lambda
-
Get all the records from tbl_Dept whose Description is not null
select * from tbl_Dept Where [Description] IS NOT NULL var res = dev.tbl_Dept.Where(x => x.Description != “”); //lambda var res = from re in dev.tbl_Dept where (re.Description !=“”) select re; //query
-
Get all the records from tbl_Emp
select * from tbl_Emp var res = from re in dev.tbl_Emp select re; //query var res = dev.tbl_Emp; //lambda
-
Get sum of salaries of all the employees from tbl_Emp
select SUM(ESalary) AS SumOfTheSalaries from tbl_Emp var res = dev.tbl_Emp.Sum(x => x.ESalary);//lambda var res = (from re in dev.tbl_Emp select re.ESalary).Sum(); //query
-
Get Avg of salaries of all the employees from tbl_Emp
select AVG(ESalary) AS SumOfTheSalaries from tbl_Emp var res = dev.tbl_Emp.Average(x => x.ESalary);//lambda var res = (from re in dev.tbl_Emp select re.ESalary).Average();//query
-
Get the max salary from tbl_Emp
select MAX(ESalary) AS SumOfTheSalaries from tbl_Emp var res = dev.tbl_Emp.Max(x => x.ESalary);//lambda var res = (from re in dev.tbl_Emp select re.ESalary).Max();//query
-
Get the min salary from tbl_Emp
select MIN(ESalary) AS SumOfTheSalaries from tbl_Emp var res = dev.tbl_Emp.Min(x => x.ESalary); //lambda var res = (from re in dev.tbl_Emp select re.ESalary).Min(); //query
-
Get Eid, EName and Esalary from tbl_Emp
select Eid, EName, ESalary from tbl_Emp var res= dev.tbl_Emp.Select(x=>new{x.Eid,x.EName,x.ESalary}); //lambda var res = from re in dev.tbl_Emp select new { re.Eid, re.EName, re.ESalary }; //query
-
Get All Eid, EName and 38% of Esalary as HRA from tbl_Emp
select Eid, EName, ESalary * 0.38 AS HRA from tbl_Emp var res = dev.tbl_Emp.Select(x => new _ { x.Eid, x.EName, HRA = x.ESalary * 0.38 }).ToList(); //lambda var res=(from re in dev.tbl_Emp select new_ {re.Eid,re.EName,HRA=re.ESalary*0.38}).ToList(); //query
-
Get All Eid, EName and gross salary where 38% of Esalary(Basic) is HRA from tbl_Emp
select Eid, EName, ESalary * 0.38 AS HRA, ESalary + (ESalary * 0.38) As GS from tbl_Emp var res = dev.tbl_Emp.Select(x => new { x.Eid, x.EName, _ HRA = x.ESalary * 0.38, GS = x.ESalary + (x.ESalary * 0.38) }).ToList(); //lambda var res = (from re in dev.tbl_Emp select new { re.Eid, re.EName, _ HRA = re.ESalary * 0.38, GS = re.ESalary + (re.ESalary * 0.38) }).ToList(); //query
-
Get all records from tbl_Emp where ENames ends with “l”
select * from tbl_Emp where EName like ‘%l’ var res = from re in dev.tbl_Emp where (re.EName.EndsWith(“l”)) select re; //query var res = dev.tbl_Emp.Where(x => x.EName.EndsWith(“l”)); //lambda
-
Get all records from tbl_Emp where ENames starts with “rah”
select * from tbl_Emp where EName like ‘rah%’ var res = dev.tbl_Emp.Where(x => x.EName.StartsWith(“rah”)); //lambda var res = from re in dev.tbl_Emp where (re.EName.StartsWith(“rah”)) select re; //lambda
-
Get number of female employees from tbl_Emp
select COUNT(*) from tbl_Emp where EGender = ‘F’ var res = dev.tbl_Emp.Where(x => x.EGender == “F”).Count();//lambda var res=(from re in dev.tbl_Emp where(re.EGender==“F”) select re).Count(); //query
-
Get number of male and female employees from tbl_Emp along with gender as one column
select COUNT(*) NoOfEmp, EGender from tbl_Emp Group By EGender var res = dev.tbl_Emp.GroupBy(x => x.EGender).Select_ (y => new { EGender = y.Key, count = y.Count() }); //lambda var res = from c in dev.tbl_Emp group c by c.EGender into g _ select new { EGender = g.Key, count = g.Count() }; //query
-
Get number of employees in each department from tbl_Emp
select COUNT(*) NoOfEmp, Did from tbl_Emp Group By Did var res=dev.tbl_Emp.GroupBy(x=>x.Did).Select(y=> _ new{Did=y.Key,numberofemp=y.Count()}); //lambda var res = from re in dev.tbl_Emp group re by re.Did into k _ select new { Did = k.Key, numberofemp = k.Count() }; //query
-
Get sum of salaries for the employees as per department from tbl_Emp
select SUM(ESalary) SumOfSal, Did from tbl_Emp Group By Did var res = dev.tbl_Emp.GroupBy(x => x.Did).Select(y => _ new { Did = y.Key, sumofsalary = y.Sum(z => z.ESalary) }); //lambda var res = from re in dev.tbl_Emp group re by re.Did into k _ select new { Did = k.Key, sumofsalary = k.Sum(g => g.ESalary) }; //query
-
Get sum of salaries for the employees as per gender and department from tbl_Emp
select SUM(ESalary) SumOfSal, EGender from tbl_Emp Group By EGender var res = dev.tbl_Emp.GroupBy(x => x.EGender).Select(y => _ new { EGender = y.Key, Sumofsalary = y.Sum(z => z.ESalary) }); //lambda var res = from re in dev.tbl_Emp group re by re.EGender into k _ select new { EGender = k.Key, sumofsalary = k.Sum(g => g.ESalary) }; //query
-
Get sum of salaries for the employees as per gender and department from tbl_Emp whose sum of salaries is greater and equal to 20000
select SUM(ESalary) SumOfSal, EGender, _ Did from tbl_Emp Group By Did,EGender Having Sum(ESalary) >= 20000 var res = dev.tbl_Emp.GroupBy(x => new { x.Did, x.EGender }).Select(y => _ new { EGender = y.Key.EGender,Did = y.Key.Did, Sumofsalary = y.Sum(z => _ z.ESalary) }).Where(s => s.Sumofsalary > 20000); //lambda var res = (from re in dev.tbl_Emp group re bynew { re.Did, re.EGender } _ into k selectnew { EGender = k.Key.EGender, Did = k.Key.Did, _ sumofsalary = k.Sum(z => z.ESalary) }).Where(z => z.sumofsalary > 20000); //query
-
Get all Eid, EName, DName from tbl_Emp and tbl_Dept (using joins)
select E.Eid,E.EName,D.DName from tbl_Emp E join tbl_Dept D on E.Did=D.Did var res = from dep in dev.tbl_Dept join emp in dev.tbl_Emp on dep.Did _ equals emp.Eid selectnew { emp.Eid, emp.EName, dep.DName }; //query var res=dev.tbl_Dept.Join(dev.tbl_Emp,x=>x.Did,y=>y.Eid,(x,y)=> _ new{y.Eid,y.EName,x.DName}).ToList(); //lambda
Thanks for reading!