CODEWITHSUNDEEP
c#entity-frameworklinq-to-sql
Raymond Morphy
Raymond Morphy

Reputation: 2526

How to select sum or 0 if no records exist?

using (AdviserReserveEntities Adv=new AdviserReserveEntities())
{
   decimal sum= Adv.Credits.Where(u => u.UserIdRef == 2).Sum(a => a.Amount);
}

if no record founds following exception is occurred:

The cast to value type 'Int64' failed because the materialized value is null. Either the result type's generic parameter or the q`uery must use a nullable type.

how to return 0 if no record founds?

Upvotes: 0

Views: 2270

Answers (2)

user2674389
user2674389

Reputation: 1143

One way is to use a nullable type:

decimal? sum = Adv.Credits
                  .Where(u => u.UserIdRef == 2)
                  .Sum(a => (decimal?)a.Amount);

Watch the cast in the sum expression. This way you will get null back when there was no element. You can add the coalesce operator to get a default value:

decimal sum = Adv.Credits
                 .Where(u => u.UserIdRef == 2)
                 .Sum(a => (decimal?)a.Amount)
              ?? 0;

The solution mentioned by Raphael and Justin is worse, because first checking with Any() will cause in two database queries instead of just one.

Upvotes: 7

Justin Niessner
Justin Niessner

Reputation: 245399

var records = Adv.Credits.Where(u => u.UserIdRef == 2);
decimal sum = records.Any() ? records.Sum(a => a.Amount) : 0m;

Upvotes: 1

Related Questions