Query: Selecting column from wrong table when filtering on navigation property and using skip/take

[austinleroy]

When filtering on a navigation property and then paging results using Skip/Take, incorrect SQL is generated. This causes a SQL exception (in both SQL Server and SQLite).

Exception message: 
    SQLite Error 1: 'no such column: q.Book.MysteryBook.AuthorID'.
Stack trace:
       at Microsoft.Data.Sqlite.Interop.MarshalEx.ThrowExceptionForRC(Int32 rc, Sqlite3Handle db)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
   at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at IncorrectSQLGeneration.Program.Main(String[] args) in C:\Users\ablake\Documents\Visual Studio 2015\Projects\IncorrectSQLGeneration\src\IncorrectSQLGeneration\Program.cs:line 51

Steps to reproduce

Project that reproduces this issue can be found at: https://github.com/austinleroy/EFCore_IncorrectSQLGeneration

Source also attached to this issue.
IncorrectSQLGeneration.zip

Console application that describes this issue:

Program.cs

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace IncorrectSQLGeneration
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var connectionStringBuilder = new SqliteConnectionStringBuilder
            {
                DataSource = ":memory:"
            };
            string connectionString = connectionStringBuilder.ToString();
            var connection = new SqliteConnection(connectionString);
            connection.Open();

            DbContextOptionsBuilder<DataContext> builder = new DbContextOptionsBuilder<DataContext>();
            //Issue appears when generating SQL for SQLite
            builder.UseSqlite(connection);
            //Issue appears when generating SQL for SQL Server
            //builder.UseSqlServer("Data Source=localhost;Initial Catalog=EFCoreBroke;Integrated Security=true;");

            //Issue is not a problem with an in-memory database
            //builder.UseInMemoryDatabase();

            var log = new Microsoft.Extensions.Logging.LoggerFactory();
            log.AddConsole(LogLevel.Debug);
            log.AddDebug();
            builder.UseLoggerFactory(log);

            using (var context = new DataContext(builder.Options))
            {
                context.Database.EnsureCreated();

                IQueryable<Quote> quotes =
                    context.Quotes
                    .Include(q => q.Book).ThenInclude(b => b.MysteryBook);

                //Commenting out filter on navigation property causes SQL to be generated correctly
                quotes = quotes.Where(q => q.Book.MysteryBook.Hero == "Sherlock");
                quotes = quotes.OrderBy(q => q.ID);
                //Commenting out Skip/Take causes SQL to be generated correctly
                quotes = quotes.Skip(0).Take(10);

                var authorIDs = quotes.Select(q => q.AuthorID).ToList();

                foreach(var id in authorIDs)
                {
                    Console.WriteLine(id);
                }
            }
        }
    }
}

Context & Models

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace IncorrectSQLGeneration
{
    public class DataContext : DbContext
    {
        public DataContext(DbContextOptions<DataContext> builder)
            :base(builder)
        { }

        public DbSet<Quote> Quotes { get; set; }
        public DbSet<Book> Books { get; set; }
        public DbSet<MysteryBook> Publishers { get; set; }
    }

    [Table("Quote")]
    public partial class Quote
    {
        [Key]
        public int ID { get; set; }

        public int AuthorID { get; set; }

        public int? BookID { get; set; }

        [ForeignKey("BookID")]
        public virtual Book Book { get; set; }
    }

    [Table("Book")]
    public partial class Book
    {
        [Key]
        public int ID { get; set; }
        public virtual ICollection<Quote> Quotes { get; set; }
        public virtual MysteryBook MysteryBook { get; set; }
    }

    [Table("MysteryBook")]
    public partial class MysteryBook
    {
        [Key]
        public int BookID { get; set; }
        public string Hero { get; set; }

        [ForeignKey("BookID")]
        public virtual Book Book { get; set; }
    }
}

Further technical details

EF Core version: 1.1.0
Database Provider:
"Microsoft.EntityFrameworkCore.Sqlite": "1.1.0"
"Microsoft.EntityFrameworkCore.SqlServer": "1.1.0"
Operating system: Windows 10
IDE: Visual Studio 2015

[austinleroy]

(Incorrect) SQL Generated from execution:

SELECT "q.Book"."ID", "q.Book.MysteryBook"."BookID", "q.Book.MysteryBook"."Hero", "q.Book.MysteryBook"."AuthorID"
FROM "Quote" AS "q"
LEFT JOIN "Book" AS "q.Book" ON "q"."BookID" = "q.Book"."ID"
LEFT JOIN "MysteryBook" AS "q.Book.MysteryBook" ON "q.Book"."ID" = "q.Book.MysteryBook"."BookID"
WHERE "q.Book.MysteryBook"."Hero" = 'Sherlock'
ORDER BY "q"."ID", "q"."BookID", "q.Book"."ID"
LIMIT @__p_1 OFFSET @__p_0

"q.Book.MysteryBook"."AuthorID" is not valid

[divega]

Assigning to 1.1.1 assuming this could be a regression we need to fix. We can reevaluate based on the repro.

[smitpatel]

This is confirmed bug though not a regression.
A work-around is to apply the projection before doing Skip/Take.
The working query would be

var authorIDs = context.Quotes.Where(q => q.Book.MysteryBook.Hero == "Sherlock").OrderBy(q => q.ID).Select(q => q.AuthorID).Skip(0).Take(10).ToList();

//SQL
exec sp_executesql N'SELECT [q].[ID], [q].[AuthorID], [q].[BookID], [q.Book].[ID], [q.Book.MysteryBook].[BookID], [q.Book.MysteryBook].[Hero]
FROM [Quotes] AS [q]
LEFT JOIN [Books] AS [q.Book] ON [q].[BookID] = [q.Book].[ID]
LEFT JOIN [MysteryBook] AS [q.Book.MysteryBook] ON [q.Book].[ID] = [q.Book.MysteryBook].[BookID]
WHERE [q.Book.MysteryBook].[Hero] = N''Sherlock''
ORDER BY [q].[ID]
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=10

Also you wouldn't need Include if you are not materializing entity in projection.

[smitpatel]

Verify this is fixed when #7613 is closed.

[anpete]

@smitpatel #7613 doesn't fix the general case. I added another disabled test that also needs to pass.