CODEWITHSUNDEEP
c#.netlinqentity-frameworkorm
Ronnie Overby
Ronnie Overby

Reputation: 46490

Problem with LINQ to Entities query using Sum on child object property

Given this query:

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 doesn't have any reservations, this exception is thrown:

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

I get it, but how should I deal with it? My intention is if there are no reservations, then HeadCount be assigned 0.

Upvotes: 7

Views: 10115

Answers (4)

amiry jd
amiry jd

Reputation: 27605

This should resolve your problem: Try to cost the int to 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;

Upvotes: 2

Justin Williams
Justin Williams

Reputation: 697

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

something like this:

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

This will handle for both null and empty situations

Upvotes: 1

Craig Stuntz
Craig Stuntz

Reputation: 126587

There's 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
}

Note the cast. This may also produce simpler SQL than @Ahmad's suggestion.

Essentially, you're just helping out type inference.

Upvotes: 11

Ahmad Mageed
Ahmad Mageed

Reputation: 96557

You should check for it:

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

Upvotes: 7

Related Questions