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.

Tags: C#

Post By Josh Twist

Follow @joshtwist

5:19 AM
06 Oct 2008

» Next Post: Silverlight 2 File Uploader: Final!
« Previous Post: How to use Ukadc.Diagnostics with Trace and Debug

Comments are closed for this post.

Posted by Kamesh Bora @ 17 Jun 2009 10:31 PM
How do we convert the var Groups to an IList<something>?

Posted by josh @ 18 Jun 2009 12:16 AM
groups.ToList()?

Not sure what you mean. If you mena not using anonymous types then you'll need a type with the appropriate properties, e.g.

public class MyType
{
public string Dept { get; set;}
public DateTime EarliestEmployeeStartDate { get; set;}
}

And then the query would be:

from emp in employees
group emp by emp.Dept into g
select new MyType { Dept = g.Key, StartDate = g.Min(e => e.DateTime) };

This would return IEnumerable<MyType> and there's an extension method .ToList() if you really want List<MyType>

Posted by Gustaf Lindqvist @ 12 Mar 2010 3:56 AM
Thanks a lot, you just saved me a lot of time. I Read your post and could really easily translate it into my problem, even though it became a little bit more complex. The conclusion is that you explained it pedagogically!

Thanks a lot!

Posted by San @ 08 Jun 2010 12:08 PM
Thanks Josh. For a LINQ beginner like me this is what i exactly needed, The explanation & conversion to a list<Mytype> was very useful.I needed LINQ results in typed Classes rather than in anoynomous.

Posted by Peternac @ 09 Jan 2011 2:03 PM
I try to build a LINQ statement where the list of fields is
empty but I cannot get it done.

This works:

Dim q = From p In dt_TAttrDataForData_Id _

Group By p.Attribute_Id Into inputFormula = GroupResults(p.Attribute_Id + "#" + p.Value)

Select New With {inputFormula}

but what I want is this:

Dim q = From p In dt_TAttrDataForData_Id _

Group By Into inputFormula = GroupResults(p.Attribute_Id + "#" + p.Value)

Select New With {inputFormula}

Can you give me same help?

Posted by Arvi @ 23 Feb 2011 2:25 AM
How can we then enumerate the grouped items later on? For instance, I wish to later project the names of the employees in a certain department, i'd ideally like to do something like
var groups = from emp in employees
group emp by emp.Dept into g
select new { Dept = g.Key, Names = g.SelectMany(x=>Name).ToList() }; or something of that nature. However, this doesn't work. What would be a good alternate query to accomplish this? Thanks.

Welcome

Feeds

Archive

Editors