Naveen's Weblog

Bridge to future

LINQ with best example – 1

Posted by codingsense on September 8, 2008


What is LINQ??

LINQ stand for Language-Integrated Query is now available as a integral part of Visual Studio 2008.

LINQ has a great power of querying on any source of data, data source could be the collections of objects, database or XML files. We can easily retrieve data from any object that implements the IEnumerable<T> interface. Microsoft basically divides LINQ into three areas and that are give below.

  • LINQ to Object
  • LINQ to ADO.Net
    • LINQ to SQL
    • LINQ to Dataset
    • LINQ to Entities
  • LINQ to XML 

Basics samples of LINQ:

http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

 

Assuming you know the basics of LINQ I will continue with a bit high end sample lets move further with an example of a company.

 

The Company Sample

Downloads:

Download complete Project (56.66kb)

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:

CompanyHierarchyLinq

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

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 from comp in ListCompany and then within each selected company it loops through each employee from emp in comp.ListEmp Count no of employees in each company:

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.

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.

Var EmpHighSal = 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.

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.

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.

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

 
Next >> LINQ with best example – 2

More references on LINQ: Standard Query Operators in a Nutshell: http://msdn.microsoft.com/en-us/library/bb308959.aspx#linqoverview_topic8 More LINQ: http://msdn.microsoft.com/en-us/library/bb397676.aspx

Suggestions are most welcome. 
Happy learning 🙂
Advertisements

2 Responses to “LINQ with best example – 1”

  1. Asad said

    Great work. Simply the best to learn LINQ.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: