Our configuration

We use a simple primary key for all our DB tables defined as number(19, 0).
We use the same column name for all our primary key fields in every table namely (SID)
I have a class that has more than one ForeignKeyField attribute defined. Like so:

type
  [Entity]
  [Table('VENDOR', 'rps')]
  [Sequence('Select GetSid as Sid from dual')]
  TVendor = class
  private
    FSid: Int64;
    FCreatedBy: string;
    FCreatedDatetime: TDateTime;
    FModifiedBy: Nullable<string>;
    FModifiedDatetime: Nullable<TDateTime>;
    FControllerSid: Int64;
    FOriginApplication: string;
    FPostDate: Nullable<TDateTime>;
    FRowVersion: Integer;
    FTenantSid: Nullable<Int64>;
    FVendCode: string;
    FActive: Int16;
    FVendName: Nullable<string>;
    FInfo1: Nullable<string>;
    FInfo2: Nullable<string>;
    FTermType: Nullable<Int16>;
    FAccountNo: Nullable<string>;
    FTradeDiscPerc: Nullable<Double>;
    FVendLeadTime: Nullable<Int16>;
    FApFlag: Nullable<Integer>;
    FCurrencySid: Nullable<Int64>;
    FRegional: Nullable<Int16>;
    FCountrySid: Nullable<Int64>;
    FQbId: Nullable<string>;
    FArchived: Int16;
    FUdf1String: Nullable<string>;
    FUdf2String: Nullable<string>;
    FUdf3String: Nullable<string>;
    FUdf4String: Nullable<string>;
    FUdf5String: Nullable<string>;
    FUdf6String: Nullable<string>;
    FUdf1Date: Nullable<TDateTime>;
    FUdf2Date: Nullable<TDateTime>;
    FNotes: Nullable<string>;
    FImage: Nullable<TMemoryStream>;
    FVendId: Nullable<Integer>;
    FFirstName: Nullable<string>;
    FLastName: Nullable<string>;
    FSbsSid: Nullable<Int64>;
    FTitleSid: Nullable<Int64>;
    FLanguageSid: Nullable<Int64>;
    [OneToMany(False, [ckCascadeAll])]
    FVendorContactAddresses: Lazy<IList<TVendorContactAddress>>;
    [OneToMany(False, [ckCascadeAll])]
    FVendorEmails: Lazy<IList<TVendorEmail>>;
    [OneToMany(False, [ckCascadeAll])]
    FVendorPhones: Lazy<IList<TVendorPhone>>;
    [OneToMany(False, [ckCascadeAll])]
    FVendorTerms: Lazy<IList<TVendorTerm>>;
    function GetVendorContactAddresses: IList<TVendorContactAddress>;
    function GetVendorEmails: IList<TVendorEmail>;
    function GetVendorPhones: IList<TVendorPhone>;
    function GetVendorTerms: IList<TVendorTerm>;
  public
    constructor Create; virtual;
    [Column('SID',[cpRequired,cpNotNull,cpPrimaryKey],19,0)]
    property Sid: Int64 read FSid write FSid;
    [Column('CREATED_BY',[cpRequired,cpNotNull],20)]
    property CreatedBy: string read FCreatedBy write FCreatedBy;
    [Column('CREATED_DATETIME',[cpRequired,cpNotNull])]
    property CreatedDatetime: TDateTime read FCreatedDatetime write FCreatedDatetime;
    [Column('MODIFIED_BY',[],20)]
    property ModifiedBy: Nullable<string> read FModifiedBy write FModifiedBy;
    [Column('MODIFIED_DATETIME',[])]
    property ModifiedDatetime: Nullable<TDateTime> read FModifiedDatetime write FModifiedDatetime;
    [ForeignJoinColumn('CONTROLLER_SID', 'CONTROLLER', 'SID', [fsOnDeleteCascade, fsOnUpdateCascade])]
    [Column('CONTROLLER_SID',[cpRequired,cpNotNull],19,0)]
    property ControllerSid: Int64 read FControllerSid write FControllerSid;
    [Column('ORIGIN_APPLICATION',[cpRequired,cpNotNull],20)]
    property OriginApplication: string read FOriginApplication write FOriginApplication;
    [Column('POST_DATE',[])]
    property PostDate: Nullable<TDateTime> read FPostDate write FPostDate;
    [Version('ROW_VERSION', 1)]
    property RowVersion: Integer read FRowVersion write FRowVersion;
    [ForeignJoinColumn('TENANT_SID', 'TENANT', 'SID', [fsOnDeleteCascade, fsOnUpdateCascade])]
    [Column('TENANT_SID',[],19,0)]
    property TenantSid: Nullable<Int64> read FTenantSid write FTenantSid;
    [Column('VEND_CODE',[cpRequired,cpNotNull],6)]
    property VendCode: string read FVendCode write FVendCode;
    [Column('ACTIVE',[cpRequired,cpNotNull],1,0)]
    property Active: Int16 read FActive write FActive;
    [Column('VEND_NAME',[],25)]
    property VendName: Nullable<string> read FVendName write FVendName;
    [Column('INFO1',[],20)]
    property Info1: Nullable<string> read FInfo1 write FInfo1;
    [Column('INFO2',[],20)]
    property Info2: Nullable<string> read FInfo2 write FInfo2;
    [Column('TERM_TYPE',[],5,0)]
    property TermType: Nullable<Int16> read FTermType write FTermType;
    [Column('ACCOUNT_NO',[],10)]
    property AccountNo: Nullable<string> read FAccountNo write FAccountNo;
    [Column('TRADE_DISC_PERC',[],16,4)]
    property TradeDiscPerc: Nullable<Double> read FTradeDiscPerc write FTradeDiscPerc;
    [Column('VEND_LEAD_TIME',[],5,0)]
    property VendLeadTime: Nullable<Int16> read FVendLeadTime write FVendLeadTime;
    [Column('AP_FLAG',[],10,0)]
    property ApFlag: Nullable<Integer> read FApFlag write FApFlag;
    [ForeignJoinColumn('CURRENCY_SID', 'CURRENCY', 'SID', [fsOnDeleteCascade, fsOnUpdateCascade])]
    [Column('CURRENCY_SID',[],19,0)]
    property CurrencySid: Nullable<Int64> read FCurrencySid write FCurrencySid;
    [Column('REGIONAL',[],1,0)]
    property Regional: Nullable<Int16> read FRegional write FRegional;
    [ForeignJoinColumn('COUNTRY_SID', 'COUNTRY', 'SID', [fsOnDeleteCascade, fsOnUpdateCascade])]
    [Column('COUNTRY_SID',[],19,0)]
    property CountrySid: Nullable<Int64> read FCountrySid write FCountrySid;
    [Column('QB_ID',[],40)]
    property QbId: Nullable<string> read FQbId write FQbId;
    [Column('ARCHIVED',[cpRequired,cpNotNull],1,0)]
    property Archived: Int16 read FArchived write FArchived;
    [Column('UDF1_STRING',[],100)]
    property Udf1String: Nullable<string> read FUdf1String write FUdf1String;
    [Column('UDF2_STRING',[],100)]
    property Udf2String: Nullable<string> read FUdf2String write FUdf2String;
    [Column('UDF3_STRING',[],100)]
    property Udf3String: Nullable<string> read FUdf3String write FUdf3String;
    [Column('UDF4_STRING',[],100)]
    property Udf4String: Nullable<string> read FUdf4String write FUdf4String;
    [Column('UDF5_STRING',[],100)]
    property Udf5String: Nullable<string> read FUdf5String write FUdf5String;
    [Column('UDF6_STRING',[],100)]
    property Udf6String: Nullable<string> read FUdf6String write FUdf6String;
    [Column('UDF1_DATE',[])]
    property Udf1Date: Nullable<TDateTime> read FUdf1Date write FUdf1Date;
    [Column('UDF2_DATE',[])]
    property Udf2Date: Nullable<TDateTime> read FUdf2Date write FUdf2Date;
    [Column('NOTES',[])]
    property Notes: Nullable<string> read FNotes write FNotes;
    [Column('IMAGE',[])]
    property Image: Nullable<TMemoryStream> read FImage write FImage;
    [Column('VEND_ID',[],10,0)]
    property VendId: Nullable<Integer> read FVendId write FVendId;
    [Column('FIRST_NAME',[],30)]
    property FirstName: Nullable<string> read FFirstName write FFirstName;
    [Column('LAST_NAME',[],30)]
    property LastName: Nullable<string> read FLastName write FLastName;
    [ForeignJoinColumn('SBS_SID', 'SUBSIDIARY', 'SID', [fsOnDeleteCascade, fsOnUpdateCascade])]
    [Column('SBS_SID',[],19,0)]
    property SbsSid: Nullable<Int64> read FSbsSid write FSbsSid;
    [ForeignJoinColumn('TITLE_SID', 'TITLE', 'SID', [fsOnDeleteCascade, fsOnUpdateCascade])]
    [Column('TITLE_SID',[],19,0)]
    property TitleSid: Nullable<Int64> read FTitleSid write FTitleSid;
    [ForeignJoinColumn('LANGUAGE_SID', 'RPS_LANGUAGE', 'SID', [fsOnDeleteCascade, fsOnUpdateCascade])]
    [Column('LANGUAGE_SID',[],19,0)]
    property LanguageSid: Nullable<Int64> read FLanguageSid write FLanguageSid;

    property VendorContactAddresses: IList<TVendorContactAddress> read getVendorContactAddresses;
    property VendorEmails: IList<TVendorEmail> read getVendorEmails;
    property VendorPhones: IList<TVendorPhone> read getVendorPhones;
    property VendorTerms: IList<TVendorTerm> read getVendorTerms;
  end;

Notice a [ForeignJoinColumn] attribute on several fields including Controller_Sid, Tenant_Sid, Title_Sid, SBS_Sid, Language_Sid, Country_Sid, Currency_Sid and so forth.

The Seeming Issue

Because the RttiExplorer.GetForeignKeyColumn Method only looks for a foreignJoinColumn definition based on the referring column name it just matches the first foreign key in the list every time since we use exactly the same column name on all tables for the primary key.

Possible Solution

Pass the BaseTable Attribute along with the BasePrimaryKeyAttribute to the GetForeignKeyColumn method and check both referenced table and referenced column.

Here is how I had to change the RTTIExplorers GetForeignKeyColumn method to lookup the referenced table along with the referenced column.

class function TRttiExplorer.GetForeignKeyColumn(AClass: TClass;
  const ABaseTable : TableAttribute; const ABaseTablePrimaryKeyColumn: ColumnAttribute): ForeignJoinColumnAttribute;
var
  LForeignCol: ForeignJoinColumnAttribute;
begin
  for LForeignCol in TEntityCache.Get(AClass).ForeignColumns do
  begin
    if SameText(ABaseTablePrimaryKeyColumn.ColumnName, LForeignCol.ReferencedColumnName) and
       SameText(ABaseTable.TableName, LForeignCol.ReferencedTableName) then
    begin
      Exit(LForeignCol);
    end;
  end;
  Result := nil;
end;

The calling procedure needs to be changed to also pass the BaseTableAttribute

procedure TSelectCommand.SetFromForeignColumn(ABaseTableClass, AForeignTableClass: TClass);
var
  LPrimaryKeyColumn: ColumnAttribute;
  LTable : TableAttribute;
  LWhereField: TSQLWhereField;
begin
  FForeignColumn := nil;
  LPrimaryKeyColumn := TEntityCache.Get(AForeignTableClass).PrimaryKeyColumn;
  LTable := TEntityCache.Get(AForeignTableClass).EntityTable;

  if not Assigned(LPrimaryKeyColumn) then
    Exit;

  FForeignColumn := TRttiExplorer.GetForeignKeyColumn(ABaseTableClass, LTable, LPrimaryKeyColumn);
  if not Assigned(FForeignColumn) then
    Exit;

  LWhereField := TSQLWhereField.Create(FForeignColumn.Name, FTable, nil,
    GetAndIncParameterName(FForeignColumn.Name));
  WhereFields.Add(LWhereField);
end;

More then one ForeignKeyColumn defined causes incorrect Key to be chosen in some cases

Our configuration

The Seeming Issue

Possible Solution

Comments (2)