Getting rows from one table and latest 1 row from an associated table, in LINQ...

Question

Ok, I think this question is similar to this one:

http://stackoverflow.com/questions/1704821/linq-to-sql-groupby-and-max-to-get-the-object-with-latest-date

But I have a table which I want to return a lot of rows from. It's primary key is used as a foreign key in a second table. For each row returned from the first row, I also want to return (in the same result set) the latest (in this case, ordered by a datetime field) 1 row.

How would this be constructed in LINQ (I'm using C#) ?

The best I have is:

    var instances = from i in ContextDB.Instances
                    join p in ContextDB.Points on i.InstanceID equals p.InstanceID
                    where categoryIDs.Contains(j.CategoryID)
                    group p by p.InstanceID into latestPoint
                    select new latestPoint.OrderBy(i => i.Instance.Title).FirstOrDefault();

But this is clearly wrong, although I think I'm understanding group correctly I don't think I'm understanding into correctly.

[EDIT] To avoid confusion, I think this question can be summed up as "how to do I rows from one table along with the latest 1 row for each from another table?"

Answer 1

How about:

var instances = from i in ContextDB.Instances
                where i.CategoryInstances.Any(categoryIDs.Contains(ci => ci.CategoryID))
                select new
                {
                    Instance = i,
                    LatestPoint = i.Points.OrderBy(i => i.Instance.Title).FirstOrDefault()
                };

Note that I can remove a lot of redundancy by using associations instead of join.