score:2
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)
Source: stackoverflow.com
Related Query
- 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?
- Retrieving an image from database with Linq to SQL
- C#- Select specific items from a list based on partial intersection with another list (Linq + Lambda)
- Accessing SQL Server time in code with LINQ
- 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
- LINQ to SQL join two tables to select parent table twice based on two different columns from child table
- 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?
- LINQ to SQL to find values starting with characters from another table
- 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 write SQL procedure to insert multiple items at a time from the list?
- 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?
- SQL Linq with contains items from list
- 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
More Query from same tag
- Get time only of datetime
- Ignoring empty fields in a CSV using LINQ
- LINQ conditions from previous result
- NSubstitute not matching Linq Expression
- MongoDB C#Driver 2.0: Why can't I use a boolean method in Query?
- Count child record and show zero if empty
- XML to custom Object
- XML to Datatable LINQ
- SQL LINQ Convert to String
- How to pass Datetime externally into SQL
- could not instance a hashset inside of linq
- LINQ to XML. Enumeration yielded no results?
- C# Efficiently replacing -Infinity values in a dictionary
- How can I use LIKE operator instead of IN operator for multiple commas contains string in stored procedure
- What does the "aggregate list" do for LINQ group by clauses?
- LINQ grouping and counting
- Reading Complex XML Attributes
- LINQ where to Match exact string
- How to orderby conditionally using lambda and non lambda linq?
- Search IDictionary based on value member variable
- LINQ consumes a lot of CPU resources
- LINQ can't use string.contains?
- LINQ GroupBy, finding Max from Count (and then some...)
- Group list by multiple conditions, LINQ
- Filter Data table to list using Linq
- how to add days to datetime by selecting in linq query
- How can I execute this System.Linq statement faster?
- C# XML complex query to XDocument
- Linq Group Join with Where clause
- LINQ OrderBy with more than one field