Hi, I think this is easy to solve, but I cannot get it to work.

I have 3 tables (entities) which are related

A , B, C

A is related to B B is related to C

I want to retrieve all B entities filtered on a value in the A entity. Every B entity has also a value in C, which should also be retrieved. Actually all records form B and the related (1:1) records from C

I use the following code where A = anamnesis, B= IbpAnamnesisInputEntity and C = IbpAnamnesisItem:

 IbpAnamnesisEntity anamnesis = LoadAnamnesis(clientId);

            EntityCollection<IbpAnamnesisInputEntity> myCollection =
                new EntityCollection<IbpAnamnesisInputEntity>(new IbpAnamnesisInputEntityFactory());
        
            PrefetchPath2 path = new PrefetchPath2((int)EntityType.IbpAnamnesisInputEntity);


            IRelationPredicateBucket filter = new RelationPredicateBucket();
            filter.PredicateExpression.Add(IbpAnamnesisInputFields.IbpanamnesisId == anamnesis.IbpanamnesisId);

            //IRelationPredicateBucket RelationAnamnesisItem = new RelationPredicateBucket();
            //RelationAnamnesisItem.PredicateExpression.Add(IbpAnamnesisInputFields.IbpanamnesisItemId == anamnesis.IbpanamnesisId);
            ISortExpression sort = new SortExpression(IbpAnamnesisItemFields.OrderId | SortOperator.Ascending);

            path.Add(IbpAnamnesisInputEntity.PrefetchPathIbpAnamnesisItem, 0, null, null, sort);

            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchEntityCollection(myCollection, filter,path);
            }
            return myCollection;

The problem is I want to show the collection sorted on the orderId in IbpAnamnesisItem-table but I cannot get it to work. If I take a look at the generated query the related Table (IbpAnamnesisItem) is not available. Only the fields in the IbpAnamnesisInput are in the query.

How can I solve this?

To sort on a field from a related entity/table, you should pass (specify) the relation between the fetched entities and that related entity.

Similar to SQL, if you want to order on a field from another table you should join to that table.

e.g (Northwind)

EntityCollection<OrderEntity> orders = new EntityCollection<OrderEntity>(new OrderEntityFactory());

// relations, this is a must when you order on a relatied entity
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(OrderEntity.Relations.CustomerEntityUsingCustomerId);

// the desire sort expression
ISortExpression sorter = new SortExpression();
sorter.Add(CustomerFields.CompanyName | SortOperator.Ascending);

// fetch results
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(orders, filter, 0, sorter); 
}

Hi Walaa,

I'm still cursious why the my code doesn't work. As far as I can tell it is not much different from the code in the manual:

EntityCollection<EmployeeEntity> employees = new EntityCollection<EmployeeEntity>();
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.EmployeeEntity);
ISortExpression sorter = new SortExpression();
sorter.Add(OrderFields.OrderDate | SortOperator.Descending);
prefetchPath.Add(EmployeeEntity.PrefetchPathOrders, 1, null, null, sorter);
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(employees, null, prefetchPath);

I also put the sorter in the prefechPath instead of the FechtEntityCollection. So I'm a little confused.

BTW the code is working fine now.

Stephan wrote:

Hi Walaa,

I'm still cursious why the my code doesn't work. As far as I can tell it is not much different from the code in the manual:

EntityCollection<EmployeeEntity> employees = new EntityCollection<EmployeeEntity>();
IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.EmployeeEntity);
ISortExpression sorter = new SortExpression();
sorter.Add(OrderFields.OrderDate | SortOperator.Descending);
prefetchPath.Add(EmployeeEntity.PrefetchPathOrders, 1, null, null, sorter);
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(employees, null, prefetchPath);

I also put the sorter in the prefechPath instead of the FechtEntityCollection. So I'm a little confused.

BTW the code is working fine now.

That code snippet sorts the orders fetched by the sorter specified, so it's not sorting entity A on a related entity B, it sorts related entity B on a field in B.