Hi.
I am on v4.0 Final using adapter pattern.
I have a table called Role that has a relation to itself on the ParentRoleId field.
I have a datasource feeding data to a gridview. The title field of the parent role entity is a column in the grid. The grid has sort. LivePersistence is off and I do my own sorting and filtering.
When user sorts by parent role title, I add the required relation with an alias for the end-entity, i.e. the parent. I specify the same alias for the sort expression:
SorterToUse = new SortExpression(RoleFields.Title.SetObjectAlias("ParentRole") | GetSortOperator(RoleGridView.SortDirection));
FilterToUse.Relations.Add(RoleEntity.Relations.RoleEntityUsingParentRoleId, "ParentRole", JoinHint.Left);
This generates the following sql:
SELECT [ServiceManager].[dbo].[Role].[Description], [ServiceManager].[dbo].[Role].[IsActive], [ServiceManager].[dbo].[Role].[IsBase], [ServiceManager].[dbo].[Role].[IsEnquiryAssignmentGroup], [ServiceManager].[dbo].[Role].[ParentRoleId], [ServiceManager].[dbo].[Role].[RoleId], [ServiceManager].[dbo].[Role].[RoleTypeId], [ServiceManager].[dbo].[Role].[Title]
FROM ( [ServiceManager].[dbo].[Role]
LEFT JOIN [ServiceManager].[dbo].[Role] [LPA_P1] ON [ServiceManager].[dbo].[Role].[RoleId]=[LPA_P1].[ParentRoleId]) ORDER BY [LPA_P1].[Title] ASC
It wrongly uses the FK (ParentRoleId) on the end-entity. It also does not have the page size of 8 rows in the select.
I had expected the following:
SELECT TOP 8 [ServiceManager].[dbo].[Role].[Description], [ServiceManager].[dbo].[Role].[IsActive], [ServiceManager].[dbo].[Role].[IsBase], [ServiceManager].[dbo].[Role].[IsEnquiryAssignmentGroup], [ServiceManager].[dbo].[Role].[ParentRoleId], [ServiceManager].[dbo].[Role].[RoleId], [ServiceManager].[dbo].[Role].[RoleTypeId], [ServiceManager].[dbo].[Role].[Title]
FROM ( [ServiceManager].[dbo].[Role]
LEFT JOIN [ServiceManager].[dbo].[Role] [LPA_P1] ON [ServiceManager].[dbo].[Role].[ParentRoleId]=[LPA_P1].[RoleId]) ORDER BY [LPA_P1].[Title] DESC
What is the correct way to add a sort expression on a field of self-related entity?