When the following entity class is queried and the second reference to the associate entity is used in a criteria statement, the ORM picks the wrong table alias for the join and fails to return data.

Entity Files

Controller Associate

  [Table('CONTROLLER')]
  [Entity]
  [Sequence('Select GetSid() as Sid from dual')]
  TController = class
  private

    [Column('SID',[cpRequired,cpPrimaryKey,cpNotNull],19,0)]
    FSid: Int64;

    [Column('CONTROLLER_NO',[],5,0)]
    FControllerNo: Nullable<Int16>;

    [Column('CONTROLLER_NAME',[],15)]
    FControllerName: Nullable<string>;

  protected
  public
    property Sid: Int64 read FSid write FSid;
    property ControllerNo: Nullable<Int16> read FControllerNo write FControllerNo;
    property ControllerName: Nullable<string> read FControllerName write FControllerName;
  end;

Remote Connection (References Controller Twice)

  [Table('REMOTE_CONNECTION')]
  [Entity]
  [Sequence('Select GetSid() as Sid from dual')]
  TRemoteConnection = class
  private

    [Column('SID',[cpRequired,cpPrimaryKey,cpNotNull],19,0)]
    FSid: Int64;


    [Column('REMOTE_CONTROLLER_SID',[cpRequired,cpNotNull],19,0)]
    FRemoteControllerSid: Int64;


    [Column('LOCAL_CONTROLLER_SID',[cpRequired,cpNotNull],19,0)]
    FLocalControllerSid: Int64;

    [Column('CONNECTION_TYPE',[cpRequired,cpNotNull],1,0)]
    FConnectionType: TServerMode;

    [Spring.Persistence.Mapping.Attributes.Transient]
    [ManyToOne(false, [ckCascadeAll], 'FLocalControllerSid')]
    FLocalController: TController;

    [Spring.Persistence.Mapping.Attributes.Transient]
    [ManyToOne(false, [ckCascadeAll], 'FRemoteControllerSid')]
    FRemoteController: TController;

  protected
    function GetLocalControllerName: Nullable<string>;
    procedure SetLocalControllerName(const Value: Nullable<string>);    
    function GetLocalAddress:Nullable<string>;
    function GetRemoteControllerName: Nullable<string>;
    procedure SetRemoteControllerName(const Value: Nullable<string>);
    function GetRemoteAddress:Nullable<string>;
    function GetConnectionName: string;
    procedure SetConnectionName(const Value: string);
  public
    property Sid: Int64 read FSid write FSid;
    property ConnectionName: string read GetConnectionName write SetConnectionName;
    property RemoteControllerSid: Int64 read FRemoteControllerSid write FRemoteControllerSid;
    property ConnectionType: TServerMode read FConnectionType write FConnectionType;
    property LocalControllerSid: Int64 read FLocalControllerSid write FLocalControllerSid;

    property LocalControllerName: Nullable<string> read GetLocalControllerName write SetLocalControllerName;

    property RemoteControllerName: Nullable<string> read GetRemoteControllerName write SetRemoteControllerName;

  end;

Criteria that fails

var
  RemoteController : IProperty;
  Column ColumnAttribute;
begin
  //Fails if issued against the Remote Controller Name (second controller referenced)  
  ...  //Not all code provided for brevity
  Fld := <Get ControllerName Field from Controller Entity using Rtti>
  if not Fld.TryGetCustomAttribute<ColumnAttribute>(Column) then
      raise Exception.Create('Field does not exist');
  RemoteController := TProperty.Create(Column.ColumnName, TController);  
  Criteria.Add(RemoteController.Eq('SomeName'));
  //At this point we have lost information that specifies the second controller table alias should be used
end;

Primary Issue

When the above code is used to construct a criteria to select a remote connection based on the RemoteControllerName we loose the fact that it is the second instance of the controller associate that is being queried and the FindTable method of the Spring.Persistence.Sql.Commands just looks for the first table referenced with the table name associated with TController.

Seems like there needs to be a way to link the table alias with the instance of the table referenced in the Entity.

Here is the offending code as it just picks the first table and hence the first alias that matches the table name.

function TSelectCommand.FindTable(entityClass: TClass): TSQLTable;
var
  tableName: string;
  currentTable: TSQLTable;
begin
  if entityClass = nil then
    Exit(fTable);

  tableName := TEntityCache.Get(entityClass).EntityTable.TableName;

  for currentTable in fTables do
    if SameText(currentTable.NameWithoutSchema, tableName) then
      Exit(currentTable);
  Result := fTable;
end;

Where clause fails to get the right Table Alias when an ORM entity is referenced twice as an associate of another Entity

Entity Files

Controller Associate

Remote Connection (References Controller Twice)

Criteria that fails

Primary Issue

Comments (12)