FetchEntityCollection - Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?

Posts   
 
    
hotchill avatar
hotchill
User
Posts: 180
Joined: 22-Jan-2007
# Posted on: 17-Nov-2022 23:25:06   

Hi.

I get the following ORMRelationException calling FetchEntityCollection of the DataAccessAdapter class: Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?

Template group: Adapter

.Net version: .Net Framework 4.8

Db: Microsoft Sql Server version 13.0.4259.0

LLBLGen Pro version + buildnr.: 5.9.3-hotfix-20221013

Runtime library version: 5.9.3.0

The issue was introduced in version 5.9.1. It works in version 5.9.0. I have tried the following versions and they all have the issue: 5.9.1, 5.9.2, 5.9.3-hotfix-20221013 and 5.9.3-hotfix-20221116.

I am fetching from the User table. The Computer table inherits the User table. There is no other inheritance.

I use only one relation for the query:

FilterToUse.Relations.Add(new DynamicRelation(UserFields.UserId, JoinHint.Inner,
TvfCallFactory.GetActiveWorkPeriods("WorkPeriods", fromDateToShow.Value, toDateToShow), string.Empty,
UserFields.UserId == GetActiveWorkPeriodsResultFields.UserId.Source("WorkPeriods")));

When getting the exception, I see that the ORM has added a relation from User to Computer. This relation is the one referred to in the exception message.

The User table has a relation to self: Manager. This is prefetched. If I remove this prefetch, the fetch works. The user table also has three other relations to self: UpdatedBy, Responsible, EndDateSetBy. They are all prefetched without problem. The set of these users on the FK side is very small compared to the manager set.

I have attached the ORM profiler and see that the fetch of user entities works fine. It also starts the pre-fetching from the user table. I am not getting a failing query in profiler. It looks like it fails when building a prefetch query.

If I fetch a smaller number of entities, it works. I was able to get it working (very slow) by fetching in 50 at a time:

if (e.PageNumber == 0 && (_dateToShow.HasSelectedDate || _fromDateToShow.HasSelectedDate)) {
    // no paging (filtering or export to Excel) - Workaround for issue in FetchEntityCollection: "Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?"
    var pageNumber = 1;
    const int pageSize = 50;
    EntityCollection<UserEntity> userPage;
    if (sorterToUse == null || sorterToUse.Count == 0) {
        sorterToUse = new SortExpression(UserFields.UserId | SortOperator.Ascending);
    }
    do {                        
        userPage = new EntityCollection<UserEntity>();
        adapter.FetchEntityCollection(userPage, FilterToUse, pageSize, sorterToUse, e.PrefetchPath, pageNumber++, pageSize);
        foreach (var user in userPage) {
            e.ContainedCollection.Add(user);
        }
    } while (userPage.Count >= pageSize);
} else {
    adapter.FetchEntityCollection(e.ContainedCollection, FilterToUse, e.MaxNumberOfItemsToReturn, sorterToUse, e.PrefetchPath, e.PageNumber, e.PageSize);
}

Stack trace

at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryTextInternal(Boolean ansiJoins, String& nonAnsiWhereClause, String nonAnsiRootTableReference, String nonAnsiFieldSuffix)
   at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryText()
   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.AppendFromClause(QueryParameters parameters, QueryFragments destination, RetrievalQuery queryToAppendTo, IFieldPersistenceInfo[] fieldsPersistenceInfo, IEntityFieldCore[] selectList)
   at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQImpl(QueryParameters parameters, DbConnection connectionToUse, Boolean emitQueryHints)
   at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(QueryParameters parameters, DbConnection connectionToUse)
   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(QueryParameters parameters, DbConnection connectionToUse)
   at SD.LLBLGen.Pro.ORMSupportClasses.DbSpecificCreatorBase.CreateSubQuery(QueryParameters parameters)
   at SD.LLBLGen.Pro.DQE.SqlServer.SqlServerSpecificCreator.CreateSubQuery(QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.FieldCompareSetPredicate.ToQueryText(Boolean inHavingClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.PredicateExpression.ToQueryText(Boolean inHavingClause)
   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.AppendWhereClause(IPredicate filter, QueryFragments destination, IQuery query)
   at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQImpl(QueryParameters parameters, DbConnection connectionToUse, Boolean emitQueryHints)
   at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(QueryParameters parameters, DbConnection connectionToUse)
   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(QueryParameters parameters, DbConnection connectionToUse)
   at SD.LLBLGen.Pro.ORMSupportClasses.Adapter.QueryCreationManager.CreateSelectDQ(QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollectionInternal(QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPathFetcher.FetchPrefetchPath(QueryParameters rootNodeParameters, Boolean forceParameterizedPPath, ITransaction transactionToUse, Int32 parameterisedPrefetchPathThreshold, Action`1 fetchNodeFunc, Action`4 mergeManyToManyFunc)
   at SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.FetchPrefetchPath(QueryParameters rootNodeParameters, Boolean forceParameterizedPPath, ITransaction transactionToUse, Int32 parameterisedPrefetchPathThreshold, Action`1 fetchNodeFunc, Action`4 mergeManyToManyFunc)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollection(QueryParameters parameters)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass10_0.<FetchEntityCollection>b__0()
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Int32 pageNumber, Int32 pageSize)
   at Collabora.ServiceManager.SharePoint.Layouts.ServiceManager.PersonViewer.UserDsPerformSelect(Object sender, PerformSelectEventArgs2 e) in D:\Source\IdentityStream\Products\ServiceManager\IdentityStream.ServiceManager.Web\wwwroot\PersonViewer.aspx.cs:line 986
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-Nov-2022 09:07:07   

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) simple_smile

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.

Frans Bouma | Lead developer LLBLGen Pro
hotchill avatar
hotchill
User
Posts: 180
Joined: 22-Jan-2007
# Posted on: 18-Nov-2022 09:26:10   

Thank you simple_smile

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.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-Nov-2022 09:35:58   

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! simple_smile

(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)

Frans Bouma | Lead developer LLBLGen Pro
hotchill avatar
hotchill
User
Posts: 180
Joined: 22-Jan-2007
# Posted on: 18-Nov-2022 09:41:09   

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.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-Nov-2022 09:44:11   

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 simple_smile I'll see if we can reproduce it regardless.

Frans Bouma | Lead developer LLBLGen Pro
hotchill avatar
hotchill
User
Posts: 180
Joined: 22-Jan-2007
# Posted on: 18-Nov-2022 09:54:41   

Prefetch

UserDS.PrefetchPathToUse = new PrefetchPath2((int)EntityType.UserEntity) {
    UserEntity.PrefetchPathEndDateSetByUser,
    UserEntity.PrefetchPathManager,
    UserEntity.PrefetchPathResponsibleUser,
    UserEntity.PrefetchPathDepartmentRole,
    UserEntity.PrefetchPathUpdatedByUser,
    UserEntity.PrefetchPathTenant,
};

Filter

FilterToUse = new RelationPredicateBucket(new PredicateExpression(UserFields.ObjectTypeId == UserEntity.ObjectTypeEnum.User))
FilterToUse.PredicateExpression.Add(UserFields.EmploymentTypeInt != (int)UserEntity.TypeOfEmployment.OperationsUser &
    UserFields.EmploymentTypeInt != (int)UserEntity.TypeOfEmployment.ExternalGuestUser &
    UserFields.EmploymentTypeInt != (int)UserEntity.TypeOfEmployment.CloudOperationsUser &
    UserFields.EmploymentTypeInt != (int)UserEntity.TypeOfEmployment.ServiceAccount &
    UserFields.EmploymentTypeInt != (int)UserEntity.TypeOfEmployment.TestUser &
    UserFields.EmploymentTypeInt != (int)UserEntity.TypeOfEmployment.UnknownUser &
    UserFields.EmploymentTypeInt != (int)UserEntity.TypeOfEmployment.SupportUser &
    UserFields.EmploymentTypeInt != (int)UserEntity.TypeOfEmployment.LocalUser
);

Tenant filter A tenant filter is added by a method that supports adding the proper tenant filter to any entity query. The GetObjectAlias method returns the empty string for this scenario.

var field = UserFields.TenantId;
field = field.SetObjectAlias(GetObjectAlias(objectAlias));
var predicate = field == tenantId;
filter.PredicateExpression.Add(predicate);

Resulting sql (That gets generated and run ok also when fetching many rows)

SELECT [LPA_L1].[AccountExpiration]                               AS [F36_0],
       [LPA_L1].[AdditionalPhoneNumber]                           AS [F36_1],
       [LPA_L1].[AzureId]                                         AS [F36_2],
       [LPA_L1].[CanAuthenticateUsingAD]                          AS [F36_3],
       [LPA_L1].[City]                                            AS [F36_4],
       [LPA_L1].[Company]                                         AS [F36_5],
       [LPA_L1].[CompanyRoleId]                                   AS [F36_6],
       [LPA_L1].[CreatedDate]                                     AS [F36_7],
       [LPA_L1].[CriminalRecordLastChecked]                       AS [F36_8],
       [LPA_L1].[DeletedDate]                                     AS [F36_9],
       [LPA_L1].[DeletedInAD]                                     AS [F36_10],
       [LPA_L1].[DeletionDisabled]                                AS [F36_11],
       [LPA_L1].[Department]                                      AS [F36_12],
       [LPA_L1].[DepartmentRoleId]                                AS [F36_13],
       [LPA_L1].[Description]                                     AS [F36_14],
       [LPA_L1].[DisabledInAD]                                    AS [F36_15],
       [LPA_L1].[DisplayName]                                     AS [F36_16],
       [LPA_L1].[DistinguishedName]                               AS [F36_17],
       [LPA_L1].[Division]                                        AS [F36_18],
       [LPA_L1].[EmploymentPercentage]                            AS [F36_19],
       [LPA_L1].[EmploymentTypeInt]                               AS [F36_20],
       [LPA_L1].[EmploymentTypeTransitionNotificationSent]        AS [F36_21],
       [LPA_L1].[EndDate]                                         AS [F36_22],
       [LPA_L1].[EndDateAlertSentDate]                            AS [F36_23],
       [LPA_L1].[EndDateSetByUserId]                              AS [F36_24],
       [LPA_L1].[EndDateSetDate]                                  AS [F36_25],
       [LPA_L1].[EnquiryAlertFrequencyId]                         AS [F36_26],
       [LPA_L1].[ExternalIdentityProviderTenantId]                AS [F36_27],
       [LPA_L1].[ExternalIdentityProviderUserId]                  AS [F36_28],
       [LPA_L1].[FakeNationalIdentificationNumber]                AS [F36_29],
       [LPA_L1].[FirstName]                                       AS [F36_30],
       [LPA_L1].[FirstPasswordExpirationNotificationLastSentDate] AS [F36_31],
       [LPA_L1].[ForceAccessCertificationDate]                    AS [F36_32],
       [LPA_L1].[HasOnlyLeaveOfAbsenceAccess]                     AS [F36_33],
       [LPA_L1].[HasPicture]                                      AS [F36_34],
       [LPA_L1].[HomeDriveWasDeletedDate]                         AS [F36_35],
       [LPA_L1].[HomePostalAddress]                               AS [F36_36],
       [LPA_L1].[HomePostalCode]                                  AS [F36_37],
       [LPA_L1].[HomeStreetAddress]                               AS [F36_38],
       [LPA_L1].[HrData]                                          AS [F36_39],
       [LPA_L1].[HrEmploymentCode]                                AS [F36_40],
       [LPA_L1].[IgnoreUserInFinalizationJob]                     AS [F36_41],
       [LPA_L1].[IsGuest]                                         AS [F36_42],
       [LPA_L1].[JobTitle]                                        AS [F36_43],
       [LPA_L1].[JobTitleRoleId]                                  AS [F36_44],
       [LPA_L1].[LastAccessCertification]                         AS [F36_45],
       [LPA_L1].[LastAccessCertificationEnquiryId]                AS [F36_46],
       [LPA_L1].[LastEnquiryAlertSent]                            AS [F36_47],
       [LPA_L1].[LastName]                                        AS [F36_48],
       [LPA_L1].[LastPasswordExpirationNotificationLastSentDate]  AS [F36_49],
       [LPA_L1].[LastPictureSet]                                  AS [F36_50],
       [LPA_L1].[LockedInAd]                                      AS [F36_51],
       [LPA_L1].[Login]                                           AS [F36_52],
       [LPA_L1].[Mail]                                            AS [F36_53],
       [LPA_L1].[MailboxWasDeletedDate]                           AS [F36_54],
       [LPA_L1].[MailDomain]                                      AS [F36_55],
       [LPA_L1].[ManagerAlertFrequencyInt]                        AS [F36_56],
       [LPA_L1].[ManagerUserID]                                   AS [F36_57],
       [LPA_L1].[MobilePhoneNumber]                               AS [F36_58],
       [LPA_L1].[NationalIdentificationNumber]                    AS [F36_59],
       [LPA_L1].[ObjectGUID]                                      AS [F36_60],
       [LPA_L1].[ObjectTypeId]                                    AS [F36_61],
       [LPA_L1].[OfficePhoneNumber]                               AS [F36_62],
       [LPA_L1].[OptNotToCreateMailbox]                           AS [F36_63],
       [LPA_L1].[OptNotToGiveAccessToDepartmentCommonResources]   AS [F36_64],
       [LPA_L1].[OtherMobilePhoneNumber]                          AS [F36_65],
       [LPA_L1].[PersonalID]                                      AS [F36_66],
       [LPA_L1].[PhysicalLocation]                                AS [F36_67],
       [LPA_L1].[PostalCode]                                      AS [F36_68],
       [LPA_L1].[PreferencesData]                                 AS [F36_69],
       [LPA_L1].[ProxyAddresses]                                  AS [F36_70],
       [LPA_L1].[RecipientTypeDetails]                            AS [F36_71],
       [LPA_L1].[RegisteredDate]                                  AS [F36_72],
       [LPA_L1].[ReplicatedLastLogon]                             AS [F36_73],
       [LPA_L1].[ResponsibleLocation]                             AS [F36_74],
       [LPA_L1].[ResponsibleUserId]                               AS [F36_75],
       [LPA_L1].[RunMailboxRulesLastFailed]                       AS [F36_76],
       [LPA_L1].[RunMailboxRulesLastFailedMessage]                AS [F36_77],
       [LPA_L1].[RunMailboxRulesLastRequired]                     AS [F36_78],
       [LPA_L1].[RunMailboxRulesLastSucceeded]                    AS [F36_79],
       [LPA_L1].[SalaryEndDate]                                   AS [F36_80],
       [LPA_L1].[SalaryStep]                                      AS [F36_81],
       [LPA_L1].[SelectedTenantId]                                AS [F36_82],
       [LPA_L1].[ShowInDepartmentHierarchy]                       AS [F36_83],
       [LPA_L1].[ShowInJmlViewer]                                 AS [F36_84],
       [LPA_L1].[Sid]                                             AS [F36_85],
       [LPA_L1].[SipAddress]                                      AS [F36_86],
       [LPA_L1].[SipPhoneNr]                                      AS [F36_87],
       [LPA_L1].[SkipLeaverNotifications]                         AS [F36_88],
       [LPA_L1].[StartDate]                                       AS [F36_89],
       [LPA_L1].[State]                                           AS [F36_90],
       [LPA_L1].[StreetAddress]                                   AS [F36_91],
       [LPA_L1].[TenantId]                                        AS [F36_92],
       [LPA_L1].[TimeLock]                                        AS [F36_93],
       [LPA_L1].[TotpLastWindowUsed]                              AS [F36_94],
       [LPA_L1].[TotpSecretEncrypted]                             AS [F36_95],
       [LPA_L1].[UpdatedByUserId]                                 AS [F36_96],
       [LPA_L1].[UpdatedDate]                                     AS [F36_97],
       [LPA_L1].[UseDepartmentAddress]                            AS [F36_98],
       [LPA_L1].[UserId]                                          AS [F36_99],
       [LPA_L1].[UserPrincipalName]                               AS [F36_100],
       [LPA_L1].[WhenChangedAD]                                   AS [F36_101],
       [dbo].[Computer].[ComputerId]                              AS [F5_102],
       [dbo].[Computer].[Location]                                AS [F5_103],
       [dbo].[Computer].[OperatingSystem]                         AS [F5_104],
       [dbo].[Computer].[OperatingSystemHotfix]                   AS [F5_105],
       [dbo].[Computer].[OperatingSystemServicePack]              AS [F5_106],
       [dbo].[Computer].[OperatingSystemVersion]                  AS [F5_107]
FROM   (([dbo].[User] [LPA_L1]
         INNER JOIN (SELECT [UserId]    AS [UserId],
                            [StartDate] AS [StartDate],
                            [EndDate]   AS [EndDate]
                     FROM   [dbo].[fn_GetActiveWorkPeriods] (@p2,
                                                             @p3)) [LPA_W2]
             ON [LPA_L1].[UserId] = [LPA_W2].[UserId])
        LEFT JOIN [dbo].[Computer]
            ON [LPA_L1].[UserId] = [dbo].[Computer].[ComputerId])
WHERE  ([LPA_L1].[ObjectTypeId] = @p4
    AND [LPA_L1].[TenantId] = @p5
    AND ((((((([LPA_L1].[EmploymentTypeInt] <> @p6
               AND [LPA_L1].[EmploymentTypeInt] <> @p7)
              AND [LPA_L1].[EmploymentTypeInt] <> @p8)
             AND [LPA_L1].[EmploymentTypeInt] <> @p9)
            AND [LPA_L1].[EmploymentTypeInt] <> @p10)
           AND [LPA_L1].[EmploymentTypeInt] <> @p11)
          AND [LPA_L1].[EmploymentTypeInt] <> @p12)
         AND [LPA_L1].[EmploymentTypeInt] <> @p13))
ORDER  BY [LPA_L1].[DisplayName] collate Danish_Norwegian_CI_AS ASC 
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-Nov-2022 10:06:16   

hotchill wrote:

The user table also has three other relations to self: UpdatedBy, Responsible, EndDateSetBy. They are all prefetched without problem. The set of these users on the FK side is very small compared to the manager set.

The SQL queries produced for these fetches, do these use an IN(list of parameters) predicate to filter the 'updated by'/responsible etc. user entities ? The odd thing is that the fetches for these entities should be the same as for managers, but that one fails. The number of user entities of the root node of the prefetch path is the same, so the queries should all have the same where clause, including the manager query.

I see your prefetch path defines Managers on the second spot, so does it fetch the UserEntity.PrefetchPathEndDateSetByUser set, but then fails with the exception on manager? as it fetches these in-order, so it should crash after the first set is fetched I think. The SQL Query for UserEntity.PrefetchPathEndDateSetByUser could you post that too please?

As it's the second prefetch path child node, it has to work with the cleaned up filter used by the first prefetch path child node, which might cause the problem here (the exception occurs in the part where it tries to figure out which part of the join is already in the list of elements in the FROM clause as in SQL A join B, B join C becomes A join B join C so it has to figure out in the case of B join C it's B that's already in there after A join B or C. If in the case of A join B in this example, B is aliased to X, then the B in B join C also has to be aliased to X, and I think the alias stuck on A join B is the problem here, but we have to build a repro case to see if that's indeed a correct assumption)

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-Nov-2022 10:33:50   
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 smile flushed

            }
            base.Traverse(objectToTraverse);
            foreach(IRelation relation in objectToTraverse)
            {
                EntityRelation relationAsEntityRelation = relation as EntityRelation;
                if(relationAsEntityRelation == null)
                {
                    continue;
                }

Fixing...

Frans Bouma | Lead developer LLBLGen Pro
hotchill avatar
hotchill
User
Posts: 180
Joined: 22-Jan-2007
# Posted on: 18-Nov-2022 10:56:00   

Yes, it uses "IN".

Yes, it generates sql for and fetches UserEntity.PrefetchPathEndDateSetByUser before it fails on Manager.

I have added the sql below.

Example of "IN" for UserEntity.PrefetchPathEndDateSetByUser

SELECT DISTINCT [dbo].[User].[AccountExpiration]                               AS [F36_0],
                [dbo].[User].[AdditionalPhoneNumber]                           AS [F36_1],
                [dbo].[User].[AzureId]                                         AS [F36_2],
                [dbo].[User].[CanAuthenticateUsingAD]                          AS [F36_3],
                [dbo].[User].[City]                                            AS [F36_4],
                [dbo].[User].[Company]                                         AS [F36_5],
                [dbo].[User].[CompanyRoleId]                                   AS [F36_6],
                [dbo].[User].[CreatedDate]                                     AS [F36_7],
                [dbo].[User].[CriminalRecordLastChecked]                       AS [F36_8],
                [dbo].[User].[DeletedDate]                                     AS [F36_9],
                [dbo].[User].[DeletedInAD]                                     AS [F36_10],
                [dbo].[User].[DeletionDisabled]                                AS [F36_11],
                [dbo].[User].[Department]                                      AS [F36_12],
                [dbo].[User].[DepartmentRoleId]                                AS [F36_13],
                [dbo].[User].[Description]                                     AS [F36_14],
                [dbo].[User].[DisabledInAD]                                    AS [F36_15],
                [dbo].[User].[DisplayName]                                     AS [F36_16],
                [dbo].[User].[DistinguishedName]                               AS [F36_17],
                [dbo].[User].[Division]                                        AS [F36_18],
                [dbo].[User].[EmploymentPercentage]                            AS [F36_19],
                [dbo].[User].[EmploymentTypeInt]                               AS [F36_20],
                [dbo].[User].[EmploymentTypeTransitionNotificationSent]        AS [F36_21],
                [dbo].[User].[EndDate]                                         AS [F36_22],
                [dbo].[User].[EndDateAlertSentDate]                            AS [F36_23],
                [dbo].[User].[EndDateSetByUserId]                              AS [F36_24],
                [dbo].[User].[EndDateSetDate]                                  AS [F36_25],
                [dbo].[User].[EnquiryAlertFrequencyId]                         AS [F36_26],
                [dbo].[User].[ExternalIdentityProviderTenantId]                AS [F36_27],
                [dbo].[User].[ExternalIdentityProviderUserId]                  AS [F36_28],
                [dbo].[User].[FakeNationalIdentificationNumber]                AS [F36_29],
                [dbo].[User].[FirstName]                                       AS [F36_30],
                [dbo].[User].[FirstPasswordExpirationNotificationLastSentDate] AS [F36_31],
                [dbo].[User].[ForceAccessCertificationDate]                    AS [F36_32],
                [dbo].[User].[HasOnlyLeaveOfAbsenceAccess]                     AS [F36_33],
                [dbo].[User].[HasPicture]                                      AS [F36_34],
                [dbo].[User].[HomeDriveWasDeletedDate]                         AS [F36_35],
                [dbo].[User].[HomePostalAddress]                               AS [F36_36],
                [dbo].[User].[HomePostalCode]                                  AS [F36_37],
                [dbo].[User].[HomeStreetAddress]                               AS [F36_38],
                [dbo].[User].[HrData]                                          AS [F36_39],
                [dbo].[User].[HrEmploymentCode]                                AS [F36_40],
                [dbo].[User].[IgnoreUserInFinalizationJob]                     AS [F36_41],
                [dbo].[User].[IsGuest]                                         AS [F36_42],
                [dbo].[User].[JobTitle]                                        AS [F36_43],
                [dbo].[User].[JobTitleRoleId]                                  AS [F36_44],
                [dbo].[User].[LastAccessCertification]                         AS [F36_45],
                [dbo].[User].[LastAccessCertificationEnquiryId]                AS [F36_46],
                [dbo].[User].[LastEnquiryAlertSent]                            AS [F36_47],
                [dbo].[User].[LastName]                                        AS [F36_48],
                [dbo].[User].[LastPasswordExpirationNotificationLastSentDate]  AS [F36_49],
                [dbo].[User].[LastPictureSet]                                  AS [F36_50],
                [dbo].[User].[LockedInAd]                                      AS [F36_51],
                [dbo].[User].[Login]                                           AS [F36_52],
                [dbo].[User].[Mail]                                            AS [F36_53],
                [dbo].[User].[MailboxWasDeletedDate]                           AS [F36_54],
                [dbo].[User].[MailDomain]                                      AS [F36_55],
                [dbo].[User].[ManagerAlertFrequencyInt]                        AS [F36_56],
                [dbo].[User].[ManagerUserID]                                   AS [F36_57],
                [dbo].[User].[MobilePhoneNumber]                               AS [F36_58],
                [dbo].[User].[NationalIdentificationNumber]                    AS [F36_59],
                [dbo].[User].[ObjectGUID]                                      AS [F36_60],
                [dbo].[User].[ObjectTypeId]                                    AS [F36_61],
                [dbo].[User].[OfficePhoneNumber]                               AS [F36_62],
                [dbo].[User].[OptNotToCreateMailbox]                           AS [F36_63],
                [dbo].[User].[OptNotToGiveAccessToDepartmentCommonResources]   AS [F36_64],
                [dbo].[User].[OtherMobilePhoneNumber]                          AS [F36_65],
                [dbo].[User].[PersonalID]                                      AS [F36_66],
                [dbo].[User].[PhysicalLocation]                                AS [F36_67],
                [dbo].[User].[PostalCode]                                      AS [F36_68],
                [dbo].[User].[PreferencesData]                                 AS [F36_69],
                [dbo].[User].[ProxyAddresses]                                  AS [F36_70],
                [dbo].[User].[RecipientTypeDetails]                            AS [F36_71],
                [dbo].[User].[RegisteredDate]                                  AS [F36_72],
                [dbo].[User].[ReplicatedLastLogon]                             AS [F36_73],
                [dbo].[User].[ResponsibleLocation]                             AS [F36_74],
                [dbo].[User].[ResponsibleUserId]                               AS [F36_75],
                [dbo].[User].[RunMailboxRulesLastFailed]                       AS [F36_76],
                [dbo].[User].[RunMailboxRulesLastFailedMessage]                AS [F36_77],
                [dbo].[User].[RunMailboxRulesLastRequired]                     AS [F36_78],
                [dbo].[User].[RunMailboxRulesLastSucceeded]                    AS [F36_79],
                [dbo].[User].[SalaryEndDate]                                   AS [F36_80],
                [dbo].[User].[SalaryStep]                                      AS [F36_81],
                [dbo].[User].[SelectedTenantId]                                AS [F36_82],
                [dbo].[User].[ShowInDepartmentHierarchy]                       AS [F36_83],
                [dbo].[User].[ShowInJmlViewer]                                 AS [F36_84],
                [dbo].[User].[Sid]                                             AS [F36_85],
                [dbo].[User].[SipAddress]                                      AS [F36_86],
                [dbo].[User].[SipPhoneNr]                                      AS [F36_87],
                [dbo].[User].[SkipLeaverNotifications]                         AS [F36_88],
                [dbo].[User].[StartDate]                                       AS [F36_89],
                [dbo].[User].[State]                                           AS [F36_90],
                [dbo].[User].[StreetAddress]                                   AS [F36_91],
                [dbo].[User].[TenantId]                                        AS [F36_92],
                [dbo].[User].[TimeLock]                                        AS [F36_93],
                [dbo].[User].[TotpLastWindowUsed]                              AS [F36_94],
                [dbo].[User].[TotpSecretEncrypted]                             AS [F36_95],
                [dbo].[User].[UpdatedByUserId]                                 AS [F36_96],
                [dbo].[User].[UpdatedDate]                                     AS [F36_97],
                [dbo].[User].[UseDepartmentAddress]                            AS [F36_98],
                [dbo].[User].[UserId]                                          AS [F36_99],
                [dbo].[User].[UserPrincipalName]                               AS [F36_100],
                [dbo].[User].[WhenChangedAD]                                   AS [F36_101],
                [dbo].[Computer].[ComputerId]                                  AS [F5_102],
                [dbo].[Computer].[Location]                                    AS [F5_103],
                [dbo].[Computer].[OperatingSystem]                             AS [F5_104],
                [dbo].[Computer].[OperatingSystemHotfix]                       AS [F5_105],
                [dbo].[Computer].[OperatingSystemServicePack]                  AS [F5_106],
                [dbo].[Computer].[OperatingSystemVersion]                      AS [F5_107]
FROM   ([dbo].[User]
        LEFT JOIN [dbo].[Computer]
            ON [dbo].[User].[UserId] = [dbo].[Computer].[ComputerId])
WHERE  ([dbo].[User].[UserId] IN (@p1, @p2, @p3, @p4,
                                  @p5, @p6, @p7, @p8, @p9)) 
hotchill avatar
hotchill
User
Posts: 180
Joined: 22-Jan-2007
# Posted on: 18-Nov-2022 11:00:03   

If I place Manager as the first prefetch, it still fails on that prefetch. I.e. there is no prefetch query in the ORM after the fetch for the 7910 rows of the user fetch.

hotchill avatar
hotchill
User
Posts: 180
Joined: 22-Jan-2007
# Posted on: 18-Nov-2022 11:09:30   

Great to see that you are on to the cause simple_smile

I already tried setting ParameterisedPrefetchPathThreshold to a very high value. That did not work and now I know the reason. Thanks simple_smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-Nov-2022 11:50:52   

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. simple_smile

Frans Bouma | Lead developer LLBLGen Pro
hotchill avatar
hotchill
User
Posts: 180
Joined: 22-Jan-2007
# Posted on: 18-Nov-2022 12:47:41   

Success! Thank you. That was quick. Well done!

Have a great weekend.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-Nov-2022 14:50:12   

hotchill wrote:

Success! Thank you. That was quick. Well done!

thanks simple_smile 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 simple_smile

Have a great weekend.

You too! simple_smile

Frans Bouma | Lead developer LLBLGen Pro