score:2

Accepted answer

I am slightly having to guess because of the wording of the question, but if I'm understanding correctly, I believe this will do it:

    fxRates
        .Where(fx => fxLookUpList.Contains(fx.Value) && fx.createdOn <= today)
        .GroupBy(fx => fx.Value)
        .Select(fxGrp => fxGrp.OrderByDesc(fx => fx.createdOn).FirstOrDefault());

The .Where() obviously filters down to just values in fxLookupList. At this point, the result set will contain multiple records for the same value with different createdOn dates, and if I'm understanding the question correctly, you just what the most recent of each value that is on/before today's date. So we also filter the .Where to filter on cratedOn <= today, where today is expected to be DateTime variable containing the exact time to filter on (didn't want to assume exactly what that means but you could also just write DateTime.UtcNow)

Following this, we .GroupBy value, so that each Grouping set will contain all the fxRates for a given Value with all dates before today (was already filtered in the .Where).

Finally, we project (.Select) out the most recent date by ordering the above grouping descending by createdOn, taking the .FirstOrDefault() to give you the most recent item (again already filtered to <= today).

Finally note that logically we would use .First() in the select, but can't because EF does not allow that in the query. Alternatively you could do a .ToArray() after the GroupBy and use .First() on the in-memory .Select()at that point.

score:0

Just extend the Where method to have only those values which were created before today

.Where(fx => fxLookUpList.Contains(fx.Value) && fx.createdOn <= DateTime.UtcNow)

Or you could also order them desc to get the closest:

.Where(fx => fxLookUpList.Contains(fx.Value) && fx.createdOn <= DateTime.UtcNow).OrderByDescending(fx => fx.createdOn)

Related Articles