tobi tobi - 1 year ago 124
C# Question

Entity Framework error using DefaultIfEmpty()

I have a problem with Entity Framework using the DefaultIfEmpty method. The following query is returning empty when it should return an offer that matches all criteria in the database.

If I remove one or both

method calls it works, but with them it doesn't. I need those to prevend another problem in the query.

When I execute the generated SQL query directly on the database it works and it returns the offer.

I also made an Unit Test reproducing the same example and it also passes so it must be an Entity Framework issue.

Here's the query:

private static Expression<Func<Offer, bool>> AddFilter(Service criteria)
return offer => offer.Restrictions.

SelectMany(rest => rest.OperatorRange.DefaultIfEmpty(), (rest, alop) => new { Restriction = rest, OperatorRange = alop.Id }).
Where(alop => criteria.ServiceUseNet == null || alop.OperatorRange.ToUpper() == criteria.ServiceUseNet.ToUpper()).

SelectMany(rest => rest.Restriction.CallType.DefaultIfEmpty(), (rest, till) => new { Restriction = rest, CallType = till.Id }).
Any(till => criteria.UseServiceCoverage == null || till.CallType.ToUpper() == criteria.UseServiceCoverage.ToUpper());

Answer Source

Change it into two Any calls:

return offer => offer.Restrictions
        => rest.OperatorRange
                .Where(alop => criteria.ServiceUseNet == null
                             || alop.OperatorRange.ToUpper() == criteria.ServiceUseNet.ToUpper())
        .Any(till => criteria.UseServiceCoverage == null
                  || till.CallType.ToUpper() == criteria.UseServiceCoverage.ToUpper()));

The predicate is supposed to test whether there are any OperatorRanges (meeting some criteria) having any CallTypes meeting some criteria. If there are no OperatorRanges, there won't be any CallTypes either, let alone matching CallTypes.

In this form, the predicate always returns true or false.