Query: Wrong SQL generated for query with group join on a subquery that is not present in the final projection

[hugoterelle]

Hi,
I have 2 entities with the following code:

	public class Brand : RootEntity, IDisabledEntity
	{
		public string Name { get; set; }
		public bool Disabled { get; set; }
		public virtual ICollection<Smartphone> Smartphones { get; set; }
	}

	public class Smartphone : RootEntity, IDisabledEntity
	{
		public string Model { get; set; }
		public virtual Brand Brand { get; set; }
		public bool Disabled { get; set; }
	}

Where RootEntity and Entity are

    public class RootEntity : Entity, IAuditableEntity
    {
        public DateTime CreatedOn { get; set; }
        public DateTime ModifiedOn { get; set; }
    }
    public class Entity
    {
        public int Id { get; set; }
    } 

So nothing special. Just a one-to-many relationship between Brand and Smartphone.
The resulting SQL table are:

CREATE TABLE "Brands" 
( "Id" INTEGER NOT NULL CONSTRAINT "PK_Brands" PRIMARY KEY AUTOINCREMENT, 
"CreatedOn" TEXT NOT NULL,
 "Disabled" INTEGER NOT NULL, 
"ModifiedOn" TEXT NOT NULL, 
"Name" TEXT )

CREATE TABLE "Smartphones" 
( "Id" INTEGER NOT NULL CONSTRAINT "PK_Smartphones" PRIMARY KEY AUTOINCREMENT, 
"BrandId" INTEGER, 
"CreatedOn" TEXT NOT NULL, 
"Disabled" INTEGER NOT NULL, 
"Model" TEXT, 
"ModifiedOn" TEXT NOT NULL, 
CONSTRAINT "FK_Smartphones_Brands_BrandId" FOREIGN KEY ("BrandId") REFERENCES "Brands" ("Id") ON DELETE RESTRICT )

I try to retrieve my "Smartphone" list with the following parameters:

• sort by brand name, then by smartphone model
• skip at least 'skip' rows
• take maximum 'take' rows

Here is the LINQ code below:

		public Task<List<SmartphoneDto>> GetAll(int skip, int take)
		{
			return DataContext.Set<Smartphone>()
				.Where(x => !x.Disabled)
				.Include(x => x.Brand)
				.OrderBy(x => x.Brand.Name).ThenBy(x => x.Model)
				.Skip(skip) 
				.Take(take)
				.Select(x => new SmartphoneDto {Id = x.Id, Model = x.Model, Brand = x.Brand.Name})
				.ToListAsync();
		}

The issue

Using a SQLite database, the generated SQL is:

SELECT "x"."Id", "x"."BrandId", "x"."CreatedOn", "x"."Disabled", "x"."Model", "x"."ModifiedOn", "x.Brand"."Id", "x.Brand"."CreatedOn", "x.Brand"."Disabled", "x.Brand"."ModifiedOn", "x.Brand"."Name", "x.Brand"."BrandId", "x.Brand"."Id", "x.Brand"."Model"
FROM "Smartphones" AS "x"
LEFT JOIN "Brands" AS "x.Brand" ON "x"."BrandId" = "x.Brand"."Id"
WHERE "x"."Disabled" = 0
ORDER BY "x"."BrandId"

When the query is executed by EF, I get the error:

Microsoft.Data.Sqlite.SqliteException: SQLite Error 1: 'no such column: x.Brand.BrandId'.

In fact, all columns from 'Smartphone' are duplicated in the SQL Query for the 'Brand' (BrandId, Model, etc.)

Moreover, when I check the SQL query I can see that the ORDER BY is wrong

When I remove the 'Skip(skip)' and 'Take(take)' parameters in the LINQ query, the following SQL query is generated:

SELECT "x"."Id", "x"."BrandId", "x"."CreatedOn", "x"."Disabled", "x"."Model", "x"."ModifiedOn", "x.Brand"."Id", "x.Brand"."CreatedOn", "x.Brand"."Disabled", "x.Brand"."ModifiedOn", "x.Brand"."Name"
FROM "Smartphones" AS "x"
LEFT JOIN "Brands" AS "x.Brand" ON "x"."BrandId" = "x.Brand"."Id"
WHERE "x"."Disabled" = 0
ORDER BY "x"."BrandId"

This query is executed without errors and gives me the expected result.
But the second problem concerning the ORDER BY remains.

Further technical details

project.json

"dependencies": {
     "NETStandard.Library": "1.6.0",

     "Domain": "*",

     "Microsoft.EntityFrameworkCore": "1.0.1",
     "Microsoft.EntityFrameworkCore.Sqlite": "1.0.1",

     "Microsoft.EntityFrameworkCore.Design": {
         "version": "1.0.1",
         "type": "build"
     }
 },
"tools": {
     "Microsoft.EntityFrameworkCore.Tools": {
         "version": "1.0.0-preview2-final",
         "imports": [
             "portable-net45+win8+dnxcore50",
             "portable-net45+win8"
         ]
     }
 },
 "frameworks": {
     "netstandard1.6": {
         "imports": "dnxcore50"
     }
 }

Operating system: Visual Studio 2015 Update 3

[maumar]

This still repros on your current bits. Simplified repro:

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

                var query = ctx.Smartphones
                    .OrderBy(x => x.Brand.Name)
                    .Take(2)
                    .Select(x => new { Id = x.Id, Brand = x.Brand.Name });

                query.ToList();
            }
        }
    }

    public class Brand
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual ICollection<Smartphone> Smartphones { get; set; }
    }

    public class Smartphone
    {
        public int Id { get; set; }
        public string Model { get; set; }
        public virtual Brand Brand { get; set; }
    }

    public class MyContext : DbContext
    {
        public DbSet<Smartphone> Smartphones { get; set; }
        public DbSet<Brand> Brands { get; set; }

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

[maumar]

wrt the second query's order - this happens because we introduce order by key any time we produce left outer join (which under the covers we translate into SelectMany-GroupJoin, and we rely on ordered results to group them correctly). The second orderby was performed on the client because of #4588 (now fixed).

in the current bits, for the query without Skip/Take we produce the following:

SELECT [x].[Id], [x].[BrandId], [x].[Model], [x.Brand].[Id], [x.Brand].[Name]
FROM [Smartphones] AS [x]
LEFT JOIN [Brands] AS [x.Brand] ON [x].[BrandId] = [x.Brand].[Id]
ORDER BY [x.Brand].[Name], [x].[Model], [x].[BrandId]

[hugoterelle]

Ok, thanks for your answer. When the new release will fix these issues?

[ErikEJ]

@hugoterelle Very soon!

[maumar]

Fixed in f51502f