Sum nested values with Linq
Simple problem: I have Users that can have many Orders that can have many Products. What does the Linq (lambda) query look like to get a User's grand total of all Product.Price values?
I've tried this:
int total = users.Sum(u => u.Orders.Sum(o => o.Products.Sum(p => p.Price)));
But it's giving me:
The cast to value type 'I开发者_Go百科nt32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.
Of course, a user may not have any orders, and an order may not have any products. But Product.Price is not a nullable value.
So I tried this, thinking it was choking on empty collections:
int total = users.Sum(u => u.Orders.Sum(o => o.Products.Sum(p => p.Price) ?? 0) ?? 0) ?? 0;
But it's throwing compile errors, saying the left side of the ??
is not nullable.
What am I doing wrong?
Thanks in advance.
UPDATE: A working version of my examples above after using Marc's logic from his answer:
int total = users.Sum(u => u.Orders.Sum(o => o.Products.Sum(p => (int?)p.Price))) ?? 0;
int total = (from user in users
from order in user.Orders
from product in order.Products
select (int?)product.Price).Sum() ?? 0;
would be my offering; there is an annoying glitch that the SUM in SQL over 0 rows is NULL
, not 0
- the above works around that.
or as lambdas (from comments):
int total = users.SelectMany(user => user.Orders)
.SelectMany(order => order.Products)
.Sum(product => (int?)product.Price) ?? 0;
精彩评论