Hello,

I've got a really weird issue where subsequent GetMulti queries produce an exception The multi-part identifier could not be bound. The first query always executes with out problems. The second time i try to execut it sometimes throw an exception and sometimes it won't - depending on the customer id provided in the filter. For example querying for customer id = 1 goes fine, then for 2 goes fine, and if I ask again for 1 it throws an exception.

Do you have any idea what's going on and how to fix it? Only change I can remember is updating to LLBLgen 5.10.1.

"Message": "An exception was caught during the execution of a retrieval query: The multi-part identifier \"__LLBLPP.CartID\" could not be bound.\r\nThe multi-part identifier \"__LLBLPP.ShopInstance\" could not be bound..

Here's the query executed that produces the error:

Query: SELECT [CDNXL_MAKRO_ABRA].[dbo].[ProSearchCartItem].[Active], [CDNXL_MAKRO_ABRA].[dbo].[ProSearchCartItem].[AutoOptionType], [CDNXL_MAKRO_ABRA].[dbo].[ProSearchCartItem].[AutoQuantityType], [CDNXL_MAKRO_ABRA].[dbo].[ProSearchCartItem].[CartID] AS [CartId], [CDNXL_MAKRO_ABRA].[dbo].[ProSearchCartItem].[Deleted], [CDNXL_MAKRO_ABRA].[dbo].[ProSearchCartItem].[ID] AS [Id], [CDNXL_MAKRO_ABRA].[dbo].[ProSearchCartItem].[ParentID] AS [ParentId], [CDNXL_MAKRO_ABRA].[dbo].[ProSearchCartItem].[ProductID] AS [ProductId], [CDNXL_MAKRO_ABRA].[dbo].[ProSearchCartItem].[ProductQuantity], [CDNXL_MAKRO_ABRA].[dbo].[ProSearchCartItem].[ShopInstance] 
FROM [CDNXL_MAKRO_ABRA].[dbo].[ProSearchCartItem] 
WHERE ( ( ( [__LLBLPP].[CartID] = @p1 AND [__LLBLPP].[ShopInstance] = @p2)))
    Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 18551640.
    Parameter: @p2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 200.

Here's the prefetchPath I'm using for the GetMulti query.

var customerPrefetchPath = new PrefetchPath((int)EntityType.ProSearchCustomerEntity);
                    customerPrefetchPath.Add(ProSearchCustomerEntity.PrefetchPathSearchAddressDelivery);
                    customerPrefetchPath.Add(ProSearchCustomerEntity.PrefetchPathSearchAddressBilling);
                    customerPrefetchPath.Add(ProSearchCustomerEntity.PrefetchPathSearchAddressContact);
                    customerPrefetchPath.Add(ProSearchCustomerEntity.PrefetchPathSearchCustomerExternalAuthentications);
                    customerPrefetchPath.Add(ProSearchCustomerEntity.PrefetchPathSearchCustomerPasswordResetRequests);
                    customerPrefetchPath.Add(ProSearchCustomerEntity.PrefetchPathSearchMailArchives);
                    customerPrefetchPath.Add(ProSearchCustomerEntity.PrefetchPathSearchCustomerConsents);
                    customerPrefetchPath.Add(ProSearchCustomerEntity.PrefetchPathProSearchCustomerWatchHistoryItems);
                    customerPrefetchPath.Add(ProSearchCustomerEntity.PrefetchPathSearchCustomerLoginTokens);

                    {
                        var wishlistPrefetchPatch = customerPrefetchPath.Add(ProSearchCustomerEntity.PrefetchPathSearchWhishlist);
                        wishlistPrefetchPatch.SubPath.Add(ProSearchWhishlistEntity.PrefetchPathSearchWhishlistItems);
                    }

                    {
                        var cartPretetchPath = customerPrefetchPath.Add(ProSearchCustomerEntity.PrefetchPathSearchCart);

                        {
                            var cartItemPretetchPath = cartPretetchPath.SubPath.Add(ProSearchCartEntity.PrefetchPathSearchCartItems);
                            cartItemPretetchPath.SubPath.Add(ProSearchCartItemEntity.PrefetchPathSearchCartItemChilds);
                        }
                    }

                    {
                        var ordersPrefetchPath = customerPrefetchPath.Add(ProSearchCustomerEntity.PrefetchPathSearchOrders);
                        ordersPrefetchPath.SubPath.Add(ProSearchOrderEntity.PrefetchPathSearchAddressBilling);
                        ordersPrefetchPath.SubPath.Add(ProSearchOrderEntity.PrefetchPathSearchAddressDelivery);
                        ordersPrefetchPath.SubPath.Add(ProSearchOrderEntity.PrefetchPathSearchAddressContact);
                        ordersPrefetchPath.SubPath.Add(ProSearchOrderEntity.PrefetchPathSearchCustomerConsents);
                        ordersPrefetchPath.SubPath.Add(ProSearchOrderEntity.PrefetchPathSearchAttachments);
                        ordersPrefetchPath.SubPath.Add(ProSearchOrderEntity.PrefetchPathSearchMailArchives);
                        ordersPrefetchPath.SubPath.Add(ProSearchOrderEntity.PrefetchPathSearchOrderDiscounts);
                         
                        { 
                            var transactionFilter = new PredicateExpression();
                            transactionFilter.Add(ProSearchOrderPaymentTransactionFields.Status != "CANCELED");

                            var transactionsPrefetch = ordersPrefetchPath.SubPath.Add(ProSearchOrderEntity.PrefetchPathSearchOrderPaymentTransactions);
                            transactionsPrefetch.Filter = transactionFilter;
                        }

                        {
                            var orderReturnsPrefetch = ordersPrefetchPath.SubPath.Add(ProSearchOrderEntity.PrefetchPathSearchOrderReturns);
                            orderReturnsPrefetch.SubPath.Add(ProSearchOrderReturnEntity.PrefetchPathReturnDeliveryAddress);
                            orderReturnsPrefetch.SubPath.Add(ProSearchOrderReturnEntity.PrefetchPathSearchOrderReturnItems);
                        }

                        {
                            var orderItemsPrefetchPath = ordersPrefetchPath.SubPath.Add(ProSearchOrderEntity.PrefetchPathSearchOrderItems);
                            orderItemsPrefetchPath.SubPath.Add(ProSearchOrderItemEntity.PrefetchPathSearchOrderItemsChilds);
                        }

                        {
                            var shipmentPretetchPath = ordersPrefetchPath.SubPath.Add(ProSearchOrderEntity.PrefetchPathSearchOrderShipments);
                            shipmentPretetchPath.SubPath.Add(ProSearchOrderShipmentEntity.PrefetchPathSearchOrderShipmentItems);
                        }
                    }

                    _CustomerPrefetchPath = customerPrefetchPath;

Problem went away (I think) when I stopped using the same Prefetch Path object for subsequent queries. Yet, before 5.10.1 it worked fine for years. Am I not supposed to do that or is it a bug?

The prefetch path nodes contain the data fetched for that node after a query. It can be if you keep the path around, it might be used in multiple requests/threads, which will lead to issues. The objects are meant to be used in a query and then tossed away (they're fast to create).

We'll see if we can reproduce it with re-using the same path object in subsequential queries with different root entities. The system does clear the containers in the nodes when fetching data so that's not it.

Can't reproduce it, works fine:

[Test]
public void MultiUsageSinglePrefetchPathTest()
{
    try
    {
        CustomerEntity.EnableLazyLoading = false;
        var ppath = new PrefetchPath(EntityType.CustomerEntity);
        ppath.Add(CustomerEntity.PrefetchPathOrders);
        var customers = new CustomerCollection();
        customers.GetMulti(CustomerFields.Country == "USA", ppath);
        Assert.AreEqual(13, customers.Count);
        customers.Clear();
        customers.GetMulti(CustomerFields.Country == "Germany", ppath);
        Assert.AreEqual(11, customers.Count);
        foreach(var c in customers)
        {
            foreach(var o in c.Orders)
            {
                Assert.AreEqual(c.CustomerId, o.CustomerId);
            }
        }
    }
    finally
    {
        CustomerEntity.EnableLazyLoading = true;
    }
}

Looking at your path, I don't see anything out of the ordinary too... so I think it's about sharing the path among threads, but not sure if that's happening in your application (e.g. a desktop app where querying is happening on the main thread you won't see sharing of objects among threads, but a webapp will)