Recently, while working on a project, I came across a certain requirement to use LINQ and get data from two different sources and at the same time I had to use inner join and group the data. It took a while for me to figure out how best to write the query as I could either do inner join or group but was not able to achieve both in the same query. Finally, I figured out the procedural approach to get it done therefore, I thought of sharing the solution with you all on blog assuming it might be helpful to you. For the sake of simplicity, I am presenting the same challenge but with very simple and common real world entities such employee and department example.

Given two lists, one for departments and another for employees, we need to get the listing of all departments along with employee having least salary for that department. For example, find below listing of departments and employees:

ID Name
1 IT
2 Finance


Name DepartmentID Address Salary
Ram 2 Parel 2500
Shyam 2 Borivali 1500
Sita 2 Panvel 2000
Gita 1 Virar 1600
Kishan 1 Vikroli 1400
Kanhaiya 1 Surat 1300


Now we want to get the highlighted rows as output, i.e. departments listing along with employee with minimum salary for that department.

I have initialized these two data sources in code as shown below:

private List<Department> _deptartments = new List<Department>()
				{
					new Department{ ID=1, Name="IT" },
					new Department{ ID=2, Name="Finance" }
				};

private List<Employee> _employees = new List<Employee>()
				{
					new Employee{ Name="Ram", Address="Parel", Salary=2500, DepartmentID=2 },
					new Employee{ Name="Shyam", Address="Borivali", Salary=1500, DepartmentID=2 },
					new Employee{ Name="Gita", Address="Panvel", Salary=2000, DepartmentID=2 },
					new Employee{ Name="Sita", Address="Virar", Salary=1600, DepartmentID=1 },
					new Employee{ Name="Kishan", Address="Vikroli", Salary=1400, DepartmentID=1 },
					new Employee{ Name="Kanhaiya", Address="Surat", Salary=1300, DepartmentID=1 }
				};

With LINQ, there are two approaches possible, procedural approach or using new keywords for LINQ. Find below the procedural code to get the desired output using GroupJoin method:

var list = _deptartments.GroupJoin(_employees,
	Dep => Dep.ID,
	Emp => Emp.DepartmentID,
	(dep, empList) =>
		new
		{
			DepartmentName = dep.Name,
			MinSalary = empList.Min(x => x.Salary),
			EmployeeName = (from e in empList 
                    where e.Salary == empList.Min(x => x.Salary) 
                    select e.Name).FirstOrDefault()
		}
	);


Thanks to my colleague Aditya Dhiwar who showed me how to achieve the same using LINQ keywords in C#. Find below code for same:

var list = from e in _employees
            group e by e.DepartmentID into dptgrp
            join d in _deptartments
            on dptgrp.Key equals d.ID
            let minsal = dptgrp.Min(x => x.Salary)
            let employee = dptgrp.Where(e => e.Salary == minsal)
            from e in employee 
            select new
            {
              EmployeeName = e.Name,
              DepartmentName = d.Name,
              MinSalary = minsal,
            }

Find below the complete source code:


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

public class Employee
{
	public string Name { get; set; }
	public string Address { get; set; }
	public decimal Salary { get; set; } 
	public int DepartmentID { get; set; } 
}

public class Department
{
	public int ID  { get; set; }
	public string Name  { get; set; }
	public Employee Manager { get; set; }
}

public class LINQTest
{
	private List<Department> _deptartments = new List<Department>()
					{
						new Department{ ID=1, Name="IT" },
						new Department{ ID=2, Name="Finance" }
					};

	private List<Employee> _employees = new List<Employee>()
					{
						new Employee{ Name="Ram", Address="Parel", Salary=2500, DepartmentID=2 },
						new Employee{ Name="Shyam", Address="Borivali", Salary=1500, DepartmentID=2 },
						new Employee{ Name="Gita", Address="Panvel", Salary=2000, DepartmentID=2 },
						new Employee{ Name="Sita", Address="Virar", Salary=1600, DepartmentID=1 },
						new Employee{ Name="Kishan", Address="Vikroli", Salary=1400, DepartmentID=1 },
						new Employee{ Name="Kanhaiya", Address="Surat", Salary=1300, DepartmentID=1 }
					};

	public void GetEmployeesWithMinSalaryInDepartment()
	{
        // Get listing of all departments along with the employee who is
        // paid least salary for that department

        var list = _deptartments.GroupJoin(_employees,
	        Dep => Dep.ID,
	        Emp => Emp.DepartmentID,
	        (dep, empList) =>
		        new
		        {
			        DepartmentName = dep.Name,
			        MinSalary = empList.Min(x => x.Salary),
			        EmployeeName = (from e in empList 
                        where e.Salary == empList.Min(x => x.Salary) 
                        select e.Name).FirstOrDefault()
		        }
	        );

		foreach (var d in list)
		{
			Console.WriteLine(d.DepartmentName);
			Console.WriteLine(d.EmployeeName);
			Console.WriteLine(d.MinSalary);
		}
	}
}

Hope you find it useful. Happy Coding!!

Vande Mataram!

(A salute to motherland)

P.S. In addition to blogging, I use Twitter to share tips, links, etc. My Twitter handle is: @girishjjain