Print Page | Close Window

OrderBy on subquery

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=323
Printed Date: 12-Jun-2026 at 12:10am


Topic: OrderBy on subquery
Posted By: Markh
Subject: OrderBy on subquery
Date Posted: 23-Jul-2007 at 12:15am
How do you orderby on a subquery?
 
I am trying to do multiple "order by"s  of properties(column) within related Entities.
ie 
first order Entity1 by a property(Column) in Entity2
second order Entity1 by a  property(Column) in Entity3
third order Entity1 by a  property(Column) in Entity4
 
I can do this by using ICompare in Entity1, Is there another way? 
 
I thought that a subquery "orderby" would order the query entity (as in SQL where you can order by joined table), but
I assume that a subquery "orderby" orders the Entity attached by the relation. Is this Correct ?
 
 


Replies:
Posted By: pkarsh
Date Posted: 24-Jul-2007 at 3:48pm
It's not clear to me what you want to accomplish by adding AddOrderBy clauses to your subqueries. I did find, though, that the "AddOrderBy" clause of a subquery is ignored when the raw SQL is generated from the OQL unless the TOP property of the subquery is set.

You can see the SQL that gets generated by your OQL code by looking in the DebugLog, which is in the same directory as the startup executable.

The following code:

      EntityQuery orderSummaryQuery = new EntityQuery(typeof(OrderSummary));
      EntitySubquery aShipperSubQuery = orderSummaryQuery.AddSubquery(EntityRelations.Shipper_OrderSummary);
      //aShipperSubQuery.Top = 4;
      aShipperSubQuery.AddOrderBy(Shipper.CompanyNameEntityColumn);
      EntitySubquery aCustomerSubQuery = orderSummaryQuery.AddSubquery(EntityRelations.Customer_OrderSummary);
      //aCustomerSubQuery.Top = 100;
      aCustomerSubQuery.AddOrderBy(Customer.CompanyNameEntityColumn);
      orders = mPm.GetEntities<OrderSummary>(orderSummaryQuery);

yields the following raw SQL:
select * from "dbo"."OrderSummary" where ((Id < 10290) and (("dbo"."OrderSummary"."CustomerId" in (select "dbo"."Customer"."Id" from "dbo"."Customer") and "dbo"."OrderSummary"."ShipperId" in (select "dbo"."Shipper"."Id" from "dbo"."Shipper"))))

If I uncomment the statements in the code above such that each subquery has the value set for the "Top" property, the SQL generated looks like this:

select * from "dbo"."OrderSummary" where ((Id < 10290) and (("dbo"."OrderSummary"."CustomerId" in (select TOP 100 "Id" from "dbo"."Customer" ORDER BY "dbo"."Customer"."CompanyName" ASC) and "dbo"."OrderSummary"."ShipperId" in (select TOP 4 "Id" from "dbo"."Shipper" ORDER BY "dbo"."Shipper"."CompanyName" ASC))))

Notice that the Subquerys now contain ORDERBY clauses.

FWIW, SQL itself has the same restriction. If you try to add OrderBy clauses to subqueries without specifying TOP values you will get an error.

In my testing I did not see that the AddOrderBy clauses affected the display of the results. I did notice that adding "ApplySort" method calls to the EntityList did sort the results by the contents of the specified Relational Property column. For example, the code:

      orders.ApplySort("Customer.CompanyName", ListSortDirection.Ascending, true);

did cause the Orders to be sorted by the Customer CompanyName property.



Posted By: Markh
Date Posted: 29-Jul-2007 at 5:55pm

Paul

 

I need to add the data to a tree view and order the data within the nodes to be based on Names not IDs.

 

The applySort only allow you to sort by one column. I need to sort by 4 different levels

Level1

         Level2

                  Level3

                           Level4

 

FYI

I changed the query and got Order By Inconsistency

 

1.The following code returns order by data for each entity correctly only the first time.

Entity1 ordered by Descending

Entity2 ordered by Descending

Entity3 ordered by Descending

Entity4 ordered by Descending

 

query.AddOrderBy(Level1.Level1NameEntityColumn,ListSortDirection.Descending);

 

EntitySubquery subQueryLevel2 = query.AddSubquery(EntityRelations.Level1_Level2);

subQueryLevel2.AddOrderBy(Level2.LocationNameEntityColumn, ListSortDirection.Descending);

 

EntitySubquery subQueryLevel3 = subQueryLevel2.AddSubquery(EntityRelations.Level2_Level3);

subQueryLevel3.AddOrderBy(Level3.WorkSiteDescEntityColumn, ListSortDirection.Descending);

 

EntitySubquery subQueryLevel4 = subQueryLevel3.AddSubquery(EntityRelations.Level3_CostCentre);

subCostCentre.AddOrderBy(CostCentre.CostCentreDescriptionEntityColumn,ListSortDirection.Descending);

 

 

2. When I change the code (as follows) to make Entity1 Ascending. The returned data is correct

Entity1 ordered by Ascending

Entity2 ordered by Descending

Entity3 ordered by Descending

Entity4 ordered by Descending

 

query.AddOrderBy(Level1.Level1NameEntityColumn,ListSortDirection.Ascending);

 

EntitySubquery subQueryLevel2 = query.AddSubquery(EntityRelations.Level1_Level2);

subQueryLevel2.AddOrderBy(Level2.LocationNameEntityColumn, ListSortDirection.Descending);

 

EntitySubquery subQueryLevel3 = subQueryLevel2.AddSubquery(EntityRelations.Level2_Level3);

subQueryLevel3.AddOrderBy(Level3.WorkSiteDescEntityColumn, ListSortDirection.Descending);

 

EntitySubquery subQueryLevel4 = subQueryLevel3.AddSubquery(EntityRelations.Level3_CostCentre);

subCostCentre.AddOrderBy(CostCentre.CostCentreDescriptionEntityColumn,ListSortDirection.Descending);

 

3. When I change the code (as follows) to make Entity2 Ascending. The returned data is incorrect

 

Entity1 ordered by Ascending

Entity2 ordered by Descending

Entity3 ordered by Descending

Entity4 ordered by Descending

 

query.AddOrderBy(Level1.Level1NameEntityColumn,ListSortDirection.Ascending);

 

EntitySubquery subQueryLevel2 = query.AddSubquery(EntityRelations.Level1_Level2);

subQueryLevel2.AddOrderBy(Level2.LocationNameEntityColumn, ListSortDirection.Ascending);

 

EntitySubquery subQueryLevel3 = subQueryLevel2.AddSubquery(EntityRelations.Level2_Level3);

subQueryLevel3.AddOrderBy(Level3.WorkSiteDescEntityColumn, ListSortDirection.Descending);

 

EntitySubquery subQueryLevel4 = subQueryLevel3.AddSubquery(EntityRelations.Level3_CostCentre);

subCostCentre.AddOrderBy(CostCentre.CostCentreDescriptionEntityColumn,ListSortDirection.Descending);

 

 

By creating a new persistence manager each time the query is executed the code above works correctly.

Only the Entity1 “OrderBy” works correctly each time the sort direction is changed. (ie without creating a new persistence manager or removing entities)

 

By adding the "subquery.Top = 100" to all subquerys fixed the problem.

 

Using Top = 100 means using an arbitrary number for the number of records to return.

 

In SQL you use the "Top 100 percent" to return all associated records.

 

 

I know I can all retrieve Level1s apply a ApplySort() then interate through them all, get all Level2s attached to the first Level1, ApplySort to Level2s etc.....

 

Is this the way you recommend ?

 

 

 

Thanks

 


Print Page | Close Window