Linq to select latest records

Question

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.

Answer 1

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:

1. Select rows for the specific ItemID
2. Group by CurrencyID
3. 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)
4. Pull out the information you want from these rows