Linq to select latest records

c#, linq

Solution

This should work:

db.tblStoreItemPrices
    .Where(c => c.ItemID == ID)
    .GroupBy(c => c.CurrencyID)
    .Select(g => g.OrderByDescending(c => c.Date).First())
    .Select(c => new { c.CurrencyID, c.Amount });

Explanation:

- Select rows for the specific ItemID

- Group by CurrencyID

- From within each currency group select the row that has the most recent date (leaving one row for for each CurrencyID in the result set)

- Pull out the information you want from these rows

Problem

I have the data structure For each item there is a record of it's price on a certain date in each currency. I need to create a query that returns the most current price for each currency. This query works, but returns multiple `Amounts` for currency ID `1`. It should only return 3 records, `7,8 and 9` as these represent the most up to date prices in all currencies for this item. ``` var q = (from c in db.tblStoreItemPrices where c.ItemID == ID select new { c.CurrencyID, c.Amount }); ``` Please ignore all ordering and assume that records are randomly ordered.

Original source