Hi!

I am trying to accomplish fetch, but I just can't do it right. I am using adapter scenario.

I have a couple of tables:

table ApplicationUser: ApplicationUserId (PK), BusinessRoleId (FK to BusinessRole table), ApplicationUserUsername, ApplicationUserActive (bit)...

table BusinessRole: BusinessRoleId (PK), BusinessRoleActive (bit)...

table BusinessSystemRole: BusinessSystemRoleId (PK), BusinessRoleId (FK to BusinessRole table), SystemRoleId (FK to SystemRole table)...

table SystemRole: SystemRoleId (PK), SystemRoleActive (bit)...

I need to fetch single ApplicationUserEntity object for specified ApplicationUserUsername with it's related BusinessRoleEntity object that has collection of related SystemRoleEntity objects. Well, I don't seem to have any problems with creating prefetch paths. Furthermore, user needs to have active business role (BusinessRoleActive = 1) and that business role has to contain only active system roles (SystemActiveRole = 1).

No matter how I set predicates, I get ApplicationUser with it's BusinessRole that contains all (active and non-active) SystemRole-s.

What is the proper way to set predicates for this search? Please help!

Do you set the filters for the related entities in the prefetch path construction code? because you have to. (use an overload of the Add() method)

If you do, could you paste the code you use to construct the fetch? thanks.

Otis wrote:

Do you set the filters for the related entities in the prefetch path construction code? because you have to. (use an overload of the Add() method)

If you do, could you paste the code you use to construct the fetch? thanks.

I did set filters the way I understand. Here's the code:

            EntityCollection users = new EntityCollection(new ApplicationUserEntityFactory());
            IPrefetchPath2 userPath = new PrefetchPath2((int)EntityType.ApplicationUserEntity);
            IPrefetchPathElement2 bPath = userPath.Add(ApplicationUserEntity.PrefetchPathBusinessRole);
            IPrefetchPathElement2 sPath = bPath.SubPath.Add(BusinessRoleEntity.PrefetchPathBusinessSystemRole);
            sPath.SubPath.Add(BusinessSystemRoleEntity.PrefetchPathSystemRole);

            IRelationPredicateBucket filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(PredicateFactory.CompareValue(ApplicationUserFieldIndex.ApplicationUserUsername,
                ComparisonOperator.Equal, userName));
            filter.Relations.Add(ApplicationUserEntity.Relations.BusinessRoleEntityUsingBusinessRoleId);
            filter.PredicateExpression.AddWithAnd(PredicateFactory.CompareValue(BusinessRoleFieldIndex.BusinessRoleActive,
                ComparisonOperator.Equal, true));
            filter.Relations.Add(BusinessRoleEntity.Relations.BusinessSystemRoleEntityUsingBusinessRoleId);
            filter.Relations.Add(BusinessSystemRoleEntity.Relations.SystemRoleEntityUsingSystemRoleId);
            filter.PredicateExpression.Add(PredicateFactory.CompareValue(SystemRoleFieldIndex.SystemRoleActive,
                ComparisonOperator.Equal, true));
            new DataAccessAdapter().FetchEntityCollection(users, filter, userPath);

I tryed adding relations and predicates in different order, but I always got same results.

That's what I meant

Prefetch paths work as follows: the parent entity's query is used to limit the fetch of the path node entities (with an IN (select ... ) query) plus the filter specified with the path node Add() call will be applied to the query for the path node.

In your case, you construct a filter, and you apply it to the parent node fetch (the applicationuser fetch). That filter is then also used in the fetch for BusinessRole entities, but in this way: SELECT * FROM BusinessRole WHERE BusinessRoleId IN (SELECT BusinessRoleId FROM ApplicationUser WHERE (your filter)). As you can see, it will not filter the BusinessRole fetch.

So the best you can do is this: assign the filter predicates for BusinessRole and the other entities which should be limited, to variables. Then add the variables to the RelationPredicateBucket (for filtering on ApplicationUser) and also pass them to the Add() method where you add the prefetch path nodes for BusinessRole and the others. This way, the filter will also be applied to the node fetches of the child entities like BusinessRole.

If you run into problems constructing this, let me know.

Otis wrote:

So the best you can do is this: assign the filter predicates for BusinessRole and the other entities which should be limited, to variables. Then add the variables to the RelationPredicateBucket (for filtering on ApplicationUser) and also pass them to the Add() method where you add the prefetch path nodes for BusinessRole and the others. This way, the filter will also be applied to the node fetches of the child entities like BusinessRole.

If you run into problems constructing this, let me know.

Sorry, I don't know what you mean. Is there an illustration of what should be done?

Something like this:

IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(ApplicationUserEntity.Relations.BusinessRoleEntityUsingBusinessRoleId);
filter.Relations.Add(BusinessRoleEntity.Relations.BusinessSystemRoleEntityUsingBusinessRoleId);
filter.Relations.Add(BusinessSystemRoleEntity.Relations.SystemRoleEntityUsingSystemRoleId);

filter.PredicateExpression.Add(PredicateFactory.CompareValue(ApplicationUserFieldIndex.ApplicationUserUsername,
    ComparisonOperator.Equal, userName));

IPredicate businessRoleFilter = PredicateFactory.CompareValue(BusinessRoleFieldIndex.BusinessRoleActive,
    ComparisonOperator.Equal, true);
filter.PredicateExpression.AddWithAnd(businessRoleFilter);

IPredicate systemRoleActiveFilter = PredicateFactory.CompareValue(SystemRoleFieldIndex.SystemRoleActive,
    ComparisonOperator.Equal, true)
filter.PredicateExpression.AddWithAnd(systemRoleActiveFilter);
    
EntityCollection users = new EntityCollection(new ApplicationUserEntityFactory());
IPrefetchPath2 userPath = new PrefetchPath2((int)EntityType.ApplicationUserEntity);
// specify as well the filter for the business role
IPrefetchPathElement2 bPath = userPath.Add(
    ApplicationUserEntity.PrefetchPathBusinessRole, 0, 
    new PredicateExpression(businessRoleFilter));
IPrefetchPathElement2 sPath = bPath.SubPath.Add(BusinessRoleEntity.PrefetchPathBusinessSystemRole);
sPath.SubPath.Add(BusinessSystemRoleEntity.PrefetchPathSystemRole, 0, 
    new PredicateExpression(systemRoleActiveFilter));
    
new DataAccessAdapter().FetchEntityCollection(users, filter, userPath);

I'm not sure if this exactly works as planned, but this is the idea.

Works great! Thanks!!!