- changed title to When two resources have the same table name and one is an associate of another, the SQL Where is not correctly generated
- edited description
When two resources have the same table name and one is an associate of another, the SQL Where is not correctly generated
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
Comments (6)
-
reporter -
repo owner - changed milestone to Future version
- removed responsible
- changed version to 1.2 (develop)
-
repo owner - changed status to open
-
repo owner - changed status to resolved
fixed issue
#219→ <<cset e191df1fef83>>
-
repo owner - changed version to 1.2
-
repo owner - removed milestone
Removing milestone: Future version (automated comment)
- Log in to comment