Relation to self and sort on field of related entity

Posts   
 
    
hotchill avatar
hotchill
User
Posts: 180
Joined: 22-Jan-2007
# Posted on: 30-Sep-2013 11:14:12   

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?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 30-Sep-2013 20:53:59   

Please go to the Designer and check that the relation "RoleEntity.Relations.RoleEntityUsingParentRoleId" has the correct PK-FK assigned to it.

hotchill avatar
hotchill
User
Posts: 180
Joined: 22-Jan-2007
# Posted on: 01-Oct-2013 09:48:19   

This is correct:

Role.ParentRole - Role.ChildRoles (m:1)
    Role.ParentRoleId (FK) - Role.RoleId (PK)
    Role.ParentRole
    Role.ChildRoles
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 01-Oct-2013 20:31:06   

Could you please state exact LLBLGen pro runtime library version (build no.) used? (Check the forum guidelines for more details).

hotchill avatar
hotchill
User
Posts: 180
Joined: 22-Jan-2007
# Posted on: 01-Oct-2013 22:15:09   

The file version of 'SD.LLBLGen.Pro.ORMSupportClasses.dll' is 4.0.13.422.

hotchill avatar
hotchill
User
Posts: 180
Joined: 22-Jan-2007
# Posted on: 01-Oct-2013 22:18:44   

I have an extra pair of hands available on this project now. Please let me know if you need us to make a repro solution.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 02-Oct-2013 07:52:03   

You are using the wrong relation here:

...
FilterToUse.Relations.Add(RoleEntity.Relations.RoleEntityUsingParentRoleId, "ParentRole", JoinHint.Left);
...

Use the other one. It should look like:

...
FilterToUse.Relations.Add(RoleEntity.Relations.RoleEntityUsingRoleIdParentRoleId, "ParentRole", JoinHint.Left);
...

You can understand why if you go see the code for RoleEntity.Relations.RoleEntityUsingRoleIdParentRoleId (Go to definition).

David Elizondo | LLBLGen Support Team
hotchill avatar
hotchill
User
Posts: 180
Joined: 22-Jan-2007
# Posted on: 02-Oct-2013 09:56:57   

Thanks for this, it works now simple_smile

Yes, I see why. The relation I was using is for the children.