Tutorial :How do you find the group-wise max in LINQ?



Question:

I'm trying to solve the "group-wise max" problem in LINQ. To start, I have a database modeled using the Entity Framework with the following structure:

Customer:  ---------  CustomerID : Int32  Name : String    Order:  -------  OrderID : Int32  CustomerID : Int32  Total : Decimal  

This gives me navigation from a Customer to her orders and an Order to the owner.

I'm trying to create a LINQ query that allows me to find the top-10 customer orders in the database. The simple case was pretty easy to come up with:

var q = (      from order in _data.Orders  // ObjectQuery<Order>      orderby order.Amount descending select order  ).Take(10);  

However, I'd like to only show unique customers in this list. I'm still a bit new to LINQ, but this is what I've come up with:

var q = (      from order in _data.Orders  // ObjectQuery<Order>      group order by order.Customer into o      select new {          Name = o.Key.Name,          Amount = o.FirstOrDefault().Amount      }  ).OrderByDescending(o => o.Amount).Take(10);  

This seems to work, but I'm not sure if this is the best approach. Specifically, I wonder about the performance of such a query against a very large database. Also, using the FirstOrDefault method from the group query looks a little strange...

Can anyone provide a better approach, or some assurance that this is the right one?


Solution:1

You could do:

var q = (      from order in _data.Orders  // ObjectQuery<Order>      orderby order.Amount descending select order  ).Distinct().Take(10);  

I would normally look at the generated SQL, and see what is the best.


Solution:2

Customer  .Select(c=>new {Order= c.Orders.OrderByDescending(o=>o.Total).First()})  .OrderByDescending(o=>o.Total)  .Take(10);  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »