New Posts New Posts RSS Feed: OrderBy on subquery
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

OrderBy on subquery
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
Markh View Drop Down
Newbie
Newbie
Avatar

Joined: 14-Jun-2007
Location: Australia
Posts: 14 		Post Options Post Options   Quote Markh Quote  Post ReplyReply Direct Link To This Post Topic: OrderBy on subquery
    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 ?
 
 


Edited by Markh - 23-Jul-2007 at 5:15pm
Back to Top
pkarsh View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 14-Jun-2007
Location: United States
Posts: 32 		Post Options Post Options   Quote pkarsh Quote  Post ReplyReply Direct Link To This Post 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.


Back to Top
Markh View Drop Down
Newbie
Newbie
Avatar

Joined: 14-Jun-2007
Location: Australia
Posts: 14 		Post Options Post Options   Quote Markh Quote  Post ReplyReply Direct Link To This Post 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

 
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down