LINQ - sub query using OrderBy and GroupBy

Question

I have some customers and related orders.

Sometimes I want to see all orders for every customer - straightforward enough.

Other times I'd like to see the last order for every customer, if they have no orders I want to see them anyway.

Heres my LINQ pseudo code :

from customers in DataSet.Customers
join orders in DataSet.Orders on customers.CustomerId equals orders.CustomerId
into customerOrders
let customerLastOrder = customerOrders.Select(CustomerId, OrderAmount)
                                      .OrderByDescending(OrderTimestamp)
                                      .GroupBy(CustomerId)

I then want to total all Customer Last Orders.

I'm not quite there as you can see - any help really appreciated.

Thanks, Joe

score 4 · Accepted Answer · answered Oct 31 '11 at 17:18

I suspect you want:

from customer in DataSet.Customers
join order in DataSet.Orders on customer.CustomerId equals order.CustomerId
    into customerOrders
select new {
    CustomerId = customer.Id,
    LastOrderId = customerOrders.OrderByDescending(order => order.OrderTimestamp)
                                .Select(order => order.OrderId)
                                .FirstOrDefault()
};

There's no need to perform any more grouping - the group join (join ... into) has already done that for you. You're looking at the orders for a single customer, so you just need to order them, select the ID, and then use FirstOrDefault() to get the first value in the ordered sequence (or null if the sequence is empty).

Note that I changed the name of your range variable - at any one point, customer refers to a single customer, and order refers to a single order. It's worth keeping that sort of thing clear in your query for readability.

Excellent, sure Thank you - then it will be a simple case off looping through the returned rows to get the total? — Joe.Net, Oct 31 '11 at 17:33
@Joe.Net: Yes - but if you're only interested in the total last orders, why do you need customers with no orders? — Jon Skeet, Oct 31 '11 at 17:36
Ok Thanks. I need to view Customers regardless of if they have Orders or not - but I can do that in another peice of LINQ then join the 2 together.. — Joe.Net, Oct 31 '11 at 17:41

score 2 · Answer 2 · edited May 23 '17 at 11:55

2

I was playing around and came up with:

var sumAmounts = Orders.OrderBy(o => o.CustomerId)
                       .ThenByDescending(o => o.DateStamp)
                       .DistinctBy(o => o.CustomerId)
                       .Sum(o => o.OrderAmount);

DistinctBy comes from Mr Skeet on this thread

This covers your requirement of totalling 'all Customer Last Orders'.

Cheers.

edited May 23 '17 at 11:55

Community

1
1

answered Oct 31 '11 at 18:06

christofr

2,680
1
18
19

Hi christofr - this isn't quite right - it sums all customer orders, it doesn't sum the last Customer Orders - each CustomerId must be unique.. – Joe.Net Nov 01 '11 at 09:39
Hi, the DistinctBy should take care of that -I've pasted my code to here: http://pastebin.com/z8T7AMGV - the output is 50, as I would expect. – christofr Nov 01 '11 at 09:53
Ah Yes- thanks for the code - that's really helped my understanding - I would up vote more if I could! – Joe.Net Nov 01 '11 at 10:35

score 1 · Answer 3 · answered Oct 31 '11 at 17:16

I think you could use

customerOrders.OrderByDescending(OrderTimestamp)
              .GroupBy(CustomerId)
              .Select(c => new { CustomerId = c.Key, LastOrder = c.Last() });

although you might be better using Aggregate() to build the customer ID to last order ID mapping, which would avoid the sort and use only a single scan.

LINQ - sub query using OrderBy and GroupBy

3 Answers3