Handle null in LINQ sum expression

Question

I am using a LINQ query to find sum of a column and there is a slight chance that the value might be null in few cases

The query I am using now is

int score = dbContext.domainmaps.Where(p => p.SchoolId == schoolid).Sum(v => v.domainstatement.Score ?? 0);

where domainstatement can be null and score also can be null

Now after executing this query, I am getting the error

The cast to value type 'Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

So how can I handle null exceptions effectively and return sum as an INT value?

Answer 1

Here

Sum(v => v.domainstatement.Score ?? 0);

you've just put the null-coalescing operator on the wrong place. Usually such error is solved by promoting the non nullable type to nullable (no DefaultOrEmpty and null checks are needed), but here you already have a nullable type, and with null-coalescing operator you did the opposite of what the error message is telling you - the query must use a nullable type.

Simply move it after the Sum call and the issue is gone:

Sum(v => v.domainstatement.Score) ?? 0;

Answer 2

Use DefaultIfEmpty extension method:

int score = dbContext.domainmaps.Where(p => p.SchoolId == schoolid 
                                         && p.domainstatement != null)
                                .DefaultIfEmpty()
                                .Sum(v => v.domainstatement.Score ?? 0);

Your problem is there are not entities with that schoolId.Something like this is the sql code is going to generate:

-- Region Parameters
DECLARE @p0 Int = 3
DECLARE @p1 Int = 0
-- EndRegion
SELECT SUM([t3].[value]) AS [value]
FROM (
    SELECT COALESCE([t2].[Score ],@p1) AS [value]
    FROM (
        SELECT NULL AS [EMPTY]
        ) AS [t0]
    LEFT OUTER JOIN (
        SELECT [t1].[Score ]
        FROM [domainmaps] AS [t1]
        WHERE [t1].[SchoolId] = @p0
        ) AS [t2] ON 1=1 
    ) AS [t3]