EF Linq Product Sum when no records returned

Question

I’ve seen variations of this question all over the place but none of the answers work for me. Most of them are just trying to sum a single column too – nothing more complex such as the sum of a product as below:

public double Total

    {
        get
        {
            return _Context.Sales.Where(t => t.Quantity > 0)
                .DefaultIfEmpty()
                .Sum(t => t.Quantity * t.Price);                 
        }
    }

If no rows are returned I want to return zero. However if no rows are returned the .Sum() fails. There are various options of trying to insert Convert.ToDouble and using null coalesce operators, but they all still gave me errors.

I’m sure I am missing a simple way to do this – any help greatly appreciated after too long banging head against google brick wall!

Answer 1

Remove DefaultIfEmpty() operator. Cast Sum result to nullable type. Then use null-coalescing operator to return default value if query returned null

public double Total    
{
   get
   {
       return _Context.Sales
                      .Where(t => t.Quantity > 0)
                      .Sum(t => (double?)(t.Quantity * t.Price)) ?? 0; 
   }
}

Generated SQL will look like:

SELECT SUM([Filter1].[A1]) FROM 
(SELECT CAST(CAST([Extent1].[Quantity] AS decimal(19,0)) * 
                  [Extent1].[Price] AS float) AS [A1]
 FROM [dbo].[Sales] AS [Extent1]
 WHERE [Extent1].[Quantity] > 0) AS Filter1