We constantly use two Entities to represent the same table data. 1. A full entity that contains all the fields in the table for a given type. 2. A simple version of the same entity we call an associate that generally only contains a couple fields from the underlying table.

Both of the above will point at the same physical table. We do this in order to add the associate version as a Many to One relationship to other entities so that we do not include all the fields in a given relationship when we only want one or two fields.

Occasionally we find that the Full entity may reference the associate entity, or may reference another entity that references the associate entity.

This all works fine in the ORM until a Criteria Property is defined for the Base table that is based on the Full entity but where the associate entity may also be part of the SQL.

Note the following simplified definition of the Full entity and the associate entity, with the full entity referencing the associate.

TResourceAssociate = class;

  [Table('RESOURCE')]
  TResource = class
  private
    [Column('SID', [cpRequired, cpPrimaryKey], 19,0)]
    FSid: Integer;

    [Column('RESOURCE_NAME', [], 50)]
    FResourceName: string;

    [Column('ASSOC_RES_SID', [], 50)]
    FResourceAssociateSid: Integer;

    [ManyToOne(False, [ckCascadeAll], 'FResourceAssociateSid')]
    FResourceAssociate : TResourceAssociate;

    function GetResourceAssociateName: string;
    procedure SetResourceAssociateName(const Value: string);
  public
    constructor Create;
    destructor Destroy; override;
    property Sid : Integer read FSid write FSid;
    property ResourceName : string read FResourceName write FResourceName;
    property ResourceAssociateSid : Integer read FResourceAssociateSid write FResourceAssociateSid;
    property ResourceAssociateName : string read GetResourceAssociateName write SetResourceAssociateName;

  end;

  [Table('RESOURCE')]
  TResourceAssociate = class
  private
    [Column('SID', [cpRequired, cpPrimaryKey], 19,0)]
    FSid: Integer;

    [Column('RESOURCE_NAME', [], 50)]
    FResourceName: string;
  public
    property Sid : Integer read FSid write FSid;
    property ResourceName : string read FResourceName write FResourceName;
  end;

Now when Attempting to filter using a criteria by the resourceName we get an incorrect SQL Statement because instead of the full entity Alias being used the current code will pick the associate entity alias.

Expected Result: t0."RESOURCE_NAME" = :RESOURCE_RESOURCE_NAME1

Actual Result: t1."RESOURCE_NAME" = :RESOURCE_RESOURCE_NAME1

Sample Code block that fails to return the expected result

  Criteria := FSession.CreateCriteria<TResource>;
  {No Entity provided so this should generate where clause against Base table}
  ResourceName := TProperty.Create('RESOURCE_NAME');
  Criteria.Add(ResourceName.Eq('Test'));
  List := Criteria.ToList;

Notice that the following code (the Criteria.Add method) will set the Entity in the Criterion even though we created it without an Entity

function TCriteria<T>.Add(const criterion: ICriterion): ICriteria<T>;
begin
  if criterion.EntityClass = nil then
    criterion.EntityClass := fEntityClass;  <-- Setting the entity class here causes the next method to incorrectly return the wrong alias
  fCriterions.Add(criterion);
  Result := Self;
end;

Because of the above code when the ToSqlString of the Criterion is called the following code will not return the base table but look for the table among the associates.

function TSelectCommand.FindTable(entityClass: TClass): TSQLTable;
var
  tableName: string;
  currentTable: TSQLTable;
begin
  if entityClass = nil then
    Exit(fTable);  < -- If No Entity Is Set then return Base Table, otherwise look through Associate Tables below.

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

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

By Commenting out the setting of the entity in the TCriteria<T>.Add method seems to resolve the issue.

function TCriteria<T>.Add(const criterion: ICriterion): ICriteria<T>;
begin
//  if criterion.EntityClass = nil then
//    criterion.EntityClass := fEntityClass;  <-- Setting the entity class here causes the next method to incorrectly return the wrong alias
  fCriterions.Add(criterion);
  Result := Self;
end;

Not sure what side effects this might have.

Attached is an update to the Unit test files, TestCoreCriteria and TestEntities that demonstrates the issue. Please see the Test Called TestSelfReferencedAssociate

When two resources have the same table name and one is an associate of another, the SQL Where is not correctly generated

Attached is an update to the Unit test files, TestCoreCriteria and TestEntities that demonstrates the issue. Please see the Test Called TestSelfReferencedAssociate

Comments (6)