How can I make Sum() return 0 instead of 'null'?

Question

I'm trying to use LINQ-to-entities to query my DB, where I have 3 tables: Room, Conference, and Participant. Each room has many conferences, and each conference has many participants. For each room, I'm trying to get a count of its conferences, and a sum of all of the participants for all of the room's conferences. Here's my query:

var roomsData = context.Rooms
    .GroupJoin(
        context.Conferences
            .GroupJoin(
                context.Participants,
                conf => conf.Id,
                part => part.ConferenceId,
                (conf, parts) => new { Conference = conf, ParticipantCount = parts.Count() }
            ),
        rm => rm.Id,
        data => data.Conference.RoomId,
        (rm, confData) => new {
            Room = rm,
            ConferenceCount = confData.Count(),
            ParticipantCount = confData.Sum(cd => cd.ParticipantCount)
        }
    );

When I try and turn this into a list, I get the error:

The cast to value type 'System.Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

I can fix this by changing the Sum line to:

ParticipantCount = confData.Count() == 0 ? 0 : confData.Sum(cd => cd.ParticipantCount)

But the trouble is that this seems to generate a more complex query and add 100ms onto the query time. Is there a better way for me to tell EF that when it is summing ParticipantCount, an empty list for confData should just mean zero, rather than throwing an exception? The annoying thing is that this error only happens with EF; if I create an empty in-memory List<int> and call Sum() on that, it gives me zero, rather than throwing an exception!

Answer 1

I made it work by changing the Sum line to:

ParticipantCount = (int?)confData.Sum(cd => cd.ParticipantCount)

Confusingly, it seems that even though IntelliSense tells me that the int overload for Sum() is getting used, at runtime it is actually using the int? overload because the confData list might be empty. If I explicitly tell it the return type is int? it returns null for the empty list entries, and I can later null-coalesce the nulls to zero.