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



Question:

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!


Solution: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.


Solution: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()                                     };  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »