Entity Framework Core - LINQ select over navigational properties creates invalid query

Question

Currently, I'm working on an Asp.Net Core website and use Entity Framework Core (currently RC1) for database access with SQL Express LocalDB on the development machine, although MS SQL on the test server gives the same error.

I've run into an issue that when querying multiple levels of navigational properties, invalid SQL would be generated. I created the following test code to reproduce the issue:

4 Classes that represent database tables

public class CarManufacturer
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public List<CarModel> CarModels { get; set; }
}

public class CarModel
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public Guid CarManufacturerId { get; set; }
    public CarManufacturer CarManufacturer { get; set; }
    public List<CarTestDriver> CarTestDrivers { get; set; }
}

public class CarTestDriver
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public Guid CarModelId { get; set; }
    public CarModel CarModel { get; set; }
    public List<SpeedingTicket> SpeedingTickets { get; set; }
}

public class SpeedingTicket
{
    public Guid Id { get; set; }
    public decimal Amount { get; set; }
    public Guid CarTestDriverId { get; set; }
    public CarTestDriver CarTestDriver { get; set; }
}

DbSets in the Context class

    public DbSet<CarManufacturer> CarManufacturers { get; set; }
    public DbSet<CarModel> CarModels { get; set; }
    public DbSet<CarTestDriver> CarTestDrivers { get; set; }
    public DbSet<SpeedingTicket> SpeedingTickets { get; set; }

Now I create a linq query to give me the total amount of tickets sorted by manufacturers

        var ticketCostByManufacturers = Context.CarManufacturers
            .Select(manufacturer => new
            {
                manufacturer.Name,
                TotalTicketAmount = manufacturer.CarModels.SelectMany(model => model.CarTestDrivers).SelectMany(driver => driver.SpeedingTickets).Sum(ticket => ticket.Amount)
            })
            .ToList();

Which does two things:

1. Creates a single query to retrieve all manufacturer Ids and Names

2. Queries all the tickets with the following, invalid SQL: (Only when the first query does yield results)

   SELECT [model].[Id],
   [model].[Amount],
   [model].[CarTestDriverId],
   [model].[CarManufacturerId], -- Invalid Column
   [model].[Id],
   [driver].[Id],
   [ticket].[Amount]
   FROM [SpeedingTicket] AS [model]
   CROSS JOIN [CarTestDriver] AS [driver]
   CROSS JOIN [SpeedingTicket] AS [ticket]      
   

The query results in an exception due to an invalid column name CarManufacturerId on the table for entity SpeedingTicket, since the query does select [SpeedingTicket] AS [model].

I've experimented with .Include() as well as with the Fluent API, but both didn't get me to a solution for how to properly use Entity Framework Core here to perform the query.

The exception message itself suggests that my model and the database are out of sync and I should create a new migration, but in fact they are in sync. I guess this suggestion comes from EF assuming a database schema mismatch when a column is not found.

Edit for clarification:

The intention of the question was to find out whether this is an error in my setup (like missing configuration with Fluent API) or if it's still a bug in the current version of Entity Framework Core. @Ivan Stoev did point it it's the latter, a bug in the current code of Entity Framework.

Answer 1

As I understand,There are no issue with entity framework code.Output of entity framework code. But in Sql query [model] mentioned here is [SpeedingTickets] table created by entity framework. That table does not contain any column call [CarManufacturerId].That is the error.