Hi guys,

Here's the issue that I have while using LLBLGen Pro (Adapter) generated code.

I'm Fetching an EntityCollection called Projects that will have a prefetchPath to a ProjectCells entity, that same entity will have an m:n relationship with Cells, so I will end up having n Cells for a specific Project. After I fetch everything I can have access to the ProjectCells and count the number of records it holds for each Project.

My question is, is possible to create a Sort based on the Count of ProjectCells when consuming the method FetchEntityCollection?

Thanks in advance for your help.

I think this one is better done at client side(in code) after fetching the collections. Otherwise if you want to do it in the database.

Then the first thing to think of is how the SELECT query of the projects should look like with the required Order By. Would you post it here, please.

Thanks for your help. Unfortunately we cannot do it at client side since the result set is too big and it will be delivered through the cloud, so we have to maintain the call to the service with paging to maximize performance.

This is a sample query to illustrate what we need to achieve using LLBLGen:

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

So the problem here is how, using LLBLGen, sort by the number of records that a prefetched entity (PROJECT_CELLS) will have.

You can't use Group By when fetching a collection, instead you should use a dyanmic list.

For EntityCollection: In v2.6 you can orderby on an expression but that would mean a scalar query per row, which is kind of slow. You could also join a derived table with the counts using a dynamic relation and orderby on the field in the derived table, which should be faster.

I must assume I'm a little lost. Do you have any example code on that?

Thanks in advance.

For the derived table and dynamic relation solution, please check the following section in the manual: Using the generated code -> Derived tables and dynamic relations

Thanks very much, I'll check that now

Trying to make this work but receiving "Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?", must be doing something wrong here, could you have a look at the following:

RelationPredicateBucket bucket = new RelationPredicateBucket();

                // first specify the elements in the derived table select (which is a dyn. list)
                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, "ProjectCellsCounters", null, dtGroupBy);

                // then specify the relation. 
                // derivedtable spec, join type, end entity type, alias first element, alias end element, on clause filter
                DynamicRelation relation = new DynamicRelation(dtDefinition, JoinHint.Inner,
                                    EntityType.ProjectsEntity, "P",
                                   (new EntityField2(ProjectCellsFieldIndex.ProjectId.ToString(), "ProjectCellsCounters", typeof(int)) ==
                                           ProjectsFields.ProjectId.SetObjectAlias("P")));

                // then specify the rest of the query elements
                bucket.Relations.Add(relation);
                bucket.SelectListAlias = "P";

Thanks!

I was able to get back results from the database, but how can I have access to the values from the alias "NumberOfCells" after it fetches the data to the entitycollection?

We were under the impression you wanted to fetch the entities in a given order, so sorting on related data in the db, but not having / wanting this data to be fetched into memory, hence suggested a sort in the db instead of in-memory. But it now seems you want to have the data available in memory as well, e.g. to bind to a grid?

We need to be able to sort by "NumberOfCells" (among other possible sorts that the user may choose) and also have that same values available to be binded to a grid. Any ideas on how to do this?

Thanks

You have 2 options depending on what you want to do with the data afterwards.

Either extend the ProjectEntityFactory class to fetch the extra computed colum. (data should be used for read-Only). Or create a custom property in the ProjectEntity class which returns ProjectCells.Count, which should return the number that you want if the underlying ProjectsCells property was prefetched.

For more details please check the following thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=10287

Thanks Walaa for your reply.

I extended the ProjectEntityFactory to include the extra computed column, and it works fine

Should I also extend ProjectFields enum (and related classes) in order to apply a sort on the extended field?

I wouldn't unless you need to.

Matt