Home > Programming > LinqToSql Sum over empty collection causes exception

LinqToSql Sum over empty collection causes exception

Linq to Sql Sum method over empty collection returns null. Linq to object Sum method over empty collection returns 0.

This Linq To Sql behavior can cause exception :

"The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type."

This will happen if you try to assign sum to decimal. Decimal is not nullable. If sum is called over empty collection (e.g. no items are return from where clause) sum will try to return null. decimal is not nullable and you have exception Sad smile

One solution is to cast sum so it returns nullable decimal (decimal ?), Second solution is to use linq to object, because, linq to object sum over empty collection returns 0.

Why is all this happening?

In SQL, Sum() aggregate operator returns null for an empty set. So this is as designed.

Since users often rely on LINQ to SQL producing the same results as an equivalent SQL query, we decided not to convert the null to zero as in case of LINQ to Objects. There are a number of cases where we considered forcing .NET/C#/VB semantics for consistency with LINQ to Objects vs retaining SQL semantics for consistency with SQL. In most cases, we favored the latter for consistency and more enforceable and performant implementation,

Source: http://social.msdn.microsoft.com/Forums/en-US/linqprojectgeneral/thread/479f2823-c942-4990-adca-21e03f969a12

 

 

You can read more about this issue on

http://weblogs.asp.net/zeeshanhirani/archive/2008/07/15/applying-aggregates-to-empty-collections-causes-exception-in-linq-to-sql.aspx

and

http://stackoverflow.com/questions/696431/linq-query-with-nullable-sum-problem/

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: