LLBLGen Pro version 2.6 (final), SelfService +Northwind db

Hello, I'd like to filter parent table using a condition for its child table and filter the child table using the same condition as well. Eg. using Northwind database tables Suppliers and Products I tried to select suppliers which offer products beginning with "Sir%" this way:

private SuppliersCollection _suppliersCollection;
// procedure body
IPredicateExpression predicateExpression =
                new PredicateExpression { new FieldLikePredicate( ProductsFields.ProductName, @"Sir%" ) };
IRelationCollection relationCollection =
                new RelationCollection { SuppliersEntity.Relations.ProductsEntityUsingSupplierId };
relationCollection.Add(SuppliersEntity.Relations.ProductsEntityUsingSupplierId).CustomFilter =
                predicateExpression;
IPrefetchPath prefetchPath =
                new PrefetchPath( EntityType.SuppliersEntity ) { SuppliersEntity.PrefetchPathProducts };

_suppliersCollection.GetMulti(predicateExpression, 0, null, relationCollection, prefetchPath);

As the result I received 2 rows of Suppliers table (that's ok) containing the products beginning with "Sir%" and in related Products collections all the products (wrong) of each supplier. I expect that the CustomFilter for the relations will filter the Products collections to the items beginning with "Sir%". Could you please advice how to properly apply the predicateExpression both for Suppliers and Products?

Hi etk,

The CustomFiler only add the predicate on the join of the main fetch (suppliers), so in this case it has no value. The second fetch (products) is another routine. To filter on that prefetchPath use the filter on the prefetchPath.Add(...) overload that accepts a PredicateExpession. Something like:

IPredicateExpression predicateExpression =
     new PredicateExpression { new FieldLikePredicate( ProductsFields.ProductName, @"Sir%" ) };

IRelationCollection relationCollection =
                new RelationCollection { SuppliersEntity.Relations.ProductsEntityUsingSupplierId };

IPrefetchPath prefetchPath =new PrefetchPath( EntityType.SuppliersEntity);
prefetchPath.Add(SuppliersEntity.PrefetchPathProducts, 
     0, predicateExpression , null, null);

_suppliersCollection.GetMulti(predicateExpression, 0, null, relationCollection, prefetchPath);

More about this: http://www.llblgening.com/archive/2009/10/prefetchpaths-in-depth/#filteringandsorting

Works Thanks for the help!