Tuesday, August 19, 2008

Better Linq with better example - 1

Here in this example we take an example of 2 companies with many employees and learn how to fetch data from multilevel list (list within list) or multilevel classes( class within a class) using LINQ.

In the following example i have tried to cover all the things that can be explained using a company example.

Just copy and paste the following console application.



using System;
using System.Collections.Generic;
using System.Linq;

namespace ITCompany
{
class Program
{
static void Main(string[] args)
{
//Create a list of Employees
List LEmp1 = new List();
LEmp1.Add(new Employee() { Name = "Emp01", Age = 24, salary = 40000, Address = new Address() { PinCode = 123451, City = "Bangalore" }});
LEmp1.Add(new Employee() { Name = "Emp02", Age = 25, salary = 35000, Address = new Address() { PinCode = 123452, City = "Delhi" } });
LEmp1.Add(new Employee() { Name = "Emp03", Age = 24, salary = 22000, Address = new Address() { PinCode = 123453, City = "Bombay" } });
LEmp1.Add(new Employee() { Name = "Emp04", Age = 25, salary = 14000, Address = new Address() { PinCode = 123454, City = "Chennai" } });
LEmp1.Add(new Employee() { Name = "Emp05", Age = 26, salary = 25000, Address = new Address() { PinCode = 123455, City = "Bangalore" } });

Company cmp1 = new Company() { Name = "Relyon", ListEmp = LEmp1};

List LEmp2 = new List();
LEmp2.Add(new Employee() { Name = "Emp07", Age = 25, salary = 35000, Address = new Address() { PinCode = 123456, City = "Bangalore" } });
LEmp2.Add(new Employee() { Name = "Emp08", Age = 24, salary = 15000, Address = new Address() { PinCode = 123457, City = "Bombay" } });
LEmp2.Add(new Employee() { Name = "Emp09", Age = 25, salary = 10000, Address = new Address() { PinCode = 123458, City = "Delhi" } });
LEmp2.Add(new Employee() { Name = "Emp10", Age = 26, salary = 25000, Address = new Address() { PinCode = 123459, City = "Bangalore" } });

Company cmp2 = new Company() { Name = "CellOn" , ListEmp = LEmp2};

Company[] ListCompany = { cmp1, cmp2 };

//
//Employee Details
//
Console.WriteLine("--------------------------------------------------------------------");
Console.WriteLine("Employee Details");
Console.WriteLine("--------------------------------------------------------------------");
Console.WriteLine("Company | Name | Age | Salary | Address |");
Console.WriteLine("--------------------------------------------------------------------");

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

foreach (var t in EmpDetails)
{
Console.WriteLine(string.Join("\n", t.Emp.Select(emp => emp.Company + " |" + emp.emp.Name + " |" + emp.emp.Age + " |" + emp.emp.salary + " |" + emp.emp.Address.City + "-" + emp.emp.Address.PinCode).ToArray()));
}

Console.WriteLine();
Console.WriteLine();

//
//company with Employee count
//
Console.WriteLine("--------------------------------------------------");
Console.WriteLine("Nos of employees");
Console.WriteLine("--------------------------------------------------");

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

foreach(var t in LessEmp)
{
Console.WriteLine("Company Name : " + t.Name + ", Nos Of Employees : " + t.EmpCount);
}
Console.WriteLine(); Console.WriteLine();

//
//Employees who are staying in BANGALORE
//
Console.WriteLine("--------------------------------------------------");
Console.WriteLine("Employees statying in bangalore");
Console.WriteLine("--------------------------------------------------");

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

foreach (var t in EmpInACity)
{
Console.WriteLine("Company Name : " + t.CompName + ", Employee Name : " + t.EmployeeName);
}
Console.WriteLine(); Console.WriteLine();

//
//Employee with Hightest salary in each company
//
Console.WriteLine("--------------------------------------------------");
Console.WriteLine("Highest paid employee in each company");
Console.WriteLine("--------------------------------------------------");

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

foreach (var t in EmpHighSalEachComp)
{
Console.WriteLine("Company : " + t.CompanyName + ", Employee : " + t.EmpHighName + ", Salary : " + t.EmpHighSal);
}
Console.WriteLine(); Console.WriteLine();

//
//Employee with Hightest salary
//
Console.WriteLine("--------------------------------------------------");
Console.WriteLine("Highest paid employee from all companies");
Console.WriteLine("--------------------------------------------------");

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

foreach (var t in EmpHighSal)
{
Console.WriteLine("Company : " + t.CompanyName + ", Employee : " + t.EmployeeName + ", Salary : " + t.EmpSal);
}
Console.WriteLine(); Console.WriteLine();

//
//Salary Paid in Each City together by all companies
//
Console.WriteLine("--------------------------------------------------");
Console.WriteLine("Payment done in cities together by all companies");
Console.WriteLine("--------------------------------------------------");

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

foreach (var t in CompanyCityWise)
{
Console.WriteLine("City : " + t.State + ", " + "Total Salary : " + t.TotalSalary);
}
Console.WriteLine(); Console.WriteLine();

//
//Salary Paid in Each City by each company
//
Console.WriteLine("--------------------------------------------------");
Console.WriteLine("Payment done in cities by each company");
Console.WriteLine("--------------------------------------------------");

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

foreach (var t in CityWiseSalary)
{
//t.emp comes as a structure so to easy the printing
//we will use string.join to join all the values in a single string
//It can also be done by looping through t.emp and
//printing t.emp.state and t.emp.totalsalary separately

Console.WriteLine("company : " + t.Name + "\n" + string.Join("\n", t.Emp.Select(emp => emp.State + " : " + emp.TotalSalary).ToArray()));
Console.WriteLine(); Console.WriteLine();
}
Console.WriteLine(); Console.WriteLine();


Console.Read();
}
}

public class Company
{
public string Name
{ get; set; }

public List ListEmp
{ get; set; }
}

public class Employee
{
public string Name
{ get; set; }

public Address Address
{ get; set; }

public int Age
{ get; set; }

public double salary
{ get; set; }
}

public struct Address
{
public int PinCode
{ get; set; }
public string City
{ get; set; }
}
}


Output of the above program:



If any suggestions for improving the access or improving the class are always open.

If you need example on any specific topic you can leave a comment and it will be included in the coming posts.

Keep Learning. :)

3 comments:

siju said...

Masthu jabar jasthuuuuu

Anonymous said...

Who knows where to download XRumer 5.0 Palladium?
Help, please. All recommend this program to effectively advertise on the Internet, this is the best program!

Anu said...

Hi,
Naveen,
I have a small doubt regarding linq and silverlight application.
my tables consists of Class(classid,classanme,status)
and Groups(Groupid,grpname,classid,status)
Now i want to retrieve the groups under particular class
i have written some thing like dis.
var grps=from grp in db.Groups
where grp.classid.equals(classid)
select grp
but iam getting a error on foriegn ley.so i removed foriegn ley in class table now iam able to retrieve details.
so how can i retrieve data with out removing foriegn key please help me!!
Thanks in advance