.Sum()+.Count() inside .Select can throw exception because .Sum() can be null

[DavidKarlas]

Databases/EF is not my home... I will throw bunch of code and theory and you guys will probably know better if this is bug or not...

I want to display events to volunteer and also number of registered volunteers per event...
Number of volunteers is Count of all Volunteers(Foreign key ManyToMany) + Each volunteer can add few extra volunteers who don't have account on website... ExtraVolunteers...

        //
        // GET: /Volunteer/Index
        [HttpGet]
        public IActionResult Index()
        {
            var model = new IndexViewModel();
            model.Events.AddRange(dbContext.Events
                .Where(e => e.StartTime > DateTime.Now)
                .Include(e => e.Location)
                .Include(e => e.Volunteers)
                .Select(e => new IndexViewModel.EventIndexViewModel()
                {
                    Id = e.Id,
                    Name = e.Name,
                    LocationName = e.Location.Name,
                    StartTime = e.StartTime,
                    EndTime = e.EndTime,
                    TotalVolunteersNeeded = e.TotalVolunteersNeeded,
                    CurrentVolunteers = e.Volunteers.Sum(v => v.ExtraVolunteers)  + e.Volunteers.Count()
                }));
            return View(model);
        }

This produces this SQL:

SELECT [e].[Id], [e].[Name], [e.Location].[Name], [e].[StartTime], [e].[EndTime], [e].[TotalVolunteersNeeded], (
SELECT SUM([v].[ExtraVolunteers])
FROM [DbUserAtEvent] AS [v]
WHERE [e].[Id] = [v].[EventId]
) + (
SELECT COUNT(*)
FROM [DbUserAtEvent] AS [d]
WHERE [e].[Id] = [d].[EventId]
)
FROM [DbEvent] AS [e]
INNER JOIN [Location] AS [e.Location] ON [e].[LocationId] = [e.Location].[Id]
WHERE [e].[StartTime] > GETDATE()

And throws this exception in aspnet console:

fail: Microsoft.Data.Entity.Query.Internal.SqlServerQueryCompilationContextFactory[1]
      An exception occurred in the database while iterating the results of a query.
      System.NullReferenceException: Object reference not set to an instance of an object.
         at lambda_method(Closure , TransparentIdentifier`2 )
         at System.Linq.Enumerable.<>c__DisplayClass2`3.<CombineSelectors>b__3(TSource x)
         at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
         at Microsoft.Data.Entity.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()

And website says:

Workaround is to change line into:

CurrentVolunteers = (int?)(e.Volunteers.Sum(v => v.ExtraVolunteers)  + e.Volunteers.Count()) ?? 0

With workaround produces:

SELECT [e].[Id], [e].[Name], [e.Location].[Name], [e].[StartTime], [e].[EndTime], [e].[TotalVolunteersNeeded], COALESCE((
          SELECT SUM([v].[ExtraVolunteers])
          FROM [DbUserAtEvent] AS [v]
          WHERE [e].[Id] = [v].[EventId]
      ) + (
          SELECT COUNT(*)
          FROM [DbUserAtEvent] AS [d]
          WHERE [e].[Id] = [d].[EventId]
      ), 0) AS [Coalesce]
      FROM [DbEvent] AS [e]
      INNER JOIN [Location] AS [e.Location] ON [e].[LocationId] = [e.Location].[Id]
      WHERE [e].[StartTime] > GETDATE()

Models:

    public class DbUser : IdentityUser
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string City { get; set; }
        public string MobilePhone { get; set; }
        public ICollection<DbEvent> CreatedEvents { get; set; }
        public ICollection<DbUserAtEvent> Events { get; set; }
    }

    public class DbEvent
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Key]
        public int Id { get; set; }
        public DbUser CreatedBy { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public Location Location { get; set; }
        public DateTime StartTime { get; set; }
        public DateTime EndTime { get; set; }
        public int TotalVolunteersNeeded { get; set; }
        public ICollection<DbUserAtEvent> Volunteers { get; set; }
    }

    public class DbUserAtEvent
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Key]
        public int Id { get; set; }

        public string UserId { get; set; }
        public DbUser User { get; set; }

        public int EventId { get; set; }
        public DbEvent Event { get; set; }

        public int ExtraVolunteers { get; set; }

        public bool NeedsRide { get; set; }
        public int EmptySeats { get; set; }
    }

Extra interesting finding with this code:

CurrentVolunteers = e.Volunteers.Sum(v => (int?)v.ExtraVolunteers) ?? 0 + e.Volunteers.Count()

It produces this SQL:

SELECT [e].[Id], [e].[Name], [e.Location].[Name], [e].[StartTime], [e].[EndTime], [e].[TotalVolunteersNeeded], COALESCE((
          SELECT SUM([v].[ExtraVolunteers])
          FROM [DbUserAtEvent] AS [v]
          WHERE [e].[Id] = [v].[EventId]
      ), 0 + (
          SELECT COUNT(*)
          FROM [DbUserAtEvent] AS [d]
          WHERE [e].[Id] = [d].[EventId]
      )) AS [Coalesce]
      FROM [DbEvent] AS [e]
      INNER JOIN [Location] AS [e.Location] ON [e].[LocationId] = [e.Location].[Id]
      WHERE [e].[StartTime] > GETDATE()

Look at placing of ")" for COALESCE, it's after COUNT(*) not before... Feels awkward, but correct(at least in this case) :S

[mikary]

Verified that the issue does not reproduce in current dev and added test coverage to prevent regressions