DevForce Community Forum : Subquery.AddOrderBy doesn't order so well...

Subquery.AddOrderBy doesn't order so well... : I agree with Paul K's approach.Here's...

Tue, 27 Nov 2007 15:36:56 -700

Author: pnschofield
Subject: 388
Posted: 27-Nov-2007 at 3:36pm

I agree with Paul K's approach.

Here's another way you could approach the problem. If you want your list to be sorted by a property of ShiftFilter, then conceptually, you're performing a query of ShiftFilter, and displaying a list of ShiftFilters in your UI, along with some of the properties of each ShiftFilter's related Shift entity. You would just use an EntityQuery(typeof(ShiftFilter)), bind that to your UI controls and create additional BindingDescriptors that display the desired properties from entity A via navigation.

EntityQuery query = new EntityQuery(typeof(ShiftFilter));

query.AddClause(ShiftFilter.RosterIDEntityColumn, EntityQueryOp.EQ, rosterName);

query.AddClause(ShiftFilter.CostCentreIDEntityColumn, EntityQueryOp.EQ, costCentreId);

query.Top = 100;

query.AddOrderBy(ShiftFilter.OrderEntityColumn, System.ComponentModel.ListSortDirection.Ascending);

// Go ahead and pull down all the related Shifts
query.AddSpan(EntityRelations.Shift_ShiftFilter.ToParent);

EntityList<ShiftFilter> result = m_PersistenceManager.GetEntities<ShiftFilter>(query);

// You want the grid to display some of the properties of each ShiftFilter's parent Shift object.
// Assuming ShiftFilter has a relation property called Shift:

myGridBindingManager.Descriptors.Add("Property A", "Shift.PropertyA");
myGridBindingManager.Descriptors.Add("Property B", "Shift.PropertyB");

Paul S.

Edited by pnschofield - 27-Nov-2007 at 3:37pm

Subquery.AddOrderBy doesn't order so well... : Given that it a sort on a relational...

Thu, 15 Nov 2007 16:47:11 -700

Author: pkarsh
Subject: 388
Posted: 15-Nov-2007 at 4:47pm

Given that it a sort on a relational property only makes sense if the relation is 1 to 1, the way to get a list of Entities sorted by the relational property is to use the ApplySort method of the EntityList class. I have looked into this and AddOrderBy on the subquery has no effect and was never intended to be used for this.

In the IdeaBladeTutorial database there is a table called EmployeeExempt. If you look at this table you will see that it consists of only three columns, Id, SalaryAnnual, and EmployeeId. There is not a relationship defined in the database between Employee.Id and EmployeeExempt.EmployeeId but it makes sense to define this relationship in the Object Mapper as 1 to 1 with EmployeeExempt being the child Entity.

Once you have done this, the following OQL and other code will give you a list of Employees for whom corresponding entries exist in the EmployeeExempt table, sorted by the Employee.EmployeeExempt relational property of the Employee Entity class.

     EntityQuery aQuery = new EntityQuery(typeof (Employee));
      EntitySubquery aEmployeeExemptSubquery = aQuery.AddSubquery(EntityRelations.Employee_EmployeeExempt, QueryDirection.ChildQuery);

      EntityList MyEntityList = mPm.GetEntities(aQuery);
      MyEntityList.ApplySort("EmployeeExempt", System.ComponentModel.ListSortDirection.Ascending, true);

      foreach (Employee aEntityInstance in MyEntityList) {
        Console.WriteLine("EmployeeExempt Id is: {0}, Employee Id is {1}, Employee salary is: {2}", aEntityInstance.EmployeeExempt.Id.ToString(), aEntityInstance.Id.ToString(), aEntityInstance.EmployeeExempt.SalaryAnnual.ToString());
      }

If someone thought that the behavior of AddOrderBy on a subquery had changed they are mistaken. I am guessing that they thought it was doing a sort because the data was being returned in an order such that it appeared that a sort was being applied and now it is not.

Hope this helps.

Paul K.

Edited by pkarsh - 15-Nov-2007 at 5:29pm

Subquery.AddOrderBy doesn't order so well... : Hi Paul You are correct. The...

Thu, 15 Nov 2007 14:06:53 -700

Author: Markh
Subject: 388
Posted: 15-Nov-2007 at 2:06pm

Hi Paul

You are correct. The behaviour we expected was present in an early version of DevForce . Unfortunately this was prototype code, which has now been discarded.

I believe the version we had before 3.5.3.1 was 3.5.2.3, but I am not sure that 3.5.2.3 was the version used in the prototype code.

thanks

Subquery.AddOrderBy doesn't order so well... : I get the idea that the behavior...

Thu, 15 Nov 2007 12:25:41 -700

Author: pkarsh
Subject: 388
Posted: 15-Nov-2007 at 12:25pm

I get the idea that the behavior you expected was present in some earlier version of DevForce. Am I correct in thinking that? If so, which version?

Subquery.AddOrderBy doesn't order so well... : Hi Paul Sorry. You have answered....

Wed, 14 Nov 2007 20:54:40 -700

Author: Markh
Subject: 388
Posted: 14-Nov-2007 at 8:54pm

Hi Paul

Sorry. You have answered. (I should read the responses more judiciously)

My expectation on the order by, was that it would order the Customers by freight value.

You are correct the original problem by jozza was in fact a one to one relation.

A previous version of Devforce ordered the Shift Entities by a numeric value, in the subQuery OrderBy, stored in the ShiftFilter entities.

There for we expected the first entity to be ordered by the result set of the second entity.

Edited by Markh - 14-Nov-2007 at 8:55pm

Subquery.AddOrderBy doesn't order so well... : Hi Mark This SQL appears to be...

Wed, 14 Nov 2007 17:38:33 -700

Author: pkarsh
Subject: 388
Posted: 14-Nov-2007 at 5:38pm

Hi Mark

This SQL appears to be almost exactly what I had included in my post of Nov. 13. In addition to this SQL, I also included some OQL that would give results that to my best understanding roughly corresponds to what it appears that you are looking for as indicated in your previous post. This OQL queries against the OrderSummary object and not the Customer object.

I remain puzzled as to what you are looking for that is not addressed in the post I mention above.

I still suspect that in the specific situation that you and others who have participated in this thread are working with, as opposed to the examples using our IdeaBladeTutorial database that I have given, that the relations you are working with are 1 to 1. Only in that situation would the desire to sort by a relational property as the most significant sort field make sense.

If you or someone else in this thread can address these specific questions I would appreciate it.

Regards,
Paul K.

Subquery.AddOrderBy doesn't order so well... : Paul Here is are the two scripts...

Wed, 14 Nov 2007 16:19:59 -700

Author: Markh
Subject: 388
Posted: 14-Nov-2007 at 4:19pm

Paul

Here is are the two scripts from Debuglog.xml

RdbKey: [Default] Sql: select * from "dbo"."Customer" where (("dbo"."Customer"."Id" in (select TOP 1000 "CustomerId" from "dbo"."OrderSummary" ORDER BY "dbo"."OrderSummary"."Freight" ASC) and "dbo"."Customer"."Id"

script 2

RdbKey: [Default] Sql: select TOP 1000 * from "dbo"."OrderSummary" where ("dbo"."OrderSummary"."CustomerId" in (select "dbo"."Customer"."Id" from "dbo"."Customer" where (("dbo"."Customer"."Id" in (select TOP 1000 "CustomerId" from "dbo"."OrderSummary" ORDER BY "dbo"."OrderSummary"."Freight" ASC) and "dbo"."Customer"."Id"

the two scripts converted to run in SQL

Script 1

select * from "dbo"."Customer" where

(("dbo"."Customer"."Id" in

(select TOP 1000 "CustomerId" from "dbo"."OrderSummary"

ORDER BY "dbo"."OrderSummary"."Freight" ASC) and "dbo"."Customer"."Id"<11))

Script 2

select TOP 1000 * from "dbo"."OrderSummary" where

("dbo"."OrderSummary"."CustomerId" in

(select "dbo"."Customer"."Id" from "dbo"."Customer" where

(("dbo"."Customer"."Id" in

(select TOP 1000 "CustomerId" from "dbo"."OrderSummary" ORDER BY "dbo"."OrderSummary"."Freight" ASC)

and "dbo"."Customer"."Id"<11 )))) ORDER BY "dbo"."OrderSummary"."Freight" ASC

Subquery.AddOrderBy doesn't order so well... : Hi Mark It looks to me like the...

Wed, 14 Nov 2007 15:03:41 -700

Author: pkarsh
Subject: 388
Posted: 14-Nov-2007 at 3:03pm

Hi Mark

It looks to me like the SQL results you have posted are actually a query against the Order table (or OrderSummary as it actually is in the database). If I'm wrong about that please let me know. Also, if you could post the actual SQL that gave you those results it would be helpful as well.

Thanks.

Paul K.

Subquery.AddOrderBy doesn't order so well... : Paul With the ,order by, I...

Wed, 14 Nov 2007 14:50:50 -700

Author: Markh
Subject: 388
Posted: 14-Nov-2007 at 2:50pm

Paul

With the ,order by, I was expecting, that it was not Freight by each customer, Only Order by Frieght. Which is the result set of the second sql script.

The first customer should have been

Order Id is: -0.01 Customer is: Blauer See Delikatessen

Second

Order Id is: 1.18 Customer is: Alfreds Futterkistex

Etc...

My results where as follows

Order Id is: 1.48 Customer is: Ana Trujillo Emparedados y helados
Order Id is: 11.91 Customer is: Ana Trujillo Emparedados y helados
Order Id is: 39.87 Customer is: Ana Trujillo Emparedados y helados
Order Id is: 43.8 Customer is: Ana Trujillo Emparedados y helados
Order Id is: 2.98 Customer is: Around the Horn
Order Id is: 4.44 Customer is: Around the Horn
Order Id is: 10.88 Customer is: Around the Horn
Order Id is: 21.66 Customer is: Around the Horn
Order Id is: 23.64 Customer is: Around the Horn
Order Id is: 23.68 Customer is: Around the Horn
Order Id is: 25.22 Customer is: Around the Horn
Order Id is: 29.56 Customer is: Around the Horn
Order Id is: 33.77 Customer is: Around the Horn
Order Id is: 34.07 Customer is: Around the Horn
Order Id is: 41.79 Customer is: Around the Horn
Order Id is: 172.83 Customer is: Around the Horn
Order Id is: 1146.24 Customer is: Around the Horn
Order Id is: 5.59 Customer is: Blondesddsl p�re et fils
Order Id is: 7.01 Customer is: Blondesddsl p�re et fils
Order Id is: 7.91 Customer is: Blondesddsl p�re et fils
Order Id is: 27.85 Customer is: Blondesddsl p�re et fils
Order Id is: 30.26 Customer is: Blondesddsl p�re et fils
Order Id is: 53.16 Customer is: Blondesddsl p�re et fils
Order Id is: 55.04 Customer is: Blondesddsl p�re et fils
Order Id is: 59.02 Customer is: Blondesddsl p�re et fils
Order Id is: 88.26 Customer is: Blondesddsl p�re et fils
Order Id is: 131.54 Customer is: Blondesddsl p�re et fils
Order Id is: 156.51 Customer is: Blondesddsl p�re et fils
Order Id is: -0.01 Customer is: Blauer See Delikatessen
Order Id is: 1.83 Customer is: Blauer See Delikatessen
Order Id is: 8.69 Customer is: Blauer See Delikatessen
Order Id is: 27.59 Customer is: Blauer See Delikatessen
Order Id is: 31.11 Customer is: Blauer See Delikatessen
Order Id is: 44.61 Customer is: Blauer See Delikatessen
Order Id is: 53.77 Customer is: Blauer See Delikatessen
Order Id is: 2.22 Customer is: Bottom-Dollar Markets
Order Id is: 13.97 Customer is: Bottom-Dollar Markets
Order Id is: 23.46 Customer is: Bottom-Dollar Markets
Order Id is: 24.09 Customer is: Bottom-Dollar Markets
Order Id is: 32.23 Customer is: Bottom-Dollar Markets
Order Id is: 44.02 Customer is: Bottom-Dollar Markets
Order Id is: 47.25 Customer is: Bottom-Dollar Markets
Order Id is: 48.78 Customer is: Bottom-Dollar Markets
Order Id is: 52.49 Customer is: Bottom-Dollar Markets
Order Id is: 52.87 Customer is: Bottom-Dollar Markets
Order Id is: 62.74 Customer is: Bottom-Dollar Markets
Order Id is: 70.55 Customer is: Bottom-Dollar Markets
Order Id is: 74.39 Customer is: Bottom-Dollar Markets
Order Id is: 243.65 Customer is: Bottom-Dollar Markets
Order Id is: 1.18 Customer is: Alfreds Futterkistex
Order Id is: 29.37 Customer is: Alfreds Futterkistex
Order Id is: 40.38 Customer is: Alfreds Futterkistex
Order Id is: 60.93 Customer is: Alfreds Futterkistex
Order Id is: 69.45 Customer is: Alfreds Futterkistex
Order Id is: 123.86 Customer is: Alfreds Futterkistex
Order Id is: 3.35 Customer is: Berglunds snabbk�p
Order Id is: 6.71 Customer is: Berglunds snabbk�p
Order Id is: 8.78 Customer is: Berglunds snabbk�p
Order Id is: 9.15 Customer is: Berglunds snabbk�p
Order Id is: 13.25 Customer is: Berglunds snabbk�p
Order Id is: 13.33 Customer is: Berglunds snabbk�p
Order Id is: 32.31 Customer is: Berglunds snabbk�p
Order Id is: 55.17 Customer is: Berglunds snabbk�p
Order Id is: 92.48 Customer is: Berglunds snabbk�p
Order Id is: 95.66 Customer is: Berglunds snabbk�p
Order Id is: 109.05 Customer is: Berglunds snabbk�p
Order Id is: 110.03 Customer is: Berglunds snabbk�p
Order Id is: 116.3 Customer is: Berglunds snabbk�p
Order Id is: 138.59 Customer is: Berglunds snabbk�p
Order Id is: 151.47 Customer is: Berglunds snabbk�p
Order Id is: 168.47 Customer is: Berglunds snabbk�p
Order Id is: 188.79 Customer is: Berglunds snabbk�p
Order Id is: 244.65 Customer is: Berglunds snabbk�p
Order Id is: 3.94 Customer is: Antonio Moreno Taquer�a
Order Id is: 15.5 Customer is: Antonio Moreno Taquer�a
Order Id is: 21.83 Customer is: Antonio Moreno Taquer�a
Order Id is: 36.04 Customer is: Antonio Moreno Taquer�a
Order Id is: 47.29 Customer is: Antonio Moreno Taquer�a
Order Id is: 58.37 Customer is: Antonio Moreno Taquer�a
Order Id is: 84.71 Customer is: Antonio Moreno Taquer�a
Order Id is: 16.12 Customer is: B�lido Comidas preparadas
Order Id is: 77.75 Customer is: B�lido Comidas preparadas
Order Id is: 97.01 Customer is: B�lido Comidas preparadas
Order Id is: 10.03 Customer is: Bon app'
Order Id is: 10.92 Customer is: Bon app'
Order Id is: 16.63 Customer is: Bon app'
Order Id is: 16.89 Customer is: Bon app'
Order Id is: 19.72 Customer is: Bon app'
Order Id is: 20.04 Customer is: Bon app'
Order Id is: 38.25 Customer is: Bon app'
Order Id is: 60.37 Customer is: Bon app'
Order Id is: 63.12 Customer is: Bon app'
Order Id is: 63.46 Customer is: Bon app'
Order Id is: 64.41 Customer is: Bon app'
Order Id is: 95.87 Customer is: Bon app'
Order Id is: 112.21 Customer is: Bon app'
Order Id is: 113.06 Customer is: Bon app'
Order Id is: 134.59 Customer is: Bon app'
Order Id is: 166.16 Customer is: Bon app'
Order Id is: 350.48 Customer is: Bon app'

Sql script

I reduced the sql to OrderId,CustomerId,Frieght

10509 6 -0.01

11011 1 1.18

10308 2 1.48

10614 6 1.83

10410 10 2.22

10864 4 2.98

10444 5 3.35

10677 3 3.94

10793 4 4.44

10297 7 5.59

10778 5 6.71

10826 7 7.01

10559 7 7.91

10501 6 8.69

10280 5 8.78

10445 5 9.15

10331 9 10.03

10741 4 10.88

10525 9 10.92

10759 2 11.91

10837 5 13.25

10689 5 13.33

10982 10 13.97

10535 3 15.50

10970 8 16.12

10755 9 16.63

10732 9 16.89

10940 9 19.72

10730 9 20.04

10707 4 21.66

10365 3 21.83

10411 10 23.46

10743 4 23.64

10953 4 23.68

11048 10 24.09

10453 4 25.22

10582 6 27.59

10679 7 27.85

10643 1 29.37

10920 4 29.56

10628 7 30.26

11058 6 31.11

10975 10 32.23

10875 5 32.31

11016 4 33.77

10383 4 34.07

10682 3 36.04

11076 9 38.25

10926 2 39.87

10952 1 40.38

10355 4 41.79

10625 2 43.80

10431 10 44.02

10956 6 44.61

10389 10 47.25

10507 3 47.29

10918 10 48.78

11027 10 52.49

10944 10 52.87

10449 7 53.16

10853 6 53.77

10265 7 55.04

10654 5 55.17

10856 3 58.37

10584 7 59.02

10876 9 60.37

10692 1 60.93

10492 10 62.74

10715 9 63.12

10827 9 63.46

10470 9 64.41

10835 1 69.45

11045 10 70.55

10949 10 74.39

10326 8 77.75

10573 3 84.71

10566 7 88.26

10278 5 92.48

10672 5 95.66

10362 9 95.87

10801 8 97.01

10866 5 109.05

10733 5 110.03

10871 9 112.21

10663 9 113.06

10572 5 116.30

10702 1 123.86

10360 7 131.54

10932 9 134.59

10626 5 138.59

10924 5 151.47

10436 7 156.51

10340 9 166.16

10384 5 168.47

10558 4 172.83

10857 5 188.79

10742 10 243.65

10524 5 244.65

10511 9 350.48

10768 4 1146.24

Edited by Markh - 14-Nov-2007 at 2:53pm

Subquery.AddOrderBy doesn't order so well... : Hi Mark I did as you suggested...

Wed, 14 Nov 2007 10:23:25 -700

Author: pkarsh
Subject: 388
Posted: 14-Nov-2007 at 10:23am

Hi Mark

I did as you suggested and I get the same result. The orders for each customer are sorted by freight cost. I don't see anything basically different.

Paul K.

Subquery.AddOrderBy doesn't order so well... : Paul Change your order by clause...

Tue, 13 Nov 2007 22:13:05 -700

Author: Markh
Subject: 388
Posted: 13-Nov-2007 at 10:13pm

Paul

Change your order by clause

from

aOrderSubquery.AddOrderBy(OrderSummary.IdEntityColumn);
to

aOrderSubquery.AddOrderByOrderSummary.FreightCostEntityColumn);

Now rerun your query test

Subquery.AddOrderBy doesn't order so well... : Hi I get confused when you say...

Tue, 13 Nov 2007 19:03:39 -700

Author: pkarsh
Subject: 388
Posted: 13-Nov-2007 at 7:03pm

Hi

I get confused when you say it should return records in the same order as the generated SQL because when I try this out I get not one but two SQL commands that between them get the data that the OQL is asking for. I don't see that the OQL could return data in a different order than it does, given what it is asked to do. If what I have done is different from what you are looking for please let me know.

Let me step through what I did.

I wrote the following OQL against the IdeaBladeTutorial database:

      EntityQuery aQuery = new EntityQuery(typeof (Customer));
      aQuery.AddClause(Customer.IdEntityColumn, EntityQueryOp.LT, 11); // restrict amount of data
      EntitySubquery aOrderSubquery = aQuery.AddSubquery(EntityRelations.Customer_OrderSummary);
      aOrderSubquery.AddOrderBy(OrderSummary.IdEntityColumn);
      aOrderSubquery.Top = 1000;

     EntityList MyEntityList = mPm.GetEntities(aQuery);
      int rowcount = 0;
      foreach (Customer aEntityInstance in MyEntityList) {
        foreach (OrderSummary aOrderSummary in aEntityInstance.OrderSummaries) {
          Console.Write("Order Id is: {0}", aOrderSummary.Id.ToString());
          Console.WriteLine(" Customer is: {0}", aEntityInstance.CompanyName);
          rowcount++;
        }
      }

As you can see, I am querying against Customer and have added an "AddOrderBy" call to a subquery on OrderSummary, which is a child Entity to Customer where there will be "many" orders for a given Customer. When I run the application I get output that is grouped by Customer for Customers whose ID's are 1 through 10. I do this just to reduce the amount of data that we are looking at. The OrderSummaryId's for each customer are sorted in ascending order. I won't show the total output but a sample of the output for the first few customers looks like this:

Order Id is: 10308 Customer is: Ana Trujillo Emparedados y helados
Order Id is: 10625 Customer is: Ana Trujillo Emparedados y helados
Order Id is: 10759 Customer is: Ana Trujillo Emparedados y helados
Order Id is: 10926 Customer is: Ana Trujillo Emparedados y helados
Order Id is: 10389 Customer is: Bottom-Dollar Markets
Order Id is: 10410 Customer is: Bottom-Dollar Markets
Order Id is: 10411 Customer is: Bottom-Dollar Markets
Order Id is: 10431 Customer is: Bottom-Dollar Markets
Order Id is: 10492 Customer is: Bottom-Dollar Markets
Order Id is: 10742 Customer is: Bottom-Dollar Markets
Order Id is: 10918 Customer is: Bottom-Dollar Markets
Order Id is: 10944 Customer is: Bottom-Dollar Markets
Order Id is: 10949 Customer is: Bottom-Dollar Markets
Order Id is: 10975 Customer is: Bottom-Dollar Markets
Order Id is: 10982 Customer is: Bottom-Dollar Markets
Order Id is: 11027 Customer is: Bottom-Dollar Markets
Order Id is: 11045 Customer is: Bottom-Dollar Markets
Order Id is: 11048 Customer is: Bottom-Dollar Markets
Order Id is: 10326 Customer is: B�lido Comidas preparadas
Order Id is: 10801 Customer is: B�lido Comidas preparadas
Order Id is: 10970 Customer is: B�lido Comidas preparadas

When I look in the DebugLog I see basically two SQL queries. These are as follows:

Query for customers:

RdbKey: [default] Sql: select * from "dbo"."Customer" where (("dbo"."Customer"."Id" in (select TOP 1000 "CustomerId" from "dbo"."OrderSummary" ORDER BY "dbo"."OrderSummary"."Id" ASC) and "dbo"."Customer"."Id"
Query for OrderSummaries:

RdbKey: [default] Sql: select TOP 1000 * from "dbo"."OrderSummary" where ("dbo"."OrderSummary"."CustomerId" in (select "dbo"."Customer"."Id" from "dbo"."Customer" where (("dbo"."Customer"."Id" in (select TOP 1000 "CustomerId" from "dbo"."OrderSummary" ORDER BY "dbo"."OrderSummary"."Id" ASC) and "dbo"."Customer"."Id"
If I paste these queries into Query windows in SQL Server Management Studio and make the modifications needed to get them to run (removing the text "RdbKey: [default] Sql: " at the beginning and replacing the ? in the query text with the value in the Params: directive, and then run them, I get the following results:

For the first query, I get the Customer records from 1 to 10. For the second query, I get the OrderSummary records ordered by their Id.

So far, the results I am getting are pretty much what I would expect. It had occurred to me that perhaps what people are looking for is a list of Customers ordered by the Id's of the orders they had placed, but then it occurred to me that this wouldn't make sense as each customer has several orders. If that is what someone is looking for, they should create an EntityQuery that returns OrderSummaries. The Sql generated by this would resemble the second SQL query above.

I did write OQL code that does this. It looks like this:

     EntityQuery aQuery = new EntityQuery(typeof (OrderSummary));
      aQuery.AddOrderBy(OrderSummary.IdEntityColumn);
      EntitySubquery aCustomerSubquery = aQuery.AddSubquery(EntityRelations.Customer_OrderSummary);
      aCustomerSubquery.AddClause(Customer.IdEntityColumn, EntityQueryOp.LT, 11);

      EntityList MyEntityList = mPm.GetEntities(aQuery);
      int rowcount = 0;
      foreach (OrderSummary aEntityInstance in MyEntityList) {
        Console.WriteLine("OrderSummary Id is: {0}, Customer is: {1}", aEntityInstance.Id.ToString(), aEntityInstance.Customer.CompanyName);
        rowcount++;
      }

It returns a list of OrderSummaries ordered by their Id.

Keep in mind that all of this is based on a 1 to many relationship between the parent and child Entity types. I can see that for parent and child Entity types where there is a 1 to 1 relationship it might make sense to sort by the Id's of the child Entities. I had asked the other poster on this thread if that was what he was interested in but so far I have not gotten a reply. If this is what you are interested in please send me a private message and I will look into it. If the OQL examples I have shown above do not correspond to your situation let me know and we can try to work out an example that does.

I would very much like to hear back from you or shoey on this.

Subquery.AddOrderBy doesn't order so well... : Hi Shoey Unfortunately Devforce...

Tue, 13 Nov 2007 14:54:56 -700

Author: jozza
Subject: 388
Posted: 13-Nov-2007 at 2:54pm

Hi Shoey

Unfortunately Devforce fail to see this as a problem :(

Subquery.AddOrderBy doesn't order so well... : Hi, My expectation is simply...

Tue, 13 Nov 2007 14:44:06 -700

Author: jozza
Subject: 388
Posted: 13-Nov-2007 at 2:44pm

Hi,

My expectation is simply that devforce returns data in the same order as it is returned in SQL. Like i mentioned, when the query from the debug xml is fed into query analyser it returns the data in the expected order, however the list in devforce is ordered differently. I think it should be a reasonable assumption that ordering should reflect sql ordering.

Cheers.

Subquery.AddOrderBy doesn't order so well... : What is your expectation? In...

Tue, 06 Nov 2007 18:21:16 -700

Author: pkarsh
Subject: 388
Posted: 06-Nov-2007 at 6:21pm

What is your expectation?

In the case of Entity A which has a relational property B, if B is an EntityList (actually a ReadOnlyEntityList), then if ApplySort is added to a subquery that accesses (so to speak) property B, then for each A the Entities in B will be sorted as specified. From what I can see, this will have no effect on the order of the EntityList of A Entities.

Do you have a different expectation? Are you making a distinction between a relational property based on a 1 to 1 relation versus a relational property based on a 1 to many relation?

Subquery.AddOrderBy doesn't order so well... : Hi Have you heard anything from...

Tue, 06 Nov 2007 09:38:51 -700

Author: shoey
Subject: 388
Posted: 06-Nov-2007 at 9:38am

Hi Have you heard anything from ideablade about this as we are having the same issue. using ib version 3.5.1.1

Subquery.AddOrderBy doesn't order so well... : Hi There seems to be an issue...

Tue, 28 Aug 2007 00:00:28 -700

Author: jozza
Subject: 388
Posted: 28-Aug-2007 at 12:00am

There seems to be an issue with the AddOrderBy method. I have the following query:

EntityQuery query = new EntityQuery(typeof(Shifts));

//Match on cost centre and roster name using the relationship.

EntitySubquery subQuery = query.AddSubquery(EntityRelations.ShiftFilter_Shifts);

subQuery.AddClause(ShiftFilter.RosterIDEntityColumn, EntityQueryOp.EQ, rosterName);

subQuery.AddClause(ShiftFilter.CostCentreIDEntityColumn, EntityQueryOp.EQ, costCentreId);

subQuery.Top = 100;

subQuery.AddOrderBy(ShiftFilter.OrderEntityColumn, System.ComponentModel.ListSortDirection.Ascending);

EntityList<Shifts> result = m_PersistenceManager.GetEntities<Shifts>(query);

When i look in the DebugLog.Xml it generates the following SQL:

select TOP 100 * from "dbo"."MIC_CC_Shift_Edit" where ((("dbo"."MIC_CC_Shift_Edit"."NM_SHIFT" in (select "dbo"."MIC_Shifts"."NM_SHIFT" from "dbo"."MIC_Shifts" where ("dbo"."MIC_Shifts"."NM_SHIFT" in (select TOP 100 "NM_SHIFT" from "dbo"."MIC_CC_Shift_Edit" where (("dbo"."MIC_CC_Shift_Edit"."ID_COST_CENTRE"=@v3 and "dbo"."MIC_CC_Shift_Edit"."NM_ROSTER"=@v2)) ORDER BY "dbo"."MIC_CC_Shift_Edit"."No_Order_Number" ASC))) and "dbo"."MIC_CC_Shift_Edit"."ID_COST_CENTRE"=@v1) and "dbo"."MIC_CC_Shift_Edit"."NM_ROSTER"=@v0)) ORDER BY "dbo"."MIC_CC_Shift_Edit"."No_Order_Number" ASC

When I pump this sql directly into query analyser, it returns the items in the correct order. However, the EntityList result does not receive the data in the correct order. We've prototyped using this approach in the past on older versions of devforce, but in 3.5.3 this doesn't work (unfortunately we don't have the old prototype code to test against 3.5.3). However, I would expect that the entitylist should contain the records in the order that they are returned from SQL.

Cheers

Jacob