CODEWITHSUNDEEP
linq-to-sql
sgmoore
sgmoore

Reputation: 16067

Finding latest price per product using LinqToSql

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?

Upvotes: 0

Views: 157

Answers (3)

bruno conde
bruno conde

Reputation: 48265

I think this would do the job...

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

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1500075

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!

Upvotes: 1

David Espart
David Espart

Reputation: 11780

 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

Upvotes: 0

Related Questions