The multi-part identifier could not be bound

Posts   
 
    
18548
User
Posts: 5
Joined: 22-Jun-2023
# Posted on: 24-Aug-2023 12:40:49   

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;
18548
User
Posts: 5
Joined: 22-Jun-2023
# Posted on: 24-Aug-2023 12:56:38   

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?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 25-Aug-2023 08:21:08   

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.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39712
Joined: 17-Aug-2003
# Posted on: 25-Aug-2023 09:57:46   

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)

Frans Bouma | Lead developer LLBLGen Pro