I have hierarchy setup where the last/third level is linked back to both the parent and the grandparent. I'm trying to get the prefetch to return just the entities that match the parent and grandparent but instead I end up with the entities that match just the parent. I am using self servicing LLBLGen v2.6 on SQL Server 2005.

An example of the hierarchy achieved through linking tables on both Strategy->Goal and Activity->Strategy/Goal is: Goal1 -Strategy1 --Activity1 (Linked to Strategy1 and Goal1) Goal1 -Strategy2 --Activity2 (Linked to Strategy2 and Goal1)

Instead both activities are getting prefetched under the strategy thus: Goal1 -Strategy1 --Activity1 --Activity2 Goal1 -Strategy2 --Activity1 --Activity2

Basically I need to tell the prefetch that fetches the Activity collection to merge/etc on both the StrategyId and GoalId.

Tables are as follows: Goal->GoalStrategy->Strategy->StrategyActivity (PK of 3 fields: GoalId, StrategyId, ActivityId)->Activity

The below code is what I have so far. I've played around with different filters/relations/etc in the prefetch but have not yet figured out what the trick is.

GoalCollection goals = new GoalCollection();
            
IPredicateExpression goalFilter = new PredicateExpression(GoalFields.EntityId == entityId);
IPrefetchPath prefetchPath = new PrefetchPath((int)EntityType.GoalEntity);

prefetchPath.Add(GoalEntity.PrefetchPathStrategyCollectionViaGoalStrategy).SubPath.Add( StrategyEntity.PrefetchPathActivityCollectionViaStrategyActivity);

//Not really being used but....
RelationCollection relationsToUse = new RelationCollection();
relationsToUse.Add(GoalEntity.Relations.GoalStrategyEntityUsingGoalId);
relationsToUse.Add(GoalEntity.Relations.StrategyActivityEntityUsingGoalId);
relationsToUse.Add(GoalStrategyEntity.Relations.StrategyEntityUsingStrategyId);
relationsToUse.Add(StrategyEntity.Relations.StrategyActivityEntityUsingStrategyId);
relationsToUse.Add(StrategyActivityEntity.Relations.ActivityEntityUsingActivityId);

goals.GetMulti(goalFilter, 0, null, relationsToUse, prefetchPath);

Indeed the problem seems to be the way you setup the prefetchpath: you are prefetching the subPath of the first path. You instead should reach the activities through goal. Something like:

prefetchPath.Add(GoalEntity.PrefetchPathStrategyCollectionViaGoalStrategy)
prefetchPath.Add(GoalEntity.PrefetchPath(... reach the activiy through not by strategy but by goal ...);

Don't get 100% how to achieve that based on your relations, but you got the point.

daelmo wrote:

Indeed the problem seems to be the way you setup the prefetchpath: you are prefetching the subPath of the first path. You instead should reach the activities through goal. Something like:

prefetchPath.Add(GoalEntity.PrefetchPathStrategyCollectionViaGoalStrategy)
prefetchPath.Add(GoalEntity.PrefetchPath(... reach the activiy through not by strategy but by goal ...);

Don't get 100% how to achieve that based on your relations, but you got the point.

The problem is I need to reach the Activity through the goal and the strategy. I don't know how LLBLGen handles two prefetch paths to the same collection but I have a feeling that only one ends up working as I'm getting the same results.

prefetchPath.Add(GoalEntity.PrefetchPathStrategyCollectionViaGoalStrategy).SubPath.Add(StrategyEntity.PrefetchPathActivityCollectionViaStrategyActivity);
prefetchPath.Add(GoalEntity.PrefetchPathActivityCollectionViaStrategyActivity);

Does that help make more sense into what I'm trying to do? Basically when I'm fetching the Activities I need it to select and merge/etc by GoalId and StrategyId instead of just one.

-Keith

Tables are as follows: Goal->GoalStrategy->Strategy->StrategyActivity (PK of 3 fields: GoalId, StrategyId, ActivityId)->Activity

IMHO, you should avoid 3-way relations Instead I recommend having the following schema:

Goal---GoalStrategy---Strategy | GoalStrategyActivity (PK of 2 fields: GoalStrategyId, ActivityId) | Activity

prefetchPath.Add(GoalEntity.PrefetchPathActivityCollectionViaStrategyActivity);

From your model, I don't know how do get the above code comiled. You don't have any inheritance hierarchies in there, do you?

I would agree with you that your solution is better, unfortunately it's a legacy system and only two places in the system are using this new relationship so it's not worth changing everything. Your idea spurred me to the resolution which was painfully obvious. Allow the duplicate Activities to be returned from the database and then when actually using the returned data to populate the tree just check the current goal that is being looped against the current StrategyActivity.

Thanks for your help,

Just for informational purposes the code is as follows:

GoalCollection goals = new GoalCollection();
IPrefetchPath prefetchPath = new PrefetchPath((int)EntityType.GoalEntity);
prefetchPath.Add(GoalEntity.PrefetchPathStrategyCollectionViaGoalStrategy).SubPath.Add(StrategyEntity.PrefetchPathStrategyActivity).SubPath.Add(StrategyActivityEntity.PrefetchPathActivity);

goals.GetMulti(new PredicateExpression(GoalFields.EntityId == entityId), 0, null, null, prefetchPath);

            foreach (GoalEntity goal in GoalCollection.GetActionPlanTreeForEntity(SiteSettings.CurrentUserEntityId))
            {
                GoalDragTreeNode goalNode = new GoalDragTreeNode(goal.GoalId, goal.Title);
                PlanTree.Nodes.Add(goalNode);
                foreach (StrategyEntity strategy in goal.StrategyCollectionViaGoalStrategy)
                {
                    StrategyDragTreeNode strategyNode = new StrategyDragTreeNode(strategy.StrategyId, strategy.Title);
                    goalNode.Nodes.Add(strategyNode);
                    foreach (StrategyActivityEntity strategyActivity in strategy.StrategyActivity)
                    {
                        if (goal.GoalId == strategyActivity.GoalId)
                        {
                            ActivityDragTreeNode activityNode = new ActivityDragTreeNode(strategyActivity.Activity);
                            strategyNode.Nodes.Add(activityNode);
                        }
                    }
                }
            }