Query: The wrong type is being selected when an extension method is used in a subquery

[rchowe]

The issue

I have a somewhat complicated expression in a subquery, which I'd like to simplify since I use it a lot, so I defined an extension method on IEnumerable<MyModelType> to make it clearer what's happening in the queries. When I try to run the code with the full expression in the subquery, it works, but when I use the extension method, it doesn't.

Steps to reproduce

I have the following, somewhat contrived example (similar to the MusicStore example):

public class Song
{
    int Id { get; set; }
    public int Order { get; set; }
    public int Rating { get; set; }

    public Album Album { get; set; }
}

public class Album
{
    int Id { get; set; }
    public List<Song> Songs { get; set; }
}

In my code, I have this query, which works as expected (gets a list of albums with a song where the highest rated song on the album is in the top three songs).

var goodAlbums = await _db.Albums
    .Include(album => album.Songs)
    .Where(album => album.Songs.OrderByDescending(s => s.Rating).First().Order <= 3)
    .ToListAsync();
Console.WriteLine($"Good Album Count: {goodAlbums.Count()}");

This works as expected and generates the following SQL:

SELECT "a"."Id"
FROM "Albums" AS "a"
WHERE (
    SELECT "s"."Order"
    FROM "Songs" AS "s"
    WHERE "a"."Id" = "s"."AlbumId"
    ORDER BY "s"."Rating" DESC
    LIMIT 1
) <= 3
ORDER BY "a"."Id"

However, I want to simplify getting the highest rated song in the subquery using the following extension method:

public static Song HighestRated(this IEnumerable<Song> songs)
{
    return songs.OrderByDescending(s => s.Rating).First();
}

So then when I do this query,

var goodAlbums = await _db.Albums
    .Include(a => a.Songs)
    .Where(a => a.Songs.HighestRated().Order <= 3)
    .ToListAsync();

I get this SQL

SELECT "s0"."Id", "s0"."AlbumId", "s0"."Order", "s0"."Rating"
FROM "Songs" AS "s0"
WHERE EXISTS (
    SELECT 1
    FROM "Albums" AS "a"
    WHERE ((
        SELECT "s"."Order"
        FROM "Songs" AS "s"
        WHERE "a"."Id" = "s"."AlbumId"
        ORDER BY "s"."Rating" DESC
        LIMIT 1
    ) <= 3) AND ("s0"."AlbumId" = "a"."Id"))
ORDER BY "s0"."AlbumId"

And then I get this error because the fields for songs (from the subquery) are selected from the database instead of the fields for albums, which is the type the result should be:

System.InvalidOperationException: The property 'AlbumId' on entity type 'Album' could not be found. Ensure that the property exists and has been included in the model.
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.IterateCompositePropertyExpression(Expression expression, QuerySourceReferenceExpression& querySourceReferenceExpression)
   at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.BindPropertyExpressionCore[TResult](Expression propertyExpression, IQuerySource querySource, Func`3 propertyBinder)
...

Further technical details

EF Core version: 1.0.0
Operating system: macOS 10.11.6
Visual Studio version: (e.g. VS 2013 or n/a) VS Code 1.4.0

Other details about my project setup:

I'm using SQLite as the datastore, but I've also seen this happen using SQL Server.

[maumar]

This is still broken in the current bits. Full repro:

    class Program
    {
        static void Main(string[] args)
        {
            using (var ctx = new MyContext())
            {
                ctx.Database.EnsureDeleted();
                ctx.Database.EnsureCreated();

                var goodAlbums2 = ctx.Albums
                    .Where(a => a.Songs.HighestRated().Order <= 3)
                    .ToList();
            }
        }
    }

    public static class Ext
    {
        public static Song HighestRated(this IEnumerable<Song> songs)
        {
            return songs.OrderByDescending(s => s.Rating).First();
        }
    }

    public class MyContext : DbContext
    {
        public DbSet<Song> Songs { get; set; }
        public DbSet<Album> Albums { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=.;Database=Repro6363;Trusted_Connection=True;MultipleActiveResultSets=True");
        }
    }

    public class Song
    {
        public int Id { get; set; }
        public int Order { get; set; }
        public int Rating { get; set; }

        public Album Album { get; set; }
    }

    public class Album
    {
        public int Id { get; set; }
        public List<Song> Songs { get; set; }
    }

[maumar]

It's worth pointing out, that even when this is fixed, using a helper method like this to DRY the queries is not recommended. We are unable to translate such method, so it will be evaluated on the client, leading to inefficient N+1 queries (querying for list of Albums and then issuing a query for each Album, rather than getting all the information in a single query)

[maumar]

fixed in dd15830