Don't miss this next article : Live Project On ASP.Net Core 2.0 MVC - Free Webinar

I am sure you are gonna bookmark this for future references

Introduction: Basically you can write LINQ queries using method syntax (some time 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();

1. Select * from dbo.tbl_Dept

var res= dev.tbl_Dept.ToList(); //lambda

var res=from re in dev.tbl_Dept select re; //query

2. Select Did as ‘Department Id’, DName as ‘Department Name’ from dbo.tbl_Dept

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

3. Select top(2) * from tbl_Dept

var res = dev.tbl_Dept.Take(2).ToList(); //lambda

var res = from re in dev.tbl_Dept.Take(2) select re; // query

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

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

6. Select top(1) * from tbl_Dept order by Did desc

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

8. Select * from tbl_Dept order by DName, Did

var res =dev.tbl_Dept.OrderBy(X => X.DName).OrderBy(X => X.Did); //lambda

var res = from re in dev.tbl_Dept orderby (re.DName) orderby (re.Did) select re; //query

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

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

11. select * from tbl_Dept Where Did 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

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

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

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

15. 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();

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

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

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

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

20. select * from tbl_Emp

var res = from re in dev.tbl_Emp select re; //query

var res = dev.tbl_Emp; //lambda

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

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

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

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

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

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

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

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

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

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

31. 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 gselect new { EGender = g.Key, count = g.Count() }; //query

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

33. 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_Empgroup re by re.Did into kselect new { Did = k.Key, sumofsalary = k.Sum(g => g.ESalary) }; //query

34. 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_Empgroup re by re.EGender into k select new { EGender = k.Key, sumofsalary = k.Sum(g => g.ESalary) }; //query

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

36. 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.Didequals 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 🙂

Don't miss this next article : Live Project On ASP.Net Core 2.0 MVC - Free Webinar