Query: Projecting second level collection navigation using First() generates invalid SQL

[MaklaCof]

I am trying to query all parts, then there first title and after that Title field from Parts-Title_Translates.
This is my table relationship.

Exception:

Intellisense shows correct fields.

Steps to reproduce

var result = this.context.Parts.Include(t => t.Titles).ThenInclude(t => t.Translates)
                    .Select(t => new {
                        id = t.Id,
                        translate = t.Titles.OrderByDescending(t2 => t2.IsDefault).First().Translates.Where(t2 => t2.LangISOCode == lang).First()
                    });

                var resultarray = await result.ToArrayAsync();

Further technical details

EF Core version: 1.1.2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Win 10, Database: win 7 SQL Server 2016
IDE: Visual Studio 2017 Preview 2 (from About: 15.3.0 PReview 2.0)

Notes:
I also try with this query (which works)

var result = this.context.Parts.Include(t => t.Titles).ThenInclude(t => t.Translates)
                .Select(t => new
                {
                    id = t.Id,
                    translate = this.context.PartTitleTranslates
                        .Include(t2 => t2.PartTitle)
                        .Where(t2 => t2.PartTitle.PartId == t.Id && t2.LangISOCode == lang)
                        .OrderByDescending(t2 => t2.PartTitle.IsDefault).First()
                });

but it takes a few minutes to fetch results, because it fetches one by one. I need a query to fetch with one round-trip to database.

[smitpatel]

Can you fetch SQL, EF sent for the query?

[MaklaCof]

I was following instructions from here and get this results.
After executing:
var resultarray = await result.ToArrayAsync();
I get this log in VS output window:

 Debug           3     - Opening connection to database 'OpPISWebDevelopment' on server '10.0.0.73\MSSQLSERVER2016'.
 Information     1     - Executed DbCommand (18ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
                           SELECT [t].[Id]
                           FROM [Parts] AS [t]
 Information     1     - Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
                           SELECT [t2].[PartId], [t2].[Id], [t2].[LangISOCode]
                           FROM [Part-Titles] AS [t2]
                           ORDER BY [t2].[IsDefault] DESC
 Error           1     - An exception occurred in the database while iterating the results of a query.
                           System.Data.SqlClient.SqlException: Invalid column name 'LangISOCode'.

and same in SQL Server Profiler:

SQL:BatchCompleted	SELECT [t].[Id] FROM [Parts] AS [t]	Core .Net SqlClient Data Provider		
SQL:BatchCompleted	SELECT [t2].[PartId], [t2].[Id], [t2].[LangISOCode] FROM [Part-Titles] AS [t2] ORDER BY [t2].[IsDefault] DESC	Core .Net SqlClient Data Provider		

Hope it helps.

[MaklaCof]

I want to write this sql with EF:

SELECT [Id],
	(select top 1 
		(select top 1 L.Title from [Parts-Title_Translates] L WHERE L.PartTitleId = T.Id AND L.LangISOCode = 'SLV') 
	from [Part-Titles] T WHERE T.PartId = P.Id ORDER BY T.IsDefault DESC)
FROM [Parts] P

[smitpatel]

This repros in 1.1.2 release.
I tested the query with preview2 bits.
due to #8398 it throws error. Removing Includes from the query, the query works correctly.
It still sends 3 different queries but valid SQL.
The above exception is likely to be fixed during all refactoring happened in query pipeline.

Few notes:

1. It sends multiple queries because you are materializing Translate in final projection inside anonymous type with ordered include on first level and filtered include on second level. At present that result into N+1 queries only.
2. Using First() causes client eval always. Because the linq behavior for First is to throw exception if sequence is empty. (SqlServer does not throw error if top(1) returned no rows. To mimic linq behavior, we have to evaluate sequence on client. Using FirstOrDefault() is more likely to translate more parts of query to server.

Based on SQL you posted above (which select Title instead of Parts-Title_Translate, notice difference between a domain class & string property) this query should give you correct results. (it works in 1.1.2 version also)

var result = db.Parts
    .Select(p => new
    {
        id = p.Id,
        translate = p.Titles.OrderByDescending(t => t.IsDefault)
            .Select(t => t.Translates.Where(l => l.LangISOCode == lang).Select(l => l.Title).FirstOrDefault()).FirstOrDefault()
    });

Generated SQL:

SELECT [p].[Id], (
          SELECT TOP(1) (
              SELECT TOP(1) [l].[Title]
              FROM [Translates] AS [l]
              WHERE ([l].[LangISOCode] = @__lang_0) AND ([t].[Id] = [l].[TitleId])
          )
          FROM [Titles] AS [t]
          WHERE [p].[Id] = [t].[PartId]
          ORDER BY [t].[IsDefault] DESC
      ) AS [translate]
      FROM [Parts] AS [p]