Geek asks and answers

Only project the latest data in a linq query

OK, that’s the problem.

I have a table (actually a view, but it doesn’t matter) that contains the price per item at a time. Fields:

  • ItemName
  • Price
  • Mark.

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

I need to join the list of element names with their current price, and I want to do this in linq. In pure SQL, this is no longer fun. I'm basically looking for a definition of "view" (IQueryable for more merging) that contains only the very last object for a given element name. This then allows you to use the JOIN operator with another filtered table (a list of items for which the price is shown). The db design is a fixed third party, so this problem cannot 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 other fields for the exact timestamp.

Any idea what is the best approach in LINQ? Lambda, please, I don't like sql style syntax.

+3
source
2

Linq-to-objects :

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

, SQL.

0

:

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);
0

More articles:


All Articles