Quick question: association n:m

I have a User Table (UserID, UserName)
I have a Role Table (RoleID, RoleName)
I have a n:m UserRole Table (UserID, RoleID)

Now I want get the RoleList of a User

MyRoleList := Manager.Find<TRole>
                       .CreateAlias('UserRole', 'ur')
                       .Where(Linq['ur.UserId'] = 'UserID')
                       .List;

Error: Property "UserRole" not found on class "TRole"

Where is the error? Or what must I do?

Mapping:

Table User

  [Entity]
  [Table('t_user')]
  [Id('FUserID', TIdGenerator.None)]
  TUser = class
  private
    [Column('usr_id', [TColumnProp.Required])]
    FUserID: TGuid;
   
    [Column('usr_name', [TColumnProp.Required], 200)]
    FName: string;
    
    [ManyValuedAssociation([TAssociationProp.Required], [TCascadeType.SaveUpdate, TCascadeType.Merge], 'FUserId')]
    FUserRoleList: TList<TUserRole>;
  public
    constructor Create;
    destructor Destroy; override;
    property UserID: TGuid read FUserID write FUserID;
    property Name: string read FName write FName;
    property UserRoleList: TList<TUserRole> read FUserRoleList;
  end;

Table Role

  [Entity]
  [Table('t_role')]
  [Id('FRoleID', TIdGenerator.None)]
  TRole = class
  private
    [Column('rol_id', [TColumnProp.Required])]
    FRoleID: TGuid;
    
    [Column('rol_name', [TColumnProp.Required], 80)]
    FName: string;
    
    [ManyValuedAssociation([TAssociationProp.Lazy, TAssociationProp.Required], [TCascadeType.SaveUpdate, TCascadeType.Merge], 'FRoleId')]
    FUserRoleList: Proxy<TList<TUserRole>>;
    function GetUserRoleList: TList<TUserRole>;
  public
    constructor Create;
    destructor Destroy; override;
    property RoleID: TGuid read FRoleID write FRoleID;
    property Name: string read FName write FName;
    property UserRoleList: TList<TUserRole> read GetUserRoleList;
  end;

Table UserRole

  [Entity]
  [Table('t_userrole')]
  [Id('FUserId', TIdGenerator.None)]
  [Id('FRoleId', TIdGenerator.None)]
  TUserRole = class
  private
    [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('usr_id', [TColumnProp.Required], 'usr_id')]
    FUserId: Proxy<TUser>;
    
    [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('rol_id', [TColumnProp.Required], 'rol_id')]
    FRoleId: Proxy<TRole>;
    function GetUserId: TUser;
    procedure SetUserId(const Value: TUser);
    function GetRoleId: TRole;
    procedure SetRoleId(const Value: TRole);
  public
    property UserId: TUser read GetUserId write SetUserId;
    property RoleId: TRole read GetRoleId write SetRoleId;
  end;

In SQL:


select RoleID, RoleName
from Role, UserRole
where Role.RoleID = UserRole.RoleID
and UserRole.UserID = 'MyUserId'

Since you named your field TRole.FUserRoleList, that's what you should use. Aurelius Criteria doesn't see databases, only objects:




MyRoleList := Manager.Find<TRole>
                       .CreateAlias('UserRoleList', 'ur')
                       .Where(Linq['ur.UserId'] = 'UserID')
                       .List;


But if you want user roles for an user, this is the way you should do:

Since you named your field TRole.FUserRoleList, that's what you should use. Aurelius Criteria doesn't see databases, only objects:


MyUserRoleList := Manager.Find<TUserRole>
                       .CreateAlias('UserId', 'u')
                       .Where(Linq['u.UserId'] = 'UserID')
                       .List;