Use LINQ to get only the most recent JOINed item for each element

Question

I have a LINQ query:

Elements.Join(ElementStates,
            element => element.ElementID,
            elementState => elementState.ElementID,
            (element , elementState ) => new { element, elementState })

OK, so each Element has an ElementState associated to it. However there can be multiple states for each element for historical purposes, marked by a DateModified column. In this query, I would like to return only the most recent ElementState for each Element.

Is such a thing possible, using LINQ?

EDIT:

Credit to Gilad Green for their helpful answer.

I have converted it to Method syntax for anyone else who would like to see this in the future:

Elements.GroupJoin(ElementStates,
    element => element.ElementID,
    elementState => elementState.ElementID,
    (element, elementState) => 
    new { element, elementState = elementState.OrderByDescending(y => y.DateModified).FirstOrDefault() });

score 2 · Accepted Answer · answered Sep 18 '18 at 16:48

You can use GroupJoin instead of Join and then retrieve the first record after ordering the group by the DateModified:

var result = from e in Elements
             join es in ElementStates on e.ElementID equals es.ElementID into esj
             select new {
                 Element = e,
                 State = esj.OrderByDescending(i => i.DateModified).FirstOrDefault()
             };

The same can be implemented with method syntax instead of query syntax but in my opinion this is more readable

For the difference between simply joining and group joining: Linq to Entities join vs groupjoin

Thank you, I didn't know about GroupJoin. As for the syntax... my team has decided that the Method syntax is better for reasons I don't agree with, but I am following that convention. I will try to convert this to method syntax. Thanks again. — UpAllNight, Sep 18 '18 at 16:55

Use LINQ to get only the most recent JOINed item for each element

1 Answers1