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());
.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.
Just extend the
Where method to have only those values which were created before
.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)
- Retrieving items from SQL based on closest available time with different values
- How can I set properties on all items from a linq query with values from another object that is also pulled from a query?
- C#- Select specific items from a list based on partial intersection with another list (Linq + Lambda)
- Get values from database with specific time interval
- How to combine node values from different nodes into one node separated with a "/" every 2 values
- How to get different values with same name from xml with linq
- How to compare values from different tables with a single query on Linq-to-SQL?
- How to create a new list from property values when grouping items with GroupBy?
- How to get record form a different table based on a value from first table with linq expression?
- filter items from list of dictionary in c# based on matching key and values
- Hashet union with preserving values from combined items
- Retrieving all items from one table that is not in other table with Linq
- How to sum value from database based on a column eg- sum of Salary based on department from a list with different department
- Given two lists of different objects, how to combine values from each into a new list based on a common Id, efficiently?
- Find a certain value from a source collection in a target collection of different type with linq
- Generic code to determine how many rows from a table are in a different table with matching structure?
- After retrieving data from a table, I am trying to condense a list of dictionaries to unique key values for "platform" with sum of "global sales"
- Updating One List with values from another list based on a condition
- How to get the closest number from a List<int> with LINQ?
- Remove items of list from another lists with criteria
- Exclude list items that contain values from another list
- Get different and common items in two arrays with LINQ
- Linq to update a collection with values from another collection?
- How to remove an element from an xml using Xdocument when we have multiple elements with same name but different attributes
- Exclude items of one list in another with different object data types, LINQ?
- How do I remove items from generic list, based on multiple conditions and using linq
- Comparing 2 objects and retrieve a list of fields with different values
- Get random items with fixed length of different types
- Getting unique values from a list of objects with a List<string> as a property
- Compare only time from datetime in entity framework 6 with odp.net Oracle 12c
- How to get values out of IGrouping?
- Comparing 2 linq applications: Unexpected result
- Linq query to return the cheapest product, only if it is unique
- LINQ Query not returning results
- Is it possible to use Linq to get a total count of items in a list of lists?
- Linq to get complete row based on duplicate values in column
- Is there a difference between putting conditionals inside or outside a LINQ query?
- Do linq generated queries get cached effectively by SQL Server 2008?
- C# how to write JSON with enumerated identifiers
- Filter null properties from list using Linq
- Casting to a type while in LINQ statement
- Two tables in a view with ViewModel
- Calculating the difference between two column with NULL's using Entity Framework
- Convert DataTable(dynamic columns) into List<T>
- Group By in LINQ with a particular column
- Ordering a list based on multiple factors
- How to cast into a list from a Linq lambda expression
- Factoring out expressions in linq queries
- System.Xml.Linq.XDocument' is defined in an assembly that is not referenced
- LINQ distinct with corresponding counts based on a List property