Linq Single vs SingleOrDefault vs First vs FirstOrDefault :

Single() SingleOrDefault() First() FirstOrDefault()
Description Returns a single, specific element of a sequence Returns a single, specific element of a sequence, or a default value if that element is not found Returns the first element of a sequence Returns the first element of a sequence, or a default value if no element is found
Exception thrown when There are 0 or more than 1 elements in the result There is more than one element in the result There are no elements in the result Only if the source is null (they all do this)
When to use If exactly 1 element is expected; not 0 or more than 1 When 0 or 1 elements are expected When more than 1 element is expected and you want only the first When more than 1 element is expected and you want only the first. Also it is ok for the result to be empty
Advertisements

Grouping Operators in Linq:

Let’s explore the various Grouping operations in Linq using ‘group by’ in both Query syntax & Method Syntax Format with the help of following program.

The Employee & Department are the Classes created for the demo purpose.


public class Employee

   {

      public int EmpNo;

      public string EmpName;

      public float salary;

      public int DeptNo;

      public int[] Reportees;//list of the emp-id’s of reportees

      public Employee(int eno, string Ename, float sal, int No,int[] r)

       {

            EmpNo = eno; EmpName = Ename; salary = sal; DeptNo = DNo;

            Reportees = r;

       }

    }

 

    public class Department

    {

        public int DeptNo;

        public string DeptName;

        public Department(int dno, string dname)

        {

            DeptNo = dno;DeptName = dname;

        }

    }

 


static void Main(string[] args)

        {          

            List<Department> dList = new List<Department>();

            dList.Add(new Department(1, “Tech Lead”));

            dList.Add(new Department(2, “Project Lead”));

            dList.Add(new Department(3, “Module Lead”));

            List<Employee> EmpList = new List<Employee>();

            EmpList.Add(new Employee(1, “ppp”, 100, 1, new

            int[]{2,3,4,5,6}));

            EmpList.Add(new Employee(2, “qqq”, 70, 2, new

            int[]{3,4,5,6}));

            EmpList.Add(new Employee(3, “rrr”, 50, 3, new

            int[]{4,5,6}));

            EmpList.Add(new Employee(4, “xxx”, 100, 1, new

            int[]{5,6}));

            EmpList.Add(new Employee(5, “yyy”, 100, 1, new int[]{6}));

            EmpList.Add(new Employee(6, “zzz”, 50, 3, new int[]{}));

            // different queries of type – Grouping

            // In the Query syntax & method syntax

            //1. Simple Group by

            var q1 = from e1 in EmpList

                     group e1 by e1.DeptNo;

            q1 = EmpList.GroupBy(e1 => e1.DeptNo);

 

            var q2 = from e1 in EmpList

                      group e1 by e1.DeptNo into g

                      select new { DeptNo = g.Key, Emp = g };

            q2 = EmpList.GroupBy(e1 => e1.DeptNo).Select(g => new

                 {DeptNo=g.Key,Emp=g });

 

            //2. Simple Group by & ordering the resulted groups on

            // deptno basis

            var q3 = from e1 in EmpList

                     group e1 by e1.DeptNo into g

                     orderby g.Key

                     select g;

            q3 = EmpList.GroupBy(e1 => e1.DeptNo).OrderBy(g => g.Key);

 

            //3.selecting only few coloumns from the grouped data

            var q4 = from e1 in EmpList

                     group e1 by e1.DeptNo into g

                     select new { count = g.Count(), deptNo = g.Key };

            q4 = EmpList.GroupBy(e1 => e1.DeptNo).Select(g => new {

                 count = g.Count(), deptNo = g.Key });

     

 

            //4.Filtering the data before grouping

            var q5 = from e1 in EmpList

                     where e1.EmpNo < 6

                     group e1 by e1.DeptNo into g

                     select g;

            q5 = EmpList.Where(e1 => e1.EmpNo < 6).GroupBy(e1 =>

                 e1.DeptNo);

 

            //5.Filtering the groups (after grouping)

            var q6 = from e1 in EmpList

                     group e1 by e1.DeptNo into g

                     where g.Key==1

                     select new {Deptno=g.Key,Count=g.Count()};

            q6 = EmpList.GroupBy(e1 => e1.DeptNo).Where(g => g.Key ==

                 1).Select(g => new { Deptno = g.Key, Count = g.Count()

                 });

 

            //6.Group By Multiple values

            var q7 = from e1 in EmpList

                     group e1 by (new { e1.EmpName, e1.DeptNo }) into g

                     select

            new{Dno=g.Key.DeptNo,Ename=g.Key.EmpName,count=g.Count()};              

        }

 

Ordering Operators :

We use order by operator to display the objects in the sorted order. Default sorting order is ‘ascending’. If we want to sort in descending order, we can mention it explicitly.

The Employee & Department are the Classes created for the demo purpose.

public class Employee

   {

      public int EmpNo;

      public string EmpName;

      public float salary;

      public int DeptNo;

      public int[] Reportees;//list of the emp-id’s of reportees

      public Employee(int eno, string Ename, float sal, int No,int[] r)

       {

            EmpNo = eno; EmpName = Ename; salary = sal; DeptNo = DNo;

            Reportees = r;

       }

    }

 

    public class Department

    {

        public int DeptNo;

        public string DeptName;

        public Department(int dno, string dname)

        {

            DeptNo = dno;DeptName = dname;

        }

    }

 

static void Main(string[] args)

        {          

            List<Department> dList = new List<Department>();

            dList.Add(new Department(1, “Tech Lead”));

            dList.Add(new Department(2, “Project Lead”));

            dList.Add(new Department(3, “Module Lead”));

            List<Employee> EmpList = new List<Employee>();

            EmpList.Add(new Employee(1, “ppp”, 100, 1, new

            int[]{2,3,4,5,6}));

            EmpList.Add(new Employee(2, “qqq”, 70, 2, new

            int[]{3,4,5,6}));

            EmpList.Add(new Employee(3, “rrr”, 50, 3, new

            int[]{4,5,6}));

            EmpList.Add(new Employee(4, “xxx”, 100, 1, new

            int[]{5,6}));

            EmpList.Add(new Employee(5, “yyy”, 100, 1, new int[]{6}));

            EmpList.Add(new Employee(6, “zzz”, 50, 3, new int[]{}));

            // different queries of type – ordering

            // In the Query syntax & method syntax

            //1.Order By – ordering by one field in ascending order

            var q1 = from e1 in EmpList

                     orderby e1.EmpName

                     select e1;

            q1 = EmpList.OrderBy(e1 => e1.EmpName);

 

            //2.Order By – ordering by one field in descending order

            var q2 = from e1 in EmpList

                     orderby e1.EmpName descending

                     select e1;

            q2 = EmpList.OrderByDescending(e1 => e1.EmpName);

 

 

            //3.Order By – ordering by two field in ascending order

            var q3 = from e1 in EmpList

                     orderby e1.DeptNo, e1.EmpName

                     select e1;

            q3 = EmpList.OrderBy(e1 => e1.DeptNo).ThenBy(e1 =>

                 e1.EmpName);

 

 

            //4.Order By – ordering by two field in descending order

            var q4 = from e1 in EmpList

                     orderby e1.DeptNo, e1.EmpName

                     select e1;

            q4 = EmpList.OrderByDescending(e1 =>

                 e1.DeptNo).ThenByDescending(e1 => e1.EmpName);

 

            //5.Reverse

            var q5 = (from e1 in EmpList

                      select e1).Reverse();

                  

        }

About ‘ThenBy’ keyword:

ThenBy is one of the ordering query operators available in linq. We can use ThenBy operator with linq to objects, linq to XML and linq to SQL. We use ThenBy operator to do secondary sort when writing queries in  method syntax. ThenBy operator is used to sort by more than 1 property. When we want to sort initially we would start with using the orderby operator. Since orderby operator returns IOrderderedEnumerable<T>,we cannot reuse the orderby operator again because it can be only used on sequences that implement IEnumerable<T>.  This is where the ThenBy operator comes into play. ThenBy operator is an extension method that is available on input sequence that implement IOrderededEnumerable<T> and also returns IOrderedEnumerable<T> which allows you to reuse it multiple times if you want to sort by more than 1 column or property. 

Partitioning Operators in Linq:

Lets explore the various partitioning operations in Linq using Take, TakeWhile, Skip, SkipWhile in both Query syntax & Method Syntax Format with the help of following program.

The Employee & Department are the Classes created for the demo purpose.


public class Employee

   {

      public int EmpNo;

      public string EmpName;

      public float salary;

      public int DeptNo;

      public int[] Reportees;//list of the emp-id’s of reportees

      public Employee(int eno, string Ename, float sal, int No,int[] r)

       {

            EmpNo = eno; EmpName = Ename; salary = sal; DeptNo = DNo;

            Reportees = r;

       }

    }

 

    public class Department

    {

        public int DeptNo;

        public string DeptName;

        public Department(int dno, string dname)

        {

            DeptNo = dno;DeptName = dname;

        }

    }

 


static void Main(string[] args)

        {          

            List<Department> dList = new List<Department>();

            dList.Add(new Department(1, “Tech Lead”));

            dList.Add(new Department(2, “Project Lead”));

            dList.Add(new Department(3, “Module Lead”));

            List<Employee> EmpList = new List<Employee>();

            EmpList.Add(new Employee(1, “ppp”, 100, 1, new

            int[]{2,3,4,5,6}));

            EmpList.Add(new Employee(2, “qqq”, 70, 2, new

            int[]{3,4,5,6}));

            EmpList.Add(new Employee(3, “rrr”, 50, 3, new

            int[]{4,5,6}));

            EmpList.Add(new Employee(4, “xxx”, 100, 1, new

            int[]{5,6}));

            EmpList.Add(new Employee(5, “yyy”, 100, 1, new int[]{6}));

            EmpList.Add(new Employee(6, “zzz”, 50, 3, new int[]{}));

            // different queries of type – Partitioning

            // In the Query syntax & method syntax

            //Take

            //1.Collecting first 2 employee’s objects

            var q1 = (from e1 in EmpList

                      select e1).Take(2);

             q1 = EmpList.Take(2);

           

            //2.Collecting first 2 employee’s of department id=1

            var q2 =(from e1 in EmpList

                     where e1.DeptNo == 1

                     select e1).Take(2);           

             q2 = EmpList.Where(e1 =&gt; e1.DeptNo = 1).Take(2);

 

            //Skip

            //3.Collects all but first 2 employee’s

             var q3 =( from e1 in EmpList

                      select e1).Skip(2);

             q3 = EmpList.Skip(2);

 

            //4.Skips first record of employee whose department id=1

             var q4 =(from e1 in EmpList

                      where e1.DeptNo == 1

                      select e1).Skip(1);

             q4 = EmpList.Where(e1 =>e1.DeptNo = 1).Skip(1);

 

 

            //5.TakeWhile-used to take records from starting to the

            //point where condition on an object is hit

            //collects all the records untill employee name becomes

            //’xxx’

            var q5 = (from e1 in EmpList

                      select e1).TakeWhile(e1=>e1.EmpName!=“xxx”);

            q5 = EmpList.TakeWhile(e1 =>e1.EmpName != “xxx”);

 

            //6.SkipWhile-used to Skip records from starting to the

            // point where condition on an object is hit

            //Skips all the records untill employee name becomes ‘xxx’

             var q6 = (from e1 in EmpList

                      select e1).SkipWhile(e1=>e1.EmpName!=“xxx”);

             q6 = EmpList.SkipWhile(e1 => e1.EmpName != “xxx”);

                  

        }

 

 

 

 

Restriction Operators :

Lets explore the various Restriction operations in Linq using ‘where’ in both Query syntax & Method Syntax Format with the help of following program.

The Employee & Department are the Classes created for the demo purpose.


public class Employee

   {

      public int EmpNo;

      public string EmpName;

      public float salary;

      public int DeptNo;

      public int[] Reportees;//list of the emp-id’s of reportees

      public Employee(int eno, string Ename, float sal, int No,int[] r)

       {

            EmpNo = eno; EmpName = Ename; salary = sal; DeptNo = DNo;

            Reportees = r;

       }

    }

 

    public class Department

    {

        public int DeptNo;

        public string DeptName;

        public Department(int dno, string dname)

        {

            DeptNo = dno;DeptName = dname;

        }

    }

 


static void Main(string[] args)

        {          

            List<Department> dList = new List<Department>();

            dList.Add(new Department(1, “Tech Lead”));

            dList.Add(new Department(2, “Project Lead”));

            dList.Add(new Department(3, “Module Lead”));

            List<Employee> EmpList = new List<Employee>();

            EmpList.Add(new Employee(1, “ppp”, 100, 1, new

            int[]{2,3,4,5,6}));

            EmpList.Add(new Employee(2, “qqq”, 70, 2, new

            int[]{3,4,5,6}));

            EmpList.Add(new Employee(3, “rrr”, 50, 3, new

            int[]{4,5,6}));

            EmpList.Add(new Employee(4, “xxx”, 100, 1, new

            int[]{5,6}));

            EmpList.Add(new Employee(5, “yyy”, 100, 1, new int[]{6}));

            EmpList.Add(new Employee(6, “zzz”, 50, 3, new int[]{}));

          // different queries of type – Restriction

          // In the Query syntax & method syntax

          //1.Filtering based on DeptNo

            var q1 = from e1 in EmpList

                     where e1.DeptNo==1

                     select e1;

            q1=EmpList.Where(e1=>e1.DeptNo==1).Select(e1=>e1);

          //2.Filtering the Employees whose name starts with ‘p’

            var q2 = from e1 in EmpList

                     where e1.EmpName.StartsWith(“p”)

                     select e1;

            q2=EmpList.Where(e1=>e1.EmpName.StartsWith(“p”));

 

          //3.Index in where condition

            var q3 = EmpList.Where((e1, i) => e1.EmpName.Length ==

                     i);                      

 

        }

 

 

Projection Operators in Linq:

Lets explore the various projection operations in Linq using ‘select’ & ‘selectmany’ in both Query syntax & Method Syntax Format with the help of following program.

The Employee & Department are the Classes created for the demo purpose.


public class Employee

   {

      public int EmpNo;

      public string EmpName;

      public float salary;

      public int DeptNo;

      public int[] Reportees;//list of the emp-id’s of reportees

      public Employee(int eno, string Ename, float sal, int No,int[] r)

       {

            EmpNo = eno; EmpName = Ename; salary = sal; DeptNo = DNo;

            Reportees = r;

       }

    }

 

    public class Department

    {

        public int DeptNo;

        public string DeptName;

        public Department(int dno, string dname)

        {

            DeptNo = dno;DeptName = dname;

        }

    }

 


static void Main(string[] args)

       {          

            List<Department> dList = new List<Department>();

            dList.Add(new Department(1, “Tech Lead”));

            dList.Add(new Department(2, “Project Lead”));

            dList.Add(new Department(3, “Module Lead”));

            List<Employee> EmpList = new List<Employee>();

            EmpList.Add(new Employee(1, “ppp”, 100, 1, new

            int[]{2,3,4,5,6}));

            EmpList.Add(new Employee(2, “qqq”, 70, 2, new

            int[]{3,4,5,6}));

            EmpList.Add(new Employee(3, “rrr”, 50, 3, new

            int[]{4,5,6}));

            EmpList.Add(new Employee(4, “xxx”, 100, 1, new

            int[]{5,6}));

            EmpList.Add(new Employee(5, “yyy”, 100, 1, new int[]{6}));

            EmpList.Add(new Employee(6, “zzz”, 50, 3, new int[]{}));

          // different queries of type – Projection

          // In the Query syntax & method syntax

          //1.selecting all the list of employee objects

            var q1 = from e1 in EmpList

                     select e1;

            q1 = EmpList.Select(e1=>e1);           

 

          //2.selecting only required field from Employee list

          //objects

            var q2 = from e1 in EmpList

                     select e1.EmpNo;

            q2 = EmpList.Select(e1 => e1.EmpNo);

 

          //3.Select – transformation Data- selecting all the yearly

          //salaries of all employees

            var q3 = from e1 in EmpList

                     select e1.salary * 12;

            q3 = EmpList.Select(e1 => e1.salary * 12);

 

          //4.Select – Anonymous types

            var q4 = from e1 in EmpList

                     select new { EmployeeNo = e1.EmpNo, AnnuualSalary

                     = e1.salary * 12 };

            q4 = EmpList.Select(e1 => new { EmployeeNo = e1.EmpNo,

                 AnnuualSalary = e1.salary * 12 });

 

 

          //5.Select – Anonymous types

            var q5 = from e1 in EmpList

                     join d1 in dList on e1.DeptNo equals d1.DeptNo

                     select new { EmployeeNo = e1.EmpNo,

                     DepartmentName=d1.DeptName };           

 

          //6.Select – Indexed

          //checking whether employee object index = employee id

            var q6=EmpList.Select((e1, index) => new { Num = e1.EmpNo,

                   InPlace = (e1.EmpNo == index) });

 

          //7.Select – Collection from more than one list

          //collects lists from both the lists ie..picks each value in

          //1st list & evaluates against all values in the 2nd list

            var q7 = from e1 in EmpList

                     from d1 in dList

                     where e1.DeptNo == d1.DeptNo

                     select new {e1.EmpNo,d1.DeptName };           

           

          //8.Select – querying the inner object

          //selecting all the reportees of all the employee’s

            var q8 = from e1 in EmpList

                     from r in e1.Reportees

                     select new {ManagerName=e1.EmpName,ReporteeId=r };

            q8 = EmpList.SelectMany(e1 => e1.Reportees.Select(r => new

                 {ManagerName=e1.EmpName,ReporteeId=r }));

 

          //9.Select – querying the inner object with a condition

          //selecting all the managers for the reportee id=2

            var q9 = from e1 in EmpList

                     from r in e1.Reportees

                     where r==2

                     select new { ManagerName = e1.EmpName, ReporteeId

                     = r };

            q9 = EmpList.SelectMany(e1 => e1.Reportees.Where(r => r ==

                 2).Select(r => new { ManagerName = e1.EmpName,

                 ReporteeId = r }));

 

          //10.Filtering on the outer as well inner object

          //selecting the managers of the reportee with id=6 & mangers

          //who are from deptid=1,2

            var q10 = from e1 in EmpList

                      where e1.DeptNo==1 || e1.DeptNo==2

                      from r in e1.Reportees

                      where r == 6

                      select new { ManagerName = e1.EmpName, ReporteeId

                      = r };

            q10 = EmpList.Where(e => e.DeptNo == 1 || e.DeptNo ==

                  2).SelectMany(e1 => e1.Reportees.Where(r => r ==

                  6).Select(r => new { ManagerName = e1.EmpName,

                  ReporteeId = r }));

 

           

          //11.SelectMany

            var q11 = EmpList.SelectMany((e1, i) =>

                      e1.Reportees.Select(r => i+1 + “)employee Name :”

                      + e1.EmpName + ” has reportee with id =” + r));

                      q11 = EmpList.SelectMany(e1 =>

                      e1.Reportees.Select(r => r.ToString()));

          //12.Selecting the First result object out of selected

          //collection

            var q12 = (from e1 in EmpList

                       select e1.EmpName).FirstOrDefault();

                      

 

        }

 

 

About Linq in c#:


A query is an expression that retrieves data from a data source. For different types of data sources, there are different types of query languages like Sql for Sql Server, XQuery for XML. So this has been tedious process to learn various languages for various kind of databases or data formats. LINQ simplifies this situation by offering a consistent model for working with data across various kinds of data sources and formats. In a LINQ query, it’s always working with objects.

Linq queries can be against Sql Server Database/Xml document/Ado.Net Datasets/Any collection of objects that support IEnnumerable.

Basically Linq Query has three operations

  • Obtain the Data source
  • Create the Query
  • Execute the query

 

Ex: EmpList is the Collection of List<Employee> objects.


var query1 = from e1 in EmpList

                    where e1.DeptNo == 1

                    select e1;

 

 

Ø       Storing Query in the variable doesn’t mean retrieving the data.

Ø       Execution can happen in two ways:

o        Deferred Execution: When we iterate the query variable. i.e. using ForEach construct.

o        Immediate Execution: When we  use Aggregate function on that query variable

       i.e. query1.Count ()

 

Basic Linq Queries:

Lets Employee & Department be two user defined classes.


public class Employee

    {

        public int EmpNo;

        public string EmpName;

        public float salary;

        public int DeptNo;

        public Employee(int eno, string Ename, float sal, int DNo)

        {

            EmpNo = eno;

            EmpName = Ename;

            salary = sal;

            DeptNo = DNo;

        }

       

    }

    public class Department

    {

        public int DeptNo;

        public string DeptName;

        public Department(int dno, string dname)

        {

            DeptNo = dno;

            DeptName = dname;

        }

    }

Program:

 


protected void Page_Load(object sender, EventArgs e)

        {

            List<Department> dList = new List<Department>();

            dList.Add(new Department(1, “Tech Lead”));

            dList.Add(new Department(2, “Project Lead”));

            dList.Add(new Department(3, “Module Lead”));

            List<Employee> EmpList = new List<Employee>();

            EmpList.Add(new Employee(1, “ppp”,100, 1));

            EmpList.Add(new Employee(2, “qqq”, 70, 2));

            EmpList.Add(new Employee(3, “rrr”, 50, 3));

            EmpList.Add(new Employee(4, “xxx”, 100, 1));

            EmpList.Add(new Employee(5, “yyy”, 100, 1));

            EmpList.Add(new Employee(6, “zzz”, 50, 3));

 

            // different types of queries

            // Filtering using where

            var query1 = from e1 in EmpList

                         where e1.DeptNo == 1

                         select e1;

            //printing all the Employee Names whose department No = 1

            foreach (Employee el in query1)

            {               

                Response.Write(el.EmpName);

            }

            //———————————————

 

            //ordering

            var query2 = from e1 in EmpList

                         where e1.EmpNo > 0

                         orderby e1.EmpName ascending

                         select e1;

            //printing all the Employee Names in the sorted order

            foreach (Employee el in query2)

            {

                Response.Write(el.EmpName);

            }

            //———————————————

 

            //grouping using some key(deptNo)

            var query3 = from e1 in EmpList

                         group e1 by e1.DeptNo into EGroups

                         select EGroups;

 

            //filtering the group(condition on the formed groups)

            var query4 = from e1 in EmpList

                         group e1 by e1.DeptNo into EmpGroups

                         where EmpGroups.Count() > 0

                         orderby EmpGroups.Key

                         select EmpGroups;

            //———————————————

            //Ordering the group(condition on the formed groups)

            var query5 = from e1 in EmpList

                         group e1 by e1.DeptNo into EmpGroups

                         where EmpGroups.Count() > 0

                         orderby EmpGroups.Key

                         select EmpGroups.Key;

          

            //———————————————

 

            //joins(joining the object collection)

            var query6 = (from e1 in EmpList

                         join d in dList on e1.DeptNo equals d.DeptNo

                         select new { Ename = e1.EmpName, Dname = d.DeptName }).ToList();

           

            //retrieving the new object framed out of known objects

            for (int i = 0; i < query6.Count; i++)

            {

                string ename = query6[i].Ename;

                string dname = query6[i].Dname;

            }

            //joins different implementation in retrival

            var query7 = from e1 in EmpList

                         join d in dList on e1.DeptNo equals d.DeptNo

                         select new { Ename = e1.EmpName, Dname = d.DeptName };

 

            //retrieving the new object framed out of known objects

            foreach (var v in query7)

            {

                string ename = v.Ename;

                string dname = v.Dname;                

            }

            //retrieving the subset of datasource using select

            //used to retrieve only few data as well formatted data.i.e. retrieving only Employee Name in

            //upper case &  yearly Salary of that employee

 

            var query8 = from e1 in EmpList

                         where e1.EmpNo > 0

                         select new { EmpName = e1.EmpName.ToUpper(), Salary = e1.salary*12 };

            foreach (var v in query8)

            {

                Response.Write(“Name :” + v.EmpName);

                Response.Write(“Yearly Gross :” + v.Salary);

            }

        }

Pros of LINQ:

  • Supports type safety
  • Allows for debugging through .NET debugger.
  • Supports abstraction and hence allows developers to extend features such as multi threading.
  • Easier to deploy
  • Simpler and easier to learn
  • Support for multiple databases

Cons of LINQ:

  • LINQ needs to process the complete query, which might have a performance impact in case of complex queries
  • LINQ is generic, whereas stored procedures etc can take full advantage of database features.
  • If there has been a change, the assembly needs to be recompiled and redeployed. 
  • It’s much easier to restrict access to tables in database using stored procedures and ACL’s than through LINQ.