Finding latest price per product using LinqToSql

Question

If I have a sql table that contains

ProductID

Price

Date

and I wish to find the latest price for a given date. In Sql I can do something like

` Select * from (

select ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Date DESC) AS row_number,

ProductID, Date, Price from ProductPrices

where date < @targetDate

) tempTable

where row_number = 1 `

Is it possible to do this easily in LinqToSql? My attempts have all ended up issuing on sql command per product?

Answer 1

 var result = (
               from context.table where date < targetDate 
               and ProductID = targetproduct 
               orderby date descending
               select new {ProductID, price, Date}
              ).Take(1);

Maybe the syntax is not perfect but you should pay attention to the Take(1), which is the equivalent to the row_number

Answer 2

How about something like this:

var query = from product in db.Products
            where product.Date < targetDate
            orderby product.Date descending
            group product by product.ProductID into groups
            select groups.First();

It's possible that calling First won't work but FirstOrDefault will - someone else found that recently in a similar situation.

EDIT: This solution was driven from the title more than from the SQL (which I don't fully understand). It aims to find the most recent entry for each product. Hope that's what you were after!

Answer 3

I think this would do the job...

var qry = ProductPrices.Where(pp => pp.Date < targetDate)
                       .Max(pp => pp.Date.Ticks);