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?

int score = dbContext.domainmaps.Where(p => p.SchoolId == schoolid).Sum(v => (v?.domainstatement?.Score).GetValueOrDefault()); Does that work? Because the default value for an integer is 0 the summation of this should work without a hitch, but I don't know if I got the syntax correct. — thinklarge, May 03 '17 at 18:10

score 15 · Answer 1 · answered May 04 '17 at 21:53

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;

ocuenca · Answer 2 · 2017-05-03T19:36:44.343

6

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]

edited May 03 '17 at 19:36

answered May 03 '17 at 17:57

ocuenca

38,548
11
89
102

This won't throw a null reference exception if v.domainstatement == null? That's awesome thanks for the pointer! – thinklarge May 03 '17 at 18:40
1

This will cause a null ref exception. Instead, you should add the filter of removing domain maps where v.domainstatement is also null. `.Where(p => p.SchoolId == schoolid && p.domainstatement != null).DefaultIfEmpty(0).Sum(v => v.Score ?? 0);` – Travis J May 03 '17 at 19:18
You're right @TravisJ, if the relationship is optional, then it could be null, thanks – ocuenca May 03 '17 at 19:37
I am getting error at DefaultIfEmpty as Instance argument: cannot convert from 'System.Linq.IQueryable' to 'System.Linq.ParallelQuery' – Sebastian May 04 '17 at 04:08

score 2 · Answer 3 · answered May 04 '17 at 07:41

2

Some time Yours domainstatement Context null so used to linq query

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

answered May 04 '17 at 07:41

kari kalan

497
3
20

thinklarge · Answer 4 · 2017-05-03T18:38:58.497

1

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

The default value of any int? is 0. So this should work.

edited May 03 '17 at 18:38

answered May 03 '17 at 18:20

thinklarge

672
8
24

I don't think that the null conditional operator is supported by query providers. – Travis J May 03 '17 at 19:14
@TravisJ that's interesting. I was really hoping that the op would respond with a yes or no to whether or not that worked. I don't have a dbContext example that I can try this out on otherwise I would. – thinklarge May 03 '17 at 19:45

Handle null in LINQ sum expression

4 Answers4

Linked