Ranking. Linq to sql question

Question

I have a table of orders made by persons:

Orders
{ 
  Guid  PersonId, 
  int   Priority, 
  Guid  GoodId 
}

Priority is some integer number. For example:

AlenId   1  CarId
DianaId  0  HouseId
AlenId   3  FoodId
DianaId  2  FlowerId

I want to retrieve highest priority orders for each person:

AlenId   1  CarId
DianaId  0  HouseId

In T-SQL I'll use ranking, how can I get the same result in Linq-2-Sql ?

Thank you in advance!

Answer 1

Something like this:

var query = from order in context.Orders
            orderby order.Priority
            group order by order.PersonId into personOrders
            select personOrders.First();

I believe that should work, but I don't know how well-defined it is, in terms of the ordering post-grouping. This would also work, although it's slightly uglier:

var query = from order in context.Orders
            group order by order.PersonId into personOrders
            select personOrders.OrderBy(order => order.Priority).First();

Or using just dot notation:

var query = context.Orders
                   .GroupBy(order => order.PersonId)
                   .Select(group => group.OrderBy(order => order.Priority)
                                         .First());