Query: .Where() ignored before .DefaultIfEmpty() (LEFT JOIN)

[helloserve]

Steps to reproduce

The following LINQ Query (to SQL) does not generate the correct SQL statement:

from p in Projects
join ps in ProjectSprints on p.Id equals ps.ProjectId into psG
from psLJ in psG.DefaultIfEmpty()
join psu in ProjectSprintUsers on psLJ.Id equals psu.ProjectSprintId into psuG
from psuLJ in psuG.Where(x => x.UserId == 2).DefaultIfEmpty()
join u in (from u in Users select u) on (psuLJ == null ? p.ProjectManagerUserId : psuLJ.UserId) equals u.Id
where (p.ProjectManagerUserId == 2 && psuLJ == null) || psuLJ != null
select new { Project = p, ProjectSprint = psLJ, ProjectSprintUser = psuLJ, User = u }

The issue

Specifically, the .Where(x => x.UserId == 2) condition is not applied to the LEFT JOIN result in the generated SQL statement, and causes incorrect results to be returned.

Expected behavior (from LINQPad 5)

-- Region Parameters
DECLARE @p0 Int = 2
DECLARE @p1 Int = 2
-- EndRegion
SELECT [t0].[Id], [t0].[TeamId], [t0].[ClientId], [t0].[ProjectTypeId], [t0].[Name], [t0].[IsActive], [t0].[StartDate], [t0].[EndDate], [t0].[ProjectManagerUserId], [t2].[test], [t2].[Id] AS [Id2], [t2].[ProjectId], [t2].[StartDate] AS [StartDate2], [t2].[EndDate] AS [EndDate2], [t2].[RetroFrom], [t2].[RetroTo], [t4].[test] AS [test2], [t4].[Id] AS [Id3], [t4].[ProjectSprintId], [t4].[UserId], [t4].[SprintRoleId], [t5].[Id] AS [Id4], [t5].[Username], [t5].[Email], [t5].[Name] AS [Name2], [t5].[IsActive] AS [IsActive2], [t5].[Password], [t5].[ShouldChangePassword]
FROM [Project] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[Id], [t1].[ProjectId], [t1].[StartDate], [t1].[EndDate], [t1].[RetroFrom], [t1].[RetroTo]
    FROM [ProjectSprint] AS [t1]
    ) AS [t2] ON [t0].[Id] = [t2].[ProjectId]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t3].[Id], [t3].[ProjectSprintId], [t3].[UserId], [t3].[SprintRoleId]
    FROM [ProjectSprintUser] AS [t3]
    ) AS [t4] ON ([t4].[UserId] = @p0) AND ([t2].[Id] = [t4].[ProjectSprintId])
INNER JOIN [User] AS [t5] ON (
    (CASE 
        WHEN [t4].[test] IS NULL THEN [t0].[ProjectManagerUserId]
        ELSE [t4].[UserId]
     END)) = ([t5].[Id])
WHERE (([t0].[ProjectManagerUserId] = @p1) AND ([t4].[test] IS NULL)) OR ([t4].[test] IS NOT NULL)

Incorrect Observed behavior (current version 1.1.0) (from SQL Profiler)

Currently observed runtime behavior generates the following SQL:

exec sp_executesql N'SELECT [p].[Id], [p].[EndDate], [p].[IsActive], [p].[Name], [p].[ProjectManagerUserId], [p].[ProjectTypeId], [p].[StartDate], [p].[TeamId], [ps].[Id], [ps].[EndDate], [ps].[ProjectId], [ps].[RetroFrom], [ps].[RetroTo], [ps].[StartDate], [psu].[Id], [psu].[ProjectSprintId], [psu].[SprintRoleId], [psu].[UserId], [u].[Id], [u].[Email], [u].[Name], [u].[Password], [u].[ShouldChangePassword], [u].[Username]
FROM [Project] AS [p]
LEFT JOIN [ProjectSprint] AS [ps] ON [p].[Id] = [ps].[ProjectId]
LEFT JOIN [ProjectSprintUser] AS [psu] ON [ps].[Id] = [psu].[ProjectSprintId]
INNER JOIN [User] AS [u] ON CASE
    WHEN [psu].[Id] IS NULL
    THEN [p].[ProjectManagerUserId] ELSE [psu].[UserId]
END = [u].[Id]
WHERE ((([p].[ProjectManagerUserId] = @__userId_1) AND [p].[ProjectManagerUserId] IS NOT NULL) AND [psu].[Id] IS NULL) OR [psu].[Id] IS NOT NULL
ORDER BY [p].[Id], [ps].[Id]',N'@__userId_1 int',@__userId_1=2

It is clear that the condition on the LEFT JOIN is omitted in this query, and instead I expected to see
LEFT JOIN [ProjectSprintUser] AS [psu] ON [psu].UserId = 2 AND [ps].[Id] = [psu].[ProjectSprintId]

Previous behavior (version 1.0.1) (from SQL Profiler)

Previously this generated three different queries, and then presumably combines the result internally:

SELECT [p].[Id], [p].[EndDate], [p].[IsActive], [p].[Name], [p].[ProjectManagerUserId], [p].[ProjectTypeId], [p].[StartDate], [p].[TeamId], [ps].[Id], [ps].[EndDate], [ps].[ProjectId], [ps].[RetroFrom], [ps].[RetroTo], [ps].[StartDate]
FROM [Project] AS [p]
LEFT JOIN [ProjectSprint] AS [ps] ON [p].[Id] = [ps].[ProjectId]
ORDER BY [p].[Id]

SELECT [psu].[Id], [psu].[ProjectSprintId], [psu].[SprintRoleId], [psu].[UserId]
FROM [ProjectSprintUser] AS [psu]

SELECT [u].[Id], [u].[Email], [u].[Name], [u].[Password], [u].[ShouldChangePassword], [u].[Username]
FROM [User] AS [u]

Further technical details

EF Core version: (1.1.0)
Operating system: Windows 10 / Windows Server 2016
Visual Studio version: (VS 2015 Update 3, .NET Core Runtime 1.1.0)

[tuespetre]

@maumar this should be fixed in #7845

[maumar]

This scenario indeed works on the current bits. We produce the following SQL (give or take, the model might not be exactly the same, as I reverse engineered it from the query):

SELECT [p].[Id], [p].[ProjectManagerUserId], [ps].[Id], [ps].[ProjectId], [t].[Id], [t].[ProjectSprintId], [t].[UserId], [u].[Id]
FROM [Projects] AS [p]
LEFT JOIN [ProjectSprints] AS [ps] ON [p].[Id] = [ps].[ProjectId]
LEFT JOIN (
	SELECT [psu].[Id], [psu].[ProjectSprintId], [psu].[UserId]
	FROM [ProjectSprintUsers] AS [psu]
	WHERE [psu].[UserId] = 2
) AS [t] ON [ps].[Id] = [t].[ProjectSprintId]
INNER JOIN [Users] AS [u] ON CASE
	WHEN [t].[Id] IS NULL
	THEN [p].[ProjectManagerUserId] ELSE [t].[UserId]
END = [u].[Id]
WHERE (([p].[ProjectManagerUserId] = 2) AND [t].[Id] IS NULL) OR [t].[Id] IS NOT NULL