Hi, I'm having trouble with the following situation and could really use some help.

Tables:

TestPlan (ID, fields...) ScenarioToTestPlan (FKTestPlanID, FKScenarioID, order) Scenario (ID, fields...) TestCaseToScenario(FKScenarioID, FKTestCaseID, order) TestCase (ID, fields...)

What I'm trying to do is to retrieve the TestPlan with prefetched Scenarios ordered by ScenarioToTestPlan.order. Also, I'm trying to prefetch all TestCases for Scenarios ordered by TestCaseToScenario.order.

I'm encountering three problems: 1 - All duplicates in the m:n tables are eliminated. I need all records including dups. 2 - The sort is not correct. 3 - I've lost my mind trying to solve this problem.

IPrefetchPath2 prefetch = new PrefetchPath2((int)EntityType.TestPlanEntity);
            IRelationCollection scenarioRelation = new RelationCollection();
            scenarioRelation.Add(TestPlanEntity.Relations.ScenarioToTestplanEntityUsingFkTestPlanId);
            scenarioRelation.Add(ScenarioToTestplanEntity.Relations.ScenarioEntityUsingFkScenarioId);

            IRelationCollection testCaseRelation = new RelationCollection();
            testCaseRelation.Add(ScenarioEntity.Relations.TestcaseToScenarioEntityUsingFkScenarioId);
            testCaseRelation.Add(TestcaseToScenarioEntity.Relations.TestcaseEntityUsingFkTestCaseId);

            SortExpression scenarioSort = new SortExpression(
                SortClauseFactory.Create(ScenarioToTestplanFieldIndex.Order, SortOperator.Ascending));
            SortExpression testCaseSort = new SortExpression(
                SortClauseFactory.Create(TestcaseToScenarioFieldIndex.Order, SortOperator.Ascending));

            prefetch.Add(TestPlanEntity.PrefetchPathScenarioCollectionViaScenarioToTestplan, 0, null, scenarioRelation, scenarioSort).SubPath.Add(
                ScenarioEntity.PrefetchPathTestcaseCollectionViaTestcaseToScenario, 0, null, testCaseRelation, testCaseSort);
                        
            DataAdapter.db.FetchEntity(testPlan, prefetch);

Any help or insights are greatly appreciated. Thanks...

Oh yeah, I'm using V1.0.2005.1

1- All duplicates in the m:n tables are eliminated. I need all records including dups.

For each TestPlan there can be many Scenarioes, but there can't be a duplicated scenario, can it? In ScenarioToTestPlan is the PK composed of(FKTestPlanID, FKScenarioID)?

Your code looks fine, except you only need the following relations:

            IRelationCollection scenarioRelation = new RelationCollection();
            scenarioRelation.Add(ScenarioEntity.Relations.ScenarioToTestplanEntityUsingFKScenarioID);

            IRelationCollection testCaseRelation = new RelationCollection();
            testCaseRelation.Add(TestcaseEntity.Relations.TestcaseToScenarioEntityFkTestCaseId);

Now if you still have problems, then I suggest: You post the LLBLGen Pro runtime library build used. And post the generated SQL queries. (ref: manual "Generated code - Troubleshooting and debugging")

Walaa, Correcting the relations has resolved the issue with the sortorder, but I'm still having problems with the dups.

For each TestPlan there can be many Scenarioes, but there can't be a duplicated scenario, can it? In ScenarioToTestPlan is the PK composed of(FKTestPlanID, FKScenarioID)?

There cannot be duplicate scenarios in the Scenario table, but in the ScenarioToTestplan table, the same scenario can be related to the same test plan multiple times. I'm using Access here so I can't create a key using both of the FKs. I forgot to mention in my original post that the m:n tables have an ID field as the PK.

The runtime is v. 1.0.20051.60719

Here is my updated code:

            IPrefetchPath2 prefetch = new PrefetchPath2((int)EntityType.TestPlanEntity);
            IRelationCollection scenarioRelation = new RelationCollection();
            scenarioRelation.Add(ScenarioEntity.Relations.ScenarioToTestplanEntityUsingFkScenarioId);

            IRelationCollection testCaseRelation = new RelationCollection();
            testCaseRelation.Add(TestcaseEntity.Relations.TestcaseToScenarioEntityUsingFkTestCaseId);

            SortExpression scenarioSort = new SortExpression(
                SortClauseFactory.Create(ScenarioToTestplanFieldIndex.Order, SortOperator.Ascending));
            SortExpression testCaseSort = new SortExpression(
                SortClauseFactory.Create(TestcaseToScenarioFieldIndex.Order, SortOperator.Ascending));

            prefetch.Add(TestPlanEntity.PrefetchPathScenarioCollectionViaScenarioToTestplan, 0, null, scenarioRelation, scenarioSort).SubPath.Add(
                ScenarioEntity.PrefetchPathTestcaseCollectionViaTestcaseToScenario, 0, null, testCaseRelation, testCaseSort);
                        
            DataAdapter.db.FetchEntity(testPlan, prefetch);

And here are the generated queries:

Query: SELECT [TestPlan].[ID] AS [Id], [TestPlan].[Guid], [TestPlan].[FK_ProjectID] AS [FkProjectId], [TestPlan].[FK_TestplanFolderID] AS [FkTestplanFolderId], [TestPlan].[Name], [TestPlan].[Description] FROM [TestPlan] WHERE ( ( [TestPlan].[ID] = @Id1)) Parameter: @Id1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 54.

Query: SELECT [Scenario].[ID] AS [Id], [Scenario].[Guid], [Scenario].[FK_ProjectID] AS [FkProjectId], [Scenario].[FK_ScenarioFolderID] AS [FkScenarioFolderId], [Scenario].[Name], [Scenario].[Description] FROM ((( [TestPlan] INNER JOIN [Scenario_To_Testplan] [LPA_S1] ON [TestPlan].[ID]=[LPA_S1].[FK_TestPlanID]) INNER JOIN [Scenario] ON [Scenario].[ID]=[LPA_S1].[FK_ScenarioID]) INNER JOIN [Scenario_To_Testplan] ON [Scenario].[ID]=[Scenario_To_Testplan].[FK_ScenarioID]) WHERE ( ( ( ( [LPA_S1].[FK_TestPlanID] = @FkTestPlanId1)))) ORDER BY [Scenario_To_Testplan].[Order] ASC Parameter: @FkTestPlanId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 54.

Query: SELECT DISTINCT [TestPlan].[ID] AS [Id0], [Scenario].[ID] AS [Id1] FROM ((( [TestPlan] INNER JOIN [Scenario_To_Testplan] [LPA_S1] ON [TestPlan].[ID]=[LPA_S1].[FK_TestPlanID]) INNER JOIN [Scenario] ON [Scenario].[ID]=[LPA_S1].[FK_ScenarioID]) INNER JOIN [Scenario_To_Testplan] ON [Scenario].[ID]=[Scenario_To_Testplan].[FK_ScenarioID]) WHERE ( ( ( ( [LPA_S1].[FK_TestPlanID] = @FkTestPlanId1)))) Parameter: @FkTestPlanId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 54.

Query: SELECT [Testcase].[ID] AS [Id], [Testcase].[Guid], [Testcase].[FK_ProjectID] AS [FkProjectId], [Testcase].[FK_TestcaseFolderID] AS [FkTestcaseFolderId], [Testcase].[Name], [Testcase].[Description] FROM ((( [Scenario] INNER JOIN [Testcase_To_Scenario] [LPA_T1] ON [Scenario].[ID]=[LPA_T1].[FK_ScenarioID]) INNER JOIN [Testcase] ON [Testcase].[ID]=[LPA_T1].[FK_TestCaseID]) INNER JOIN [Testcase_To_Scenario] ON [Testcase].[ID]=[Testcase_To_Scenario].[FK_TestCaseID]) WHERE ( ( ( ( [LPA_T1].[FK_ScenarioID] = @FkScenarioId1) OR ( [LPA_T1].[FK_ScenarioID] = @FkScenarioId2)))) ORDER BY [Testcase_To_Scenario].[Order] ASC Parameter: @FkScenarioId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 550. Parameter: @FkScenarioId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 547.

Query: SELECT DISTINCT [Scenario].[ID] AS [Id0], [Testcase].[ID] AS [Id1] FROM ((( [Scenario] INNER JOIN [Testcase_To_Scenario] [LPA_T1] ON [Scenario].[ID]=[LPA_T1].[FK_ScenarioID]) INNER JOIN [Testcase] ON [Testcase].[ID]=[LPA_T1].[FK_TestCaseID]) INNER JOIN [Testcase_To_Scenario] ON [Testcase].[ID]=[Testcase_To_Scenario].[FK_TestCaseID]) WHERE ( ( ( ( [LPA_T1].[FK_ScenarioID] = @FkScenarioId1) OR ( [LPA_T1].[FK_ScenarioID] = @FkScenarioId2)))) Parameter: @FkScenarioId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 550. Parameter: @FkScenarioId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 547.

Hold up! I just found that Access does support composite keys. I'm changing my schema and will try again. Sorry for the shortsighted posts...I'll post back with the results.

OK, I've changed my schema so the m:n tables now have composite keys consisting of the FKs plus the order fields (It's the three of these that make the records unique).

Here's my current code:

            IPrefetchPath2 prefetch = new PrefetchPath2((int)EntityType.TestPlanEntity);
            IRelationCollection scenarioRelation = new RelationCollection();
            scenarioRelation.Add(ScenarioEntity.Relations.ScenarioToTestplanEntityUsingFkScenarioId);

            IRelationCollection testCaseRelation = new RelationCollection();
            testCaseRelation.Add(TestcaseEntity.Relations.TestcaseToScenarioEntityUsingFkTestCaseId);

            SortExpression scenarioSort = new SortExpression(
                SortClauseFactory.Create(ScenarioToTestplanFieldIndex.Order, SortOperator.Ascending));
            SortExpression testCaseSort = new SortExpression(
                SortClauseFactory.Create(TestcaseToScenarioFieldIndex.Order, SortOperator.Ascending));

            prefetch.Add(TestPlanEntity.PrefetchPathScenarioCollectionViaScenarioToTestplan, 0, null, scenarioRelation, scenarioSort).SubPath.Add(
                ScenarioEntity.PrefetchPathTestcaseCollectionViaTestcaseToScenario, 0, null, testCaseRelation, testCaseSort);
                        
            DataAdapter.db.FetchEntity(testPlan, prefetch);

And here are the genreated queries

Query: SELECT [TestPlan].[ID] AS [Id], [TestPlan].[Guid], [TestPlan].[FK_ProjectID] AS [FkProjectId], [TestPlan].[FK_TestplanFolderID] AS [FkTestplanFolderId], [TestPlan].[Name], [TestPlan].[Description] FROM [TestPlan] WHERE ( ( [TestPlan].[ID] = @Id1)) Parameter: @Id1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 54.

Query: SELECT [Scenario].[ID] AS [Id], [Scenario].[Guid], [Scenario].[FK_ProjectID] AS [FkProjectId], [Scenario].[FK_ScenarioFolderID] AS [FkScenarioFolderId], [Scenario].[Name], [Scenario].[Description] FROM ((( [TestPlan] INNER JOIN [Scenario_To_Testplan] [LPA_S1] ON [TestPlan].[ID]=[LPA_S1].[FK_TestPlanID]) INNER JOIN [Scenario] ON [Scenario].[ID]=[LPA_S1].[FK_ScenarioID]) INNER JOIN [Scenario_To_Testplan] ON [Scenario].[ID]=[Scenario_To_Testplan].[FK_ScenarioID]) WHERE ( ( ( ( [LPA_S1].[FK_TestPlanID] = @FkTestPlanId1)))) ORDER BY [Scenario_To_Testplan].[Order] ASC Parameter: @FkTestPlanId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 54.

Query: SELECT DISTINCT [TestPlan].[ID] AS [Id0], [Scenario].[ID] AS [Id1] FROM ((( [TestPlan] INNER JOIN [Scenario_To_Testplan] [LPA_S1] ON [TestPlan].[ID]=[LPA_S1].[FK_TestPlanID]) INNER JOIN [Scenario] ON [Scenario].[ID]=[LPA_S1].[FK_ScenarioID]) INNER JOIN [Scenario_To_Testplan] ON [Scenario].[ID]=[Scenario_To_Testplan].[FK_ScenarioID]) WHERE ( ( ( ( [LPA_S1].[FK_TestPlanID] = @FkTestPlanId1)))) Parameter: @FkTestPlanId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 54.

Query: SELECT [Testcase].[ID] AS [Id], [Testcase].[Guid], [Testcase].[FK_ProjectID] AS [FkProjectId], [Testcase].[FK_TestcaseFolderID] AS [FkTestcaseFolderId], [Testcase].[Name], [Testcase].[Description] FROM ((( [Scenario] INNER JOIN [Testcase_To_Scenario] [LPA_T1] ON [Scenario].[ID]=[LPA_T1].[FK_ScenarioID]) INNER JOIN [Testcase] ON [Testcase].[ID]=[LPA_T1].[FK_TestCaseID]) INNER JOIN [Testcase_To_Scenario] ON [Testcase].[ID]=[Testcase_To_Scenario].[FK_TestCaseID]) WHERE ( ( ( ( [LPA_T1].[FK_ScenarioID] = @FkScenarioId1) OR ( [LPA_T1].[FK_ScenarioID] = @FkScenarioId2)))) ORDER BY [Testcase_To_Scenario].[Order] ASC Parameter: @FkScenarioId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 550. Parameter: @FkScenarioId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 547.

Query: SELECT DISTINCT [Scenario].[ID] AS [Id0], [Testcase].[ID] AS [Id1] FROM ((( [Scenario] INNER JOIN [Testcase_To_Scenario] [LPA_T1] ON [Scenario].[ID]=[LPA_T1].[FK_ScenarioID]) INNER JOIN [Testcase] ON [Testcase].[ID]=[LPA_T1].[FK_TestCaseID]) INNER JOIN [Testcase_To_Scenario] ON [Testcase].[ID]=[Testcase_To_Scenario].[FK_TestCaseID]) WHERE ( ( ( ( [LPA_T1].[FK_ScenarioID] = @FkScenarioId1) OR ( [LPA_T1].[FK_ScenarioID] = @FkScenarioId2)))) Parameter: @FkScenarioId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 550. Parameter: @FkScenarioId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 547.

Problem: My schema allows for a test case to be added to a scenario multiple times, as long as the order value is unique. When a test case is associated to a scenario more than one time in the TestCaseToScenario table, the above code/queries only return distinct records (negating that the order value is unique in all records). Ideas? I'm stumped.

I see your point now.

The runtime is v. 1.0.20051.60719

There are many things that were solved since the build you are using. Would you please download the latest build and try it out? (I'm not sure it will solve your problem, but just to ommit this possibility out).

The problem is that this can't be solved. I'll try to explain. Say I have the following entities: A: ID (pk), Name B: ID (pk), Name

and an intermediate table: AB (AID, BID, Order) (all form the PK)

which is similar to your scenario. I also have the following data:

A:

ID|Name

1 | AOne 2 | ATwo

B

ID|Name

1 | BOne 2 | BTwo

AB

AID|BID|Order

1 | 1 | 1 1 | 1 | 2 1 | 1 | 3 1 | 2 | 1 1 | 2 | 2 2 | 1 | 1 2 | 2 | 2 2 | 2 | 3

Now, you want to have for a given A, all related B's. Say I have A with ID 1, and I want all related B's... which Bs are that? just 2 entities: the B with ID1 and the B with ID 2. Not 5 entities.

Remember: you're fetching an entity set. The set contains unique entities, not duplicates. So the request 'get me all related Bs for a given A over the m:n relation between A and B', should result in 2 different instances, which you get, not 5.

Because, effectively, you're just assigning an instance of B a couple of times to an instance of A, but that doesn't mean you're assigning different B's to that instance of A. The Order field is only important if you want to know the count how many B's are associated with a given A. I mean: if I have these two AB rows: 1 | 1 | 1 and 1 | 1 | 2

so A with ID 1 is associated with B with ID 1 two times. What does it mean if I swap the order value of these two rows? Nothing .

This is a good insight Otis, but I'm not sure I'm clearly comminicating the problem.

Because, effectively, you're just assigning an instance of B a couple of times to an instance of A, but that doesn't mean you're assigning different B's to that instance of A. The Order field is only important if you want to know the count how many B's are associated with a given A. I mean: if I have these two AB rows: 1 | 1 | 1 and 1 | 1 | 2

so A with ID 1 is associated with B with ID 1 two times. What does it mean if I swap the order value of these two rows? Nothing .

Although the instances of B associated with A are not unique, the associations in AB are because the order is part of the composite key.

In your example, it wouldn't mean anything if you swapped the order because there are only two identical B records associated with A. Let my present a different scenario in the data where it would make a difference:

AID|BID|Order

1|1|1 1|2|2 1|3|3 1|1|4

"A" represents an objective. "B" represents steps to achieve the objective. "AB" links the steps to the objective in the order required to fullfil the objective. If you now change the order of any of these records, it does have meaning.

Even if the collection would fetched with duplicate value, that doesn't make sense as you don't have the order info, i think, IMHO. However, I think in this case the better for you is to retrieve the intermediate table and prefetch the entities you want. Using the above example:

// intermediate entity
EntityCollection<AbEntity> myAB = new EntityCollection<AbEntity>(new AbEntityFactory());

// path (to retrieve the B info)
IPrefetchPath2 path = new PrefetchPath2((int)EntityType.AbEntity);
path.Add(AbEntity.PrefetchPathB);

// filtering the given A
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(AbFields.Aid == 1);

// sorting on Order field
ISortExpression sorter = new SortExpression(
    new SortClause(AbFields.Aborder, null,  SortOperator.Ascending));

// fetching
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(myAB, filter, 0, sorter, path);
}

// show the results
foreach (AbEntity ab in myAB)
{
    Console.WriteLine("{0} (order: {1})", ab.B.Name, ab.Aborder);
}

Thanks daelmo, you're right about retrieving the intermediate tables to get the desired result. I was able to solve the problem like this:

            IRelationPredicateBucket scenarioToTestPlanRPB = new RelationPredicateBucket();
            scenarioToTestPlanRPB.PredicateExpression.Add(ScenarioToTestplanFields.FkTestPlanId == testPlan.Id);

            SortExpression testCaseSort = new SortExpression(
                SortClauseFactory.Create(TestcaseToScenarioFieldIndex.Order, SortOperator.Ascending));

            IPrefetchPath2 prefetchScenarios = new PrefetchPath2((int)EntityType.ScenarioToTestplanEntity);
            prefetchScenarios.Add(ScenarioToTestplanEntity.PrefetchPathScenario)
                .SubPath.Add(ScenarioEntity.PrefetchPathTestcaseToScenario, 0, null, null, testCaseSort)
                .SubPath.Add(TestcaseToScenarioEntity.PrefetchPathTestcase);
            
            DataAdapter.db.FetchEntityCollection(testPlan.ScenarioToTestplan, scenarioToTestPlanRPB, 0, new SortExpression(
                SortClauseFactory.Create(ScenarioToTestplanFieldIndex.Order, SortOperator.Ascending)), prefetchScenarios);