Working with LINQ to Entities & LINQ to DataTable

• Download source - 56.6 KB

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:

1. What is source (i.e., Table name)
2. What we need from the source (i.e. Select clause followed by column name list)
3. 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:

1. "from emp in tblEmployee" represents source of our data
2. "select emp" denotes all the columns from the table. Just like select *
3. "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"));

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

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

3. Query to get highest paid employee among all the employees:

   var res3 = objEmps.Where(e => e.Sal == objEmps.Max(emp => emp.Sal));

4. Get all employees whose name starts with R:

   var result = from emp in objEmps
                            where emp.EmpName.ToLower().StartsWith("r")
                            select emp;

5. 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;

6. 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 });

7. Get employees who are having valid email addresses:

   var ValidEmail = from emp in objEmps where IsValidEmail(emp.EmailAddress) select emp;

8. 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;
               }
           }

9. 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 };

10. 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:

    DeptNo	Count
    10	3
    20	2

    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.

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

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

13. 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;

14. 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;

15. 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);

1. 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;

2. Highest paid employee:

   var res3 = dtEmp.AsEnumerable().Where_
   (e => e.Field<decimal>("Sal") == _
   dtEmp.AsEnumerable().Max(emp => emp.Field<decimal>("Sal")));

3. 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") };

4. Example on Group By:

   var GroupBy = dtEmp.AsEnumerable()
                 .GroupBy(e=>e.Field<int>("DeptNo")).Select(d=>new{d.Key,Count = d.Count() });

5. 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 };

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

7. Emp name starts with R:

   var result = from emp in dtEmp.AsEnumerable()
                            where emp.Field<string>("EmpName").ToLower().StartsWith("r")
                            select emp;

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

9. 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 });
   

10. 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;

11. 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;

12. Get top 2 high paid employees:

    var orderBy5 = dtEmp.AsEnumerable().OrderByDescending_
        (e => e.Field<int>("Sal")).Take(2);