Better Linq examples

Here we will see how a linq can be utilized in a company module.

The company class will contain a Collection of employee class and employee will contain a struct for individual addresses.

Hierarchy: 

If we have a project with above hierarchy, let’s see how we can use LINQ to access details from multilevel classes.

After creation of companies we will try to find the details at multilevel and see how conditionally data can be fetched.

1)      List details of employees

2)      Count no of employees in each company

3)      List employees who are staying in Bangalore

4)      List employee who is paid highest in each companies

5)      Overall Highest paid employee

6)      Salary paid by companies in each city

7)      Salary paid by each company in each city.

List details of employees:

Now we shall list all the employees with their details and company name.

 Collapse | Copy Code

var EmpDetails = from comp in ListCompany 
                 select new {
                       Emp = (from emp in comp.ListEmp  
                              select new {
                                   Company = comp.Name, 
                                   emp 
                              })
                  }; 

Here we use sub queries since we need all employee details with their respective company name and both the details are at different levels.

So first we Loops through the companies in the company list  

 Collapse | Copy Code

from comp in ListCompany  

and then within each selected company it loops through each employee

 Collapse | Copy Code

from emp in comp.ListEmp 

  

Count no of employees in each company:

 Collapse | Copy Code

  var LessEmp = from  Comp in  ListCompany  
                select new { 
                                       Comp.Name,
                       EmpCount = Comp.ListEmp.Count
                            };   

List employees who are staying in Bangalore: 

Here we use compound from clause to retrieve the employees who are staying in any city that contains BAN or ban.  

 Collapse | Copy Code

var EmpInACity = from comp in ListCompany 

                 from emplist in comp.ListEmp 
                 where emplist.Address.City.ToUpper().Contains("BAN")
                 select new {
                        CompName = comp.Name,
                        EmployeeName = emplist.Name 
                 }; 

List employee who is paid highest in each companies: 

For finding the highest paid employee we first loop through all the companies and employees in each company using compound from clause.

Where condition filters only those employees those have the salary equal to the max salary in current company. 

 Collapse | Copy Code

var  EmpHighSalEachComp = from comp in ListCompany 
                          from empHigh in comp.ListEmp  
                          where empHigh.salary == comp.ListEmp.Max(
                          HighEmp => HighEmp.salary) 
                          select new {
                                 CompanyName = comp.Name, 
                                 EmpHighName = empHigh.Name,
                                 EmpHighSal = empHigh.salary
                          }; 

Overall Highest paid employee:

The same above procedure is followed but the only difference comes in the where condition where we match the employee’s salary to the max salary from all the companies. 

 Collapse | Copy Code

var EmpHighSal = from comp in ListCompany
                 from emp in comp.ListEmp 
                 where emp.salary == ListCompany.Max(
                 TComp => TComp.ListEmp.Max(HighEmp => HighEmp.salary))
                 select new {
                        CompanyName = comp.Name ,
                        EmployeeName = emp.Name,
                        EmpSal = emp.salary
                 }; 

Salary paid by companies in each city: 

Here we will group by city and sum up all the salaries of the employees who are staying in that city. 

 Collapse | Copy Code

var CompanyCityWise = from comp in ListCompany          

                      from emp in comp.ListEmp 
                                       group emp by emp.Address.City into CityWiseEmp 
                      select new {
                             State = CityWiseEmp.Key, 
                             TotalSalary = CityWiseEmp.Sum(emp => emp.salary) 
                      }; 

Salary paid by each company in each city. 

Here for each company a group by clause is applied and from each company employees staying at different locations are fetched.

 Collapse | Copy Code

var  CityWiseSalary = from comp in ListCompany 
                      select new { 
                             comp.Name,
                             Emp =(from emp in comp.ListEmp 
                             group emp by emp.Address.City into CityWiseEmp
                             select new {
                                    State = CityWiseEmp.Key,
                                    TotalSalary = CityWiseEmp.Sum(emp => emp.salary) 
                             })
                      };