one to many join - taking only the last one on the many part

Question

I'm quite a newbie in EF, so I'm sorry if my question has been answered before.. I just can't figure out the syntax..

I have two entities, Category & Product, where one category has many products.

I want to get all categories, with only their latest product (it has a date property named timestamp)

I have no idea how to do that. :-/

If possible I'd like to know the syntax of the two ways to write it, both the sql-like syntax, and the C# like syntax, e.g.:

ctx.Categories.Include("Products").ToList() 

from c in ctx.Categories.Include("Products")

Thanks!

Answer 1

Here's the SQL-like way:

var categories =
        from p in products
        group p by p.Category into g
        select new { Category = g.TheKey, LatestProduct = g.Max(p => p.TimeStamp) };

This is the Lambda-way (warning, untested):

var categories = products.GroupBy(p => p.Category)
                         .Select(g => new { Category = g.TheKey, 
                                            LatestProduct = g.Max(p => p.TimeStamp)});

A note on Categories.Include("Products"), you don't need this in your example. You use "Include" for eager-loading, so that for example if you had a list of Categories returned from EF, when you do Categories.Product you will get the associated product.

But all you require is a list of categories, and a single product for each one - which is already returned in the above LINQ query, so no need for Include.