I'm trying to accomplish the following:

SELECT PC.PROJECT_ID, P.NAME, COUNT(PC.CELL_ID) NumberOfCells, COUNT(CC.CELL_ID) NumberOfChanges FROM PROJECTS P, PROJECT_CELLS PC, CELL_CHANGE CC WHERE P.PROJECT_ID = PC.PROJECT_ID GROUP BY PC.PROJECT_ID, P.NAME ORDER BY CELL_NO;

The following code works if I only include the NumberOfCells:

**RelationPredicateBucket bucket = new RelationPredicateBucket();

ResultsetFields dtFields = new ResultsetFields(2); dtFields.DefineField(ProjectCellsFields.ProjectId, 0); dtFields.DefineField(new EntityField2("NumberOfCells", (ProjectCellsFields.CellId * 1), AggregateFunction.Count), 1); GroupByCollection dtGroupBy = new GroupByCollection(dtFields[0]); DerivedTableDefinition dtDefinition = new DerivedTableDefinition(dtFields, "CellsCounter", null, dtGroupBy);

DynamicRelation relation = new DynamicRelation(dtDefinition, JoinHint.Inner, EntityType.ProjectsEntity, "P", (new EntityField2(ProjectCellsFieldIndex.ProjectId.ToString(), "CellsCounter", typeof(int)) == ProjectsFields.ProjectId.SetObjectAlias("P")));

bucket.Relations.Add(relation); bucket.SelectListAlias = "P";**

Anyone knows how can I modify my code in order to include the other Count (NumberOfChanges )?

Thanks for your help.

You don't need a derivedTable definition or a Dynamic relation. You can just create an EntityRelation and specify fields of both sides of the relation. And the join would be generated in the SQL query.

DerivedTable definition if you would like to join to a Temp table.

By the way, don't you have relations already defined in the system between Project, ProjectCells & CellChanged entities.

Note: your query didn't show how CELL_CHANGE CC should joined.

Also you don't need to define a new EntityField for the AggregatedFields. Just use The corresponding Fields Collection (eg. ProjectCellsFields.CellId), and specify the aggregate function and alias.

Thanks for your quick reply

Could I ask you for a quick example on how to achieve that?

You haven't answer these:

By the way, don't you have relations already defined in the system between Project, ProjectCells & CellChanged entities.

Note: your query didn't show how CELL_CHANGE CC should joined.

Anyway I'lll assume relations are already there, and the code should look like this:

DataAccessAdapter adapter = new DataAccessAdapter();
ResultsetFields fields = new ResultsetFields(4);
fields.DefineField(ProjectFields.Id, 0);
fields.DefineField(ProjectFields.Name, 1);
fields.DefineField(ProjectCellsFields.CellId, 2, "NumberOfCells", AggregateFunction.Count);
fields.DefineField(CellChangeFields.CellId, 3, "NumberOfChanges", AggregateFunction.Count);

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(ProjecteEntity.Relations.ProjectCell....);
bucket.Relations.Add(ProjecteCellEntity.Relations.CellChange....);

IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);

DataTable dynamicList = new DataTable();
adapter.FetchTypedList(fields, dynamicList, bucket, 0, null, true, groupByClause);

Thanks Walaa, and you are right, the relationships were already defined.

Im actually using a fetch collection, since im providing a sorting object along with a prefetchpath and paging related values. Can I use the ResultsetFields with a FetchEntityCollection?

FetchTypedList accepts a sortExpression, and it has an overload which also accpets PageSize and PageNumber for paging.

But anyway if you would like to fetch an entityCollection, then please check my reply to you in this thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=16608