Subquery.AddOrderBy doesn't order so well...
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=388
Printed Date: 01-Jun-2024 at 7:53am
Topic: Subquery.AddOrderBy doesn't order so well...
Posted By: jozza
Subject: Subquery.AddOrderBy doesn't order so well...
Date 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
|
Replies:
Posted By: shoey
Date 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
|
Posted By: pkarsh
Date 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?
|
Posted By: jozza
Date 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.
|
Posted By: jozza
Date Posted: 13-Nov-2007 at 2:54pm
|
Hi Shoey
Unfortunately Devforce fail to see this as a problem :(
|
Posted By: pkarsh
Date 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.
|
Posted By: Markh
Date 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
|
Posted By: pkarsh
Date 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.
|
Posted By: Markh
Date 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
|
Posted By: pkarsh
Date 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.
|
Posted By: Markh
Date 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
|
Posted By: pkarsh
Date 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.
|
Posted By: Markh
Date 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.
|
Posted By: pkarsh
Date 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?
|
Posted By: Markh
Date 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
|
Posted By: pkarsh
Date 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.
|
Posted By: pnschofield
Date 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.
|
|