Introduction
Now a days, working with LINQ is an added advantage for any developer. We can write almost all types of queries when compared with SQL Server SQL querying. The important thing in LINQ is understanding the syntax and what it returns.
Background
Let me explain the scenario where I have used Linq and what is the reason. In CSV file, I have a million or more email ids. Now, the steps I have to do are read that file to a datatable, validate each email address and remove all the email ids that are not in valid format. Generally, we used to loop each row in datatable and check whether each email is valid or not.
It is a slightly lengthy code and not acceptable when we have large number of records. So, I used LINQ instead of looping. Not only this scenario, we can use LINQ in different scenarios. I have given frequently used Linq queries in this topic.
Using the Code
We start by understanding the basic LINQ syntax. In general, a SQL query can be written as:
select EmpId,Ename,JoinDate from tblEmployee where EmpId>10
Here, we have to observe 3 points:
- What is source (i.e., Table name)
- What we need from the source (i.e.
Select clause followed by column name list)
- Finally,
where condition we want to apply
In LINQ also, we have to follow the same points. The above SQL query can be written in LINQ as:
var result=from emp in tblEmployee where emp.EmpId > 10 select emp ;
From the above LINQ query:
- "
from emp in tblEmployee" represents source of our data
- "
select emp" denotes all the columns from the table. Just like select *
- "
emp.EmpId > 10" denotes the where clause
Working on LINQ to Entities
I have taken two entities Emp & Dept and some records in that. Assume that DeptNo is relation key between these two entities.
Let the class Emp & Dept be like:
public class Emp
{
public int EmpID { get; set; }
public string EmpName { get; set; }
public decimal Sal { get; set; }
public DateTime JoinDate { get; set; }
public int DeptNo { get; set; }
public string EmailAddress { get; set; }
public Emp(int eid, string name, decimal salary, DateTime joinedOn, int dno,string Email)
{
EmpID = eid;
EmpName = name;
Sal = salary;
JoinDate = joinedOn;
DeptNo = dno;
EmailAddress = Email;
}
}
public class Dept
{
public int DeptNo { get; set; }
public string DeptName { get; set; }
public Dept(int dno, string Name)
{
DeptNo = dno;
DeptName = Name;
}
}
Let's start working on queries by taking sample records into Emp & Dept:
List<Emp> objEmps = new List<Emp>();
List<Dept> objDepts = new List<Dept>();
objEmps.Add(new Emp(1, "Rihan", 10000, new DateTime(2001, 2, 1), 10, "developer1089@hotmail.com"));
objEmps.Add(new Emp(2, "Shafi", 20000, new DateTime(2000, 3, 1), 10, "developer1088@hotmail.com"));
objEmps.Add(new Emp(3, "Ajaml", 25000, new DateTime(2010, 6, 1), 10, "developer1069@hotmail.com"));
objEmps.Add(new Emp(4, "Rasool", 45000, new DateTime(2003, 8, 1), 20, "developer1080@hotmail.com"));
objEmps.Add(new Emp(5, "Masthan", 22000, new DateTime(2001, 3, 1), 20, "devehotmail.com"));
objDepts.Add(new Dept(10, "HR"));
objDepts.Add(new Dept(20, "IT"));
objDepts.Add(new Dept(30, "FINANCE"));
- Query to get employees whose
sal > 20000:
var res2 = from emp in objEmps where emp.Sal > 20000 select emp;
The above query returns anonymous type. We can cast the result to List of emps just by saying:
List<Emp> res2 = (from emp in objEmps where emp.Sal > 20000 select emp).ToList<Emp>();
- We can get the same result above by using lambda expression using the below query:
List<Emp> res1 = objEmps.Where(emp => emp.Sal > 20000).ToList<Emp>();
- Query to get highest paid employee among all the employees:
var res3 = objEmps.Where(e => e.Sal == objEmps.Max(emp => emp.Sal));
- Get all employees whose name starts with
R:
var result = from emp in objEmps
where emp.EmpName.ToLower().StartsWith("r")
select emp;
- Get only employees who are in IT dept:
var ITDept = from emp in objEmps
join dept in objDepts
on emp.DeptNo equals dept.DeptNo
where dept.DeptName == "IT"
select emp;
- Get each employee experience in years:
var result2 = objEmps.Select(e => new
{ EmpID = e.EmpID,
EmpName = e.EmpName,
JoinDate = e.JoinDate,
Exp = (DateTime.Now - e.JoinDate).Days / 365 });
- Get employees who are having valid email addresses:
var ValidEmail = from emp in objEmps where IsValidEmail(emp.EmailAddress) select emp;
- Get employees who have invalid email addresses:
var InValidEmail = from emp in objEmps where !IsValidEmail(emp.EmailAddress) select emp;
public static bool IsValidEmail(string mailAddress)
{
Regex mailIDPattern = new Regex(@"[\w-]+@([\w-]+\.)+[\w-]+");
if (!string.IsNullOrEmpty(mailAddress) && mailIDPattern.IsMatch(mailAddress))
{
return true;
}
else
{
return false;
}
}
- Now I want to display department number of each employee by joining with
Departments entity:
//The below query is example of inner join.
var InnerJoin = from emp in objEmps
join dept in objDepts
on emp.DeptNo equals dept.DeptNo
select new { EmpID = emp.EmpID,
EmpName = emp.EmpName,
DeptNo = dept.DeptNo };
- Get number of employees in each department:
//Example on Group By
var GroupBy = objEmps.GroupBy(e =>e.DeptNo).Select(d=>new{DeptNo= d.Key,Count = d.Count()});
The above query returns a list of departments having minimum of one employee.
i.e. From the test data given above, the result will be like:
Because dept 30 does not have any employees, it's not displayed in the above result. Suppose we want to include dept 30 also in by indicating count as zero. We can do this by using left join. Let's see how can we write left join using LINQ.
- Get number of employees in each department and include dept having no employees also:
//Example on Left Join
var LeftJoin = from dept in objDepts
join gd in GroupBy
on dept.DeptNo equals gd.DeptNo into dt
from gd in dt.DefaultIfEmpty()
select new { DeptNo = dept.DeptNo,
MemberCount = gd == null ? 0 : gd.Count };
The above query returns a list of all departments including 0 count also.
i.e. From the test data given above, the result will be like:
| DeptNo | Count |
| 10 | 3 |
| 20 | 2 |
| 30 | 0 |
- Insert LINQ with
Join result to a DataTable:
DataTable dtDept2 = new DataTable();
dtDept2.Columns.Add("DeptNo", typeof(int));
dtDept2.Columns.Add("MemberCount", typeof(int));
//Linq join result into a data table
var LeftJoinToTable = from dept in objDepts
join gd in GroupBy
on dept.DeptNo equals gd.DeptNo into dt
from gd in dt.DefaultIfEmpty()
select dtDept2.LoadDataRow(new object[] { dept.DeptNo,
gd == null?0:gd.Count},false);
dtDept2 = LeftJoinToTable.Any() ? LeftJoinToTable.CopyToDataTable() : dtDept2.Clone();
From the above statement, if any records exist that match the given filter condition, then those records are copied to dtDept2. If no records are found, then just empty structure copied to dtDept2.
- Get employee records
Order by Salary ASC:
var orderBy1 = objEmps.OrderBy(e => e.Sal);
(or)
var orderBy2 = from e in objEmps orderby e.Sal ascending select e;
- Get employees records
Order by Salary DESC:
var orderBy3 = objEmps.OrderByDescending(e => e.Sal);
(or)
var orderBy4 = from e in objEmps orderby e.Sal descending select e;
- Get top 2 high paid employees:
var orderBy5 = objEmps.OrderByDescending(e => e.Sal).Take(2);
?(or)
var orderBy6 = from e in objEmps orderby e.Sal descending select e;
var orderBy7 = orderBy6.Take(2);
Working with LINQ to DataTable
There is not much difference between LINQ to Entities and LINQ to DataTable. The only main difference is we have to specify data type of the column in where clause.
Below are the sample queries using LINQ to DataTable.
DataTable dtDept = new DataTable();
dtDept.Columns.Add("DeptNo", typeof(int));
dtDept.Columns.Add("DeptName", typeof(string));
DataTable dtEmp = new DataTable();
dtEmp.Columns.Add("EmpID", typeof(int));
dtEmp.Columns.Add("EmpName", typeof(string));
dtEmp.Columns.Add("Sal", typeof(decimal));
dtEmp.Columns.Add("JoinDate", typeof(DateTime));
dtEmp.Columns.Add("DeptNo", typeof(int));
dtDept.Rows.Add(10, "HR");
dtDept.Rows.Add(20, "IT");
dtDept.Rows.Add(30, "FINANCE");
dtEmp.Rows.Add(1, "Rihan", 10000, new DateTime(2001, 2, 1), 10);
dtEmp.Rows.Add(2, "Shafi", 20000, new DateTime(2000, 3, 1), 10);
dtEmp.Rows.Add(3, "Ajaml", 25000, new DateTime(2010, 6, 1), 10);
dtEmp.Rows.Add(4, "Rasool", 45000, new DateTime(2003, 8, 1), 20);
dtEmp.Rows.Add(5, "Masthan", 22000, new DateTime(2001, 3, 1), 20);
- Employees whose
sal > 20000:
var res1 = dtEmp.AsEnumerable().Where_
(emp => emp.Field<decimal>("Sal") > 20000);
//or
var res2 = from emp in dtEmp.AsEnumerable() _
where emp.Field<decimal>("Sal") > 20000 select emp;
- Highest paid employee:
var res3 = dtEmp.AsEnumerable().Where_
(e => e.Field<decimal>("Sal") == _
dtEmp.AsEnumerable().Max(emp => emp.Field<decimal>("Sal")));
- Example on
Inner Join:
var InnerJoin = from emp in dtEmp.AsEnumerable()
join dept in dtDept.AsEnumerable()
on emp.Field<int>("DeptNo") equals dept.Field<int>("DeptNo")
select new { EmpID = emp.Field<int>("EmpID"),
EmpName = emp.Field<string>("EmpName"),
DeptNo = dept.Field<int>("DeptNo") };
- Example on
Group By:
var GroupBy = dtEmp.AsEnumerable()
.GroupBy(e=>e.Field<int>("DeptNo")).Select(d=>new{d.Key,Count = d.Count() });
- Example on
Left Join:
var LeftJoin = from dept in dtDept.AsEnumerable()
join gd in GroupBy
on dept.Field<int>("DeptNo") equals gd.Key into dt
from gd in dt.DefaultIfEmpty()
select new { DeptNo = dept.Field<int>("DeptNo"),
MemberCount = gd == null ? 0 : gd.Count };
- Linq
join result into a datatable:
DataTable dtDept2 = new DataTable();
dtDept2.Columns.Add("DeptNo", typeof(int));
dtDept2.Columns.Add("MemberCount", typeof(int));
var LeftJoinToTable = from dept in dtDept.AsEnumerable()
join gd in GroupBy
on dept.Field<int>("DeptNo") equals gd.Key into dt
from gd in dt.DefaultIfEmpty()
select dtDept2.LoadDataRow(new object[]{dept.Field<int>("DeptNo"),
gd == null ? 0:gd.Count},false);
dtDept2 = LeftJoinToTable.Any() ? LeftJoinToTable.CopyToDataTable() : dtDept2.Clone();
Emp name starts with R:
var result = from emp in dtEmp.AsEnumerable()
where emp.Field<string>("EmpName").ToLower().StartsWith("r")
select emp;
- Get only
emps who are related to IT dept:
var ITDept = from emp in dtEmp.AsEnumerable()
join dept in dtDept.AsEnumerable()
on emp.Field<int>("DeptNo") equals dept.Field<int>("DeptNo")
where dept.Field<string>("DeptName") == "IT"
select emp;
DataTable dtITEmps = ITDept.CopyToDataTable();
- Get emp exp in years
var result2 = dtEmp.AsEnumerable().Select(e => new { EmpID = e.Field<int>("EmpID"),
EmpName = e.Field<string>("EmpName"),
JoinDate = e.Field<DateTime>("JoinDate"),
Exp = (DateTime.Now - e.Field<DateTime>("JoinDate")).Days / 365 });
- Get employees records
Order by Salary ASC:
var orderBy1 = dtEmp.AsEnumerable().OrderBy_
(e => e.Field<int>("Sal"));
(or)
var orderBy2 = from e in dtEmp.AsEnumerable() orderby e.Field<int>("Sal") ascending select e;
- Get employees records
Order by Salary DESC:
var orderBy3 = dtEmp.AsEnumerable().OrderByDescending_
(e => e.Field<int>("Sal"));
(or)
var orderBy4 = from e in dtEmp.AsEnumerable() orderby e.Field<int>("Sal") descending select e;
- Get top 2 high paid employees:
var orderBy5 = dtEmp.AsEnumerable().OrderByDescending_
(e => e.Field<int>("Sal")).Take(2);
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
