score:22

Accepted answer
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) 
}

score:0

        (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.

score:0

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

score:0

Similar to previous answers, but you can also cast the result of the entire sum to the nullable type.

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

Arguably this better fits what is really going on but it has the same effect as the cast in this answer.

score:1

Thought I would throw another solution out there. I had a similar issue and this is how I ended up solving it:

Where(a => a.ItemId == b.ItemId && !b.IsPointsNull()).Sum(b => b.Points)

score:2

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.

score:2

I think this is the same case. I resolved it. This is my solution:

var x = (from a in this.db.Pohybs
                 let sum = (from p in a.Pohybs
                            where p.PohybTyp.Vydej == true
                            select p.PocetJednotek).Sum()
                 where a.IDDil == IDDil && a.PohybTyp.Vydej == false
                 && ( ((int?)sum??0) < a.PocetJednotek)
                 select a);

I hope this help.

score:3

Assuming Points is a List of Int32, how about something like:

var sum = Points.DefaultIfEmpty().Sum(c => (Int32)c ?? 0)

score:4

Just to add another method into the mix :)

Where(q=> q.ItemId == b.ItemId && b.Points.HasValue).Sum(q=>q.Points.Value)

I had a similar scenario but I wasn't comparing an additional field when summing...

Where(q => q.FinalValue.HasValue).Sum(q=>q.FinalValue.Value);

score:5

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()
}

score:11

If you don't like casting to nullabe decimal you could also try using Linq To Objects with ToList() method,

LinqToObjects Sum of empty collection is 0, where LinqToSql Sum of empty collection is null.

score:14

Try to check this out:

var count = db.Cart.Where(c => c.UserName == "Name").Sum(c => (int?)c.Count) ?? 0;

So, the root of the problem is that SQL query like this:

SELECT SUM([Votes].[Value])
FROM [dbo].[Votes] AS [Votes]
WHERE 1 = [Votes].[UserId] 

returns NULL

score:20

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
}

score:75

You want to use the nullable form of Sum, so try casting your value to a nullable:

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(r => (decimal?) r.Points)
}

Your issue is discussed here in more detail:

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


Related Query

More Query from same tag