Named query doesn't work with relationship

[Tostis]

I have this exception

First chance exception at $75FAB4F2. Exception class ESQLiteException with message 'Field not found in dataset: t1$ID'. Process DUnitXTest.exe (29692)

with the following code due to detail table referenced from master table:

‌

  [Table('DETAIL')]
  TDetailEntity = class
  protected
    [Column('ID', [cpPrimaryKey])]
    fId: string;
  end;

  [Table('MASTER')]
  TMasterEntity = class
  protected
    [Column('ID', [cpPrimaryKey])]
    fId: string;
    [Column('DESCRIPTION')]
    fDescription: string;
    [Column('DETAIL1_ID')]
    fDetail1Id: string;
    [ManyToOne(True, [ckCascadeAll], 'fDetail1Id')]
    fDetail1: TDetailEntity;
  end;



type
  IMasterRepository = interface(IPagedRepository<TMasterEntity, string>)
    ['{2186ADF6-D3C6-4917-A245-CC3619615D29}']

    [Query('SELECT * FROM MASTER WHERE DESCRIPTION = :0')]
    function FindByDescription(const AName: string): TMasterEntity;

  end;
//---
var
  FMasterRepository: IMasterRepository;
//----
FMasterRepository := TProxyRepository<TMasterEntity, string>.Create(FSession, TypeInfo(IMasterRepository)) as IMasterRepository;
//---
LCustomer := FMasterRepository.FindByDescription('Foo');

I’m on origin/master commit af4ca7c751871f0c107a67741f57bb4ef0d1a5e1.

[Randy Tapson]

I don’t think this is a bug. By supplying a custom query you are overriding the frameworks built in SQL generation. So you need to supply a query that has all the data that the framework needs.

If you call FMasterRepository.FindOne() the SQL that is generated looks like this (“ removed),

SELECT t0.ID, t0.DESCRIPTION, t0.DETAIL1_ID, t1.ID AS t1$ID FROM MASTER t0 LEFT OUTER JOIN DETAIL t1 ON t1.ID = t0.DETAIL1_ID WHERE t0.ID = :0;

To make your custom query work you should do something like this,

SELECT * FROM MASTER t0 LEFT OUTER JOIN DETAIL t1 ON t1.ID = t0.DETAIL1_ID WHERE t0.DESCRIPTION = :0;

‌

[Stefan Glienke]

Relying on the internal representation of the SQL might not be the best idea (I know there are tests and the Query attribute offers that possibility).

It would be better to actually use the criteria API for that.