14

If I do:

int updateGamePlays = db.tblArcadeGames.Where(c => c.ParentGameID == GameID).Sum(c => c.Plays);

If no records are returned in this query it throws:

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

The only way to get it to return 0 is by doing:

int updateGamePlays = db.tblArcadeGames.Where(c => c.ParentGameID == GameID).Sum(c => (int?)c.Plays) ?? 0;

In the database c.Plays is a non-nullable int.

In set theory the sum of an empty set should equal 0 (ref). How comes in Linq-to-SQL did they decide to make it return null?

Edward Brey
  • 40,302
  • 20
  • 199
  • 253
Tom Gullen
  • 61,249
  • 84
  • 283
  • 456
  • 4
    This is more database and less set theory. What is the `SUM` of a nullable column in SQL Server? – Marc Dec 29 '11 at 03:06
  • @Marc in the question I link to none of the columns in question are nullable – Tom Gullen Dec 29 '11 at 03:06
  • 1
    Does the L2S dbml know that for this data set? I would assume that field would be an `int` and not an `int?` if so. – Marc Dec 29 '11 at 03:09
  • @Marc yes the dbml knows that it's a non nullable int field – Tom Gullen Dec 29 '11 at 03:10
  • 1
    http://msdn.microsoft.com/en-us/library/bb338442.aspx Unless I'm missing something, a Sum of `int`s is an `int`, not an `int?`. Is it known to be non nullable at compile time? – Tim S. Dec 29 '11 at 03:16
  • What is the original type of `v.Plays`? Why do you need to cast it to `int?`? – Sergey Kalinichenko Dec 29 '11 at 03:21
  • @dasblinkenlight `v.Plays` is a non nullable int. You have to cast it to `int?` to stop it throwing errors when no records are returned. Try it on a simple table you will see – Tom Gullen Dec 29 '11 at 03:22
  • Workaround: [How to do Linq aggregates when there might be an empty set?](http://stackoverflow.com/q/2455500/145173) – Edward Brey Jan 12 '14 at 00:42

3 Answers3

11

According to a source at Microsoft, Sum() on an empty set is null because of the way it works in SQL:

when the table is empty i´m getting this exception: InvalidOperationException

In SQL, Sum() aggregate operator returns null for an empty set. So this is as designed.

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • 1
    But if you use Sum on an empty IEnumerable, then it correctly returns 0. So, Microsoft chose to make Sum() on IQueryable to be consistent with SQL but inconsistent with Sum() on IEnumerable. Ambiguous choice... – JustAMartin Jun 28 '16 at 12:43
2

Another alternative is to add a 0 to the set to make sure there's always at least one value.

int updateGamePlays = db.tblArcadeGames.Where(c => c.ParentGameID == GameID)
                                       .Select(c => c.Plays)
                                       .Concat(new [] { 0 })
                                       .Sum();
jt000
  • 3,196
  • 1
  • 18
  • 36
0

You can use the more general Aggregate method with a seed of zero:

int updateGamePlays = db.tblArcadeGames
    .Where(c => c.ParentGameID == GameID)
    .Aggregate(0, (a, c) => a + c.Plays);

This does not require using nullable types.

jackvsworld
  • 1,453
  • 15
  • 17