I'd like to prefetch some data from three related tables (KubusBoom -> BoomElement -> Tekstblok). The middle (BoomElement) table references itself (upto three levels deep in practice). A Tekstblok may be referenced by a BoomEelement located at any level. I apply a predicate to both the first and the last table (using SubPath) and try to retrieve all related data at once using the GetMulti() method.

// Arrange to fetch all related BoomElementen and Tekstblokken at once
IPrefetchPath prefetchPath = new PrefetchPath((int)EntityType.KubusBoomEntity);
prefetchPath.Add(KubusBoomEntity.PrefetchPathBoomElementCollectionViaBoomElement).SubPath.Add( BoomElementEntity.PrefetchPathTekstblok, Construct_TekstblokPredicate());

// Get selected KubusBoom via selected ZaakType and all related entities
KubusBoomCollection colKubusBoom = new KubusBoomCollection();
colKubusBoom.GetMulti(Construct_KubusBoomstructuurPredicate(selectedZaakTypeID, selectedNiveau, currentUser), prefetchPath);

I do always retrieve a single, top-level (KubusBoom) entity as intended. I however also always get all related Tekstblokken; the SubPath predicate seems to be ignored.

When I retrieve the same data step-by-step applying the same predicates all is well but I would really like to prefetch the whole lot at once. Anybody here have an idea how to achieve this?

• LLBLGen Pro v1.0.2005.1 Final
• SelfServicing
• MS .NET Framework v2.0.50727
• SQL Server v8.00.2039

There is an overload of Add() for prefetch paths that contains the ability to add a predicate expression for that prefetch. I'm not sure I understand the problem otherwise.

You fetch the KubusBoom that you expected. Then for each of the KubusBoom entities in the KubusBoomCollection you receive a collection of BoomElementEntities that are related to that particular KubusBoom entity. Then each BoomElementEntity in the BoomElementEntityCollection has their related Tekstbloks.

Are you saying that the boomElementEntity.Tekstblok collection contains the wrong Tekstblok entities?

Could you also try using a Context together with the prefetch path?

@bclubb

There is an overload of Add() for prefetch paths that contains the ability to add a predicate expression for that prefetch.

I'm already using that: prefetchPath.Add(KubusBoomEntity.PrefetchPathBoomElementCollectionViaBoomElement ).SubPath.Add(BoomElementEntity.PrefetchPathTekstblok, Construct_TekstblokPredicate());

Are you saying that the boomElementEntity.Tekstblok collection contains the wrong Tekstblok entities?

Not exactly: I'm trying to say I get all related Tekstblok entities, unfiltered. Basically a Tekstblok may have been deleted by the user which results in it being flagged (Deleted = true). I don't want to retrieve the Tekstblokken that have been deleted.

@Otis

Could you also try using a Context together with the prefetch path?

I'll look into that and report back.

Ok so Construct_TekstblokPredicate() creates a PredicateExpression that will result in something like deleted == false. Have you enabled the trace to see what the sql looks like? Is that PredicateExpression not being applied?

Ok, I'm sorry to do this but my project is out of time and over budget so I'm going to forget about prefetching in above case. Thanks for your suggestions anyway; apologies for having them fall on deaf ears.

Later...