LINQ group by query with projection
This post falls into the category of being posted for my own future reference - hopefully you'll find it useful too (and apologies to regular readers for the recent silence on theJoyOfCode.com - I've been away on holiday).
I was recently working on a project and had the need to write a LINQ query (this is good old L2O, or Linq-to-objects) that grouped some objects together. If this were SQL (which it isn't) the table might have looked like this:
| Name | Dept | StartDate |
|---|
| Bill Bloggs | HR | 1976-06-11 |
| Barry Williams | HR | 2000-08-02 |
| Terry Dickins | IT | 2007-11-20 |
| Andy Robinson | IT | 2006-10-01 |
An I wanted to group by department, selecting the earliest StartDate. The T-SQL to do so may have looked like this:
SELECT Dept, MIN(StartDate)
FROM Employees
GROUP BY Dept
That was easy enough. Now, what if my Employees table was really a List
of Employee objects, each with a Name, Dept and StartDate property?
var groups = from emp in employees
group emp by emp.Dept into g
select new { Dept = g.Key, StartDate = g.Min(e => e.DateTime) };
Hmmm.... not so easy peasy. One of the reasons I'm a big fan of LINQ is that I think it often makes your intent clearer. I'm really not sure that that is the case here but perhaps that is just because we're not as used to it yet.Let's look at the query and dissect it line by line.
var groups = from emp in employees
That's easy enough - we're creating an anonymous type called groups by selecting from the employees collection of objects.
group emp by emp.Dept into g
Now we're saying we want to create groups of employees by bunching together employees with the same Dept property. This effectively creates a new 'g' variable that will hold the groups in question. The g variable is another IEnumerable, as you'd expect, but it also offers a Key property, which in this case is the Dept property of its employees.
select new { Dept = g.Key, StartDate = g.Min(e => e.StartDate) };
Here we create our 'projection' - a new anonymous type with a Dept property (the key of the group) and a StartDate property where we use a Lambda on g to select the minimum start date of that particular group.Nice.