Oct 8, 2011

Conversion of sql server query to Linq in C#

Linq to sql

Suppose we have an employee table with following data
Table name: Employee
EmpId
Name
Salary
1
Davis     
500
2
Alan
800
3
Greg
400

Table name: Student
StuId
Name
Subject
Marks
1
Robert
Phy
60
2
Pacino                                                                                              
Phy
54
3
Pacino                                                                                             
Che
45
4
Robert
Che
56
5
Pacino                                                                                              
Bio
67
6
Nicholson                                                                                          
Che
32
7
Nicholson                                                                                           
Phy
41
8
Nicholson                                                                                          
Bio
44
9
Robert
Bio
72

SELECT Clause in Linq
Selecting all the columns of a table using Linq in c sharp by Select operator of Linq

Sql query:
SELECT * FROM Employee
Equivalent Linq statement in c sharp
var query = from emp in EntityObj.Employees
select emp;

Selecting specific rows from table using Linq in C#
Selecting only two columns of a table using Linq in c sharp by constructor of Select operator of Linq
Sql query
SELECT Name,Salary FROM Employee
Equivalent Linq statement in c sharp
var query = from emp in EntityObj.Employees
select new {
emp.Name ,
emp.Salary
};

Output:
Name
Salary
Davis
500
Alan
800
Greg
400

Top clause in Linq
Selecting the top two rows of a table using Linq in c sharp by Take operator of Linq
Sql query
SELECT TOP(2) Name,Salary FROM Employee
Equivalent Linq statement in c sharp
var query = (from emp in EntityObj.Employees
select new {
emp.Name ,
emp.Salary
}).Take(2);

Output:
Name
Salary
Davis
500
Alan
800

Where Clause in Linq
Restricting the rows by using Where operator of Linq
Sql query
SELECT * FROM Employee WHERE Salary < 600
Equivalent Linq statement in c sharp
var query = from emp in EntityObj.Employees
    where emp.Salary < 600
   select emp;

Output:
EmpId
Name
Salary
1
Davis
500
3
Greg
400

Where caluse with more than one condition in Linq
We can put more than one conditions to selecting the rows from table by using && operatots in Linq statement of c sharp

Sql query
SELECT Name,Salary FROM Employee WHERE Name = 'Davis' AND EmpId IS NOT NULL
Equivalent Linq statement in c sharp
var query = from emp in EntityObj.Employees
where emp.Name == "Davis" && emp.EmpId != null  
select new{
emp.Name ,
emp.Salary
};

Output:
EmpId
Name
Salary
1
Davis
500

Order by caluse in Linq
Selecting the rows of table in asceding order by using the Linq operator OrderBy

Sql query
SELECT * FROM Employee ORDER BY Salary ASC
Equivalent Linq statement in c sharp
var query = from emp in EntityObj.Employees
orderby emp.Salary 
   select emp;
Output:
EmpId
Name
Salary
3
Greg
400
1
Davis
500
2
Alan
800

Order by descending in Linq
Selecting the rows of table in descending order by using the Linq operator OrderBy
Sql query
SELECT * FROM Employee ORDER BY Salary DESC
Equivalent Linq statement in c sharp
var query = from emp in EntityObj.Employees
    orderby emp.Salary  descending
    select emp;

Output:
EmpId
Name
Salary
2
Alan
800
1
Davis
500
3
Greg
400

Order by more than one column in Linq
We can select the rows order by more than one columns in Linq using c sharp
Sql query
SELECT * FROM Student ORDER BY Name ASC ,[Subject] DESC
Equivalent Linq statement in c sharp
var query = from stu in EntityObj.Students
orderby stu.Name, stu.Subject descending
    select stu;

Output:
StuId
Name
Subject
Marks
7
Nicholson
Phy
41
6
Nicholson                                                                                            
Che
32
8
Nicholson                                                                                            
Bio
44
2
Pacino
Phy
54
3
Pacino                                                                                             
Che
45
5
Pacino                                                                                          
Bio
67
1
Robert                                                                                          
Phy
60
4
Robert                                                                                           
Che
56
9
Robert
Bio
72

No comments:

Post a Comment