Wrong SQL query generation

[serber]

Steps to reproduce

I have code

IQueryable<Series> query = GetSeriesQuery();
IQueryable<SeriesData> seriesDataQuery = query.Select(x => new SeriesData
{
    Series = x,
    Subscribed = x.Subscriptions.Any(y => y.UserId == userId),
    CurrentSeasonNumber = x.Episodes.Where(z => z.ReleaseDate < date).Max(y => y.SeasonNumber),
    Channel = x.Channel,
    Country = x.Channel.Country,
    ReleaseGroups = x.Episodes.SelectMany(z => z.Releases)
                                .Select(y => y.ReleaseGroup)
                                .Select(r => new ReleaseGroupData
                                {
                                    ReleaseGroup = r,
                                    Subscribed = x.Subscriptions.Any(y => y.UserId == userId && y.ReleaseGroupId == r.Id)
                                }).ToList()
});

The issue

When i call SingleOrDefaultAsync() i get an exception

Exception message: SqlException: The multi-part identifier "x.ID" could not be bound.
Stack trace:

System.Data.SqlClient.SqlException: The multi-part identifier "x.ID" could not be bound.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean closeConnection)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)
   at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](Func`2 operation, Func`2 verifySucceeded, TState state)
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext()
   at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at lambda_method(Closure , TransparentIdentifier`2 )
   at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
   at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source)
   at lambda_method(Closure , QueryContext )
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass20_0`1.<CompileQueryCore>b__0(QueryContext qc)
   at System.Linq.Queryable.SingleOrDefault[TSource](IQueryable`1 source)
   at MySeries.Core.Services.SeriesService.<Get>d__9.MoveNext() in D:\Sources\myseries-2\MySeries.Core\Services\SeriesService.cs:line 179
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at MySeries.Web.Controllers.SeriesController.<Details>d__5.MoveNext() in D:\Sources\myseries-2\MySeries.Web\Controllers\SeriesController.cs:line 63
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__27.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeNextActionFilterAsync>d__25.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeNextResourceFilter>d__22.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ResourceExecutedContext context)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeAsync>d__20.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Builder.RouterMiddleware.<Invoke>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware`1.<Invoke>d__18.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.<Invoke>d__7.MoveNext()
ClientConnectionId:197cdf22-f50b-41f3-81c0-7fc3a0568045
Error Number:4104,State:1,Class:16

Further technical details

Generated SQL query:

SELECT [z.Releases.ReleaseGroup].[ID], [z.Releases.ReleaseGroup].[KEY], [z.Releases.ReleaseGroup].[LOGO_URL], [z.Releases.ReleaseGroup].[NAME], [z.Releases.ReleaseGroup].[RSS], [z.Releases.ReleaseGroup].[WEB_SITE], (
  SELECT CASE
	  WHEN EXISTS (
		  SELECT 1
		  FROM [SUBSCRIPTION] AS [y0]
		  WHERE (([y0].[USER_ID] = @__userId_3) AND ([y0].[RELEASE_GROUP_ID] = [z.Releases].[RELEASE_GROUP_ID])) AND ([x].[ID] = [y0].[SERIES_ID]))
	  THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
  END
)
FROM [EPISODE] AS [z0]
INNER JOIN [RELEASE] AS [z.Releases] ON [z0].[ID] = [z.Releases].[EPISODE_ID]
INNER JOIN [RELEASE_GROUP] AS [z.Releases.ReleaseGroup] ON [z.Releases].[RELEASE_GROUP_ID] = [z.Releases.ReleaseGroup].[ID]
WHERE @_outer_Id = [z0].[SERIES_ID]

([x].[ID] = [y0].[SERIES_ID]) should be (@_outer_Id = [y0].[SERIES_ID])

EF Core version: 1.1.0

[maumar]

Can you also post your model? Specifically code listings for all the entities and contents of OnModelCreating method on your DbContext

[serber]

okay, i do it later.
This problem appeared after updating to EF Core version: 1.1.0

[maumar]

Note for Triage looks like a regression, 1.1.1?

[smitpatel]

Similar to #7033

[divega]

@maumar @smitpatel do you know if this is a duplicate of #7033?

[smitpatel]

duplicate of #7033