New Posts New Posts RSS Feed: Subquery.AddOrderBy doesn't order so well...
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Subquery.AddOrderBy doesn't order so well...
 Post Reply Post Reply Page  12>
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
jozza View Drop Down
Newbie
Newbie


Joined: 11-Jul-2007
Posts: 25 		Post Options Post Options   Quote jozza Quote  Post ReplyReply Direct Link To This Post Topic: Subquery.AddOrderBy doesn't order so well...
    Posted: 28-Aug-2007 at 12:00am

Hi

 
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<Shifts> 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
Back to Top
shoey View Drop Down
Newbie
Newbie
Avatar

Joined: 06-Nov-2007
Location: United Kingdom
Posts: 1 		Post Options Post Options   Quote shoey Quote  Post ReplyReply Direct Link To This Post 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
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: 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?
Back to Top
jozza View Drop Down
Newbie
Newbie


Joined: 11-Jul-2007
Posts: 25 		Post Options Post Options   Quote jozza Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
jozza View Drop Down
Newbie
Newbie


Joined: 11-Jul-2007
Posts: 25 		Post Options Post Options   Quote jozza Quote  Post ReplyReply Direct Link To This Post Posted: 13-Nov-2007 at 2:54pm
Hi Shoey
 
Unfortunately Devforce fail to see this as a problem :(
 
 
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: 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<Customer> MyEntityList = mPm.GetEntities<Customer>(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"<?)) Params: v0=11

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"<?)))) ORDER BY "dbo"."OrderSummary"."Id" ASC Params: v0=11

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<OrderSummary> MyEntityList = mPm.GetEntities<OrderSummary>(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.


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: 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
 
 
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: 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.
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: 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
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: 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.
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: 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"<?)) Params: v0=11

 

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"<?)))) ORDER BY "dbo"."OrderSummary"."Freight" ASC Params: v0=11

 

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

 

 
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: 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.
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: 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
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: 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?
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: 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
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: 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<Employee> MyEntityList = mPm.GetEntities<Employee>(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
Back to Top
 Post Reply Post Reply Page  12>

Forum Jump Forum Permissions View Drop Down