Hi,

I have an Employee entity and a Department entity and each Employee instance have a Department instance. If I want an Employee collection sorted by Department name (the department name is in the department entity), what is the best way to do that?

Thank you,

Some databases have problems with fields in ORDER BY clauses which are not in the resultset, however if you are retrieving the entities in the EmployeeCollection via a GetMulti, you can specify a relations collection, in which you can add the relation between Employee and Department, and in the sortexpression you can refer to the department name in Department.

Ok I did that, but I checked the SQL command generated to fill my Employee collection (using GetMulti with a RelationCollection instance containing the relation between Employee and Department and a SortExpression telling to sort using the DepartmentFieldIndex.Name) and the SQL command selects individually all fields from Employee table (SELECT [dbo].[Employee].[field1],[dbo].[Employee].[field2],etc..) left joined with Department table (I've set the ObeyWeakRelations property to true) on the good condition and then it orders everything based on the [dbo].[Department].[Name], but since this field is not part of the selected fields (only fields from Employee are part of the selected fields), it doesn't sort things out properly. Am I doing something wrong? Thank you!

I've done this in Query Analyzer:

SELECT  Customers.* 
FROM    Customers LEFT JOIN Orders
    ON Customers.CustomerID = Orders.CustomerID
ORDER BY Orders.EmployeeID DESC

And this in code:

CustomerCollection allCustomers = new CustomerCollection();
RelationCollection relationsToUse = new RelationsCollection();
relationsToUse.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID);
relationsToUse.ObeyWeakRelations=true;
ISortExpression sorter = new SortExpression();
sorter.Add(SortClauseFactory.Create(OrderFieldIndex.EmployeeID, SortOperator.Descending));
allCustomers.GetMulti(null, 0, sorter, relationsToUse);

and then I ran into a bug in the templates which told me that ORDER BY fields should be in the select list when DISTINCT was used. The templates still told the DQE to generate a query which would not allow duplicates, but this is silly of course because duplicates are already filtered out when they're added to the collection object. This works if a field violates with DISTINCT (ntext or image for example) so the DISTINCT keyword is removed.

When I fixed the template error, the retrieved customer collection was in exact the same order as the rows in the query analyzer grid. (I bound the allCustomers collection to a grid on a form).

Does your result look the same as the one in query analyzer with the same query?

Ok I think I found out where the problem comes from. I made a simple test in SQL Server with my two tables Employee and Department. Employee as 3 fields: empId, empName and depId. Department as 2 fields: depId and depName

If I do the following:

select      Employee.empName
from        Employee left join Department on Department.depId = Employee.depId
order by    Department.depName

it sorts the employees by their respective department name in ascending order -> FINE. However, if I try this one:

select      Employee.empName as depName
from        Employee left join Department on Department.depId = Employee.depId
order by    Department.depName

then it sorts everything out by the employee name! In my real case, the Department name column is only labeled Name in my database and in my entity the Employee Name column is renamed to Name: so the DQE generates the following query:

SELECT [dbo].[Employee].[empName] AS [Name]
FROM [dbo].[Employee] LEFT JOIN [dbo].[Department] ON [dbo].[Department].[depId] = [dbo].[Employee].[depId]
ORDER BY [dbo].[Department].[Name]

and I fall exactly in the second case and that's why it doesn't sort properly. If in the above query I just change the alias 'AS [Name]' to 'AS [EmployeeName]', everything works fine.

(Sorry for not getting back to you sooner)

SELECT  Customers.ContactName As ShipName
FROM     Customers LEFT JOIN Orders
    ON Customers.CustomerID = Orders.CustomerID
ORDER BY Orders.ShipName DESC

gives the same results as:

SELECT  Customers.ContactName
FROM     Customers LEFT JOIN Orders
    ON Customers.CustomerID = Orders.CustomerID
ORDER BY Orders.ShipName DESC

Which is expected, because I sort on a field I clearly describe. You specify a field with object name and schema name, and if SqlServer just uses just the name of the field and ignores the object name and schema name, that's a bug in SqlServer.

Can you please check which service pack you've installed on sqlserver? Here I use SP3(a).

SELECT @@VERSION: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

That's it, the service packs are not installed on the SQL server...

Hi,

Is there a way you can sort by a foreign key value AFTER a call to GetMulti?

Thanks in advance for your help!

billa1972 wrote:

Hi,

Is there a way you can sort by a foreign key value AFTER a call to GetMulti?

Thanks in advance for your help!

You can enable sorting for grids in a databinding scenario by setting SupportsSorting to true.

You can also manually sort the entity list, by calling ApplySorting() with the right parameters. This is the routine also used by for example a DataGrid when you click a heading of a column.

See this thread for more details: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=432