EF Sum Error Due to Empty Rows After Filtering on MySQL

I actually have been waiting when I will encounter this kind of error. This time the error happens when performing EF query on MySQL database.

Problem

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)

Solution

One helpful article:

https://coding.abel.nu/2012/08/null-sematics-in-linqs-sum/

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()