Entity Framework error using DefaultIfEmpty()

Question

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 DefaultIfEmpty 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());
}

Gert Arnold · Accepted Answer · 2016-11-18T20:24:28.740

0

Change it into two Any calls:

return offer => offer.Restrictions
    .Any(rest
        => 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.

edited Nov 18 '16 at 20:24

answered Nov 18 '16 at 19:32

Gert Arnold

105,341
31
202
291

The solution you are saying has a problem, because I need the query to generate two `LEFT OUTER JOIN`s and if I do that the query will generate an `INNER JOIN` and then a `LEFT OUTER JOIN`. – tobi Nov 18 '16 at 19:48
I tried to explain that the first outer join doesn't add anything. – Gert Arnold Nov 18 '16 at 19:49
The thing is, I need both to be `LEFT OUTER JOIN`, because if the criteria for the first filter returns null, then no matter the value of the criteria of the other filter, the query will return null also. – tobi Nov 18 '16 at 20:04
It can be done by two `Any` calls, please see the edit. – Gert Arnold Nov 18 '16 at 20:24
I've found another solution, using `if` s to determine the value of the criteria for each filter and returning a different query depending on that. However the solution you posted also works, the only issues there would be that that would affect the performance, and that it wouldn't work on an Oracle 11 database. – tobi Nov 18 '16 at 21:00
Yes, that's another issue I didn't want to address (yet). The 2 `Any`s are still applicable, also with the conditionally built queries. It's a bit more concise than `SelectMany`. – Gert Arnold Nov 18 '16 at 21:04

Entity Framework error using DefaultIfEmpty()

1 Answers1