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

1. Get all the records from tbl_Dept

   SQL

   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

2. Get all the records from tbl_Dept with column aliasing

   SQL

   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

3. Get top two records from tbl_Dept

   SQL

   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

4. Get all the records from tbl_Dept which are sorted by Did ascending

   SQL

   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. Get all the records from tbl_Dept which are sorted by Did descending

   SQL

   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. Get the record from tbl_Dept with highest Did

   SQL

   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

7. Get all the records from tbl_Dept which are sorted by DName and then by Did ascending

   SQL

   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

8. Get all the records from tbl_Dept whose Did is less than or equal to 4

   SQL

   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

9. Get all the records from tbl_Dept whose Did is either 4 or 7

   SQL

   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

10. Get all the records from tbl_Dept whose Did is among 1, 5 and 6

    SQL

    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

11. Get all the records from tbl_Dept whose Did is neither 3 nor 4

    SQL

    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

12. Get all the records from tbl_Dept whose Did is not among 1, 5 and 6

    SQL

    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

13. Get all the records from tbl_Dept whose Did is greater than or equal to 2 and less than or equal to 4

    SQL

    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

14. Get all the records from tbl_Dept whose Did is between 2 and 4

    SQL

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

15. Get all the records from tbl_Dept whose Did is not between 2 and 4

    SQL

    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

16. Get all the records from tbl_Dept whose Did is not between 2 and 4

    SQL

    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

17. Get all the records from tbl_Dept whose Description is null

    SQL

    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

18. Get all the records from tbl_Dept whose Description is not null

    SQL

    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

19. Get all the records from tbl_Emp

    SQL

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

20. Get sum of salaries of all the employees from tbl_Emp

    SQL

    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

21. Get Avg of salaries of all the employees from tbl_Emp

    SQL

    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

22. Get the max salary from tbl_Emp

    SQL

    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

23. Get the min salary from tbl_Emp

    SQL

    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

24. Get Eid, EName and Esalary from tbl_Emp

    SQL

    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

25. Get All Eid, EName and 38% of Esalary as HRA from tbl_Emp

    SQL

    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

26. Get All Eid, EName and gross salary where 38% of Esalary(Basic) is HRA from tbl_Emp

    SQL

    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

27. Get all records from tbl_Emp where ENames ends with “l”

    SQL

    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

28. Get all records from tbl_Emp where ENames starts with “rah”

    SQL

    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

29. Get number of female employees from tbl_Emp

    SQL

    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    

30. Get number of male and female employees from tbl_Emp along with gender as one column

    SQL

    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

31. Get number of employees in each department from tbl_Emp

    SQL

    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

32. Get sum of salaries for the employees as per department from tbl_Emp

    SQL

    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

33. Get sum of salaries for the employees as per gender and department from tbl_Emp

    SQL

    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

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

    SQL

    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

35. Get all Eid, EName, DName from tbl_Emp and tbl_Dept (using joins)

    SQL

    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!