I am still using v1.0.2004.1 of llblgen. I have some manager templates. roles have users. I have some sample tests that I am working through.

Test 1 = fetch a collection of roles. Test 2 = fetch a collection of roles with a desc sort on role name Test 3 = fetch a collection of roles with a desc sort on role name and a users collection for each role Test 4 = fetch a collection of roles with a desc sort on role name and a users collection for each role using a desc sort on user name.

Tests 1 - 3 work fine. Test 3 executes the prefetch path portions to get a users collection for each role.

I cannot seem to get test 4 to return the proper results. The users are all returned in ascending order. I am attempting to use the PrefetchElement2Collection to hold my additional prefetch elements.

Any thoughts would be helpful, here is the code:

            Assert.IsNotNull(RolesController, "Roles controller is null");

            ISortExpression sortRoleName = new SortExpression(
                SortClauseFactory.Create(RolesFieldIndex.RoleName,
                SortOperator.Descending));

            ISortClause sortUserName = SortClauseFactory.Create(UsersFieldIndex.Username, 
                SortOperator.Descending);

            RolesEntity.PrefetchPathUsersCollection.Sorter.Add(sortUserName);

            IPrefetchPathElement2 userNameSortElement = RolesEntity.PrefetchPathUsersCollection;

            PrefetchPathElement2Collection pfCol = new PrefetchPathElement2Collection();
            pfCol.Add(userNameSortElement);

            EntityCollection roles = 
                RolesController.FetchCollectionWithUsersCollection(null, pfCol, sortRoleName);

            foreach(RolesEntity role in roles)
            {
                Console.WriteLine(role.RoleName);
                foreach(UsersEntity user in role.UsersCollection)
                {
                    Console.WriteLine("  UserID:{0} ; UserName:{1} ; Password:{2}", user.UserID, user.Username, user.Password);
                }
            }

In the SQL, the ORDER BY statements are present? (I'm not familiar with the templates, so I don't know what's going on under the hood)

There are 4 sql statements generated: one for fetching the roles collection (this one has an order by clause) and 3 more, one to fetch all users for each role. None of these has an orderby clause, and thats what I was trying to do.

Here is the method that gets invoked internally in the manager:

        protected EntityCollection FetchCollectionWithUsersCollection( RelationPredicateBucket filter, PrefetchPathElement2Collection additionalPrefetchElements, ISortExpression sorter, IDataAccessAdapter adapter )
        {
            PrefetchPath2 prefetch = new PrefetchPath2( (int)EntityType.RolesEntity );
            prefetch.Add( RolesEntity.PrefetchPathUsersCollection );
        
            if (additionalPrefetchElements != null)
            {
                foreach( IPrefetchPathElement2 element in additionalPrefetchElements )
                {
                    prefetch.Add( element );
                }
            }
            
            return FetchCollectionWithPrefetch( filter, sorter, prefetch, adapter );
        }

In the code in the original post, I add a sort clause to the users collection (I think), then I create a prefetch element and add it to the collection. The collection is then enumerated in the protected method and added to the prefetch in the protected method.

When I debug the code, the collection does contain an element so I am assuming it is the one that I added in the calling method.

I havent really used this particular overload with the manager templates so I am not sure if I am doing things as expected.

Also, one thing to note: the element variable that is being enumerated has no value for the "EntityFactoryToUse" member. Could this be the issue?

Your line: RolesEntity.PrefetchPathUsersCollection.Sorter.Add(sortUserName);

creates a new IPrefetchPathElement2, it sets the sorter but it's thrown away. The object is created each time the Property is called.

So after: IPrefetchPathElement2 userNameSortElement = RolesEntity.PrefetchPathUsersCollection;

do userNameSortElement.Sorter.Add(sortUserName);

(from my bare head )

Well, actually, your first suggestion did not work entirely. If I make the change to the test case, AND remove

prefetch.Add( RolesEntity.PrefetchPathUsersCollection);

from the FetchCollectionWithUsersCollection method all works well.

The kicker is that if the caller doesnt pass in a collection containing an element built from PrefetchPathUsersCollection then the child collection retreival fails completely.

Hmm, I have a delima now, I think.

Devildog74 wrote:

Well, actually, your first suggestion did not work entirely. If I make the change to the test case, AND remove

prefetch.Add( RolesEntity.PrefetchPathUsersCollection);

from the FetchCollectionWithUsersCollection method all works well.

The kicker is that if the caller doesnt pass in a collection containing an element built from PrefetchPathUsersCollection then the child collection retreival fails completely.

hmm. I think the cause is that the FetchCollectionWithUsersCollection routine thinks that the passed in sorter is for the root entity fetch, not for the UsersCollection node. So it adds a clean node and you can't add the sorter for that node.

The FetchCollectionWithUsersCollection should have an overload which accepts an additionalSorter which is passed to the UsersCollection node which is created inside the routine. I'm not sure if this code is preferable over a few lines setting up the prefetch path itself though.