Efcore: Included entity in let clause is not included

[sam-wheat]

Project attached.

var query = (from w in db.WatchListItems
                        let s = db.Series.Where(x => x.Symbol == w.Symbol && x.DataProviderID == w.DataProviderID).Include(x => x.NativeFrequency).First() // NativeFrequency not included
                        where w.UserID == user.ID && w.DataProviderID == dataProvider.ID
                        select new WatchListItem
                        {
                            ID = w.ID,
                            DataProviderID = w.DataProviderID,
                            Symbol = w.Symbol,
                            UserID = w.UserID,
                            Series = s
                        }).ToList();

EFAddTest.zip

Tested using EF 2.1.4 and 2.2.0-preview3-35497
Visual Studio 15.8.8

[sam-wheat]

When I inspect the sql for the above query I see this:

SELECT [w].[ID], [w].[DataProviderID], [w].[Symbol], [w].[UserID]
FROM [WatchListItems] AS [w]
WHERE ([w].[UserID] = 1) AND ([w].[DataProviderID] = 1)

I do not see the SQL for the Series object even though the Series object is populated correctly.
Why is that? Is this itself an issue or a byproduct of the problem that is reported?

Using IQueryableHelper found here: https://github.com/aspnet/EntityFrameworkCore/issues/6482

[ajcvickers]

@sam-wheat Translation of First is currently problematic. You might try FirstOrDefault instead. /cc @maumar

Beyond that, we will revisit this after 3.0 query changes.

[maumar]

Problem happens because IncludeReplacingExpressionVisitor does not replace qsres inside a subquery. However the fix is more involved - when we simply apply include replacing visitor recursively, additional issues/assumptions we had in the code are exposed. e.g. this would fail:

(from w in db.WatchListItems
 let s = db.Series.Where(x => x.Symbol == w.Symbol && x.DataProviderID == w.DataProviderID).Include(x => x.NativeFrequency)//.FirstOrDefault()
 where w.UserID == user.ID && w.DataProviderID == dataProvider.ID
 select new
 {
     ID = w.ID,
     DataProviderID = w.DataProviderID,
     Symbol = w.Symbol,
     UserID = w.UserID,
     Series = s
 }).ToList();

Exception:

System.InvalidCastException: 'Unable to cast object of type 'QueryableAdapter`1[Repro13787.Series]' to type 'Microsoft.EntityFrameworkCore.Query.IIncludableQueryable`2[Repro13787.Series,Repro13787.NativeFrequency]'.'

   at lambda_method(Closure , QueryContext , ValueBuffer )
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ProjectionShaper.TypedProjectionShaper`3.Shape(QueryContext queryContext, ValueBuffer& valueBuffer) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\ExpressionVisitors\Internal\ProjectionShaper.cs:line 101
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ProjectionShaper.TypedProjectionShaper`3.Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.IShaper<TOut>.Shape(QueryContext queryContext, ValueBuffer& valueBuffer)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer) in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\Internal\QueryingEnumerable.cs:line 132
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded) in D:\git\EntityFrameworkCore\src\EFCore.SqlServer\Storage\Internal\SqlServerExecutionStrategy.cs:line 47
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext() in D:\git\EntityFrameworkCore\src\EFCore.Relational\Query\Internal\QueryingEnumerable.cs:line 83
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext() in D:\git\EntityFrameworkCore\src\EFCore\Query\Internal\LinqOperatorProvider.cs:line 143

[maumar]

@sam-wheat because the query in the end projects entity and a scalar EF produces N+1 queries (see #11186).

We issue a query for WatchListItems (the one you see), and then for each matching watch list item we issue another query to fetch the relevant series:

SELECT [x].[ID], [x].[DataProviderID], [x].[Name], [x].[NativeFrequencyID], [x].[Symbol]
FROM [Series] AS [x]
WHERE ([x].[Symbol] = @_outer_Symbol) AND ([x].[DataProviderID] = @_outer_DataProviderID)

Wrt the actual issue, you can work around it by rewriting it to group joins, like so:

(from w in db.WatchListItems
 join s in db.Series.Include(x => x.NativeFrequency) on new { w.Symbol, w.DataProviderID } equals new { s.Symbol, s.DataProviderID } into grouping
 where w.UserID == user.ID && w.DataProviderID == dataProvider.ID
 select new //WatchListItem
 {
     ID = w.ID,
     DataProviderID = w.DataProviderID,
     Symbol = w.Symbol,
     UserID = w.UserID,
     Series = grouping
 }).ToList();

if (rewritten.First().Series.First().NativeFrequency == null)
   throw new Exception("NativeFrequency is null");

The problem is that we will bring all the matching series for a given watchlistitem, not only the first one.
However everything is performed in a single query, so it should be faster for most cases. Generated query is as follows:

SELECT [w].[ID] AS [ID0], [w].[DataProviderID] AS [DataProviderID0], [w].[SeriesID], [w].[Symbol] AS [Symbol0], [w].[UserID], [s].[ID], [s].[DataProviderID], [s].[Name], [s].[NativeFrequencyID], [s].[Symbol]
FROM [WatchListItems] AS [w]
LEFT JOIN [Series] AS [s] ON (([w].[Symbol] = [s].[Symbol]) OR ([w].[Symbol] IS NULL AND [s].[Symbol] IS NULL)) AND ([w].[DataProviderID] = [s].[DataProviderID])
WHERE ([w].[UserID] = 1) AND ([w].[DataProviderID] = 1)
ORDER BY [Symbol0], [DataProviderID0])

[sam-wheat]

@maumar Great explanation and revised query. Thanks very much.

[smitpatel]

This works in 3.1 correctly.
Generated SQL

exec sp_executesql N'SELECT [w].[ID], [w].[DataProviderID], [w].[Symbol], [w].[UserID], [t0].[ID], [t0].[DataProviderID], [t0].[Name], [t0].[NativeFrequencyID], [t0].[Symbol], [t0].[ID0], [t0].[DataProviderID0], [t0].[NativeFrequencyName], [t0].[ReportingFrequencyID], [t0].[c]
FROM [WatchListItems] AS [w]
LEFT JOIN (
    SELECT [t].[ID], [t].[DataProviderID], [t].[Name], [t].[NativeFrequencyID], [t].[Symbol], [t].[ID0], [t].[DataProviderID0], [t].[NativeFrequencyName], [t].[ReportingFrequencyID], [t].[c]
    FROM (
        SELECT [s].[ID], [s].[DataProviderID], [s].[Name], [s].[NativeFrequencyID], [s].[Symbol], [n].[ID] AS [ID0], [n].[DataProviderID] AS [DataProviderID0], [n].[NativeFrequencyName], [n].[ReportingFrequencyID], 1 AS [c], ROW_NUMBER() OVER(PARTITION BY [s].[Symbol], [s].[DataProviderID] ORDER BY [s].[ID], [n].[ID]) AS [row]
        FROM [Series] AS [s]
        INNER JOIN [NativeFrequencies] AS [n] ON [s].[NativeFrequencyID] = [n].[ID]
    ) AS [t]
    WHERE [t].[row] <= 1
) AS [t0] ON (([w].[Symbol] = [t0].[Symbol]) OR ([w].[Symbol] IS NULL AND [t0].[Symbol] IS NULL)) AND ([w].[DataProviderID] = [t0].[DataProviderID])
WHERE ([w].[UserID] = @__user_ID_0) AND ([w].[DataProviderID] = @__dataProvider_ID_1)',N'@__user_ID_0 int,@__dataProvider_ID_1 int',@__user_ID_0=1,@__dataProvider_ID_1=1