LINQ Examples 1

1. Example of Inner join

We will using two classes Book and Order and use the Join operator on them
class Program{
      static void Main(string[] args)
     {
           List<Book> bookList = new List<Book>
          {
                     new Book{BookID=1, BookNm="DevCurry.com Developer Tips"},
                     new Book{BookID=2, BookNm=".NET and COM for Newbies"},
                     new Book{BookID=3, BookNm="51 jQuery ASP.NET Recipes"},
                     new Book{BookID=4, BookNm="Motivational Gurus"},
                     new Book{BookID=5, BookNm="Spiritual Gurus"}
           };

           List<Order> bookOrders = new List<Order>
          {
                     new Order{OrderID=1, BookID=1, PaymentMode="Cheque"},
                     new Order{OrderID=2, BookID=5, PaymentMode="Credit"},
                     new Order{OrderID=3, BookID=1, PaymentMode="Cash"},
                     new Order{OrderID=4, BookID=3, PaymentMode="Cheque"},
                     new Order{OrderID=5, BookID=3, PaymentMode="Cheque"},
                     new Order{OrderID=6, BookID=4, PaymentMode="Cash"}
           };
      }
}

var orderForBooks = from bk in bookList
                                   join ordr in bookOrders
                                  on bk.BookID equals ordr.BookID
                                  select new
                                 {
                                          bk.BookID,
                                          Name = bk.BookNm,
                                          ordr.PaymentMode
};

foreach (var item in orderForBooks)
Console.WriteLine(item);

Console.ReadLine();

foreach (var item in orderForBooks)
Console.WriteLine(item);





2. Example of Left Outer join


var orderForBooks = from bk in bookList
                                   join ordr in bookOrders
                                   on bk.BookID equals ordr.BookID
                                   into a
                                   from b in a.DefaultIfEmpty(new Order())
                                   select new
                                   {
                                             bk.BookID,
                                             Name = bk.BookNm,
                                             b.PaymentMode
                                    };
 



3. Aggregate Function

An aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of greater significance or measurement such as a set. Aggregate functions return a single value.
Common Aggregate Functions are:

• SUM() : Returns the sum of column values.
• AVERAGE() : Returns the average of column values.
• COUNT() : Returns the total number of rows in a table.
• MAX() : Returns the maximum value in the column.
• MIN() : Returns the minimum value in the column.

SELECT * FROM EMPLOYEE


SUM() Function

This SUM() function returns a single value that is the result of adding all row's values for a single column or can specify a certain criteria. The SQL SUM() function takes an argument specifying which column to add all values of every row for, or some criteria for a column. In the following code I am showing the sum of salaries for an individual employee and all employees.

1. In SQL Server
SELECT SUM(SALARY) AS [TOTAL SALARY] FROM EMPLOYEE

SELECT Name,SUM(SALARY) AS [SALARY] FROM EMPLOYEE GROUP BY Name


2. In LINQ To SQL

private void SumGroupSalary()

{

  EmployeeOperationDataContext employee = new EmployeeOperationDataContext();

     /* Addition of salary by employee */

    var salaryEmpSum = from emp in employee.EMPLOYEEs

                           group emp by emp.Name into empg

                           select new

                           {

                               Name = empg.Key,

                               Salary = empg.Sum(x => x.SALARY)

                           };

        grdEmployee.DataSource = salaryEmpSum;

        grdEmployee.DataBind();

        /*Total Salaries for all employee */

        var salarySum = (from emp in employee.EMPLOYEEs

                         select emp.SALARY).Sum();

        Response.Write(string.Format("Addition of Salary is : {0} ", salarySum));

    }

AVERAGE() Function

This AVERAGE() function returns a single value that is the average of all row's values for a single column or can specify a criteria for a single column. The SQL AVG() function takes an argument specifying which column to average all values of every row for, or some criteria for a column. In the following code I am showing the average of salaries for an individual employee and all employees.

1. In SQL Server
SELECT AVG(SALARY) AS SALARY FROM EMPLOYEE
SELECT AVG(SALARY) AS SALARY FROM EMPLOYEE GROUP BY Name


2. In LINQ To SQL

private void AverageSalary()

{

  EmployeeOperationDataContext employee = new EmployeeOperationDataContext();

        /*Averge salary from multiple salries for employee */

        var salaryGroupAvg = from emp in employee.EMPLOYEEs

                             group emp by emp.Name into empg

                             select new

                             {

                                 Name = empg.Key,

                                 Salary = empg.Average(x => x.SALARY)

                             };

        grdEmployee.DataSource = salaryGroupAvg;

        grdEmployee.DataBind();

        /*Averge of whole salary in Employee table*/

        var salaryAvg = (from emp in employee.EMPLOYEEs

                         select emp.SALARY).Average();

Response.Write(string.Format("Average of Salary is : {0} ", salaryAvg));

    }


COUNT() Function

The COUNT() function returns a single value that is the count of all rows for a single column or can specify a criteria for a single column. The SQL COUNT() function takes an argument that represents which column will be used to count the total rows for or some criteria for a column. In the following code I am showing the total number employees with the same employee name and the total number of employees.

1. In SQL Server
SELECT COUNT(Id) AS [Total Number] FROM EMPLOYEE


 

SELECT Name, COUNT(Id) AS [Total Number] FROM EMPLOYEE GROUP BY Name

2. In LINQ To SQL
 

private void TotalNumberOfEmployee()

{

  EmployeeOperationDataContext employee = new EmployeeOperationDataContext();

        var totalEmployee = from emp in employee.EMPLOYEEs

                            group emp by emp.Name into empg

                            select new

                            {

                                Name = empg.Key,

                                TotalEmployee = empg.Count()

                            };

        grdEmployee.DataSource = totalEmployee;

        grdEmployee.DataBind();

                           

        var employeeCount = (from emp in employee.EMPLOYEEs

                         select emp.Id).Count();

        Response.Write(string.Format("Total number of Employee is : {0} ", employeeCount));

    }


MAX() Function

This MAX() function returns a single value that is the maximum of all rows for a single column or can specify a criteria for a single column. The SQL MAX() function takes an argument specifying a column to get the maximum value from, or some criteria for a column. In the following code I am showing the maximum salary for an individual employee and all employees.

1. In SQL Server
SELECT MAX(SALARY) AS SALARY FROM EMPLOYEE

SELECT Name, MAX(SALARY) AS SALARY FROM EMPLOYEE GROUP BY Name

2. In LINQ To SQL
 

private void MaxSalary() 

{

  EmployeeOperationDataContext employee = new EmployeeOperationDataContext();

        var salaryMaximum = from emp in employee.EMPLOYEEs

                            group emp by emp.Name into empg

                            select new

                            {

                                Name = empg.Key,

                                MaximumSalary = empg.Max(x => x.SALARY)

                            };

        grdEmployee.DataSource = salaryMaximum;

        grdEmployee.DataBind();

        var salaryMax = (from emp in employee.EMPLOYEEs

                         select emp.SALARY).Max();

        Response.Write(string.Format("Maximun Salary is : {0} ", salaryMax));      

    }

MIN() Function

This MIN() function returns a single value that is the minimum in all rows for a single column or can be from a specified criteria for a single column. The SQL MIN() function takes a argument that specifies a column to get the minimum value for from all rows or can be a specified criteria. In the following code I am showing the minimum salary for an individual employee and all employees.

1. In SQL Server
SELECT MIN(SALARY) AS SALARY FROM EMPLOYEE

SELECT Name, MIN(SALARY) AS SALARY FROM EMPLOYEE GROUP BY Name

2. In LINQ To SQL
 

private void MinSalary()

 {

  EmployeeOperationDataContext employee = new EmployeeOperationDataContext();

        /*Minimum salary per employee name */

        var salaryMinimum = from emp in employee.EMPLOYEEs

                            group emp by emp.Name into empg

                            select new

                            {

                                Name = empg.Key,

                                MinimumSalary = empg.Min(x => x.SALARY)

                            };

        grdEmployee.DataSource = salaryMinimum;

        grdEmployee.DataBind();

        /*Minimum salary in all employees */

        var salaryMin = (from emp in employee.EMPLOYEEs

                         select emp.SALARY).Min();

        Response.Write(string.Format("Minimun Salary is : {0} ", salaryMin));      

    }