New Posts New Posts RSS Feed: AsScalarAsync().Sum on empty query
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

AsScalarAsync().Sum on empty query
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
Walid View Drop Down
Senior Member
Senior Member
Avatar

Joined: 14-Nov-2010
Posts: 161 		Post Options Post Options   Quote Walid Quote  Post ReplyReply Direct Link To This Post Topic: AsScalarAsync().Sum on empty query
    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
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post 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.


Edited by kimj - 08-Oct-2012 at 1:38pm
Back to Top
mgood View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 18-Nov-2010
Location: Emeryville, CA
Posts: 583 		Post Options Post Options   Quote mgood Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
Walid View Drop Down
Senior Member
Senior Member
Avatar

Joined: 14-Nov-2010
Posts: 161 		Post Options Post Options   Quote Walid Quote  Post ReplyReply Direct Link To This Post Posted: 09-Oct-2012 at 12:29am
Thank you Kim & Marcel.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down