Grouping with Linq

Linq offers pretty much the same aggregate functions that SQL does.  And like SQL, often times you want to group results together when aggregating.  Grouping is a little less intuitive with Linq especially when using VB to write your queries.  Here is an example:

Dim MostRecentByCategory = (
    From mt In MyTable
    Group mt By mt.CategoryId Into g = Group
    Select CategoryId, MostRecentDate = g.Max(Function(mt) mt.CreatedDate) )

In our example, we have a table called MyTable. Each record has a category specified by a CategoryId field. If you wanted the most recent date that a record was created in each category, this query should do the trick. Of course, this is not real practical. More than likely, you are going to want the whole record. In other words, you're going to want a result set that represents the most recent record for each category (not just the date). As far as I know, this cannot be accomplished in just one Linq query.  To accomplish this, I would just create a new linq query and join MyTable to the result set we just queried (on CreatedDate and CategoryId).

As far as I can tell, you can only group when a single table or result set exists in your query.  If you want to group across multiple tables, you'll need to join the tables in a result set and then group the single result set.

For example, let's say there is a table named Category and each record in that table has CategoryId and Description.  How can we group MyTable by CategoryId and display the description of each category?  First we need to join the tables in a result set like this:

Dim Records = ( _
    From a In MyTable
        Join b In Category
            On a.CategoryId Equals b.Category
    Select New With {.CategoryId = a.CategoryId, .CategoryDescription = b.Description})

Now, have a single result set with the field that we need (Description) in it.  Then group this result table like above:

Dim CountsByCategory = (
    From r In Records
    Group r By r.CategoryDescription Into g = Group
    Select CategoryDescription, NumberFound = g.Count() )