using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Text.RegularExpressions;

using System.Data;

using System.Data.DataSetExtensions;

​

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;

    }

}

public class Program

{

    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;

        }

    }

    public static void Main()

    {

        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"));

        Console.WriteLine("\nEmployees List:\n");

        foreach (Emp emp in objEmps)

        {

            Console.WriteLine("Employee Name= {0} \t\t JoinDate= {1} \t\t Sal= {2}\n", emp.EmpName, emp.JoinDate,emp.Sal);

        }   

        Console.WriteLine("\nDepartment List:\n");

        foreach (Dept dept in objDepts)

        {

            Console.WriteLine("DeptNo= {0} \t\t DeptName= {1}\n", dept.DeptNo, dept.DeptName);

        }   

        Console.WriteLine("\nFinding Employees List whose salary greater than 20000:\n");

        var res2 = from emp in objEmps where emp.Sal > 20000 select emp;

        foreach (Emp emp in res2)

        {

            Console.WriteLine("Employee Name= {0} \t\t JoinDate= {1} \t\t Sal= {2}\n", emp.EmpName, emp.JoinDate,emp.Sal);

        }   

        Console.WriteLine("\nFinding Employee Maximum salary:\n");

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

        foreach (Emp emp in res3)

        {

            Console.WriteLine("Employee Name= {0} \t\t JoinDate= {1} \t\t Sal= {2}\n", emp.EmpName, emp.JoinDate,emp.Sal);

        }   

        Console.WriteLine("\nGet all employees whose name starts with R:\n");

        var result = from emp in objEmps

                         where emp.EmpName.ToLower().StartsWith("r")

                         select emp;

        foreach (Emp emp in result)

        {

            Console.WriteLine("Employee Name= {0} \t\t JoinDate= {1} \t\t Sal= {2}\n", emp.EmpName, emp.JoinDate,emp.Sal);

        }   

        Console.WriteLine("\nGet only employees who are in IT dept:\n");

        var ITDept = from emp in objEmps

                         join dept in objDepts

                         on emp.DeptNo equals dept.DeptNo

                         where dept.DeptName == "IT"

                         select emp;

        foreach (Emp emp in ITDept)

        {

            Console.WriteLine("Employee Name= {0} \t\t JoinDate= {1} \t\t Sal= {2}\n", emp.EmpName, emp.JoinDate,emp.Sal);

        }

        Console.WriteLine("\nGet each employee experience in years:\n");

        var result2 = objEmps.Select(e => new 

                                  { EmpID = e.EmpID, 

                                    EmpName = e.EmpName, 

                                    JoinDate = e.JoinDate,

                                    Exp = (DateTime.Now - e.JoinDate).Days / 365 });

        foreach (var emp in result2)

        {

            Console.WriteLine("Employee Name= {0} \t\t JoinDate= {1} \t\t Exp= {2}\n", emp.EmpName, emp.JoinDate,emp.Exp);

        }

        Console.WriteLine("\nGet employees who are having valid email addresses:\n");

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

        foreach (Emp emp in ValidEmail)

        {

            Console.WriteLine("Employee Name= {0} \t\t JoinDate= {1} \t\t EmailAddress= {2}\n", emp.EmpName, emp.JoinDate,emp.EmailAddress);

        }       

        Console.WriteLine("\nNow I want to display department number of each employee by joining with Departments entity:\n");

        //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 };

        foreach (var emp in InnerJoin)

        {

            Console.WriteLine("Employee Name= {0} \t\t EmpID= {1} \t\t DeptNo= {2}\n", emp.EmpName, emp.EmpID,emp.DeptNo);

        }

        Console.WriteLine("\nGet number of employees in each department:\n");

        //Example on Group By

        var GroupBy = objEmps.GroupBy(e =>e.DeptNo).Select(d=>new{DeptNo= d.Key,Count = d.Count()});

        foreach (var objResult in GroupBy)

        {

            Console.WriteLine("DeptNo= {0} \t\t Count= {1} \n", objResult.DeptNo, objResult.Count);

        }   

        Console.WriteLine("\nGet number of employees in each department and include dept having no employees also\n");

        //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 };

        foreach (var objLeftJoin in LeftJoin)

        {

            Console.WriteLine("DeptNo= {0} \t\t MemberCount= {1} \n", objLeftJoin.DeptNo, objLeftJoin.MemberCount);

        }   

        Console.WriteLine("\nInsert LINQ with Join result to a DataTable:\n");

​

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

        foreach(DataRow row in dtDept2.Rows)

        {

            Console.WriteLine("DeptNo= {0} \t\t MemberCount= {1} \n",  row["DeptNo"].ToString(),  row["MemberCount"].ToString());

        }

        Console.WriteLine("\nGet employee records Order by1 Salary ASC:\n");

​

        var orderBy1 = objEmps.OrderBy(e => e.Sal);  

        foreach(Emp emp in orderBy1)

        {

           Console.WriteLine("Employee Name= {0} \t\t JoinDate= {1} \t\t EmailAddress= {2}\n", emp.EmpName, emp.JoinDate,emp.EmailAddress);

        }

        Console.WriteLine("\nGet employee records Order by2 Salary ASC:\n");

        var orderBy2 = from e in objEmps orderby e.Sal ascending select e;

        foreach(Emp emp in orderBy2)

        {

           Console.WriteLine("Employee Name= {0} \t\t JoinDate= {1} \t\t EmailAddress= {2}\n", emp.EmpName, emp.JoinDate,emp.EmailAddress);

        }

        Console.WriteLine("\nGet employee records Order by3 Salary DESC:\n");

​

        var orderBy3 = objEmps.OrderByDescending(e => e.Sal);  

        foreach(Emp emp in orderBy3)

        {

           Console.WriteLine("Employee Name= {0} \t\t JoinDate= {1} \t\t EmailAddress= {2}\n", emp.EmpName, emp.JoinDate,emp.EmailAddress);

        }

        Console.WriteLine("\nGet employee records Order by4 Salary DESC:\n");

        var orderBy4 = from e in objEmps orderby e.Sal descending select e;

        foreach(Emp emp in orderBy4)

        {

           Console.WriteLine("Employee Name= {0} \t\t JoinDate= {1} \t\t Sal= {2}\n", emp.EmpName, emp.JoinDate,emp.Sal);

        }

        Console.WriteLine("\nGet top 2 high paid employees:\n");

        var orderBy5 = objEmps.OrderByDescending(e => e.Sal).Take(2);  

        foreach(Emp emp in orderBy5)

        {

           Console.WriteLine("Employee Name= {0} \t\t JoinDate= {1} \t\t Sal= {2}\n", emp.EmpName, emp.JoinDate,emp.Sal);

        }   

        Console.WriteLine("\nGet top 2 high paid employees:\n");

        var orderBy6 = from e in objEmps orderby e.Sal descending select e;

        var orderBy7 = orderBy6.Take(2);

        foreach(Emp emp in orderBy7)

        {

           Console.WriteLine("Employee Name= {0} \t\t JoinDate= {1} \t\t Sal= {2}\n", emp.EmpName, emp.JoinDate,emp.Sal);

        }

​

    }

}