Retrieving items from SQL based on closest available time with different values

user_1027620 0
Viewed 33

Current database structure is: Table: fxRates Fields: createdOn, Value

I also have a List<string> fxLookupList and each string object can correspond to a fxRate value property.

What I am trying to do is get all the fxRates that contain any item with the same Value from List<FxLoopUp> by doing something along the lines of .Where(fx => fxLookUpList.Contains(fx.Value))

However, since fxLookupList has multiple values, I can't figure out how to get all fxRates having the closest previous or matching today's date given that these should contain all values from fxLookupList.

Appreciate the help!

Some explanation (Note that Today, Yesterday, etc are actually DateTime values)

Data Source:
------------------------------
TODAY                   TEST
YESTERDAY               HELLO
YESTERDAY               HI
YESTERDAY               HELLO
BEFORE YESTERDAY        HEY
BEFORE YESTERDAY        HI
A WEEK AGO              HELLO
A WEEK AGO              HEY

Assuming fxLookUpList will have "HELLO" and "HI";

Output =>
YESTERDAY | HELLO
YESTERDAY | HI

Assuming fxLookUpList will have "HEY" and "HI";

Output =>
YESTERDAY | HELLO
BEFORE YESTERDAY | HEY

Assuming fxLookUpList will have "TEST" and "HEY";

Output =>
TODAY | TEST
BEFORE YESTERDAY | HEY
entity-framework linq c#
2 Answers

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.

user_15581220

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)
user_56461430
Related