Skip, Take and Select return incorrect results

[ivaylokenov]

Steps to reproduce

Using the default ASP.NET Core MVC template in Visual Studio with LocalDB.

I have Album database model:

    public class Album
    {
        public int Id { get; set; }

        [Required]
        public string Name { get; set; }

        [MaxLength(1000)]
        public string Description { get; set; }

        public DateTime CreatedOn { get; set; }

        public string UserId { get; set; }

        public virtual ApplicationUser User { get; set; }
    }

And ListAlbumViewModel:

    public class ListAlbumsViewModel
    {
        public int Id { get; set; }

        public string Name { get; set; }

        public string Creator { get; set; }

        public int TotalImages { get; set; }
    }

The issue

If I have full database with albums, this query runs fine and returns correct results:

var result = this.db.Albums
                .OrderByDescending(al => al.CreatedOn)
                .Select(al => new ListAlbumsViewModel
                {
                    Id = al.Id,
                    Name = al.Name,
                    Creator = al.User.UserName,
                    TotalImages = 0
                })
                .Skip((page - 1) * pageSize)
                .Take(pageSize)
                .ToList();

However if I move the Select call after the Take, I receive pageSize times the first found entry. For example if I have page size 10 and the first album has Id 1, I will receive 10 times the album with Id 1. Here is the query:

var result = this.db.Albums
                .OrderByDescending(al => al.CreatedOn)
                .Skip((page - 1) * pageSize)
                .Take(pageSize)
                .Select(al => new ListAlbumsViewModel
                {
                    Id = al.Id,
                    Name = al.Name,
                    Creator = al.User.UserName,
                    TotalImages = 0
                })
                .ToList();

Further technical details

EF Core version: 1.0.0
Operating system: Windows 10
Visual Studio version: 2015 Community

[smitpatel]

This is a bug. First query returns correct results since there is the skip & take are evaluated in client.
Second query generates correct SQL but while group joining results on client, we are not grouping it appropriately. Specifically due to this logic
https://github.com/aspnet/EntityFramework/blob/dev/src/Microsoft.EntityFrameworkCore.Relational/Query/QueryMethodProvider.cs#L278

It assumes that columns from inner will appear after outer, while generated query has it in reverse order.

SELECT [al.User].[Id], [al.User].[UserName], [t].[Id], [t].[Name]
FROM (
    SELECT [al0].*
    FROM [Albums] AS [al0]
    ORDER BY [al0].[CreatedOn] DESC
    OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
) AS [t]
LEFT JOIN [User] AS [al.User] ON [t].[UserId] = [al.User].[Id]
ORDER BY [t].[UserId]

[smitpatel]

fixed via 5953d96