Linq query with nullable sum

AndreasN_24821 0
Viewed 69342
from i in Db.Items
select new VotedItem
{
    ItemId = i.ItemId,
    Points = (from v in Db.Votes
              where b.ItemId == v.ItemId
              select v.Points).Sum()
}

I got this query, however it fails if no votes are found with exception:

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

I assume its because sum returns an int and not a nullable int, giving sum a int? as input only give the same error, probably cause sum only workes on ints.

Any good workaround for this?

linq-to-sql nullable sum linq c#
14 Answers
user_2370910 
from i in Db.Items
select new VotedItem
{
    ItemId = i.ItemId,
    Points = (from v in Db.Votes
              where b.ItemId == v.ItemId
              select v.Points ?? 0).Sum() 
}

EDIT - ok what about this... (Shooting again since I don't know your model...):

from i in Db.Items
select new VotedItem
{
    ItemId = i.ItemId,
    Points = (from v in Db.Votes
              where b.ItemId == v.ItemId)
              .Sum(v => v.Points) 
}
user_809410

Assuming "v.Points" is a decimal just use the following:

from i in Db.Items
select new VotedItem
{
    ItemId = i.ItemId,
    Points = (from v in Db.Votes
              where b.ItemId == v.ItemId
              select (decimal?) v.Points).Sum() ?? 0
}
user_5407480

A simple but effective workaround would be to only sum the votes where Points.Count > 0, so you never have null values:

from i in Db.Items
select new VotedItem
{    
  ItemId = i.ItemId,
  Points = (from v in Db.Votes
            where b.ItemId == v.ItemId &&
            v.Points.Count > 0
            select v.Points).Sum()
}
user_388890

I had the same problem. Solved it with empty list union:

List<int> emptyPoints = new List<int>() { 0 };

from i in Db.Items
select new VotedItem
{
 ItemId = i.ItemId,
 Points = (from v in Db.Votes
           where b.ItemId == v.ItemId
           select v.Points).Union(emptyPoints).Sum()
}

In case of "Points" is integer this should work.

user_13131440 
        (from i in Db.Items
         where (from v in Db.Votes
                where i.ItemId == v.ItemId
                select v.Points).Count() > 0
         select new VotedItem
         {
             ItemId = i.ItemId,
             Points = (from v in Db.Items
                       where i.ItemId == v.ItemId
                       select v.Points).Sum()
         }).Union(from i in Db.Items
                  where (from v in Db.Votes
                         where i.ItemId == v.ItemId
                         select v.Points).Count() == 0
                  select new VotedItem
                  {
                      ItemId = i.ItemId,
                      Points = 0
                  }).OrderBy(i => i.Points);

This works, but isn't very pretty or readable.

AndreasN_248210

I had a similar issue and came up with the solution of getting whatever I was trying to get out of the database, do a count on those and then only if I had anything returned do a sum. Wasn't able to get the cast working for some reason so posting this if anyone else had similar issues.

e.g.

Votes = (from v in Db.Votes
          where b.ItemId = v.ItemId
          select v)

And then check to see if you've got any results so that you don't get null returned.

If (Votes.Count > 0) Then
    Points = Votes.Sum(Function(v) v.Points)
End If
user_11345550
Related