When you sort on multiple fields with the same column name (e.g. a.weight asc, b.weight asc), the runtime throws an exception: A column has been specified more than once in the order by list. Columns in the order by list must be unique.

I'll check it out

Could you paste some details about teh code you're trying to execute? The error is from SqlServer, and I'm interested in if you're fetching a typed list, a dyn. list, an entity collection, what the code is you used to construct the sortclause...

I ask this because this works:

[Test]
public void CustomConstructedTypedListOrderByTest()
{
    ResultsetFields fields = new ResultsetFields(6);

    fields.DefineField(EmployeeFieldIndex.EmployeeId, 0, "EmployeeId", "Employee");
    fields.DefineField(EmployeeFieldIndex.FirstName, 1, "FirstName", "Employee");
    fields.DefineField(EmployeeFieldIndex.LastName, 2, "LastName", "Employee");
    fields.DefineField(EmployeeFieldIndex.EmployeeId, 3, "ManagerId", "Manager");
    fields.DefineField(EmployeeFieldIndex.FirstName, 4, "FirstNameManager", "Manager");
    fields.DefineField(EmployeeFieldIndex.LastName, 5, "LastNameManager", "Manager");
    IRelationCollection relations = new RelationCollection();
    relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager", JoinHint.None);

    ISortExpression sorter = new SortExpression();
    sorter.Add(new SortClause(fields[0], SortOperator.Ascending));
    sorter.Add(new SortClause(fields[3], SortOperator.Ascending));

    DataTable tlist = new DataTable();
    TypedListDAO dao = new TypedListDAO();

    dao.GetMultiAsDataTable(fields, tlist, 0, sorter, null, relations, false, null, null, 0, 0);
}

So if you're sorting on the same entity which is joined multiple times, be sure you're using aliassing and you specify the proper alias with the sortclause you're adding to the sortexpression. (here I re-use the fields, so aliasses are set up correctly)

I use selfservicing, and I'm trying to fetch entities using an entity collection. I want to sort the entities by a field called 'Weight', which appears in 2 tables. The generated query is valid in SQL server, but the LLBLGen runtime doesn't seem to allow this.

WilcoB wrote:

I use selfservicing, and I'm trying to fetch entities using an entity collection. I want to sort the entities by a field called 'Weight', which appears in 2 tables. The generated query is valid in SQL server, but the LLBLGen runtime doesn't seem to allow this.

Sorry to nittpick, but it's not clear what the error is exactly. What's the exact exception, stacktrace and what's the code you're executing? The reason I ask this is because there is no check code in the runtime library which checks if a field is added twice to a sortexpression. It doesn't care.

I'll try to reproduce it by fetching customer entities and sort on customer.customerid AND order.customerID, however I'm not sure if I'm testing what you're running, so it would be great if you paste the code you're executing, the exception message and the stacktrace

Edit: Ok I reproduced it with:

[Test]
public void ComplexSortCollectionFetchTest()
{
    CustomerCollection customers = new CustomerCollection();
    IRelationCollection relations = new RelationCollection();
    ISortExpression sorter = new SortExpression();
    relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId);
    sorter.Add(SortClauseFactory.Create(CustomerFieldIndex.CustomerId, SortOperator.Ascending));
    sorter.Add(SortClauseFactory.Create(OrderFieldIndex.CustomerId, SortOperator.Ascending));

    customers.GetMulti(null, 0, sorter, relations);
}

Looking into it.

(edit) Hmm. The query, as you said, is not wrongly generated:

SELECT  [dbo].[Customers].[CustomerID] AS [CustomerId],
    [dbo].[Customers].[CompanyName] AS [CompanyName],
    [dbo].[Customers].[ContactName] AS [ContactName],
    [dbo].[Customers].[ContactTitle] AS [ContactTitle],
    [dbo].[Customers].[Address] AS [Address],
    [dbo].[Customers].[City] AS [City],
    [dbo].[Customers].[Region] AS [Region],
    [dbo].[Customers].[PostalCode] AS [PostalCode],
    [dbo].[Customers].[Country] AS [Country],
    [dbo].[Customers].[Phone] AS [Phone],
    [dbo].[Customers].[Fax] AS [Fax] 
FROM    ( 
        [dbo].[Customers] INNER JOIN [dbo].[Orders] ON  
        [dbo].[Customers].[CustomerID]=[dbo].[Orders].[CustomerID]
    ) 
ORDER BY 
    [dbo].[Customers].[CustomerID] ASC,
    [dbo].[Orders].[CustomerID] ASC

But SqlServer apparently can't distinguish the fields in the Order By clause, even though the full schema + table name is specified.

What's even more weird is the fact that if I specify an alias for Orders and customers, it works, IF I also select from the alias (thus do C.* instead of customers.*)

(edit) It apparently works if the select is from the aliased table + everything else is aliased, OR if I change the alias for the CustomerID field in the select list, as the order by apparently sees 2 columns in the join list with the same name which don't look the same suddenly when the tables are aliased... Clearly a bug in sqlserver (as the full column specification is specified in the order by clause so it can determine which fields to sort on), but that doesn't help much.

The problem is: the GetMulti() call chain creates its own fieldlist for the select query producing routine. This means that you can specify aliasses for the tables in the relation collection, for the sortfields but not for the fields in the select list, which makes the query fail.

The only workaround for now is to rename the Weight field in one of the entities, so the select list will have a different alias, so the SqlServer query processor doesn't get confused

I posted this in the microsoft sql server programming newsgroup, an SqlServer MVP replied:

What I think happens is that an intermediate resultset is created for the ordering. This intermediate resultset consists of the columns in the select statement plus the columns in the ORDER BY clause that are not in the select. As the columns in the select are aliased, they are not the same as the columns in the order by, but they still have the same name. The error message definitely isn't correct, because when you change the name of the column alias to for example customerid1, the query works. Also, if you order on only one of the columns, the query executes fine.

I think it is a bug, but one in the "won't fix" category.

Btw, using columns in the order by clause that are not in the select is a T-SQL extension, it is not supported in standard ANSI-SQL.

-- Jacco Schalkwijk SQL Server MVP

I'll add to the todo list a routine which checks if the column aliasses are really necessary on SqlServer (or at least investigate if this is possible). Leaving out the aliasses for the columns makes the query work...