I have a query with 2 general requirements: 1) It has to dynamically filter and/or sort on fields in several separate (related) entities 2) It has to return columns from each of the related tables.

The results (EntityCollection<T>) get projected into a custom type.

The filtering/sorting is no big deal, I just add Relations to the IRelationPredicateBucket for the filterable/sortable entities. Works fine. However, when I try to access columns in the related entities for the projection, it turns out that all the references to the entities are null.

Now, I can fix <b>that</b> issue by adding Prefetches to each of the related entities. However, then I end up with BOTH a query with a number of JOINs in it, AND a full set of Prefetch queries. Which is a lot of extra work considering that all the relevant tables are joined in to the main query in the first place.

I thought there was a way to avoid this, but I have not been able to find such. Any ideas?

(FWIW, I noticed that this was <b>not</b> the case when I used the Linq provider- that yielded a single query. However, I can't use Linq for this due to some of the limitations of Linq.)

This is using v2.6 Final, 2.6.08.0903, Adapter templates, C# 3.0, SQL 2005.

Thanks again!

So, you are adding relations dinamically, adding filter dynamically, and you wan to be able to add the prefetchPaths dinamically.

Could you post the code snippet where you are adding the relations and the filter?

No- the problem is that there is a static set of fields that i need to display. However, i also need to sort and/or filter on each field in that set, depending on user input. I'm not adding the relations dynamically, I just add them all:

//add Relations for all filterable/sortable fields
 IRelationPredicateBucket bucket = new RelationPredicateBucket(SubassemblyFields.AssemblyNum % filterVal);          
            bucket.Relations.Add(SubassemblyEntity.Relations.C_StatusEntityUsingStatusId);
            bucket.Relations.Add(SubassemblyEntity.Relations.C_SubassemblyTypeEntityUsingSubassemblyTypeId);
            bucket.Relations.Add(SubassemblyEntity.Relations.FMUserAccountEntityUsingCreatedByUserAccountId, Constants.ALIAS_USER_CREATEDBY);
            bucket.Relations.Add(SubassemblyEntity.Relations.FMUserAccountEntityUsingModifiedByUserAccountId, Constants.ALIAS_USER_MODIFIEDBY);
            bucket.Relations.Add(SubassemblyEntity.Relations.C_LockStatusEntityUsingLockStatusId);
            bucket.Relations.Add(SubassemblyEntity.Relations.FMUserAccountEntityUsingLockedByUserAccountid, Constants.ALIAS_USER_LOCKEDBY, JoinHint.Left);
            

there is other code that builds up the Predicates and SortExpression. That works fine, because all the sortable/filterable fields are included in the above set of relations.

The problem comes when I want to project my resultset (an EntityCollection<SubassemblyEntity> in this case) into another form, for displaying in a grid-type control. Even though each of the tables I need has been JOINed into the query, I still have to add a matching set of Prefetches in order to actually access the related Entities. For example, to display the field C_LockStatus.Code in my search results. So, in order to access the related entities, I have to add this:

            IPrefetchPath2 pf = new PrefetchPath2(EntityType.SubassemblyEntity);
            //need to display C_LockStatus.Code in results view
            pf.Add(SubassemblyEntity.PrefetchPathC_LockStatus);
            //need to display C_SubassemblyType.Code in results view
            pf.Add(SubassemblyEntity.PrefetchPathC_SubassemblyType);
            //need to display Lock UserAccount.DisplayName in results view
            pf.Add(SubassemblyEntity.PrefetchPathLockedByFMUserAccount);
            //need to display Modified UserAccount.DisplayName in results view
            pf.Add(SubassemblyEntity.PrefetchPathModifiedByFMUserAccount);
            //need to display C_Status.Code in results view
            pf.Add(SubassemblyEntity.PrefetchPathC_Status);

Then it all works. However, it ends up generating 6 queries instead of 1. Which I would expect, since I'm having to use the Prefetches.

My question is- since the related entities are already being included in the 'main' query (using those Relations), is there some way to access them without adding the per-related-entity Prefetches? It's basically fetching the same data multiple times in this scenario.

Thanks.

My question is- since the related entities are already being included in the 'main' query (using those Relations), is there some way to access them without adding the per-related-entity Prefetches? It's basically fetching the same data multiple times in this scenario.

No. Relations are used basically for filtering purposes. So, if you need to filter on some C_LockStatus fields (C_LockStatus is related to SubassemblyEntity) then you need to add that relation. Adding this relation doesn't mean that the related data is fetched, its only for filtering (or sorting).

Prefetch is another thing: given an entity A and its related entity B, if you want to fetch A and access the fields on B, you need to prefetch B (A->B). Even if you use LLBL2LINQ (or another LINQ provider out there) this is the way it works, because projecting a big query into some graph would be tricky anyway (performance, complexity, etc.).

Depending on the number of related prefetched entities, you may want to Optimize prefetchPaths.

Hope that helps