Reuse complex projection in operators afterwards

[msmolka]

The code generated for SQL is far from optimal comparing to EF6

Steps to reproduce

I'm getting model from database in following way:

from user in context.Users
                   let info= context.LastInfo.OrderByDescending(t => t.CreatedDate).FirstOrDefault(t => t.UserId== user.Id)
                   let extra = context.Extras.OrderByDescending(t => t.CreatedDate).FirstOrDefault(s => s.UserId == user.Id)
                   select new UserExtraInfo
                   {
                       Active = user.Active,                    
                       Id = user.Id,
                       LastInfoDate = info.CreatedDate,                    
                       LastTest1 = extra.Test1,
                       LastTest2 = extra.Test2,
                       LastTest3 = extra.Test3,                   
                       InfoTest1= info.Test1,
                       InfoTest1= info.Test2                   
                   };

The code produced by EF is

SELECT
    [user].[Active],
    [user].[Id],
(
    SELECT TOP (1)
        [t].[CreatedDate]
    FROM [Info] AS [t]
    WHERE
        [t].[UserId] = [user].[Id]
    ORDER BY
        [t].[CreatedDate] DESC
),
 
(
    SELECT TOP (1)
        [t0].[Test1]
    FROM [Extra] AS [t0]
    WHERE
        [t0].[UserId] = [user].[Id]
    ORDER BY
        [t0].[CreatedDate] DESC
),
(
    SELECT TOP (1)
        [t1].[Test2]
    FROM [Extra] AS [t1]
    WHERE
        [t1].[UserId] = [user].[Id]
    ORDER BY
        [t1].[CreatedDate] DESC
),
(
    SELECT TOP (1)
        [t2].[Test3]
    FROM [Extra] AS [t2]
    WHERE
        [t2].[UserId] = [user].[Id]
    ORDER BY
        [t2].[CreatedDate] DESC
),
(
    SELECT TOP (1)
        [t4].[Test1]
    FROM [Info] AS [t4]
    WHERE
        [t4].[UserId] = [user].[Id]
    ORDER BY
        [t4].[CreatedDate] DESC
),
(
    SELECT TOP (1)
        [t5].[Test2]
    FROM [Info] AS [t4]
    WHERE
        [t5].[UserId] = [user].[Id]
    ORDER BY
        [t5].[CreatedDate] DESC
)
FROM [User] AS [user]

As far as I remember EF6 generated following query with CROSS APPLY/OUTER APPLY which is about 10x quicker.
Instead of select multiple times from the same row projection. Select each row once using OUTER APPLY.

Further technical details

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

[tuespetre]

I had this implemented in a HUGE branch that did way too much at one time. I've been piecemealing out everything I can as I have time -- I hope to get back to this one soon (note: I am not an EF team member, just doing this out of ❤️, don't lean too heavily on my words)

[tuespetre]

Alright, I've put together #7857 for implementing OUTER APPLY (although that is mostly a sugar for what can already be done with CROSS APPLY.) Other than that, the inefficiency here is that the range variables which are subqueries are expanded into each place they are used in the projection. An optimization can be applied to the query model to create a single lateral join instead (via an AdditionalFromClause.) I will see if I can tackle that.