0

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?

jheddings
  • 26,717
  • 8
  • 52
  • 65

2 Answers2

0

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.

leppie
  • 115,091
  • 17
  • 196
  • 297
  • That brings up a great question... How do I look at the generated SQL? With the LINQ to SQL DataContext, I could log the output. I haven't found a way to do that for EF. – jheddings Jan 09 '10 at 20:48
  • Hrm... Trying this, I had to create a custom equality comparator (since the default one doesn't work for this purpose). However, this throws an exception saying that EF doesn't recognize that method. Any ideas? – jheddings Jan 09 '10 at 21:02
0
Customer
.Select(c=>new {Order= c.Orders.OrderByDescending(o=>o.Total).First()})
.OrderByDescending(o=>o.Total)
.Take(10);
Alexander Taran
  • 6,655
  • 2
  • 39
  • 60