Null value in the result of a left outer join linq causes error

I have linq query, that left outer join two tables. I found if a value of a field returns null,, then I will get an error message:

"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 copied my linq below:

var SrvRef = from s in db.SrvHeads
    join r in db.Referrants on s.svhReferrer equals r.refID into r_join
    from r in r_join.DefaultIfEmpty()
    where s.svhAccID == accId &&
    s.svhHeadCnt == HeadId
    select new
    {
    s.svhBalance,
    r.refID
    };
bool FBeenPaid = SrvRef.FirstOrDefault().svhBalance == 0M; //this causes error

How can I fix this problem?

Jon Skeet
people
quotationmark

I'm slightly surprised at the kind of error you're getting, but there are two places you need to take account of the possibility of the result being null:

  • Within the query, where r can be null. (If you don't want to match when there are no elements in r_join matching s, you shouldn't be using a left outer join)
  • In the result itself: you're using FirstOrDefault() which will return null if SrvRef is empty.

So at first glance it should probably be something like:

var query = from s in db.SrvHeads
    join r in db.Referrants on s.svhReferrer equals r.refID into r_join
    from r in r_join.DefaultIfEmpty()
    where s.svhAccID == accId && s.svhHeadCnt == HeadId
    select new
    {
        s.svhBalance,
        refId = r == null ? 0 : r.refID // Adjust to the appropriate type of refID
    };
var result = query.FirstOrDefault();
bool beenPaid = result != null && result.svhBalance == 0m;

With C# 6, you can change the bottom two lines to:

bool beenPaid = query.FirstOrDefault()?.svhBalance == 0m ?? false;

Having said that:

  • You're not currently using refId in the result anyway - why are you including it in the result?
  • Are you sure you want a left outer join at all?
  • Are you sure that taking the first result is really what you want? What if there are multiple results in the join?

  • Is there any reason you're not doing the whole thing in a query? Something like:

    var paid = db.SrvHeads
             .Where(s => s.svhAccID == accId && s.svhHeadCnt == HeadId)
             .Any(s => db.Refererrants.Any(r => s.svhReferrer == r.refID
                     && s.svhBalance == 0m);

    .. but just for the precise semantics you want.

people

See more on this question at Stackoverflow