Linq query system.invalidoperationexception

Question

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 27-Apr-21 19:50pm

Siddharth Rai

Updated 27-Apr-21 21:55pm

v6

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2021-04-27T20:24:00

Solution 1

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();

Posted 27-Apr-21 20:24pm

Maciej Los

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();