Hello

I have the following command. The top portion of the command is actually in a TypedList. I would like to know how I can define the nested SELECT statement using Prefetch Paths. The entities are named the same as the table mentioned below without the 'tbl'.

Regards, Simon

SELECT
    DISTINCT
    tblLibrary.ipkLibraryID AS [ID],
    tblLibrary.sName AS [Name]
FROM
    tblLibrary
WHERE
    tblLibrary.ipkLibraryID IN
    (
    SELECT
        TOP 1
        tblDocumentSecurityGroups.ifkLibraryID
    FROM
        tblDocumentSecurityGroups
    WITH (NOLOCK)
    INNER JOIN
        tblLibrary
    ON
        tblDocumentSecurityGroups.ifkLibraryID = tblLibrary.ipkLibraryID
    INNER JOIN
        tblDocumentSecurity
    ON
        tblDocumentSecurityGroups.ifkDocumentSecurityID = tblDocumentSecurity.ipkDocumentSecurityID
    INNER JOIN
        tblGroups
    ON
        tblDocumentSecurityGroups.ifkGroupsID = tblGroups.ipkGroupsID
    INNER JOIN
        tblGroupMemberships
    ON
        tblGroups.ipkGroupsID = tblGroupMemberships.ifkGroupsID
    INNER JOIN
        tblUsers
    ON
        tblGroupMemberships.ifkUsersID = tblUsers.ipkUsersID
    WHERE
        tblDocumentSecurityGroups.ifkLibraryID = tblLibrary.ipkLibraryID AND
        tblUsers.ipkUsersID = @ifkUsersID 
    ORDER 
        tblDocumentSecurity.intClass
    ) 
ORDER BY
    tblLibrary.sName

That's 3 different things. TypedLists, PrefetchPaths and IN predicate. With PrefetchPaths can be used when fetching Entities and EntityCollections not TypedLists.

To generate an IN predicate to filter the returned results you should use FieldCompareSetPredicate.

Please refer to the LLBLGen Pro for more information on PrefetchPaths and Filtering using predicates.

Hello Walaa

Sorry I meant EntityCollection. I was thinking my TypedList for some reason. I tried the following.

                    // Initialize Class
                    retVal = new EntityCollection(new LibraryEntityFactory());

                    // Initialize Class
                    PrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.LibraryEntity);
                    // Add Path
                    prefetchPath.Add(LibraryEntity.PrefetchPathDocumentSecurityGroups);
                    prefetchPath.Add(DocumentSecurityGroupsEntity.PrefetchPathGroups);
                    prefetchPath.Add(GroupsEntity.PrefetchPathGroupMemberships);
                    
                    // Initialize Class
                    RelationPredicateBucket bucket = new RelationPredicateBucket();
                    // Add Filter
                    bucket.PredicateExpression.Add(new FieldCompareSetPredicate(
                        LibraryFields.LibraryId,
                        null, 
                        DocumentSecurityGroupsFields.LibraryId,
                        null,
                        SetOperator.In, 
                        (LibraryFields.LibraryId == DocumentSecurityGroupsFields.LibraryId)));
                    bucket.PredicateExpression.Add((GroupMembershipsFields.UsersId == UsersID));
                    
                    // Initialize Class
                    SortExpression sort = new SortExpression(LibraryFields.Name | SortOperator.Ascending);
                    // Fetch
                    adapter.FetchEntityCollection(retVal,
                        bucket,
                        0,
                        sort,
                        prefetchPath);

It keeps on throwing this error

"The multi-part identifier "ImageWarehouseMeta.dbo.tblGroupMemberships.ifkUsersID" could not be bound" What am I doing wrong?

Regards, Simon

That;s because you are fetching Libraries and you are doing a filter on GroupMemberships without adding a relation to it. Also your code have some issues, try the following:

// Initialize Class
retVal = new EntityCollection(new LibraryEntityFactory());

// Initialize Class
PrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.LibraryEntity);
// Add Path
prefetchPath.Add(LibraryEntity.PrefetchPathDocumentSecurityGroups).SubPath.Add(DocumentSecurityGroupsEntity.PrefetchPathGroups).SubPath.Add(GroupsEntity.PrefetchPathGroupMemberships);
                    
// Initialize Class
RelationPredicateBucket bucket = new RelationPredicateBucket();
// Add Filter
bucket.PredicateExpression.Add(new FieldCompareSetPredicate(
                        LibraryFields.LibraryId,
                        null, 
                        DocumentSecurityGroupsFields.LibraryId,
                        null,
                        SetOperator.In, 
                        null));
bucket.PredicateExpression.Add((GroupMembershipsFields.UsersId == UsersID));
bucket.Relations.Add(LibraryEntity.Relations.GroupMembership)//should look something like this
                    
// Initialize Class
SortExpression sort = new SortExpression(LibraryFields.Name | SortOperator.Ascending);
// Fetch
adapter.FetchEntityCollection(retVal, bucket,0,sort,prefetchPath);

Hello Walaa

Thanks it is working. I did try the SubPaths but because I forgot the relations it never worked.

One more question, how do I stop 6 queries from being executed. I just want to main query to be executed. Or can I move this query to rather use a TypedList and then use FieldCompareSetPredicate?

Thanks a mil! Simon

One more question, how do I stop 6 queries from being executed. I just want to main query to be executed.

Then maybe you don't want prefetchPaths, these are queries to fetch related entities, they are not joins to your main query.

Hello Walaa

I got it to work with the TypedList.

// Initialize Class
                    retVal = new LibraryListTypedList();

                    // Initialize Class
                    RelationPredicateBucket bucket = (RelationPredicateBucket) retVal.GetRelationInfo();

                    // Add Relations
                    IRelationCollection relations = new RelationCollection();
                    // Add 
                    relations.Add(LibraryEntity.Relations.DocumentSecurityGroupsEntityUsingLibraryId);
                    relations.Add(DocumentSecurityGroupsEntity.Relations.GroupsEntityUsingGroupsId);
                    relations.Add(GroupsEntity.Relations.GroupMembershipsEntityUsingGroupsId);

                    // Add Filters
                    bucket.PredicateExpression.Add(new FieldCompareSetPredicate(
                        LibraryFields.LibraryId,
                        null,
                        DocumentSecurityGroupsFields.LibraryId,
                        null,
                        SetOperator.In,
                        (LibraryFields.LibraryId == DocumentSecurityGroupsFields.LibraryId) &
                        (GroupMembershipsFields.UsersId == UsersID),
                        relations, 
                        false));
                                        
                    // Initialize Class
                    SortExpression sort = new SortExpression(LibraryFields.Name | SortOperator.Ascending);
                    // Fetch
                    adapter.FetchTypedList(retVal.GetFieldsInfo(),
                        retVal,
                        bucket,
                        0,
                        sort,
                        false);

Regards, Simon

Great