CODEWITHSUNDEEP
c#entity-frameworklinq
Sebastian
Sebastian

Reputation: 4811

Handle null in LINQ sum expression

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?

Upvotes: 13

Views: 18293

Answers (4)

Ivan Stoev
Ivan Stoev

Reputation: 205549

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;

Upvotes: 15

kari kalan
kari kalan

Reputation: 505

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

Upvotes: 3

ocuenca
ocuenca

Reputation: 39326

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]

Upvotes: 6

thinklarge
thinklarge

Reputation: 682

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.

Upvotes: 1

Related Questions