Problem with LINQ to Entities query using sum for child property

Question

Given this request:

from s in services select new { s.Id, s.DateTime, Class = s.Class.Name, s.Location, s.Price, HeadCount = s.Reservations.Sum(r => r.PartySize), // problem here. r.PartySize is int s.MaxSeats } 

If the service does not have any reservations, this is an exception:

System.InvalidOperationException: cast value for 'Int32' value failed because the materialized value is null. Either the general parameter of the result type, or the query should use a type with a zero value.

I understand, but how can I deal with this? My intention is that there are no reservations, then HeadCount will be assigned 0.

Answer 1

You should check it out:

 HeadCount = s.Reservations != null ? s.Reservations.Sum(r => r.PartySize) : 0, 

Answer 2

There is an even simpler solution:

 from s in services select new { s.Id, s.DateTime, Class = s.Class.Name, s.Location, s.Price, HeadCount = (int?)s.Reservations.Sum(r => r.PartySize), s.MaxSeats } 

Pay attention to the cast. It can also lead to simpler SQL than @Ahmad's suggestion.

Essentially, you are just helping type inferences.

Answer 3

This should solve your problem: Try spending an int on an int?

 from s in services select new { s.Id, s.DateTime, Class = s.Class.Name, s.Location, s.Price, HeadCount = s.Reservations.Sum(r => (int?) r.PartySize), s.MaxSeats }; HeadCount = HeadCount ?? 0; 

Answer 4

A simple ternary operator should fix the problem well ...

something like that:

 HeadCount = (s.Reservations != null && s.Reservations.Any()) ? s.Reservations.Sum(r => r.PartySize) : 0; 

This will handle both null and empty situations