Complex LINQ & SQL Queris

Complex LINQ & SQL Queris

LINQ:

linq query to remove duplicates

var distinctItems = items.GroupBy(x => x.Id).Select(y => y.First());

SQL:

sql query to delete duplicate records

WITH CTE AS(
   SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
       RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1
Method 3:

Using "Delete Top( )" clause:

If you want to delete duplicate rows for a particular empid then use "Top()" command in delete query as shown below.
    delete top(2) From EmpDup where empid=2
OR
    delete top(select count(*)-1 From EmpDup x where x.empid=2) From EmpDup where empid=2
    

Method 4:

If you want to delete all the rows if the selected columns repeated more than 1 time then use below query.
Query to delete 3 duplicated rows (in our example table) or repeated more than 1 time.
    delete from EmpDup where EmpID in(select EmpID from EmpDup group by EmpId having
    count(*) >1)

LINQ Queries in both syntaxes

LINQ Queries in both syntaxes

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 res in dev.tbl_Dept select res;   //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 g  select 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_Emp  group re by re.Did into k  select 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_Emp  group 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.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