After filtering, the query returns empty rows thus sum can't work. The error message in my case is:
"The cast to value type 'System.Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type."
The following is an example code:
Dim totalPrice = dbContext.Items.Where(Function(i) i.Color = "Blue").Sum(Function(i) i.Price)
However it works fine if we execute the query first before Sum, but it requires the rows to be pulled to memory which can be resource intensive.
Dim totalPrice = dbContext.Items.Where(Function(i) .Color = "Blue").ToList().Sum(Function(i) i.Price)
One helpful article:
The solution in my case is to perform projection, followed by DefaultIfEmpty and call Sum() afterwards. The code becomes:
Dim totalPrice = dbContext.Items.Where(Function(i) i.Color = "Blue").Select(Function(i) i.Price).DefaultIfEmpty(Decimal.Zero).Sum()
No comments:
Post a Comment