Query: Related tables sometimes result in incorrect type of join

[arman-g]

Steps to reproduce

Using EF and LINQ to query two tables that are related 1 to 1 or non fails since the sql that is generated by EF is invalid. The UserId is set as PK for both tables "User" and "Suspended". The relation is on UserId field (FK_Suspended_User). Here is a link to the complete description in stack overflow.

Here is the code:

 var user = await _db.User
           .Include(x => x.Role)
           .Include(x => x.Suspended)
           .Where(x => x.Email == username & x.Suspended == null)
           .SingleOrDefaultAsync();

The output in sql is:

SELECT [x].[UserId], [x].[Email], [x].[FirstName], [x].[LastName], [x].[Password], [x].[Phone], [x].[RCD], [x].[RoleId], [x].[Salt], [x.Suspended].[UserId], [x.Suspended].[RCD], [x.Suspended].[Reason], [s].[UserId], [s].[RCD], [s].[Reason], [r].[RoleId], [r].[Name]
FROM [User] AS [x]
INNER JOIN [Suspended] AS [x.Suspended] ON [x].[UserId] = [x.Suspended].[UserId]
LEFT JOIN [Suspended] AS [s] ON [s].[UserId] = [x].[UserId]
INNER JOIN [Role] AS [r] ON [x].[RoleId] = [r].[RoleId]

The issue

As you can see the "Suspended" table is joined twice INNER and LEFT. As a result the record is not selected if x.Suspended is null. Also there is no WHERE clause.

Further technical details

"Microsoft.EntityFrameworkCore.Tools": "1.0.0-preview2-final"
"Microsoft.EntityFrameworkCore.SqlServer.Design": "1.0.1"
Operating system: Windows 10
Visual Studio version: VS 2015

Project setup: Asp.Net Core Web API

[arman-g]

Further Explanation

If we use the code below than the sql output is what we expect.

var user = await _db.User
        .Include(x => x.Role)
        .Include(x => x.Suspended)
        .Where(x => x.Email == username)
        .SingleOrDefaultAsync();

SQL Output

exec sp_executesql N'SELECT TOP(2) [x].[UserId], [x].[Email], [x].[FirstName], [x].[LastName], [x].[Password], [x].[Phone], [x].[RCD], [x].[RoleId], [x].[Salt], [s].[UserId], [s].[RCD], [s].[Reason], [r].[RoleId], [r].[Name]
FROM [User] AS [x]
LEFT JOIN [Suspended] AS [s] ON [s].[UserId] = [x].[UserId]
INNER JOIN [Role] AS [r] ON [x].[RoleId] = [r].[RoleId]
WHERE [x].[Email] = @__username_0',N'@__username_0 varchar(80)',@__username_0='user-email'

Note that the Suspended table is correctly added to the statement as a LEFT JOIN.
Now consider the example below, it just adds a select with a reference to Suspended table in a select section to select a few columns only:

var user = await _db.User
         .Include(x => x.Role)
         .Include(x => x.Suspended)
         .Where(x => x.Email == username)
         .Select(x => new
         {
               x.UserId,
               x.Password,
               x.Salt,
               Role = x.Role.Name,
               x.Suspended
        })
        .SingleOrDefaultAsync();

SQL Output

exec sp_executesql N'SELECT TOP(2) [x.Suspended].[UserId], [x.Suspended].[RCD], [x.Suspended].[Reason], [x].[UserId], [x].[Password], [x].[Salt], [x.Role].[Name]
FROM [User] AS [x]
INNER JOIN [Suspended] AS [x.Suspended] ON [x].[UserId] = [x.Suspended].[UserId]
INNER JOIN [Role] AS [x.Role] ON [x].[RoleId] = [x.Role].[RoleId]
WHERE [x].[Email] = @__username_0',N'@__username_0 varchar(80)',@__username_0='user-email'

What we see here is that the Suspended table is added to the sql as INNER JOIN now. And if Suspended table has no record for the user the statement will not return record.

Conclusion

Referencing a related table (in a where clause or select) changes the LEFT JOIN to INNER JOIN and results no record if the referenced table has no record related to the parent table. This really kills the minimal approach.

[maumar]

This works in the current bits. We produce the following sql:

SELECT TOP(2) [x].[Id], [x].[Email], [x.Role].[Id], [x.Role].[UserId], [x.Suspended].[Id], [x.Suspended].[UserId]
FROM [Users] AS [x]
LEFT JOIN [Role] AS [x.Role] ON [x].[Id] = [x.Role].[UserId]
LEFT JOIN [Suspended] AS [x.Suspended] ON [x].[Id] = [x.Suspended].[UserId]
WHERE ([x].[Email] = @__username_0) AND [x.Suspended].[Id] IS NULL