DevForce Community Forum : OrderBy on subquery http://www.ideablade.com/forum/ This is an XML content feed of; DevForce Community Forum : DevForce Classic : OrderBy on subquery Fri, 12 Jun 2026 00:10:54 -700 Sun, 29 Jul 2007 17:55:54 -700 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 9.69 360 www.ideablade.com/forum/RSS_post_feed.asp?TID=323 DevForce Community Forum http://www.ideablade.com/forum/forum_images/IdeaBlade_logo_tm.png http://www.ideablade.com/forum/ OrderBy on subquery : Paul I need to add the data... http://www.ideablade.com/forum/forum_posts.asp?TID=323&PID=794#794 Author: Markh
Subject: 323
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

 

]]> Sun, 29 Jul 2007 17:55:54 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=323&PID=794#794 OrderBy on subquery : It's not clear to me what... http://www.ideablade.com/forum/forum_posts.asp?TID=323&PID=752#752 Author: pkarsh
Subject: 323
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.


]]> Tue, 24 Jul 2007 15:48:50 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=323&PID=752#752 OrderBy on subquery : How do you orderby on a subquery?... http://www.ideablade.com/forum/forum_posts.asp?TID=323&PID=746#746 Author: Markh
Subject: 323
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]]> Mon, 23 Jul 2007 00:15:05 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=323&PID=746#746