In my app, I have milestones and deliverables which are related via an m:n relation(milestonedeliverable). Deliverables also have deliverabledata via 1:N relations. What I'm trying to do is create a dynamic typedlist which displays all the deliverables associated with all the milestones for a given project. I have implemented the code as follows. While it works, I get repeating rows in the resultset.

    public static DataTable GetDeliverableData(OrganizationPageEntity organizationPage)
        {
            DataTable dt = new DataTable();
            RelationPredicateBucket bucket = new RelationPredicateBucket();
            DataAccessAdapter adapter = new DataAccessAdapter(true);

            ResultsetFields fields = new ResultsetFields(10);
            fields.DefineField(DeliverableFieldIndex.DeliverableId, 0, "DeliverableDataId");
            fields.DefineField(DeliverableFieldIndex.Name, 1, "Name");
            fields.DefineField(MilestoneFieldIndex.Name, 2, "MilestoneName");
            fields.DefineField(UserFieldIndex.Name, 3, "Owner");
            fields.DefineField(StatusTypeFieldIndex.Name, 4, "StatusType");
            fields.DefineField(DeliverableDataFieldIndex.PlannedDate, 5, "PlannedDate");
            fields.DefineField(DeliverableDataFieldIndex.ActualDate, 6, "ActualDate");
            fields.DefineField(DeliverableDataFieldIndex.OrganizationPageId, 7, "OrganizationPageId");
            fields.DefineField(DeliverableDataFieldIndex.CompanyId, 8, "CompanyId");
            fields.DefineField(DeliverableDataFieldIndex.DeliverableOrganizationPageId, 9, "DeliverableOrganizationPageId");

            bucket.Relations.Add(DeliverableDataEntity.Relations.StatusTypeEntityUsingStatusTypeId);
            bucket.Relations.Add(DeliverableDataEntity.Relations.DeliverableEntityUsingDeliverableId);
            bucket.Relations.Add(DeliverableDataEntity.Relations.UserEntityUsingUserId);
            
            bucket.Relations.Add(DeliverableEntity.Relations.MilestoneDeliverableEntityUsingDeliverableId);
            bucket.Relations.Add(MilestoneDeliverableEntity.Relations.MilestoneEntityUsingMilestoneId);
            
            IPredicateExpression filter = new PredicateExpression(); 
            filter.Add(PredicateFactory.CompareValue(DeliverableDataFieldIndex.OrganizationPageId, ComparisonOperator.Equal, organizationPage.OrganizationPageId));
            filter.AddWithAnd(PredicateFactory.CompareValue(DeliverableDataFieldIndex.CompanyId, ComparisonOperator.Equal, organizationPage.CompanyId));
            bucket.PredicateExpression.Add(filter);

            ISortExpression sorter = new SortExpression();
            sorter.Add(SortClauseFactory.Create(DeliverableDataFieldIndex.OrganizationPageId, SortOperator.Descending));
                        
            adapter.FetchTypedList(fields, dt, bucket, 0, sorter, false);
            adapter.CloseConnection();

            return dt;
        }

Generated SQL:

Query: SELECT DISTINCT [GuidancewareDB].[dbo].[Deliverable].[DeliverableId] AS [DeliverableDataId],[GuidancewareDB].[dbo].[Deliverable].[Name] AS [Name],[GuidancewareDB].[dbo].[Milestone].[Name] AS [MilestoneName],[GuidancewareDB].[dbo].[User].[Name] AS [Owner],[GuidancewareDB].[dbo].[StatusType].[Name] AS [StatusType],[GuidancewareDB].[dbo].[DeliverableData].[PlannedDate] AS [PlannedDate],[GuidancewareDB].[dbo].[DeliverableData].[ActualDate] AS [ActualDate],[GuidancewareDB].[dbo].[DeliverableData].[OrganizationPageId] AS [OrganizationPageId],[GuidancewareDB].[dbo].[DeliverableData].[CompanyId] AS [CompanyId],[GuidancewareDB].[dbo].[DeliverableData].[DeliverableOrganizationPageId] AS [DeliverableOrganizationPageId] FROM ((((( [GuidancewareDB].[dbo].[StatusType] INNER JOIN [GuidancewareDB].[dbo].[DeliverableData] ON [GuidancewareDB].[dbo].[StatusType].[StatusTypeId]= [GuidancewareDB].[dbo].[DeliverableData].[StatusTypeId]) INNER JOIN [GuidancewareDB].[dbo].[Deliverable] ON [GuidancewareDB].[dbo].[Deliverable].[DeliverableId]= [GuidancewareDB].[dbo].[DeliverableData].[DeliverableId]) INNER JOIN [GuidancewareDB].[dbo].[User] ON [GuidancewareDB].[dbo].[User].[UserId]=[GuidancewareDB].[dbo].[DeliverableData].[UserId]) INNER JOIN [GuidancewareDB].[dbo].[MilestoneDeliverable] ON [GuidancewareDB].[dbo].[Deliverable].[DeliverableId]= [GuidancewareDB].[dbo].[MilestoneDeliverable].[DeliverableId]) INNER JOIN [GuidancewareDB].[dbo].[Milestone] ON [GuidancewareDB].[dbo].[Milestone].[MilestoneId]= [GuidancewareDB].[dbo].[MilestoneDeliverable].[MilestoneId]) WHERE ( ( [GuidancewareDB].[dbo].[DeliverableData].[OrganizationPageId] = @OrganizationPageId1 And [GuidancewareDB].[dbo].[DeliverableData].[CompanyId] = @CompanyId2)) ORDER BY [GuidancewareDB].[dbo].[DeliverableData].[OrganizationPageId] DESC Parameter: @OrganizationPageId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2. Parameter: @CompanyId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0.

I'm not sure what the problem is as I have tried several different relations, can anyone help? Thanks.

Distinct is present, so you shouldn't get repeating rows in the resultset, though distinct might fail if hte rows simply aren't the same, but differ on a single field. Is that the case in your situation? is each field the same in these duplicate rows?

You are correct, the rows are different by one field. Here's the output: It's the "milestonename" field thats different.

Out: DeliverableDataId Name MilestoneName Owner Out: --------------------------------------------- Out: 0 Business Plan Requirements Eric Richards Out: 1 Marketing Requirements Requirements Eric Richards Out: 2 User Requirements Requirements Eric Richards Out: 1 Marketing Requirements Design Eric Richards Out: 4 Requirements 1 Design Eric Richards Out: 0 Business Plan Develop Eric Richards Out: 1 Marketing Requirements Develop Eric Richards

fyi, I removed some of the columns in the resultset to make it easier to read.

So its the MilestoneName field which is causing the problem which is the specific field I was attempting to get in the resultset. (i.e. the name of the milestone associated with the deliverabledata.

If I remove the following:

bucket.Relations.Add(DeliverableEntity.Relations.MilestoneDeliverableEntityUsingDeliverableId); bucket.Relations.Add(MilestoneDeliverableEntity.Relations.MilestoneEntityUsingMilestoneId);

and remove the MilestoneName from the resultset, everything works fine with the exception of not getting the associated milestoneName with the deliverableData (Hope that makes sense) So is there away around this? MilestoneDeliverable is an M:N relation which I believe is causing the problem. Suggestions?

If these associations are defined in the db, via the relations they have, then that's the resultset you'll get. I.o.w.: if Marketing Requirements has MilestoneNames 'Requirements', 'Design' and 'Develop' defined, you will get these out of the db and they're simply not duplicates, they're new rows. Why are they duplicates, according to you?

Sorry for not getting back to you sooner, I've been gone for the past couple of days. As I mentioned before, you were correct and they are not duplicate rows. I realized that after looking at the resultset more closely. I actually made a slight modification to the DB schema to get the desired results I wanted which works well. Thanks again for the help.