Hi,

I'm using the LLBLGen Pro Runtime Framewok Version 5.7.6 using the "adapter" style.

I just spent two days investigating a problem with prefetch paths. I managed to solve the problem on my end. But I still believe that this is a bug, or should at least be handled differently by LLBLGen.

Error case

I have a table called "TLK" with 4 PK fields (TLK_PK1, TLK_PK2, TLK_PK3, TLK_PK4). This table has a foreign key to itself, consisting of 4 fields (TLK_FK1, TLK_FK2, TLK_FK3, TLK_FK4).

If I add this foreign key to the prefetch path of an EntityCollection, then the prefetch path does not work when the main EntityCollection has more than 12 entities.

Here is the code:

var filter = new RelationPredicateBucket();
filter.PredicateExpression.AddWithAnd(TLKFields.TLK_Date >= new DateTime(2022,1,1));
filter.PredicateExpression.AddWithAnd(TLKFields.TLK_Date <= new DateTime(2022,1,1));

var prefetch = new PrefetchPath2(TLKEntity.EntityType);
prefetch.Add(TLKEntity.PrefetchPathTLK_FK1_FK2_FK3_FK4_EntityCollection);

var collection = new EntityCollection<TLKEntity>();
adapter.FetchEntityCollection(collection, filter, prefetch);

When the collection variable has more than 12 entities, then the prefetched EntityCollection is always empty. When the collection variable has less than 12 entities, then the prefetched EntityCollections are correct.

First question: is this a bug?

I'd say it is. I believe the above code should work.

Analysis

The 12-entity-limit is due to the fact that LLBLGen will generate a different subquery for the prefetched Entities, depending on how many parameters there are.

• If there are 50 or less, then the prefetch query will identify its records with the actual values passed as parameters. With 4 FK fields and 12 entities, there will be 4*12 = 48 parameters. So 12 or less entities will use this method. (The value 50 comes from the ParameterisedPrefetchPathThreshold property of the adapter.) This method works correctly.

• If there are more than 50 parameters, then the prefetch query will identify its records with a subquery. This method does not work here.

My initial fix was adding a limit parameter to the FetchEntityCollection call (Integer.MaxValue). This will force the first variant. However, at some point the number of SQL parameters will lead to an SqlException (The incoming request has too many parameters. The server supports a maximum of 2100 parameters.)

So I looked at the query generated for the prefetch by method 2. Here it is:

SELECT [db].[dbo].[TLK].*
  FROM [db].[dbo].[TLK] 
 WHERE (  EXISTS (SELECT [db].[dbo].[TLK].[TLK_PK1] 
                  FROM [db].[dbo].[TLK]
                  WHERE ( [db].[dbo].[TLK].[TLK_FK1] = [db].[dbo].[TLK].[TLK_PK1] 
                   AND [db].[dbo].[TLK].[TLK_FK2] = [db].[dbo].[TLK].[TLK_PK2] 
                   AND [db].[dbo].[TLK].[TLK_FK3] = [db].[dbo].[TLK].[TLK_PK3] 
                   AND [db].[dbo].[TLK].[TLK_FK4] = [db].[dbo].[TLK].[TLK_PK4])))

As you can see, this is obviously wrong. The query uses the same table name in the inner query and in the outer query. It should use an alias on the inner query. This also explains why only this kind of prefetch doesn't work: because it's a self-referencing foreign key, which is not the most common thing.

I can force the query to use an alias on the main table, by changing the above code as follows (adding SelectListAliasand SetObjectAlias calls):

var filter = new RelationPredicateBucket();
filter.SelectListAlias = "T1"; // <-- Added
filter.PredicateExpression.AddWithAnd(TLKFields.TLK_Date.SetObjectAlias("T1") >= new DateTime(2022,1,1)); // <-- SetObjectAlias Added
filter.PredicateExpression.AddWithAnd(TLKFields.TLK_Date.SetObjectAlias("T1") <= new DateTime(2022,1,1)); // <-- SetObjectAlias Added

var prefetch = new PrefetchPath2(TLKEntity.EntityType);
prefetch.Add(TLKEntity.PrefetchPathTLK_FK1_FK2_FK3_FK4_EntityCollection);

var collection = new EntityCollection<TLKEntity>();
adapter.FetchEntityCollection(collection, filter, prefetch);

This generates the following, correct SQL query:

SELECT [db].[dbo].[TLK].*
 FROM [db].[dbo].[TLK] 
 WHERE (  EXISTS (SELECT [LPA_T1].[TLK_PK1] 
                  FROM [db].[dbo].[TLK] [LPA_T1] 
                  WHERE ( [db].[dbo].[TLK].[TLK_FK1] = [LPA_T1].[TLK_PK1]
                   AND [db].[dbo].[TLK].[TLK_FK2] = [LPA_T1].[TLK_PK2] 
                   AND [db].[dbo].[TLK].[TLK_FK3] = [LPA_T1].[TLK_PK3] 
                   AND [db].[dbo].[TLK].[TLK_FK4] = [LPA_T1].[TLK_PK4])))

So I do have a workaround. But I do believe that this should work out of the box.

Thanks!

andreas

Please post the table definition and if possible the project file so we can see how it's setup to build the repro case. I've moved this thread to helpdesk so you can safely do so. If you can't do this for some reason, please give the table definition and any specifics (like inheritance) for the entity. The multi-field selfreferencing relationship is clear, so if those are the only specifics, I think we can work with that. I agree it looks like a bug.

Btw, the '12' entity limit you ran into is due to the default of 50 for the ParameterizedPrefetchPathThreshold for optimized prefetch path fetching. You can set this to a higher value so you won't get the subquery using query. https://www.llblgen.com/Documentation/5.9/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Adapter/gencode_prefetchpaths_adapter.htm#optimizing-prefetch-paths

Thanks. If it were not for the actual project file and sql I'd be happy for this to sit on the open forum. Someone might stumble across it.

So I just posted the CREATE TABLE script and a cleaned up project file (the actual project file is 2 MB). The foreign key is called "FK_TLK_TLK". The column names are slightly different from what I posted. There is nothing fancy (inheritance or else) in the project. It's a plain and simple mapping (with some type converters).

I did find out where the 12 entity limit came from. It's part of the analysis section of my post. And of course, I already tried what you suggested, but that has it's problems too which I also included in the post. I came prepared

Alright, I've moved it back to bugs & issues We'll have a look at it on Monday

Fixed in hotfix builds 5.8.5 and 5.9.1

Great, Thanks!

I removed the files since they are not needed anymore.