Why am I getting an InvalidOperationException with this Linq to Sql method?

Question

Hi all.

When Executing the following linq to sql statement:

    var stuff = from l in _db.SqlLinks
                select new
                           {
                               Link = l,
                               Rating = (from v in l.SqlLinkVotes
                                         where v.Tag == tagId
                                               && v.VoteDate >= since
                                         select v.Vote).Sum(),
                               NumberOfVotes = (from v in l.SqlLinkVotes
                                                where v.Tag == tagId
                                                      && v.VoteDate >= since
                                                select v.Vote).Count(),
                               NumberOfComments = (from v in l.SqlLinkVotes
                                                   where v.Tag == tagId
                                                         && v.VoteDate >= since
                                                         && v.Comment != ""
                                                   select v.Vote).Count()
                           };

I get a System.InvalidOperationException (null value cannot be assigned to Int32).

Through debugging I've seen that this comes from the Rating property of the dynamic object.

When there are no SqlLinkVotes for a particular link the Sum() results in a null value, but Rating is an int, and linq to sql thinks the Sum() will result in an int, not a nullable int.

I could easily write a stored procedure to get around this, but I thought it was a good way for me to understand linq to sql more.

Please help!

Answer 1

There's a Connect thread about this which suggests that you cast the result of Sum() to a nullable type (int? in your case). I suspect if you want the rating to be non-nullable, you can then use the null-coalescing operator:

Rating = ((int?) (from v in l.SqlLinkVotes
                  where v.Tag == tagId
                     && v.VoteDate >= 
                  select v.Vote).Sum()) ?? 0

It's worth a try, anyway.

Answer 2

Looks like simply casting the Rating field as a nullable int has fixed it.

Does this look sensible to other people?

var stuff = from l in _db.SqlLinks
                        select new
                                   {
                                       Link = l,
                                       Rating = (int?)(from v in l.SqlLinkVotes
                                                 where v.Tag == tagId
                                                       && v.VoteDate >= since
                                                 select v.Vote).Sum(),
                                       NumberOfVotes = (from v in l.SqlLinkVotes
                                                        where v.Tag == tagId
                                                              && v.VoteDate >= since
                                                        select v.Vote).Count(),
                                       NumberOfComments = (from v in l.SqlLinkVotes
                                                           where v.Tag == tagId
                                                                 && v.VoteDate >= since
                                                                 && v.Comment != ""
                                                           select v.Vote).Count()
                                   };