Query: Left Outer Join created incorrectly for some Skip/Take patterns

[rasikaw-is]

Hi everyone,

I have upgraded my project to ASP.NET Core 1.1 from 1.0. It has resolved the previous LINQ left outer join issue #6091 that was there. But there is another error that I get (see inline C# code comments and exception details below).

public class CustomerListItemViewModel
{
    public string CustomerName { get; set; }
    public string TownName { get; set; }
}

// LINQ left outer join.
var customers = from customer in _db.Customer
                join postcode in _db.Postcode
                    on customer.PostcodeId equals postcode.PostcodeId into custPCTmp
                from custPC in custPCTmp.DefaultIfEmpty()
                orderby customer.CustomerId
                select new
                {
                    CustomerName = customer.CustomerName,
                    TownName = custPC == null ? string.Empty : custPC.TownName
                };

// This line causes the error. If it is commented, the error is not thrown and it works.
customers = customers.Take(100);

// This is where the exception is thrown and it doesn't go to the database (does not appear in SQL Profiler).
List<CustomerListItemViewModel> customersList = customers.Select(c => new CustomerListItemViewModel
{
    CustomerName = c.CustomerName,
    TownName = c.TownName
}).ToList();

Customer table:

Postcode table:

Exception:
System.ArgumentException was unhandled by user code
HResult=-2147024809
Message=Expression of type 'Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ValueBufferShaper' cannot be used for parameter of type 'Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.IShaper1[MyProject.Web.MVC.Models.Postcode]' of method 'System.Collections.Generic.IEnumerable1[Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor+TransparentIdentifier2[Microsoft.EntityFrameworkCore.Storage.ValueBuffer,System.Collections.Generic.IEnumerable1[MyProject.Web.MVC.Models.Postcode]]] _GroupJoin[ValueBuffer,Postcode,Nullable1,TransparentIdentifier2](Microsoft.EntityFrameworkCore.Query.RelationalQueryContext, System.Collections.Generic.IEnumerable1[Microsoft.EntityFrameworkCore.Storage.ValueBuffer], Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.IShaper1[Microsoft.EntityFrameworkCore.Storage.ValueBuffer], Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.IShaper1[MyProject.Web.MVC.Models.Postcode], System.Func2[MyProject.Web.MVC.Models.Postcode,System.Nullable1[System.Int32]], System.Func3[Microsoft.EntityFrameworkCore.Storage.ValueBuffer,System.Collections.Generic.IEnumerable1[MyProject.Web.MVC.Models.Postcode],Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor+TransparentIdentifier2[Microsoft.EntityFrameworkCore.Storage.ValueBuffer,System.Collections.Generic.IEnumerable1[MyProject.Web.MVC.Models.Postcode]]], Microsoft.EntityFrameworkCore.Query.Internal.GroupJoinInclude, Microsoft.EntityFrameworkCore.Query.Internal.GroupJoinInclude)' Parameter name: arguments[3] ParamName=arguments[3] Source=System.Linq.Expressions StackTrace: at System.Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arguments, ParameterInfo pi, String methodParamName, String argumentParamName, Int32 index) at System.Dynamic.Utils.ExpressionUtils.ValidateArgumentTypes(MethodBase method, ExpressionType nodeKind, ReadOnlyCollection1& arguments, String methodParamName)
at System.Linq.Expressions.Expression.Call(Expression instance, MethodInfo method, IEnumerable1 arguments) at System.Linq.Expressions.MethodCallExpressionN.Rewrite(Expression instance, IList1 args)
at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.ExpressionVisitorBase.Visit(Expression node)
at System.Linq.Expressions.ExpressionVisitor.VisitAndConvert[T](ReadOnlyCollection1 nodes, String callerName) at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.QuerySourceUpdater.VisitMethodCall(MethodCallExpression methodCallExpression) at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.ExpressionVisitorBase.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitMainFromClause(MainFromClause fromClause, QueryModel queryModel) at Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.VisitQueryModel(QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor.VisitQueryModel(QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryModelVisitor.VisitQueryModel(QueryModel queryModel) at Microsoft.EntityFrameworkCore.Query.EntityQueryModelVisitor.CreateQueryExecutor[TResult](QueryModel queryModel) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](Expression query, INodeTypeProvider nodeTypeProvider, IDatabase database, ILogger logger, Type contextType) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass19_01.b__0()
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) at Remotion.Linq.QueryableBase1.GetEnumerator()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at MyProject.Web.MVC.Controllers.CustomersController.PopulateListViewModel(String q, CustomerListViewModel model)
at MyProject.Web.MVC.Controllers.CustomersController.Index(String q, String viewMode)
at lambda_method(Closure , Object , Object[] )
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.d__27.MoveNext()
InnerException:

[maumar]

Try putting Take as the last operation (after select)

[maumar]

Note to Triage/self: could be related to #7096

[rasikaw-is]

I tried below and got the same error:

// LINQ left outer join.
var customers = (from customer in _db.Customer
                join postcode in _db.Postcode
                    on customer.PostcodeId equals postcode.PostcodeId into custPCTmp
                from custPC in custPCTmp.DefaultIfEmpty()
                orderby customer.CustomerId
                select new
                {
                    CustomerName = customer.CustomerName,
                    TownName = custPC == null ? string.Empty : custPC.TownName
                }).Take(10);

[maumar]

Try putting it after the second Select (I.e the one that projects CustomerListItemViewModel), alternatively add ToList after Take, before that second select, it shouldn't impact performance but greatly simplifies the query (by having Take as the last operation)

[rasikaw-is]

Yes, putting it after the second select, where the projection is done to CustomerListItemViewModel, works.

[maumar]

This works in the current bits and produces the following sql:

SELECT TOP(@__p_1) [customer].[CustomerName], CASE
	WHEN [postcode].[Id] IS NULL
	THEN @__Empty_0 ELSE [postcode].[TownName]
END
FROM [Customers] AS [customer]
LEFT JOIN [Postcodes] AS [postcode] ON [customer].[PostcodeId] = [postcode].[Id]
ORDER BY [customer].[Id]