Nested queries with prefetch path (With()) throw exception

Posts   
 
    
Posts: 36
Joined: 19-Dec-2022
# Posted on: 16-Nov-2023 17:21:04   

Hi,

for some introduction see here

The next odd behavior is:

2. Nested queries with prefetch path (With()) throw exception

This a very simple query I expected to work, but it throws an exception and it works without the "With()". The query makes not much sense though, but I hope you see what I mean:

var customer = 
  (from customer in metaData.Customer
    select new
    {
      Customer = customer,
      Customer2 = metaData.Customer
        .With(c => c.Orders)
        .First(c => c.CustomerId == customer.CustomerId)
    })
    .First();

It throws the following exception:

Expression of type 'Entities.EntityClasses.CustomerEntity' cannot be used for parameter of type 'System.Linq.IQueryable`1[EntityClasses.CustomerEntity]' of method 'System.Linq.IQueryable`1[Entities.EntityClasses.CustomerEntity] With[CustomerEntity](System.Linq.IQueryable`1[Entities.EntityClasses.CustomerEntity], System.Linq.Expressions.Expression`1[System.Func`2[Entities.EntityClasses.CustomerEntity,System.Object]][])' (Parameter 'arg0')
  at System.Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arguments, ParameterInfo pi, String methodParamName, String argumentParamName, Int32 index)
  at System.Linq.Expressions.Expression.Call(Expression instance, MethodInfo method, Expression arg0, Expression arg1)
  at System.Linq.Expressions.Expression.Call(Expression instance, MethodInfo method, IEnumerable`1 arguments)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallWhere(MethodCallExpression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallFirstSingle(MethodCallExpression expressionToHandle, Boolean isSingle, Boolean isDefault)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpressionList(ReadOnlyCollection`1 listToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleNewExpression(NewExpression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleNewExpression(NewExpression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleLambdaExpression(LambdaExpression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallSelect(MethodCallExpression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallFirstSingle(MethodCallExpression expressionToHandle, Boolean isSingle, Boolean isDefault)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleQueryableExtensionMethod(MethodCallExpression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallPerType(MethodCallExpression expressionToHandle, Type declaringType)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleMethodCallExpression(MethodCallExpression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
  at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression)
  at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.PerformExecute(Expression expression, Type resultType)
  at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 17-Nov-2023 08:55:44   

Prefetch path calls are only suitable for an entity query and the entity query has to be the root query. What you're doing here is fetching a projection. You fetch entities in the projection which are converted to nested queries, however these can't have prefetch paths. Prefetch paths are fetched in a graph like manner and require multiple queries (one per node of the graph). Nested queries are similar but you can't mix them here as you'd introduce a graph inside a node.

Not sure what you want to accomplish however with the prefetch paths in queries like this: it might be better to rewrite this as a join query and additional where clause; prefetch paths are for fetching the data into a graph of objects. If the data is merely used for querying (but on the client) it's wiser to use joins and a where clause

Frans Bouma | Lead developer LLBLGen Pro
Posts: 36
Joined: 19-Dec-2022
# Posted on: 17-Nov-2023 09:24:24   

Thanks for the explaination and I understand it, but for the context, my main goal is the follwing: As explained in https://www.llblgen.com/tinyforum/Thread/28780 we are currently migrating from self-servicing to adapter and I try to find strategies for case where we currently use lazy loading, which doesn't work with Adapter. One of the main problems I identified is when we have a projection and call methods in-memory and in these methods lazy loading happens on passed entities.

I tried several methods: 1. Prefetching passed entitiy in the source query (see https://www.llblgen.com/tinyforum/Thread/28780)

bool CustomerHasOrders(CustomerEntity customer)
{
    return customer.Orders.Any();
}

var customerModel = 
  (from c in metaData.Customer.With(c => Orders)
    select new
    {
      Customer = c,
      HasOrders = CustomerHasOrders(c)
     })
   .First();

Doesn't work as Walaa pointed out correctly. I missed that in the docs. 2. The variant from this very thread. The original code was indeed, but I simplified it:

var customer = 
  (from customer in metaData.Customer
    select new
    {
      Customer = customer,
      Customer2 = CustomerHasOrders(metaData.Customer
           .With(c => c.Orders)
           .First(c => c.CustomerId == customer.CustomerId)
       )
    })
    .First();
  1. And the last variant is the one from https://www.llblgen.com/tinyforum/Thread/28782/1

And you're right. An additional option is to rewrite the whole query to use explicit joins and yes this would have been the better variant in the first place. But for our migration it's also the most expensive one. Replacing the lazy loaded properties with prefetches is normally quite easy. Rewriting lazy loaded properties to use joins is more complex. I hoped to avoid that, at least for the first iteration of the migration.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 17-Nov-2023 09:27:31   

Could you give the original code for selfservicing without changes and with the linqmetadata setup? So we have the whole picture, thanks simple_smile

Frans Bouma | Lead developer LLBLGen Pro
Posts: 36
Joined: 19-Dec-2022
# Posted on: 17-Nov-2023 09:38:35   

Yes, of course. How should I give it to you? Or should I open a new thread a the secure helpdesk section and add it as attachment there?

Walaa avatar
Walaa
Support Team
Posts: 14975
Joined: 21-Aug-2005
# Posted on: 17-Nov-2023 09:49:47   

Yes, please use a helpdesk thread.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 17-Nov-2023 10:54:18   

Markus@Init wrote:

Yes, of course. How should I give it to you? Or should I open a new thread a the secure helpdesk section and add it as attachment there?

I didn't mean the whole application btw, just the query method. You can copy/paste that in a thread

Frans Bouma | Lead developer LLBLGen Pro