Project only the most current data into a linq query

OK, here is the issue.

I have a table (actually a view, but it is irrelevant) c开发者_运维百科ontaining the price for an item on a time. Fields:

• ItemName
• Price
• Timestamp.

There are additional fields in this, that also must be accessible. Stuff like column and some calculated numbers. This is basically an object with fields, we have quite a long table (millions of rows).

I need to join a list of item names with their most current price, and I want to do so in linq. In pure SQL this already is not fun. I basically look for a "view" (IQueryable for further merging) definition that only contains the most current object for a given item name. This then allows to use a JOIN statement with another filtered table (list of items for which to show the price). The db design is fixed third party so this problem can not be avoided by redesign.

In pure SQL I Would define a query containing ItemName and Max(Timestamp) (Group by ItemName) then Self-Join with the same table to get the other fields in for the exact timestamp.

Any idea how to approach this best in LINQ? Lambda please, i don't like the sql style syntax.

In Linq-to-objects I would try this:

prices
    .GroupBy(p => p.ItemName)
    .Select(g => new {
                         Item = g.Key, 
                         LatestPrice = g.OrderByDescending(p => p.Timestamp).First()
                     });

Though I'm not sure whether it will be mapped correctly to SQL.

This request will do the job too :

var v = from product in Products
    join recentProduct in
        (from _product in Products
         group _product by _product.Name into _products
         select new { Name = _products.Key, TimeStamp = _products.Max(s => s.TimeStamp) })
    on new { product.Name, product.TimeStamp } equals new { recentProduct.Name, recentProduct.TimeStamp }
    select product);