I am trying to use DataAccessAdapter.FetchTypedList to sum three columns from an entity table. To get the correct records I have to join to another entity, and I'm having trouble getting the joins to work correctly.

The two entities in question, Entity1 and Entity2, are both derived from BaseEntity, so each has a one-to-one relation with the BaseEntity table. They are related to each other through Entity2.Entity1ID==Entity1.ID.

What I'm trying to get is:

SELECT sum(Entity2.Column1),sum(Entity2.Column2),sum(Entity2.Column3)
FROM Entity2 LEFT JOIN Entity1 on Entity2.Entity1ID=Entity1.ID
WHERE Entity1.GroupID='somevalue'

The code I'm using is:

RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(Entity2.Relations.Entity1UsingEntity1ID);

ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(Entity2Fields.Column1, 0, "Column1");
fields[0].AggregateFunctionToApply = AggregateFunction.Sum;
fields.DefineField(Entity2Fields.Column2, 1, "Column2");
fields[1].AggregateFunctionToApply = AggregateFunction.Sum;
fields.DefineField(Entity2Fields.Column3 , 2, "Column3");
fields[2].AggregateFunctionToApply = AggregateFunction.Sum;

RelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(Entity1Fields.GroupID == 'somevalue');

GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(Entity1Fields.Entity1GroupID);

DataTable results = new DataTable();
dataAdapter.FetchTypedList(fields, results, filter, 0, null, true, groupBy,0,0);

The SQL that gets generated from this is:

SELECT SUM([LPA_L2].[Column1]) AS [Column1], SUM([LPA_L2].[Column2]) AS [Column2], SUM([LPA_L2].[Column3]) AS [Column3] 
FROM (( [database1].[dbo].[baseentity] [LPA_L1]  
    LEFT JOIN [database1].[dbo].[entity2] [LPA_L2]  ON  [LPA_L1].[id]=[LPA_L2].[id]) 
    LEFT JOIN [database1].[dbo].[entity1] [LPA_L3]  ON  [LPA_L1].[id]=[LPA_L3].[id]) 
WHERE ( ( [LPA_L3].[GroupID] = @p1)) GROUP BY [LPA_L3].[GroupID]

So it's treating Entity1 and Entity2 as if they have a 1-1 relation through BaseEntity, rather than joining them to separate BaseEntity records.

What do I need to change?

I cant see anywhere where you use the RelationPredicateBucket defined in the first two lines

RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(Entity2.Relations.Entity1UsingEntity1ID);

Indeed, further down you define one called "filter" and use that in the query. Do you perhaps need to combine them into one bucket...?

Matt

Oops. That was indeed the problem. Thanks for straightening me out...

No problem, always happy to help!

Matt