2 level expand not working correctly

[vukasinspasic]

Hello. I noticed bugs in library.

1. When I try to make query with 2 expands of 2. level:

   query.Include(x => x.User).ThenInclude(x => x.Roles).Include(x => x.VoteDefinition).ThenInclude(x => x.PossibleAnswers)

this generated bad SQL so User.Roles is not expanded. Example of 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"

As you can see, 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

[roji]

Am unfortunately very busy with other things at the moment, but if there's a problem here it's probably in the EFCore provider and not in the Npgsql provider. Try opening this issue in the EFCore repo.

[vukasinspasic]

Thank you for quick answer

[roji]

Sure thing. Please post a link to the EFCore issue you open so I can follow it to, in case they're some Npgsql angle.

[roji]

Link to EFCore issue: dotnet/efcore#6366