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 |
