Hello

I.m using v3 / Adapter.

I'm trying to get filtered prefetch (per node filtering) working. I'm seeing I lot of unexpected results which I think are a combination of bugs and "user error". I've spend the best part of a day reading your docs and hacking at this with no luck and I'm hoping you can help.

I have a simple 3 table set up to represent users and permissions with a UserPermission link table as follows:

[User]---< [UserPermission] >----[Permission]

In my test data I have 10 sample users 5 of them have the "Serve_Scooby_Snack" Permission. Of those 5 2 also have an additional permission.

I am trying to produce a llblgen query which will return all the users which have the "Serve_Scooby_Snack" permission and prefech only UserPermissions for "Serve_Scooby_Snack". Against the above test data I would retrieve 5 UserEntity each with 1 UserPermissionEntity with 1 PermissionEntity which is "Serve_Scooby_Snack".

I have been experimenting with this using the following unit test:

[Test] public void ShouldReturnFilteredPermissions() { var connectionString = ConfigurationHelper.GetConnectionString("Authorisation");

var filterExpression = new PredicateExpression();
filterExpression.Add(PermissionFields.Description == "Serve_Scooby_Snack");

var bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(filterExpression);

bucket.Relations.Add(UserEntity.Relations.UserPermissionEntityUsingUserId);
bucket.Relations.Add(UserPermissionEntity.Relations.PermissionEntityUsingPermissionId);

var prefetch = new PrefetchPath2(EntityType.UserEntity);
prefetch.Add(UserEntity.PrefetchPathUserPermission).SubPath
    .Add(UserPermissionEntity.PrefetchPathPermission, 0,filterExpression);

var entities = new EntityCollection<UserEntity>();
using (var adaptor = new DataAccessAdapter(connectionString))
{
    adaptor.FetchEntityCollection(entities, bucket, 0, null, prefetch);
}

Assert.AreEqual(5,entities.Count);
int permissionCounter = 0, nullPermissionCounter = 0;

foreach (var userEntity in entities)
{
    foreach (var up in userEntity.UserPermission)
    {
        if (up.Permission == null)
        {
            nullPermissionCounter++;
        }
        else
        {
            Assert.AreEqual("Serve_Scooby_Snack", (up.Permission.Description));
            permissionCounter++;
        }
    }
}

Assert.AreEqual(5,permissionCounter);
Assert.AreEqual(0,nullPermissionCounter);

}

This version of the unit test is the closest I have come to solving my problem. However the the last assert fails as 7 UserPermissionEntity's are returned 5 of which have the expected PermissionEntity and 2 with a null PermissionEntity which presumably represent the permissions which where filtered out.

From these results I assumed I had put the prefetch filter expression at the wrong level, so I added it to the UserPermission prefetch instead.

I changed the prefetch code to:

 var prefetch = new PrefetchPath2(EntityType.UserEntity);
 prefetch.Add(UserEntity.PrefetchPathUserPermission, 0, filterExpression).SubPath
.Add(UserPermissionEntity.PrefetchPathPermission);

When executed this produces an ORMQueryExecutionException with the message "The multi-part identifier "AuthorisationEmpty.dbo.Permission.Description" could not be bound.."

digging deeper I found that llblgen was trying to execute this query:

Query: SELECT [AuthorisationEmpty].[dbo].[UserPermission].[Id], [AuthorisationEmpty].[dbo].[UserPermission].[PermissionId], [AuthorisationEmpty].[dbo].[UserPermission].[UserId] FROM [AuthorisationEmpty].[dbo].[UserPermission] ** WHERE ( [AuthorisationEmpty].[dbo].[UserPermission].[UserId] IN (@p1, @p2, @p3, @p4, @p5) AND ( ( [AuthorisationEmpty].[dbo].[Permission]**.[Description] = @p6))) Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5781. Parameter: @p2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5780. Parameter: @p3 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5783. Parameter: @p4 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5782. Parameter: @p5 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5784. Parameter: @p6 : String. Length: 250. Precision: 0. Scale: 0. Direction: Input. Value: "Serve_Scooby_Snack".

However this fails because [Permission] is not specified in the FROM clause.

I've been through a process of putting the query together in different ways such as specifing different root prefetch types breaking up the fluent syntax into vaiables, removing relations etc and have gotton no closer.

One notable test was removing the filter from the predicate bucket. Ie if you go back to my original unit test and comment out this line:

// bucket.PredicateExpression.Add(filterExpression);

I would expect this to return all 10 users instead of just the 5 with "Serve_Scooby_Snack". It actually makes no difference to the query at all : it still returns 5 users with a total of 7 UserPermissions. I have no idea why.

I would greatly appreciate any help you can offer!

Thanks ~Brett

I've made some further progress with this. It turns out I needed to add the apropriate relations to the prefetch as well as to the predicate bucket. In my unit test I needed to build the prefetch as follows:

var prefetch = new PrefetchPath2(EntityType.UserEntity); prefetch.Add(UserEntity.PrefetchPathUserPermission, 0, filterExpression,new RelationCollection(UserPermissionEntity.Relations.PermissionEntityUsingPermissionId)) .SubPath.Add(UserPermissionEntity.PrefetchPathPermission);

I am still not able to return all 10 users by removing the filter from the predicate. I created a new simpler example where I create a prefetch path from User -> UserPermission -> Permission with no predicates anywhere and this still only returns 5 users. The 5 users with no permissions are not returned.

Any help you can offer in solving this would be greatly appreciated!

~Brett

BrettBailey wrote:

I am still not able to return all 10 users by removing the filter from the predicate. I created a new simpler example where I create a prefetch path from User -> UserPermission -> Permission with no predicates anywhere and this still only returns 5 users. The 5 users with no permissions are not returned.

You didn't show the code you used for the simpler example, but my guess would be you are still adding the relation? The relation will create an inner join between User and UserPermission, which would effectively filter out users who don't have any UserPermissions.

HTH,

Phil

Also, if you are struggling with Prefetch Paths, check out this excellent blog post by David Elizondo (who is also on the LLBLGen support staff), which pretty much explains everything you need to know about Prefetch Paths, including a lot of the common mistakes people make:

http://www.llblgening.com/archive/2009/10/prefetchpaths-in-depth/

Phil is right.

Passing such relations make the joins discriminate such users that doesn't have any permissions. So if you want all users, just pass null in the filter bucket.

Here is an approximate final code.

[Test]
public void ShouldReturnFilteredPermissions()
{
    var connectionString = ConfigurationHelper.GetConnectionString("Authorisation");

    var userPermissionPathFilter = new PredicateExpression();
    userPermissionPathFilter.Add(PermissionFields.Description == "Serve_Scooby_Snack");

    var userPermissionPathRels = new RelationCollection(UserPermissionEntity.Relations.PermissionEntityUsingPermissionId)); 

    var prefetch = new PrefetchPath2(EntityType.UserEntity);
    prefetch.Add(UserEntity.PrefetchPathUserPermission, 0, userPermissionPathFilter, userPermissionPathRels)
          .SubPath.Add(UserPermissionEntity.PrefetchPathPermission);

    var mainFilterBucket = new RelationPredicateBucket();
    mainFilterBucket.PredicateExpression.Add(filterExpression);
    mainFilterBucket.Relations.Add(UserEntity.Relations.UserPermissionEntityUsingUserId);   
    mainFilterBucket .Relations.Add(UserPermissionEntity.Relations.PermissionEntityUsingPermissionId);

    var entities = new EntityCollection<UserEntity>();
    using (var adaptor = new DataAccessAdapter(connectionString))
    {
        // return just users which have "Serve_Scooby_Snack" permission
        adaptor.FetchEntityCollection(entities, mainFilterBucket , 0, null, prefetch);

        // return all users
        adaptor.FetchEntityCollection(entities, null, 0, null, prefetch);
    }

    ...
}

Thanks Guys,

So if I understand correctly if I want a deep fetch of all objects I specify a prefetch path but no relations in the predicate bucket? A relation in the predicate bucket specifies an inner join end therfore filters the results.

Also thanks for the link to the blog post - I wish I had found that a few days ago!

~Brett

You got it right