Print Page | Close Window

AsScalarAsync().Sum on empty query

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2012
Forum Discription: For .NET 4.5
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=3698
Printed Date: 25-Oct-2025 at 11:18pm


Topic: AsScalarAsync().Sum on empty query
Posted By: Walid
Subject: AsScalarAsync().Sum on empty query
Date Posted: 08-Oct-2012 at 2:25am
Hello,

I was expecting that AsScalarAsync().Sum() shoud return 0 on a query that is returning no record, but it throws an exception on the server.

It seems that the sum tries to cast a null value into a Decimal :

Caught exception: System.InvalidOperationException: Échec du cast en type valeur « Decimal », car la valeur matérialisée est Null. Le paramètre générique du type de résultat ou la requête doit utiliser un type Nullable


Replies:
Posted By: kimj
Date Posted: 08-Oct-2012 at 1:17pm
Hi Walid,
 
The exception is coming from EF, and according to MS this behavior is by design.  You can work around this by casting the value to be summed to a nullable, eg:
  var sum = await em.OrderSummaries.Select(o => (decimal?) o.Freight).AsScalarAsync().Sum();
or
  var sum = await em.OrderSummaries.AsScalarAsync().Sum(o => (decimal?) o.Freight);
 
We'll see if there's anything we can add to DevForce so the workaround isn't required.

Posted By: mgood
Date Posted: 08-Oct-2012 at 3:46pm
Walid,
This is a common trap in the database world. In SQL the sum of an empty resultset is NULL and not zero. This goes for AVG, MAX, MIN etc. For example you can run the following query against NorthwindIB and you'll see it returns NULL.

SELECT SUM(Freight)
FROM [NorthwindIB].[dbo].[Order]
WHERE 1 = 0

LINQ infers the type of the scalar result from the source, so if you sum a property of type double, the result is double, but if you sum an empty result set as established above, the result value is NULL which cannot be assigned to type double. The workaround as Kim already mentioned is to make sure that the property you sum is nullable by casting it yourself if you expect that the sum might be done on an empty resultset.

Posted By: Walid
Date Posted: 09-Oct-2012 at 12:29am
Thank you Kim & Marcel.


Print Page | Close Window