With this code:

PrefetchPath2 prefetch = new PrefetchPath2((int)EntityType.SecurityUserEntity);
prefetch.Add(SecurityUserEntity.PrefetchPathSecurityRoles);
adapter.FetchEntityUsingUniqueConstraint(user,
                    user.ConstructFilterForUCName(),
                    prefetch)

I can get this query:

SELECT DISTINCT
    [SecurityRole].[SecurityRoleID] AS [SecurityRoleId],
    [SecurityRole].[Name],
    [SecurityRole].[CreateDate],
    [SecurityRole].[CreateBy],
    [SecurityRole].[UpdateDate],
    [SecurityRole].[UpdateBy]
FROM
    ((( [SecurityUser] [LPA__1]
    INNER JOIN [SecurityUserApplicationRole] [LPA_S2]  ON
        [LPA__1].[SecurityUserID]=[LPA_S2].[SecurityUserID] )
    INNER JOIN [SecurityRole]  ON
        [SecurityRole].[SecurityRoleID]=[LPA_S2].[SecurityRoleID]))
WHERE
     ( ( [LPA_S2].[SecurityUserID] = 1))

All I want to do is change the first INNER JOIN to:

INNER JOIN [SecurityUserApplicationRole] [LPA_S2]  ON
        [LPA__1].[SecurityUserID]=[LPA_S2].[SecurityUserID] AND [LPA_S2].[ApplicationID] = 1)

Call me silly but by my intuition this should work

IPrefetchPathElement2 rolePath = prefetch.Add(SecurityUserEntity.PrefetchPathSecurityRoles);
rolePath.Relation.CustomFilter.Add(SecurityUserApplicationRoleFields.ApplicationId == 1);

Edit but rolePath.Relation is null. Seems like the prefetch path would know that relation and expose it for just this scenario. I'm sure there's something going on here that i don't understand, but this is how i expected it to work

Please help me solve this seemingly simple problem.

I'm stumped and would appreciate any assistance anyone can provide

Still experimenting...

This:

IPrefetchPathElement2 rolePath = prefetch.Add(SecurityUserEntity.PrefetchPathSecurityRoles);
            rolePath.Relation.CustomFilter = new PredicateExpression(SecurityUserApplicationRoleFields.ApplicationId == 1);

Has no effect on the prefetch query...

This achieves the desired effect although via a different SQL construct:

IPrefetchPathElement2 rolePath = prefetch.Add(SecurityUserEntity.PrefetchPathSecurityRoles);
rolePath.Filter = new PredicateExpression(SecurityUserApplicationRoleFields.ApplicationId.SetObjectAlias("SecurityUserApplicationRole_") == 1);

SELECT DISTINCT [SecurityRole].[SecurityRoleID] AS [SecurityRoleId], [SecurityRole].[Name], [SecurityRole].[CreateDate], [SecurityRole].[CreateBy], [SecurityRole].[UpdateDate], [SecurityRole].[UpdateBy] FROM (( [SecurityUser] [LPA__1]  INNER JOIN [SecurityUserApplicationRole] [LPA_S2]  ON  [LPA__1].[SecurityUserID]=[LPA_S2].[SecurityUserID]) INNER JOIN [SecurityRole]  ON  [SecurityRole].[SecurityRoleID]=[LPA_S2].[SecurityRoleID]) WHERE ( ( ( [LPA_S2].[SecurityUserID] = 1)) AND ( [LPA_S2].[ApplicationID] = 1))

You have to use the alias from the generated code for the prefetch path in question...which is a little hackish...

I'd still really like to know why setting the customfilter on the prefetchelement relation object doesn't work....

I'm using v2.0 but have you tried one of the overloads of the prefetch.Add method? One of the overloads includes a IPredicateExpression parameter so I'm able to code this:

IPredicateExpression filter = new PredicateExpression();
filter.Add(ContactTypeFields.Type == 1);
prefetchPath.Add(ContactEntity.PrefetchPathContactType, 0, filter);

That works fine when you are filtering on a field of the entity you are prefetching...in my case it is m:n and I'm filtering on a field of the intermediate entity not the target entity

The 'Relation' property on the prefetchpath element is used to build the m:n intermediate filter. The 'custom filter' is ignored on that. As it's an inner join, you can specify the additional predicate as a filter on the prefetchpath element instead.

Otis,

Why is the custom filter ignored in this scenario? From the outside looking in it appears that prefetchpathelement.relation.customfilter is the optimal route to accomplish this behavior. You wouldn't have to know the super secret hard coded alias string if you could do it this way. Ok, so maybe its not super secret but its definitely not easy to figure out on your own.

becker-samm wrote:

Otis,

Why is the custom filter ignored in this scenario? From the outside looking in it appears that prefetchpathelement.relation.customfilter is the optimal route to accomplish this behavior. You wouldn't have to know the super secret hard coded alias string if you could do it this way. Ok, so maybe its not super secret but its definitely not easy to figure out on your own.

It's ignored because it wasn't considered an option. The additional filters should be added to the filter in the element, not to the customfilter in the relation. The alias is a bit obscure, though in general it's not a problem as filtering on the intermediate entity isn't really necessary anyway as often it's not an objectified relationship but just an entity with 2 fk's

I agree that optimally the intermediate tables should never become objectified as it makes m:n relationships way more of a pain to deal with. In the end as long as I'm able to accomplish the behavior I'm after everything is cool...now that I know to use the alias I can move on . I was just trying to get some insight into why something I thought should work wasn't working and now I've got that too. So thanks again.