CODEWITHSUNDEEP
c#linq
Tom Gullen
Tom Gullen

Reputation: 61737

Linq to select latest records

I have the data structure

enter image description here

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.

Upvotes: 18

Views: 28368

Answers (1)

Jon
Jon

Reputation: 437336

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

Upvotes: 49

Related Questions