Hi Guys

We have a table structure - User-->Accounts-->Subscriptions

We're trying to get this structure of objects in a fetch, we need to apply a filter to all levels (user, account, subscription)

So far, we have the following

        Dim Users As New EntityCollection(Of UserEntity)
        Dim prefetchPath As IPrefetchPath2 = New PrefetchPath2(CType(EntityType.UserEntity, Integer))
        prefetchPath.Add(UserEntity.PrefetchPathAccounts).SubPath.Add(AccountEntity.PrefetchPathSubscriptions).SubPath.Add(SubscriptionEntity.PrefetchPathTrackers)

        Dim bucket As New RelationPredicateBucket
        bucket.Relations.Add(AccountEntity.Relations.UserEntityUsingUserId)
        bucket.Relations.Add(SubscriptionEntity.Relations.AccountEntityUsingAccountId)
        bucket.PredicateExpression.AddWithAnd(New FieldBetweenPredicate(SubscriptionFields.ExpiresOn, Nothing, SubscriptionRenewalStartDate, SubscriptionRenewalEndDate))
        bucket.PredicateExpression.AddWithAnd(SubscriptionFields.AutoRenew = True)
        bucket.PredicateExpression.AddWithAnd(SubscriptionFields.IsActive = True)
        bucket.PredicateExpression.AddWithAnd(UserFields.Status > 0)
        bucket.PredicateExpression.AddWithAnd(AccountFields.Status > 0)
        bucket.PredicateExpression.AddWithAnd(AccountFields.Type = Utils.AccountType.Standard)

        Dim Sorter As New SortExpression()
        Sorter.Add(New SortClause(SubscriptionFields.ExpiresOn, Nothing, SortOperator.Descending))

        Using Adapter As New DataAccessAdapter
            Adapter.FetchEntityCollection(Users, bucket, Nothing, Sorter, prefetchPath)
        End Using

The filter on UserFIelds and AccountFields works fine but the filters on the SubscriptionFields have no effect.

        bucket.PredicateExpression.AddWithAnd(SubscriptionFields.AutoRenew = True)
        bucket.PredicateExpression.AddWithAnd(SubscriptionFields.IsActive = True

Am I missing something?

From the thread's title, it's not clear to me if you want to filter on the main collection (UserEntity) or by levels (filter the Account collection for each User, then filter the Subscriptions for each Account, etc). If you want to filter each level, you must pass a filter on each path level. See this article that explains the difference: http://www.llblgening.com/archive/2009/10/prefetchpaths-in-depth/#filteringandsorting

Also, it's recommended to add the relations in the logical order. For instance:

Dim bucket As New RelationPredicateBucket
bucket.Relations.Add(UserEntity.Relations.AccountEntityUsingUserId)
bucket.Relations.Add(AccountEntity.Relations.SubscriptionEntityUsingAccountId)

If you are still in troubles, please post the Generated Sql and additional info (Runtime library version, more info about the relations, example data set, expected results and actual results, etc): http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7725

Thanks

I want to filter by levels. 1. Users 2. UserAccounts 3. AccountSubscriptions

There were multiple queries returned in the debugger. But the one in question is....

SELECT AccountId, AutoRenew, CreatedBy, CreatedOn, ExpiresOn, Id, IsActive, Model, Name, OrderId, RefreshInterval, RenewedOn, RenewedWithSubscriptionId, StartsOn, UnitPrice, UpdatedBy, UpdatedOn, VisibleRetention
FROM Subscription
WHERE (AccountId IN (11656578, 46, 1, 47, 11656547))

The relation is added but the filter is not there

bucket.PredicateExpression.AddWithAnd(SubscriptionFields.AutoRenew = True)
bucket.PredicateExpression.AddWithAnd(SubscriptionFields.IsActive = True)

llbl version: 3.5.12.0317

The filter you have added, was added to the Main fetch. i.e. to the first Query fetching the users.

Instead you should add the filter to the specific PrefetchPath. Please check all overloads of the prefetchPath.Add()

Walaa wrote:

Please check all overloads of the prefetchPath.Add()

You're a genius !, you should word work for SolutionDesign

Changed it to the below and works like a dream

        Dim Users As New EntityCollection(Of UserEntity)
        Dim prefetchPath As IPrefetchPath2 = New PrefetchPath2(CType(EntityType.UserEntity, Integer))

        Dim UserBucket As New RelationPredicateBucket
        UserBucket.PredicateExpression.AddWithAnd(UserFields.Status > 0)
        UserBucket.Relations.Add(AccountEntity.Relations.UserEntityUsingUserId)
        UserBucket.Relations.Add(SubscriptionEntity.Relations.AccountEntityUsingAccountId)

        Dim AccountsFilter As New PredicateExpression
        AccountsFilter.AddWithAnd(AccountFields.Status > 0)
        AccountsFilter.AddWithAnd(AccountFields.Type = Utils.AccountType.Standard)

        Dim SubscriptionsFilter As New PredicateExpression
        SubscriptionsFilter.AddWithAnd(SubscriptionFields.IsActive = True)
        SubscriptionsFilter.AddWithAnd(SubscriptionFields.AutoRenew = True)
        SubscriptionsFilter.AddWithAnd(New FieldBetweenPredicate(SubscriptionFields.ExpiresOn, Nothing, SubscriptionRenewalStartDate, SubscriptionRenewalEndDate))

        prefetchPath.Add(UserEntity.PrefetchPathAccounts, Nothing, AccountsFilter).SubPath.Add(AccountEntity.PrefetchPathSubscriptions, Nothing, SubscriptionsFilter).SubPath.Add(SubscriptionEntity.PrefetchPathTrackers)

        Dim Sorter As New SortExpression()
        Sorter.Add(New SortClause(SubscriptionFields.ExpiresOn, Nothing, SortOperator.Descending))

        Using Adapter As New DataAccessAdapter
            Adapter.FetchEntityCollection(Users, UserBucket, Nothing, Sorter, prefetchPath)
        End Using