Query: Joined query isn't correctly translated

[ghost]

My query is:

from t1 in Table1.Where(r => r.Id == 123)
from t2 in Table2.Where(t => t.MyField == t1.MyField).DefaultIsEmpty()
select new
{
    Id = t1.Id
}

which EF Core translates with this non-working sql

SELECT [t].[Id], [t].[Myfield]
FROM [Table1] AS [t]
go

SELECT 1
FROM (
    SELECT [t1].*
    FROM (
        SELECT NULL AS [empty]
    ) AS [empty0]
    LEFT JOIN (
        SELECT [t20].*
        FROM [Table2] AS [t20]
        WHERE [t20].[MyField] = [t].[MyField]
    ) AS [t1] ON 1 = 1
) AS [t3]
go

where [t].[MyField] doesn't exist in the WHERE clause of the second SELECT

EF6 translate the same query with this:

SELECT [t0].[Id] AS [Id]
FROM [Table1] AS [t0]
LEFT OUTER JOIN [Table2] AS [t1] ON [t1].[Myfield] = [t0].[Myfield]
WHERE [t0].[Id] = @p0

Further technical details

EF Core version: 1.1.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 & LinqPad

[maumar]

@fgiomi EF Core currently doesn't recognize this pattern as LEFT OUTER JOIN. However you can do it using GroupJoin-SelectMany-DefaultIfEmpty pattern like so:

                var query = from t1 in ctx.Table1.Where(r => r.Id == 123)
                            join t2 in ctx.Table2 on t1.MyField equals t2.MyField into grouping
                            from t2 in grouping.DefaultIfEmpty()
                            select new
                            {
                                Id = t1.Id
                            };

this produces the following sql:

SELECT [r].[Id]
FROM [Table1] AS [r]
LEFT JOIN [Table2] AS [t2] ON [r].[MyField] = [t2].[MyField]
WHERE [r].[Id] = 123

[maumar]

It's also worth pointing out that in the current dev branch the original query produces the following correct sql:

SELECT [r].[Id]
FROM [Table1] AS [r]
CROSS APPLY (
    SELECT [t0].*
    FROM (
        SELECT NULL AS [empty]
    ) AS [empty]
    LEFT JOIN (
        SELECT [t].*
        FROM [Table2] AS [t]
        WHERE [t].[MyField] = [r].[MyField]
    ) AS [t0] ON 1 = 1
) AS [t1]
WHERE [r].[Id] = 123

We could still choose to recognize the pattern and convert it to LOJ, but that's not as urgent.

[ajcvickers]

Closing this as fixed in dev. There is potential optimization here, but that is tracked by issue #7887, since it is the same root cause.