Efcore: Query :: SelectMany-GroupJoin-DefaultIfEmpty isn't being lifted into SQL for some complex queries, resulting in extensive client-side evaluation

[maumar]

This is mostly visible in scenarios with optional navigations, e.g.

from c in ctx.Customers
where c.Detail.Name == "Foo"
select c;

this gets translated into:

from c in ctx.Customers
join d in ctx.Details on c.Id equals d.CustomerId into grouping
from d in grouping.DefaultIfEmpty()
where d.Name == "Foo"
select c;

however the filter will be performed on the client resulting in unnecessary data being pulled from the database

[sheinema]

We are having a related problem. Even a single Left Outer Join results in the following warning and hundreds of additional calls to the db:

Ex:

var q = from a in context.Activities
                    from p in context.Places.Where(x => x.PlaceId == a.PlaceId).DefaultIfEmpty()
                    select new { a, p };

...

Microsoft.Data.Entity.Query.Internal.SqlServerQueryCompilationContextFactory: Warning: The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally.

Causes hundreds of additional queries (one call to Place for each Activity): 

Microsoft.Data.Entity.Storage.Internal.RelationalCommandBuilderFactory: Information: Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [x].[PlaceId]
FROM [Place] AS [x]

*Using EF 7.0.0-rc1-final; ASP.NET 1.0.0-rc1-final

[maumar]

@sheinema
The join pattern you are using is not recognized (this is being tracked by #3263)

However, alternative way to produce LOJ works in our nightly builds (available with the next release):

var q = from a in context.Activities
            join x in context.Places on a.PlaceId equals x.PlaceId into grouping
            from x in grouping.DefaultIfEmpty()
            select new { a, x }

This should yield only one query with LOJ, however DefaultIfEmpty and everything after it will still be evaluated on the client.

[jazzonaut]

Will this be implemented in the RTM release?

[kapsiR]

Do you have any roadmap for the 1.1.0 milestone release especially for this issue? (maybe a roadmap for any patch?)

[rowanmiller]

Nothing public yet, we'll share as soon as we do. Prior to undertaking the major EF Core 1.0.0 we were releasing stable updates every 3-4 months (with previews in between). I expect our cadence to be similar.