I am facing a prefetch problem. It goes like this: A(1:m)B, C(1:m)B , D(1:m)B

I want to fetch A Collection and prefetch C through B but filtering on a field in B. This is what I have done so far, but it brings rows from C which do not meet criteria specified. Any ideas how to go about this?

            //build relation
            IRelationCollection AToCRelation = new RelationCollection();
            AToCRelation.Add(AEntity.Relations.BEntityUsingB_AID);
            AToCRelation.Add(BEntity.Relations.CEntityUsingB_CID);

            //filter
            IPredicateExpression BFilter = new PredicateExpression(PredicateFactory.CompareValue(BFieldIndex.B_DID, ComparisonOperator.Equal, 'ABC'));
            
            //sorters
            ISortExpression ASorter = new SortExpression(SortClauseFactory.Create(AFieldIndex.AID, SortOperator.Ascending));
            ISortExpression CSorter = new SortExpression(SortClauseFactory.Create(CFieldIndex.CField2, SortOperator.Ascending));
            CSorter.Add(SortClauseFactory.Create(CFieldIndex.CField3, SortOperator.Ascending));


            EntityCollection AColl = new EntityCollection(new AEntityFactory());

            PrefetchPath2 AtoCPrefetchPath = new PrefetchPath2((int)EntityType.AEntity);
            AtoCPrefetchPath.Add(
                AEntity.PrefetchPathCCollectionViaB
                , 0
                , BFilter
                , AToCRelation
                , CSorter
                );
            
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(AColl, null, 0, ASorter, AtoCPrefetchPath);
            }

Im using v. 1.0.2005.1. Thanks

Give this a try. I use the B filter when defining which AEntities to retrieve and when defining which CEntities to fetch using the prefetch.

//build relation
            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(AEntity.Relations.BEntityUsingAId);

            //filter
            IPredicateExpression BFilter = new PredicateExpression();
            BFilter.Add(PredicateFactory.CompareValue(BFieldIndex.BName, ComparisonOperator.Equal, 'b'));
            bucket.PredicateExpression.Add(BFilter);
            
            //sorters
            ISortExpression ASorter = new SortExpression(SortClauseFactory.Create(AFieldIndex.AId, SortOperator.Ascending));


            EntityCollection AColl = new EntityCollection(new AEntityFactory());

            IRelationCollection Brelations = new RelationCollection();
            Brelations.Add(CEntity.Relations.BEntityUsingCId);
            PrefetchPath2 AtoCPrefetchPath = new PrefetchPath2((int)EntityType.AEntity);
            AtoCPrefetchPath.Add(AEntity.PrefetchPathCCollectionViaB, 0, BFilter, Brelations);
            
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(AColl, bucket, 0, ASorter, AtoCPrefetchPath);
            }

Hi bclubb,

I gave it a try, but does not work. One of the sqls comes out like this:

exec sp_executesql N'SELECT DISTINCT [E641].[dbo].[A].[AID] AS [AID0], [E641].[dbo].[C].[CID] AS [CID1] 
FROM ((( 
[E641].[dbo].[A]  
INNER JOIN [E641].[dbo].[B] [LPA_U1]  ON  [E641].[dbo].[A].[AID]=[LPA_U1].[B_AID]) 
INNER JOIN [E641].[dbo].[C]  ON  [E641].[dbo].[C].[CID]=[LPA_U1].[B_CID]) 
INNER JOIN [E641].[dbo].[B]  ON  [E641].[dbo].[C].[CID]=[E641].[dbo].[B].[B_CID]) 
WHERE 
( ( ( 
    ( [LPA_U1].[B_AID] = @B_AID1) OR ( [LPA_U1].[B_AID] = @B_AID2) 
    OR ( [LPA_U1].[B_AID] = @B_AID3) OR ( [LPA_U1].[B_AID] = @B_AID4)) 
    AND ( ( [E641].[dbo].[B].[B_DID] = @B_DID5)
)))', N'@B_AID1 varchar(20),@B_AID2 varchar(20),@B_AID3 varchar(20),@B_AID4 varchar(20),@B_DID5 varchar(5)', @B_AID1 = 'ABC', @B_AID2 = 'EFG', @B_AID3 = 'HIJ', @B_AID4 = 'KLM', @B_DID5 = 'BELL'

If it were

exec sp_executesql N'SELECT DISTINCT [E641].[dbo].[A].[AID] AS [AID0], [E641].[dbo].[C].[CID] AS [CID1] 
FROM ((( 
[E641].[dbo].[A]  
INNER JOIN [E641].[dbo].[B] [LPA_U1]  ON  [E641].[dbo].[A].[AID]=[LPA_U1].[B_AID]) 
INNER JOIN [E641].[dbo].[C]  ON  [E641].[dbo].[C].[CID]=[LPA_U1].[B_CID]) 
) 
WHERE 
( ( ( 
    ( [LPA_U1].[B_AID] = @B_AID1) OR ( [LPA_U1].[B_AID] = @B_AID2) 
    OR ( [LPA_U1].[B_AID] = @B_AID3) OR ( [LPA_U1].[B_AID] = @B_AID4)) 
    AND ( ( [LPA_U1].[B_DID] = @B_DID5)
)))', N'@B_AID1 varchar(20),@B_AID2 varchar(20),@B_AID3 varchar(20),@B_AID4 varchar(20),@B_DID5 varchar(5)', @B_AID1 = 'ABC', @B_AID2 = 'EFG', @B_AID3 = 'HIJ', @B_AID4 = 'KLM', @B_DID5 = 'BELL'

it would be perfect.

Try the following code:

 //build relation
            IRelationCollection CToBRelation = new RelationCollection();
            CToBRelation.Add(CEntity.Relations.BEntityUsingB_CID);

            //filter
            IPredicateExpression BFilter = new PredicateExpression(PredicateFactory.CompareValue(BFieldIndex.B_DID, ComparisonOperator.Equal, 'ABC'));
            
            //sorters 
            .
            .

            EntityCollection AColl = new EntityCollection(new AEntityFactory());

            PrefetchPath2 AtoCPrefetchPath = new PrefetchPath2((int)EntityType.AEntity);
            AtoCPrefetchPath.Add(
                AEntity.PrefetchPathCCollectionViaB
                , 0
                , BFilter
                , CToBRelation
                , CSorter
                );
            
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(AColl, null, 0, ASorter, AtoCPrefetchPath);
            }

Just use CToBRelation instead of AToCRelation. Because the prefetchPath is fetching C Entities and you are filtering them with a field in B entity. So you should specify the relation between C and B.

Hi Walaa,

Your suggestion is the same as bclubb's, except without the bucket, isnt it?

Oh yes Sorry,

So maybe it will work if you try not to add any relation to the prefetchPath, as already the "via" relation joins the middle table automatically, that's why you see B joined twice.

Without that it gives an alias not found error.

I have been searching forums for some similar examples, and there doesnt seem to be any where the filter is on the relation in between.

Please post real code. Also the runtime lib version etc. (build nr, see guidelines). As you filter on the intermediate entity, you've to specify an OBJECT alias, which has the name "B", The B entity in the a-c m:n relation is aliased as 'B'. Keep in mind that this has been changed during the lifetime of v1.0.2005.1. In the beginning this was aliased differently, so it's important to know which version (buildnr) you're working with.