How do you find the group-wise max in LINQ?
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?
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.
Customer
.Select(c=>new {Order= c.Orders.OrderByDescending(o=>o.Total).First()})
.OrderByDescending(o=>o.Total)
.Take(10);
精彩评论