Hi,

I have three tables User

UserRole

Role

User links to UserRole which links to Role

User.UserId > UserRole.UserId

UserRole.RoleId > Role.RoleId

What I want to achieve is get a list of User's that are related to a specific role entity.

Heres my code:

EntityCollection<UserEntity> userCollection = new EntityCollection<UserEntity>(new UserEntityFactory());

        IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.UserEntity);
        prefetchPath.Add(UserEntity.PrefetchPathRoleCollectionViaUserRole, int.MaxValue);
        RelationPredicateBucket filter = new RelationPredicateBucket();

        IPredicateExpression expression = new PredicateExpression()
            .Add(RoleFields.Name == "CLO")
            .Add(UserFields.IsActive == true);

        filter.PredicateExpression.Add(expression);

This fails with : SqlException - The multi-part identifier "NPL.dbo.Role.Name" could not be bound.

I also tried putting in a predicate expression in the prefetch PrefetchPathRoleCollectionViaUserRole. That ran but didn't produce the desired result.

That's because you filter on a field from the Role table, while you did not join with the Role table.

To preform a Join you should add relation(s) to the RelationPredicateBucket.Relations as Follows:

filter.Relations.Add(UserEntity.Relations.UserRoleEntity...);
filter.Relations.Add(UserRoleEntity.Relations.RoleEntity...);

Great, thanks. I'll give it a go tomorrow at work