FetchEntityCollection - Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?
We'll look into it! Btw, the project attached in this helpdesk thread https://www.llblgen.com/tinyforum/Message/Goto/150880 is that usable for us to look at your model or are there differences that are essential for this problem? If so, please attach the project file to a secure helpdesk thread (I can also move this thread to helpdesk so you can attach it here) 
As it's a prefetch path issue, it could be the ParameterizedPrefetchPathThreshold property on the adapter could be set to e.g. 1000 and you can page through the set with 1000 rows at a time, if the issue is related to the engine creating a correlated subquery (or at least trying to) to filter parent rows which fails. As you said paging works, which uses an IN query instead of a correlated subquery.
Thank you
That project is usable.
The failing user result has 7910 rows. I have 329 distinct managers in the db and most of these are probably pre-fetched here.
I am able to fetch 12000+ rows having this filter active:
predicateExpression.AddWithOr(UserFields.DisplayName.SetObjectAlias("LastUpdatedByUser") == fieldValue);
FilterToUse.Relations.Add(UserEntity.Relations.UserEntityUsingUserIdUpdatedByUserId, "LastUpdatedByUser");
It only fails when I add the DynamicRelation.
Could you post the full query that you're running (the C# part, so the code that formulates the llblgen pro query, including prefetch path setup), if that's possible. I don't have a clear image what the query looks like, as you mention Company but I don't see that back in the code in the first post. Thanks!
(I suspect it's an alias issue, as in 5.9.1 a bug related to aliases of correlated subqueries was fixed, but it might be there's an edge case which it doesn't cover)
hotchill wrote:
It should be Computer that inherits User, not Company. Just noticed the same and corrected the bug report. Sorry about that. Building the query is distributed across several methods with conditions involved. I will try locate and paste the code.
Alright, that makes it a bit more clear I'll see if we can reproduce it regardless.
public static void Main(string[] args)
{
InterceptorCore.Initialize("ServiceManager Test");
var ppath = new PrefetchPath2(EntityType.UserEntity);
ppath.Add(UserEntity.PrefetchPathEndUserSetBy);
ppath.Add(UserEntity.PrefetchPathManager);
var filterToUse = new RelationPredicateBucket();
filterToUse.Relations.Add(new DynamicRelation(UserFields.Id,
JoinHint.Inner,
EntityType.DepartmentEntity,
string.Empty, "Department",
UserFields.Id == DepartmentFields.ManagerId.Source("Department")));
var collection = new EntityCollection<UserEntity>();
using(var adapter = new DataAccessAdapter())
{
adapter.ParameterisedPrefetchPathThreshold = 1;
adapter.FetchEntityCollection(collection, filterToUse, ppath);
}
Console.WriteLine("# of users fetched: {0}", collection.Count);
}
reproduces the problem in our smaller model based on your database (User, Computer, Department and 2 relationships, EndUserSetBy and Manager)
The 'workaround' is to set the ParameterisedPrefetchPathThreshold to a value that's higher than the number of users fetched with the root query. This likely won't work in your case if the number of results are > 2200 as that's the limit on the number of parameters for a SQL query. This is by no means a viable workaround tho, but could help till we found a solution for this.
(edit) specifying a relationship in the model, even aliasing it in the join, works too
filterToUse.Relations.Add(DepartmentEntity.Relations.UserEntityUsingManagerId, "Department", string.Empty, JoinHint.Inner);
so this is likely related to the fix you've run into earlier and it skips DynamicRelation instances for cleaning up the temporary aliases it assigns. (We use a visitor pattern implementation to visit the tree of elements, it looks like it skips over these elements)
(edit) Thankfully it's friday 
}
base.Traverse(objectToTraverse);
foreach(IRelation relation in objectToTraverse)
{
EntityRelation relationAsEntityRelation = relation as EntityRelation;
if(relationAsEntityRelation == null)
{
continue;
}
Fixing...
Great to see that you are on to the cause
I already tried setting ParameterisedPrefetchPathThreshold to a very high value. That did not work and now I know the reason. Thanks
We've fixed it. It's in hotfix build of today, SD.LLBLGen.Pro.ORMSupportClasses.5.9.3-hotfix-20221118.nupkg. Dynamic relationships were previously not anticipated as we assumed the relationships that did we had to take care for in the relationship-with-self in prefetch path filters were entity relationships and we overlooked this possibility. It should work now, if not, please let us know.
hotchill wrote:
Success! Thank you. That was quick. Well done!
thanks your write up pointed quickly to the area where it could be, was still a struggle to get it in, but hopefully we now have covered all situations
Have a great weekend.
You too!