{"action":"closed","issue":{"url":"https://api.github.com/repos/aspnet/EntityFramework/issues/3913","repository_url":"https://api.github.com/repos/aspnet/EntityFramework","labels_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/3913/labels{/name}","comments_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/3913/comments","events_url":"https://api.github.com/repos/aspnet/EntityFramework/issues/3913/events","html_url":"https://github.com/aspnet/EntityFramework/issues/3913","id":119255732,"number":3913,"title":".Sum()+.Count() inside .Select can throw exception because .Sum() can be null","user":{"login":"DavidKarlas","id":774791,"avatar_url":"https://avatars.githubusercontent.com/u/774791?v=3","gravatar_id":"","url":"https://api.github.com/users/DavidKarlas","html_url":"https://github.com/DavidKarlas","followers_url":"https://api.github.com/users/DavidKarlas/followers","following_url":"https://api.github.com/users/DavidKarlas/following{/other_user}","gists_url":"https://api.github.com/users/DavidKarlas/gists{/gist_id}","starred_url":"https://api.github.com/users/DavidKarlas/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DavidKarlas/subscriptions","organizations_url":"https://api.github.com/users/DavidKarlas/orgs","repos_url":"https://api.github.com/users/DavidKarlas/repos","events_url":"https://api.github.com/users/DavidKarlas/events{/privacy}","received_events_url":"https://api.github.com/users/DavidKarlas/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/aspnet/EntityFramework/labels/bug","name":"bug","color":"f7c6c7"},{"url":"https://api.github.com/repos/aspnet/EntityFramework/labels/pri0","name":"pri0","color":"e11d21"}],"state":"closed","locked":false,"assignee":{"login":"mikary","id":6787717,"avatar_url":"https://avatars.githubusercontent.com/u/6787717?v=3","gravatar_id":"","url":"https://api.github.com/users/mikary","html_url":"https://github.com/mikary","followers_url":"https://api.github.com/users/mikary/followers","following_url":"https://api.github.com/users/mikary/following{/other_user}","gists_url":"https://api.github.com/users/mikary/gists{/gist_id}","starred_url":"https://api.github.com/users/mikary/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mikary/subscriptions","organizations_url":"https://api.github.com/users/mikary/orgs","repos_url":"https://api.github.com/users/mikary/repos","events_url":"https://api.github.com/users/mikary/events{/privacy}","received_events_url":"https://api.github.com/users/mikary/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/aspnet/EntityFramework/milestones/7","html_url":"https://github.com/aspnet/EntityFramework/milestones/1.0.0","labels_url":"https://api.github.com/repos/aspnet/EntityFramework/milestones/7/labels","id":666128,"number":7,"title":"1.0.0","description":"","creator":{"login":"rowanmiller","id":4741851,"avatar_url":"https://avatars.githubusercontent.com/u/4741851?v=3","gravatar_id":"","url":"https://api.github.com/users/rowanmiller","html_url":"https://github.com/rowanmiller","followers_url":"https://api.github.com/users/rowanmiller/followers","following_url":"https://api.github.com/users/rowanmiller/following{/other_user}","gists_url":"https://api.github.com/users/rowanmiller/gists{/gist_id}","starred_url":"https://api.github.com/users/rowanmiller/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rowanmiller/subscriptions","organizations_url":"https://api.github.com/users/rowanmiller/orgs","repos_url":"https://api.github.com/users/rowanmiller/repos","events_url":"https://api.github.com/users/rowanmiller/events{/privacy}","received_events_url":"https://api.github.com/users/rowanmiller/received_events","type":"User","site_admin":false},"open_issues":99,"closed_issues":182,"state":"open","created_at":"2014-05-21T19:25:32Z","updated_at":"2016-05-17T22:38:38Z","due_on":null,"closed_at":null},"comments":1,"created_at":"2015-11-27T22:53:20Z","updated_at":"2016-05-17T22:38:38Z","closed_at":"2016-05-17T22:38:38Z","body":"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...\r\n\r\nI want to display events to volunteer and also number of registered volunteers per event...\r\nNumber 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...\r\n```\r\n        //\r\n        // GET: /Volunteer/Index\r\n        [HttpGet]\r\n        public IActionResult Index()\r\n        {\r\n            var model = new IndexViewModel();\r\n            model.Events.AddRange(dbContext.Events\r\n                .Where(e => e.StartTime > DateTime.Now)\r\n                .Include(e => e.Location)\r\n                .Include(e => e.Volunteers)\r\n                .Select(e => new IndexViewModel.EventIndexViewModel()\r\n                {\r\n                    Id = e.Id,\r\n                    Name = e.Name,\r\n                    LocationName = e.Location.Name,\r\n                    StartTime = e.StartTime,\r\n                    EndTime = e.EndTime,\r\n                    TotalVolunteersNeeded = e.TotalVolunteersNeeded,\r\n                    CurrentVolunteers = e.Volunteers.Sum(v => v.ExtraVolunteers)  + e.Volunteers.Count()\r\n                }));\r\n            return View(model);\r\n        }\r\n```\r\nThis produces this SQL:\r\n```\r\nSELECT [e].[Id], [e].[Name], [e.Location].[Name], [e].[StartTime], [e].[EndTime], [e].[TotalVolunteersNeeded], (\r\nSELECT SUM([v].[ExtraVolunteers])\r\nFROM [DbUserAtEvent] AS [v]\r\nWHERE [e].[Id] = [v].[EventId]\r\n) + (\r\nSELECT COUNT(*)\r\nFROM [DbUserAtEvent] AS [d]\r\nWHERE [e].[Id] = [d].[EventId]\r\n)\r\nFROM [DbEvent] AS [e]\r\nINNER JOIN [Location] AS [e.Location] ON [e].[LocationId] = [e.Location].[Id]\r\nWHERE [e].[StartTime] > GETDATE()\r\n```\r\n\r\nAnd throws this exception in aspnet console:\r\n```\r\nfail: Microsoft.Data.Entity.Query.Internal.SqlServerQueryCompilationContextFactory[1]\r\n      An exception occurred in the database while iterating the results of a query.\r\n      System.NullReferenceException: Object reference not set to an instance of an object.\r\n         at lambda_method(Closure , TransparentIdentifier`2 )\r\n         at System.Linq.Enumerable.<>c__DisplayClass2`3.<CombineSelectors>b__3(TSource x)\r\n         at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()\r\n         at Microsoft.Data.Entity.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()\r\n```\r\nAnd website says:\r\n![image](https://cloud.githubusercontent.com/assets/774791/11448816/654623d0-9561-11e5-9965-ee1b6d9490ad.png)\r\n\r\nWorkaround is to change line into:\r\n```csharp\r\nCurrentVolunteers = (int?)(e.Volunteers.Sum(v => v.ExtraVolunteers)  + e.Volunteers.Count()) ?? 0\r\n```\r\nWith workaround produces:\r\n```\r\nSELECT [e].[Id], [e].[Name], [e.Location].[Name], [e].[StartTime], [e].[EndTime], [e].[TotalVolunteersNeeded], COALESCE((\r\n          SELECT SUM([v].[ExtraVolunteers])\r\n          FROM [DbUserAtEvent] AS [v]\r\n          WHERE [e].[Id] = [v].[EventId]\r\n      ) + (\r\n          SELECT COUNT(*)\r\n          FROM [DbUserAtEvent] AS [d]\r\n          WHERE [e].[Id] = [d].[EventId]\r\n      ), 0) AS [Coalesce]\r\n      FROM [DbEvent] AS [e]\r\n      INNER JOIN [Location] AS [e.Location] ON [e].[LocationId] = [e.Location].[Id]\r\n      WHERE [e].[StartTime] > GETDATE()\r\n```\r\n\r\nModels:\r\n```csharp\r\n    public class DbUser : IdentityUser\r\n    {\r\n        public string FirstName { get; set; }\r\n        public string LastName { get; set; }\r\n        public string City { get; set; }\r\n        public string MobilePhone { get; set; }\r\n        public ICollection<DbEvent> CreatedEvents { get; set; }\r\n        public ICollection<DbUserAtEvent> Events { get; set; }\r\n    }\r\n\r\n    public class DbEvent\r\n    {\r\n        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]\r\n        [Key]\r\n        public int Id { get; set; }\r\n        public DbUser CreatedBy { get; set; }\r\n        public string Name { get; set; }\r\n        public string Description { get; set; }\r\n        public Location Location { get; set; }\r\n        public DateTime StartTime { get; set; }\r\n        public DateTime EndTime { get; set; }\r\n        public int TotalVolunteersNeeded { get; set; }\r\n        public ICollection<DbUserAtEvent> Volunteers { get; set; }\r\n    }\r\n\r\n    public class DbUserAtEvent\r\n    {\r\n        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]\r\n        [Key]\r\n        public int Id { get; set; }\r\n\r\n        public string UserId { get; set; }\r\n        public DbUser User { get; set; }\r\n\r\n        public int EventId { get; set; }\r\n        public DbEvent Event { get; set; }\r\n\r\n        public int ExtraVolunteers { get; set; }\r\n\r\n        public bool NeedsRide { get; set; }\r\n        public int EmptySeats { get; set; }\r\n    }\r\n```\r\n\r\nExtra interesting finding with this code:\r\n```\r\nCurrentVolunteers = e.Volunteers.Sum(v => (int?)v.ExtraVolunteers) ?? 0 + e.Volunteers.Count()\r\n```\r\nIt produces this SQL:\r\n```\r\nSELECT [e].[Id], [e].[Name], [e.Location].[Name], [e].[StartTime], [e].[EndTime], [e].[TotalVolunteersNeeded], COALESCE((\r\n          SELECT SUM([v].[ExtraVolunteers])\r\n          FROM [DbUserAtEvent] AS [v]\r\n          WHERE [e].[Id] = [v].[EventId]\r\n      ), 0 + (\r\n          SELECT COUNT(*)\r\n          FROM [DbUserAtEvent] AS [d]\r\n          WHERE [e].[Id] = [d].[EventId]\r\n      )) AS [Coalesce]\r\n      FROM [DbEvent] AS [e]\r\n      INNER JOIN [Location] AS [e.Location] ON [e].[LocationId] = [e.Location].[Id]\r\n      WHERE [e].[StartTime] > GETDATE()\r\n```\r\nLook at placing of \")\" for COALESCE, it's after COUNT(*) not before... Feels awkward, but correct(at least in this case) :S"}}