Query: Subquery projection being selected incorrectly with required navigation filter

[felipepessoto]

Steps to reproduce

Full example

EfCoreJoinIssue.zip

The issue

Running

IQueryable<Post> posts = db.Posts;
        posts = posts.Where(x => x.Author.UserName == "myuser");
        posts = posts.Take(5);

        var query = (from post in posts
                     select new { post.Slug, C = post.Comments.Count() });

Generates a query that joins using the wrong columns(or alias).

If we move Take(5) to the end, or remove the Where. It works:

IQueryable posts = db.Posts;
posts = posts.Where(x => x.Author.UserName == "myuser");
var query = (from post in posts
select new { post.Slug, C = post.Comments.Count() }).Take(5);

Or:

IQueryable posts = db.Posts;
posts = posts.Take(5);

        var query = (from post in posts
                     select new { post.Slug, C = post.Comments.Count() });

[smitpatel]

Can you post the generated query?

[felipepessoto]

They are in comments inside the code:

                //SELECT[t].[Slug], (
                //   SELECT COUNT(*)
                //          FROM[PostComments] AS[p0]
                //          WHERE[t].[Id] = [p0].[PostId]
                //      )
                //      FROM(
                //          SELECT TOP(@__p_0)[x.Author0].*  (my note: should be [x0].*)
                //          FROM[Posts] AS[x0]
                //          INNER JOIN[Users] AS[x.Author0] ON[x0].[AuthorId] = [x.Author0].[Id]
                //          WHERE[x.Author0].[UserName] = N'myuser'
                //      ) AS[t]

                //Error: Invalid column name 'Slug'.

[smitpatel]

@rowanmiller @divega - Bumping the milestone back & submitted PR.