Click here to Skip to main content
15,039,359 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

i am getting following error




System.InvalidOperationException: Unable to translate collection subquery in projection since the parent query doesn't project key columns of all of it's tables which are required to generate results on client side. This can happen when trying to correlate on keyless entity or when using 'Distinct' or 'GroupBy' operations without projecting all of the key columns.
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyCollectionJoin(Int32 collectionIndex, Int32 collectionId, Expression innerShaper, INavigationBase navigation, Type elementType, Boolean splitQuery)
   at Microsoft.EntityFrameworkCore.Query.Internal.CollectionJoinApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitNew(NewExpression node)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.CollectionJoinApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at MetroAntiques.DataAccess.Repository.CustomerRepository.Load(CustomerSearchModel customerSearchModel, Int32 page, Int32 limit) in D:\METRO\source\app\MetroAntiquesApi\MetroAntiques.DataAccess\Repository\CustomerRepository.cs:line 82
   at MetroAntiques.Service.CustomerService.GetCustomer(CustomerSearchModel customerSearchModel, Int32 page, Int32 limit) in D:\METRO\source\app\MetroAntiquesApi\MetroAntiques.Service\CustomerService.cs:line 38
   at MetroAntiquesApi.Controllers.CustomerController.Get(CustomerSearchModel searchModel, Int32 page, Int32 limit) in D:\METRO\source\app\MetroAntiquesApi\MetroAntiques.Api\Controllers\CustomerController.cs:line 40
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at NSwag.AspNetCore.Middlewares.SwaggerUiIndexMiddleware.Invoke(HttpContext context)
   at NSwag.AspNetCore.Middlewares.RedirectToIndexMiddleware.Invoke(HttpContext context)
   at NSwag.AspNetCore.Middlewares.OpenApiDocumentMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)



What i am trying to do is

I have Customer, Address(a customer have multiple addresses ) and OrderHistory(can be Multiple for same customer) table in Sql Database.

I need to fetch the record CustomerId, CustomerAddress (can be multiple) and RecentOrder date from OrderHistory (single record ).

Below is my Model structure

C#
Customer
{        
    public string customerId { get; set; }
    public virtual ICollection<addresses> addresses { get; set; }
    [NotMapped]
    public DateTime LastOrderDate {get; set;}   
}

OrderHistory
{
   public string customerId { get; set; }
   public string orderdate { get; set; }
}

Address
{
   public string customerId { get; set; }
   public string Address { get; set; }
}


What I have tried:

C#
var order = from objOrder in _context.Order
                                group objOrder by objOrder.customerId
                                into objOrderList
                                select new
                                {
                                    customerID = objOrderList.Key,
                                    LastOrderDate = objOrderList.Max(x => x.orderdate)
                                };


 var custList = from cust in _context.customers.Include(x=>x.address)
                           join obj in order
                           on cust.customerID equals obj.customerID
                           select (new Customer
                           {
                               customerID = cust.customerID,
                               LastOrderDate = order.LastOrderDate,
                               addresses = cust.addresses
                           });

var cust1 = custList.Skip(page * limit).Take(limit).ToList();


throwing exception

Unable to translate collection subquery in projection since the parent query doesn't project key columns of all of it's tables which are required to generate results on client side. This can happen when trying to correlate on keyless entity or when using 'Distinct' or 'GroupBy' operations without projecting all of the key columns.
Posted
Updated 27-Apr-21 21:55pm
v6
Comments
OriginalGriff 28-Apr-21 2:02am
   
And?
What does it do that you didn't expect, or not do that you did?
What have you tried to do to find out why?
Are there any error messages, and if so, where and when? What did you do to make them happen?

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
Use the "Improve question" widget to edit your question and provide better information.
Siddharth Rai 28-Apr-21 3:57am
   
Sorry for inconvenience, I tried to provide better information by improving the question.

1 solution

Try to use ToList() method.

C#
var custList = (from cust in _context.customers.Include(x=>x.address)
                           join obj in order
                           on cust.customerID equals obj.customerID
                           select (new Customer
                           {
                               customerID = cust.customerID,
                               LastOrderDate = order.LastOrderDate,
                               addresses = cust.addresses
                           })).ToList();

var cust1 = custList.Skip(page * limit).Take(limit).ToList();
   
Comments
Siddharth Rai 28-Apr-21 2:52am
   
showing same error.

when i am converting both order as well as customer to List then it is working ,but i think this could cost performance .
Below code sample is working fine but in that case execution time will be higher.

var custList = from cust in _context.customers.Include(x=>x.address).ToList()
join obj in order.ToList()
on cust.customerID equals obj.customerID
select (new Customer
{
customerID = cust.customerID,
LastOrderDate = order.LastOrderDate,
addresses = cust.addresses
})

var cust1 = custList.Skip(page * limit).Take(limit).ToList();

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900