Im trying to get back a TypedList sorted by a column FeatureElement.FeatureElementUID which is aliased to ChildElementUID in the designer.

But I get this error: System.Data.SqlClient.SqlException was unhandled by user code Message="The multi-part identifier \"ArtenaNET.dbo.FeatureElement.FeatureElementUID\" could not be bound." Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=16 LineNumber=1 Number=4104

Ive tried a number of things and this is the code Ive got at the moment:

      GroupUISecurityElementTypedList SecurityElements = new GroupUISecurityElementTypedList();

      IEntityField2 ChildElement = FeatureElementFields.FeatureElementUID;
      ChildElement.Alias = "ChildElementUID";
    
      SortClause sc = new SortClause(ChildElement, null, SortOperator.Ascending);
      ISortExpression sortClauses = new SortExpression(sc);

      Adapter.FetchTypedList(SecurityElements, Filter, 0, sortClauses, false);

which generates this SQL:

SELECT 
    [LPA_C1].[FeatureElementUID] AS [ChildElementUID], 
    [LPA_P2].[Title] AS [ParentName], 
    [LPA_C1].[Title] AS [ElementName], 
    [LPA_C1].[ParentFeatureElementUID] AS [ParentElementUID], 
    [ArtenaNET].[dbo].[FeatureElementSecurity].[SecurityGroupUID] AS [GroupUID], 
    [ArtenaNET].[dbo].[FeatureElementSecurity].[FeatureElementSecurityStateUID] AS [StateUID], 
    [ArtenaNET].[dbo].[SecurityGroupUser].[SytemUserUID] 
FROM (((( [ArtenaNET].[dbo].[FeatureElement] [LPA_C1] 
INNER JOIN 
    [ArtenaNET].[dbo].[FeatureElementSecurity] 
    ON [LPA_C1].[FeatureElementUID]=[ArtenaNET].[dbo].[FeatureElementSecurity].[FeatureElementUID]) 
RIGHT JOIN 
    [ArtenaNET].[dbo].[FeatureElement] [LPA_P2] 
    ON [LPA_P2].[FeatureElementUID]=[LPA_C1].[ParentFeatureElementUID]) 
INNER JOIN 
    [ArtenaNET].[dbo].[SecurityGroup] 
    ON [ArtenaNET].[dbo].[SecurityGroup].[SecurityGroupUID]=[ArtenaNET].[dbo].[FeatureElementSecurity].[SecurityGroupUID]) 
INNER JOIN 
    [ArtenaNET].[dbo].[SecurityGroupUser] 
    ON [ArtenaNET].[dbo].[SecurityGroup].[SecurityGroupUID]=[ArtenaNET].[dbo].[SecurityGroupUser].[SecurityGroupUID]) 
WHERE 
    ( 
        ( 
            [ArtenaNET].[dbo].[SecurityGroupUser].[SytemUserUID] = @SytemUserUID1
        )
    ) 
ORDER BY [ArtenaNET].[dbo].[FeatureElement].[FeatureElementUID] ASC

As you can see, its order clause is: ORDER BY [ArtenaNET].[dbo].[FeatureElement].[FeatureElementUID] ASC instead of: ORDER BY [ChildElementUID] ASC

What do i need to do?

PS:Just got rid of the field alias and it still does it - is this problem similar to: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=1771&HighLight=1

TomDog wrote:

Im trying to get back a TypedList sorted by a column FeatureElement.FeatureElementUID which is aliased to ChildElementUID in the designer.

But I get this error: System.Data.SqlClient.SqlException was unhandled by user code Message="The multi-part identifier \"ArtenaNET.dbo.FeatureElement.FeatureElementUID\" could not be bound." Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=16 LineNumber=1 Number=4104

Ive tried a number of things and this is the code Ive got at the moment:

      GroupUISecurityElementTypedList SecurityElements = new GroupUISecurityElementTypedList();

      IEntityField2 ChildElement = FeatureElementFields.FeatureElementUID;
      ChildElement.Alias = "ChildElementUID";
    
      SortClause sc = new SortClause(ChildElement, null, SortOperator.Ascending);
      ISortExpression sortClauses = new SortExpression(sc);

      Adapter.FetchTypedList(SecurityElements, Filter, 0, sortClauses, false);

Setting the alias is the field alias, not the object alias. If you want to specify an alias for the table/view, set the fields' ObjectAlias. Though you can do it differently. See below.

which generates this SQL:

SELECT 
    [LPA_C1].[FeatureElementUID] AS [ChildElementUID], 
    [LPA_P2].[Title] AS [ParentName], 
    [LPA_C1].[Title] AS [ElementName], 
    [LPA_C1].[ParentFeatureElementUID] AS [ParentElementUID], 
    [ArtenaNET].[dbo].[FeatureElementSecurity].[SecurityGroupUID] AS [GroupUID], 
    [ArtenaNET].[dbo].[FeatureElementSecurity].[FeatureElementSecurityStateUID] AS [StateUID], 
    [ArtenaNET].[dbo].[SecurityGroupUser].[SytemUserUID] 
FROM (((( [ArtenaNET].[dbo].[FeatureElement] [LPA_C1] 
INNER JOIN 
    [ArtenaNET].[dbo].[FeatureElementSecurity] 
    ON [LPA_C1].[FeatureElementUID]=[ArtenaNET].[dbo].[FeatureElementSecurity].[FeatureElementUID]) 
RIGHT JOIN 
    [ArtenaNET].[dbo].[FeatureElement] [LPA_P2] 
    ON [LPA_P2].[FeatureElementUID]=[LPA_C1].[ParentFeatureElementUID]) 
INNER JOIN 
    [ArtenaNET].[dbo].[SecurityGroup] 
    ON [ArtenaNET].[dbo].[SecurityGroup].[SecurityGroupUID]=[ArtenaNET].[dbo].[FeatureElementSecurity].[SecurityGroupUID]) 
INNER JOIN 
    [ArtenaNET].[dbo].[SecurityGroupUser] 
    ON [ArtenaNET].[dbo].[SecurityGroup].[SecurityGroupUID]=[ArtenaNET].[dbo].[SecurityGroupUser].[SecurityGroupUID]) 
WHERE 
    ( 
        ( 
            [ArtenaNET].[dbo].[SecurityGroupUser].[SytemUserUID] = @SytemUserUID1
        )
    ) 
ORDER BY [ArtenaNET].[dbo].[FeatureElement].[FeatureElementUID] ASC

As you can see, its order clause is: ORDER BY [ArtenaNET].[dbo].[FeatureElement].[FeatureElementUID] ASC instead of: ORDER BY [ChildElementUID] ASC

What do i need to do?

The sorter doesn't work with aliases for fields, if the field doesn't have an expression/aggregate set. This is by design to avoid SQL errors.

You should do:

ISortExpression sortClauses = new SortExpression(new SortClause(SecurityElements.GetFieldsInfo()["ChildElementUID"], null, SortOperator.Ascending));

Here you simply pass the same field as in the select list.