Hi.

I am using 4.2 Final released on May 4th, 2016. I am using adapter pattern.

I am trying to fetch an entity collection using a collection of relations, but the inner joins do not behave as expected. I thought the root entity of the join would always be the entity you are fetching.

This is the fetch setup

var userAccess = new EntityCollection<UserAccessLevelEntity>();
                var filter = new RelationPredicateBucket(
                    UserAccessLevelFields.UserId == userId & new FieldCompareNullPredicate(UserAccessLevelFields.RemovedByUserId, null) &
                    RoleFields.IsActive == true &
                    RoleAccessLevelFields.IsInherited == false & new FieldCompareNullPredicate(RoleAccessLevelFields.RemovedDate, null) & 
                    UserRoleFields.UserId == userId & new FieldCompareNullPredicate(UserRoleFields.RemovedDate, null, false), 
                    new RelationCollection { UserAccessLevelEntity.Relations.RoleEntityUsingRoleId, RoleEntity.Relations.UserRoleEntityUsingRoleId, RoleEntity.Relations.RoleAccessLevelEntityUsingRoleId });

These are the joins I was expecting

Select * from UserAccessLevel ual
inner join [Role] r on r.RoleId = ual.RoleId
inner join UserRole ur on ur.RoleId = r.RoleId
inner join RoleAccessLevel ral on ral.RoleId = r.RoleId
where ual.UserId = 'xxx' and ual.RemovedByUserId is null and
r.IsActive = 1 and
ral.IsInherited = 0 and ral.RemovedDate is null and
ur.UserId = 'xxx' and ur.RemovedDate is null

This is what I get

SELECT DISTINCT [ServiceManager].[dbo].[UserAccessLevel].[AccessEnquiryId] 
FROM ((( [ServiceManager].[dbo].[Role]  
INNER JOIN [ServiceManager].[dbo].[UserAccessLevel]  ON  [ServiceManager].[dbo].[Role].[RoleId]=[ServiceManager].[dbo].[UserAccessLevel].[RoleId]) 
INNER JOIN [ServiceManager].[dbo].[UserRole]  ON  [ServiceManager].[dbo].[Role].[RoleId]=[ServiceManager].[dbo].[UserRole].[RoleId]) 
INNER JOIN [ServiceManager].[dbo].[RoleAccessLevel]  ON  [ServiceManager].[dbo].[Role].[RoleId]=[ServiceManager].[dbo].[RoleAccessLevel].[RoleId]) 
WHERE ( ( ( ( ( ( [ServiceManager].[dbo].[UserAccessLevel].[UserId] = 'xxx' AND 
[ServiceManager].[dbo].[UserAccessLevel].[RemovedByUserId] IS NULL) 
AND [ServiceManager].[dbo].[Role].[IsActive] = 1) 
AND [ServiceManager].[dbo].[RoleAccessLevel].[IsInherited] = 0) 
AND [ServiceManager].[dbo].[RoleAccessLevel].[RemovedDate] IS NULL) 
AND [ServiceManager].[dbo].[UserRole].[UserId] = 'xxx') 
AND [ServiceManager].[dbo].[UserRole].[RemovedDate] IS NULL)

Is this a bug?

Thanks, Tore.

Please close this post. There is a logic error in my predicate. That is why I am not getting the result expected. It has nothing to do with the sql generated.

QuerySpec gives expected sql, but same result.

var q = qf.UserAccessLevel
                            .From(QueryTarget.InnerJoin(qf.Role).On(RoleFields.RoleId == UserAccessLevelFields.RoleId)
                            .InnerJoin(qf.UserRole).On(UserRoleFields.RoleId == RoleFields.RoleId)
                            .InnerJoin(qf.RoleAccessLevel).On(RoleAccessLevelFields.RoleId == RoleFields.RoleId))
                            .Where( UserAccessLevelFields.UserId == userId & new FieldCompareNullPredicate(UserAccessLevelFields.RemovedByUserId, null) &
                                    RoleFields.IsActive == true &
                                    RoleAccessLevelFields.IsInherited == false & new FieldCompareNullPredicate(RoleAccessLevelFields.RemovedDate, null) &
                                    UserRoleFields.UserId == userId & new FieldCompareNullPredicate(UserRoleFields.RemovedDate, null, false));