Query: Second level expand not returning correct data

[vukasinspasic]

Hello. I noticed bugs in library.

Steps to reproduce

When I try to make query on Posts DbSet with 2 expands of 2. level:

var query = dbContext.Posts.AsQueryable();
query = query
    .Include(x => x.User)
        .ThenInclude(x => x.Roles)
    .Include(x => x.VoteDefinition)
        .ThenInclude(x => x.PossibleAnswers)

Database model (some properties omitted for clarity):

    public class Post
    {
        public int Id { get; set; }
        public int UserId { get; set; }
        public User User { get; set; }
        public int? VoteDefinitionId { get; set; }
        public VoteDefinition VoteDefinition { get; set; }
    }
    public class User
    {
        public int Id { get; set; }
        public IList<Post> Posts { get; set; }
        public IList<Role> Roles { get; set; }
    }
    public class Role
    {
        public int Id { get; set; }
        public int UserId { get; set; }
    }
    public class VoteDefinition
    {
        public int Id { get; set; }
        [ForeignKey("Post")]
        public int PostId { get; set; }
        public Post Post { get; set; }
        public IList<VoteDefinitionPossibleAnswer> PossibleAnswers { get; set; }
    }
    public class VoteDefinitionPossibleAnswer
    {
        public int Id { get; set; }
        public int VoteDefinitionId { get; set; }
        public VoteDefinition VoteDefinition { get; set; }
    }

The issue

This generated bad SQL query resulting in User.Roles not expanded.

Further technical details

As you can see in generated SQL queries, in 2. SQL query, there is line
LEFT JOIN "VoteDefinitions" AS "v" ON "v"."PostId" = "f"."Id"
which should be only in 3. SQL query

SQL generated by this query:

info: Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory[1]
Executed DbCommand (55ms) [Parameters=[@__ToLower_0='?', @__p_2='?', @__p_1='?'], CommandType='Text', CommandTimeout='30']

SELECT "f"."Id", "f"."Cancelled", "f"."CategoryId", "f"."CreatedBy", "f"."CreatedByRole", "f"."CreatedOn", "f"."Draft", "f"."ModifiedBy", "f"."ModifiedByRole", "f"."ModifiedOn", "f"."RowVersion", "f"."Text", "f"."Title", "f"."Type", "f"."UserId", "f"."VoteDefinitionId", "v"."Id", "v"."Cancelled", "v"."CreatedBy", "v"."CreatedByRole", "v"."CreatedOn", "v"."ModifiedBy", "v"."ModifiedByRole", "v"."ModifiedOn", "v"."PostId", "v"."Question", "v"."RowVersion", "v"."Type", "u"."Id", "u"."Active", "u"."BirthDate", "u"."Cancelled", "u"."CreatedBy", "u"."CreatedByRole", "u"."CreatedOn", "u"."DocumentNumber", "u"."Email", "u"."FirstName", "u"."FullName", "u"."LastName", "u"."ModifiedBy", "u"."ModifiedByRole", "u"."ModifiedOn", "u"."Password", "u"."RowVersion", "p"."Id", "p"."Cancelled", "p"."CreatedBy", "p"."CreatedByRole", "p"."CreatedOn", "p"."ModifiedBy", "p"."ModifiedByRole", "p"."ModifiedOn", "p"."Name", "p"."RowVersion"
FROM "Posts" AS "f"
LEFT JOIN "VoteDefinitions" AS "v" ON "v"."PostId" = "f"."Id"
INNER JOIN "Users" AS "u" ON "f"."UserId" = "u"."Id"
LEFT JOIN "PostCategories" AS "p" ON "f"."CategoryId" = "p"."Id"
WHERE (("f"."Cancelled" = FALSE) AND LOWER("f"."Title") LIKE ((('%' || @__ToLower_0)) || '%')) AND ("f"."Draft" = FALSE)
ORDER BY "f"."Id", "v"."Id", "u"."Id"
LIMIT @__p_2 OFFSET @__p_1
info: Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory[1]
Executed DbCommand (34ms) [Parameters=[@__ToLower_0='?', @__p_2='?', @__p_1='?'], CommandType='Text', CommandTimeout='30']
SELECT "u0"."Id", "u0"."Cancelled", "u0"."CreatedBy", "u0"."CreatedByRole", "u0"."CreatedOn", "u0"."ModifiedBy", "u0"."ModifiedByRole", "u0"."ModifiedOn", "u0"."RoleName", "u0"."RowVersion", "u0"."UserId"
FROM "UserRoles" AS "u0"
INNER JOIN (
SELECT DISTINCT "t0".*
FROM (
SELECT "f"."Id", "v"."Id" AS "Id0", "u"."Id" AS "Id1"
FROM "Posts" AS "f"
**LEFT JOIN "VoteDefinitions" AS "v" ON "v"."PostId" = "f"."Id"**
INNER JOIN "Users" AS "u" ON "f"."UserId" = "u"."Id"
WHERE (("f"."Cancelled" = FALSE) AND LOWER("f"."Title") LIKE ((('%' || @__ToLower_0)) || '%')) AND ("f"."Draft" = FALSE)
ORDER BY "f"."Id", "v"."Id", "u"."Id"
LIMIT @__p_2 OFFSET @__p_1
) AS "t0"
) AS "u1" ON "u0"."UserId" = "u1"."Id"
ORDER BY "u1"."Id", "u1"."Id0", "u1"."Id1"
info: Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory[1]
Executed DbCommand (47ms) [Parameters=[@__ToLower_0='?', @__p_2='?', @__p_1='?'], CommandType='Text', CommandTimeout='30']
SELECT "v0"."Id", "v0"."Answer", "v0"."Cancelled", "v0"."CreatedBy", "v0"."CreatedByRole", "v0"."CreatedOn", "v0"."ModifiedBy", "v0"."ModifiedByRole", "v0"."ModifiedOn", "v0"."RowVersion", "v0"."VoteDefinitionId"
FROM "VoteDefinitionPossibleAnswers" AS "v0"
INNER JOIN (
SELECT DISTINCT "t".*
FROM (
SELECT "f"."Id", "v"."Id" AS "Id0"
FROM "Posts" AS "f"
LEFT JOIN "VoteDefinitions" AS "v" ON "v"."PostId" = "f"."Id"
WHERE (("f"."Cancelled" = FALSE) AND LOWER("f"."Title") LIKE ((('%' || @__ToLower_0)) || '%')) AND ("f"."Draft" = FALSE)
ORDER BY "f"."Id", "v"."Id"
LIMIT @__p_2 OFFSET @__p_1
) AS "t"
) AS "v1" ON "v0"."VoteDefinitionId" = "v1"."Id"
ORDER BY "v1"."Id", "v1"."Id0"

EF Core version: 1.0.0-preview2-final
Operating system: Windows 10 and also Linux Ubuntu
Visual Studio version: VS 2015

[vukasinspasic]

I also noticed that Roles are not expanded if ordering is by Id (primary key) in query:

var query = dbContext.Posts.AsQueryable();
query = query.Include(x => x.User).ThenInclude(x => x.Roles).OrderBy(x => x.Id);

but for example when ordering with another property, ex. OrderBy(x => x.CreatedOn), Roles are expanded correctly

[maumar]

The problem is not necessarily about the additional JOIN to VoteDefinitions in the second query, but the fact that we are choosing incorrect column to join on between Users and Roles:

SELECT [r].[Id], [r].[Name], [r].[UserId]
FROM [Roles] AS [r]
INNER JOIN (
    SELECT DISTINCT [t0].*
    FROM (
        SELECT [v].[Id], [u].[Id] AS [Id0]
        FROM [Posts] AS [x]
        LEFT JOIN [VoteDefinitions] AS [v] ON [v].[PostId] = [x].[Id]
        INNER JOIN [Users] AS [u] ON [x].[UserId] = [u].[Id]
        ORDER BY [v].[Id], [u].[Id]
        OFFSET @__p_0 ROWS
    ) AS [t0]
) AS [u0] ON [r].[UserId] = [u0].[Id] -- this should be u0.Id0
ORDER BY [u0].[Id], [u0].[Id0]

This only seems to be happening for queries with Skip. Without it we produce the following, which works as expected:

SELECT [r].[Id], [r].[Name], [r].[UserId]
FROM [Roles] AS [r]
INNER JOIN (
    SELECT DISTINCT [v].[Id], [u].[Id] AS [Id0]
    FROM [Posts] AS [x]
    LEFT JOIN [VoteDefinitions] AS [v] ON [v].[PostId] = [x].[Id]
    INNER JOIN [Users] AS [u] ON [x].[UserId] = [u].[Id]
) AS [u0] ON [r].[UserId] = [u0].[Id0]
ORDER BY [u0].[Id], [u0].[Id0]

[vukasinspasic]

Yes, this situation is happening if Skip is used (even if Skip(0)). But in case OrderBy is by Id (primary key) then both Roles and PossibleAnswers are not expanded. In case OrderBy(x => X.CreatedOn) which is not a key property(column) then Roles are not expanded but PossibleAnswers are.

[maumar]

sending back to triage, as it's potentially a candidate for 1.0.1 - in some circumstances we could return invalid data i.e. navigations linked to wrong parent entities