exec sp_executesql N'SELECT [customers].[dbo].[CUSTOMERS].[CUG] AS [Cug], 
<snip fields>
 WHERE ( [customers].[dbo].[CUSTOMERS].[CUG] IN 
(@Cug1, @Cug2, ))', 
N'@Cug1 int,@Cug2 int,', 
@Cug1 = 0, @Cug2 = 5661

Hi

I have a problem that I hope somebody can explain. The above query is generated by LLBLGen when trying to retrieve the related customers for two other entities in a collection (related on the CUG field). One of the entities has a CUG of 5661 (seen in the second parameter),but the other has a NULL CUG field which seems to have been translated in the first parameter to a 0.

This is causing a problem as our Customer table actually has record with a CUG of 0 (historical reasons - dont ask...!) so LLBLGen is retrieving this one incorrectly.

Is there any way around this...? I'm assuming that the NULL is translated to a 0 as normally ID fields would start from 1 and would not be expected to have an entry for 0...?

TIA

Matt

the 0 is coming from a new entity I think. I'll check the prefetch path (it's prefetch path code) optimizer logic to see if it skips new root entities or not. that's not it, as the root entities are read from the db.

Very strange. Could you formulate the prefetch path construction code here and describe the relation types used so I have a clear picture what's going on? Thanks.

Blimey that was quick...

        Dim pfp As New PrefetchPath2(EntityType.WholesaleDiscountEntity)
        pfp.Add(WholesaleDiscountEntity.PrefetchPathCustomer)

        colDiscounts = New EntityCollection(New WholesaleDiscountEntityFactory)
        DA.FetchEntityCollection(colDiscounts, Nothing, pfp)

Tables Customers.CUG and WholesaleDiscount.CUG are both int's with no default. It is the primary key on the Customers table but is not autoincrementing. The relationship is defined in the database

    CONSTRAINT [FK_WholesaleDiscount_CUSTOMERS] FOREIGN KEY 
    (
        [CUG]
    ) REFERENCES [CUSTOMERS] (
        [CUG]
    ),

and was picked up automatically by LLBLGen on a schema refresh.

Thanks for the quick response !

The same thing is also happening on other relations with the same entity - just that there are no entries in the related tables with an ID of 0 so we get the expected results.

Ok, the WholeSaleDiscount entity is the FK side, the Customer is the PK side. You fetch all WholeSaleDiscount entities and their related Customer entities.

So it first fetches all WholeSaleDiscount entities (please turn on DQE tracing to see these queries being generated ('Troubleshooting and Debugging' in the documentation)). Then it checks if there are < 50 (or the set threshold ) entities fetched. Apparently there are 2. So it creates an IN (id, id, .... id) filter for the related entity, which is the customer.

So could you please check if there is a WholeSaleDiscount entity in the database which has '0' (thus not NULL) for the field CUG / customerid ?

SELECT [customers].[dbo].[WholesaleDiscount].[Id], [customers].[dbo].[WholesaleDiscount].[ProductType] AS [ProductTypeId], [customers].[dbo].[WholesaleDiscount].[ProductID] AS [ProductId], [customers].[dbo].[WholesaleDiscount].[CUG] AS [Cug], [customers].[dbo].[WholesaleDiscount].[NNG] AS [Nng], [customers].[dbo].[WholesaleDiscount].[WholesaleDiscount] FROM [customers].[dbo].[WholesaleDiscount] 

is the query that retieves the WholesaleDiscount entities

1   1       NULL    NULL    NULL       4.0
12  NULL    NULL    5661    001             32.0

is the result returned when this is run in QA. (NULL and 5661 is the CUG column) - no 0's anywhere...!

aha! good catch . Indeed, that NULL'ed FK shouldn't be used in the query. I'll file it as a bug and will fix it. Thanks for reporting. Normally this wouldn't be a problem, but as you said, if the pk side contains a table with that pk value, it's going to be problematic.

To work around it for now, pass in a filter on the Add() method where you add the Customer prefetch path node to filter on Customers with a PK > 0. This will help you continue with your work till this is fixed.

Thanks...

Now aren't you glad you agreed to extend my demo for me?

Hopefully the level of support you have demonstrated will help me get the agreement from higher up to purchase...

Thanks Again

Matt

Fixed in next build (uploaded in an hour). As you're a demouser and you use an extended version, it might be installing this demo will void your extended period. So install the demo again and copy the extension dlls/exe again over the newly installed demo. You'll lose the fixes in the designer released alsowith the updated demo, but these are pretty minor so you probably won't run into them. This fix is in the runtime libraries.