How to load optimized Master-Detail

Hi, some time ago, i asked this:
How to load a detail table, on a master-detail (Invoice-Items), with just one query? Now i have to do a for cicle, like Invoice.ItemsList.count, this will execute one query for every item of the invoice, if the invoice have Hundreds of items we have hundreds of querys. I think this is a very intresting feature for all..

That will not execute one query for every item of the invoice, but one query for each invoice.
It doesn't matter if the invoice has one or hundreds of items, all the lists are retrieved once.

Now, if you mean that having many invoices will fire many queries to retrieve the item list for every invoice, then that's correct. The only workaround would be to query all the items at once (Find<TInvoiceItem>) and then manually group the items and invoices client-side.

Hi, Wagner
I'm using Data Modeler to generate the schema, by default Many Valid Association are configured as Lazy. What i'm asking is how to eager load when needed without a for cicle? As in associations, by default, are configured as Lazy, when nedeed i can Eager Load with CreateAlias. thanks

Hi, this is the case
TDocSct = class
[Column('Id', [TColumnProp.Required])]
FId: Int64;

[Column('Description', [TColumnProp.Required], 30)]
FDescription: string;

[ManyValuedAssociation([TAssociationProp.Lazy, TAssociationProp.Required], [TCascadeType.SaveUpdate, TCascadeType.Merge], 'FDocSctId')]
FDocScStList: Proxy<TList<TDocScSt>>;

function GetDocScStList: TList<TDocScSt>;

constructor Create;
destructor Destroy; override;
property Id: Int64 read FId write FId;
property Description: string read FDescription write FDescription;
property DocScStList: TList read GetDocScStList;

TDocScSt = class
[Column('Id', [TColumnProp.Required])]
FId: Int64;

[Column('Enabled', [TColumnProp.Required])]
FEnabled: Integer;

[Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
[JoinColumn('DocSettId', [TColumnProp.Required], 'Id')]
FDocSettId: Proxy<TDocSett>;

function GetDocSettId: TDocSett;
procedure SetDocSettId(const Value: TDocSett);

property Id: Int64 read FId write FId;
property Enabled: Integer read FEnabled write FEnabled;
property DocSettId: TDocSett read GetDocSettId write SetDocSettId;

TDocSett = class
[Column('Id', [TColumnProp.Required])]
FId: Int64;

[Column('TypeCode', [TColumnProp.Required], 10)]
FTypeCode: string;

property Id: Int64 read FId write FId;
property TypeCode: string read FTypeCode write FTypeCode;

Result := Manager.Find.Add(TLinq.Eq(Dic.DocSct.Id.PropName, Id))
if Result <> nil then
//How to Eager Load, Result.DocScStList[i].DocSettId.Id, without the "for"
for i := 0 to Result.DocScStList.Count-1 do
Result.DocScStList[i].Id := Result.DocScStList[i].Id;
Result.DocScStList[i].DocSettId.Id := Result.DocScStList[i].DocSettId.Id;// MULTIPLE QUERYS

Have you tried

   Result := Manager.Find<TDocSct>
     .CreateAlias('DocScStList', 'd', TFetchMode.Eager)
     .CreateAlias('d.DocSettId', 'set', TFetchMode.Eager)
     .Add(TLinq.Eq(Dic.DocSct.Id.PropName, Id))

Hi, Wagner
I have try without success. "DocSct" master table, "DocScSt" detail table with "DocSett" association(Lookup) table, just to make it clear.

Try mngr.find<tdocsct>.FetchEager('DocScStList.DocSettId').etc.etc.etc

Hi Ferrari, thanks
but what's the difference between what you said and Wagner?
thanks again

Have you really tested any of these options?
Wagner gave you an example of FetchEager, but with CreateAlias parameters instead. It should have raised compilation errors.
The example I sent you, with your entity names, use the method .FetchEager with the full path you need, and that's enough to force it to generate just one joined query.
You would just use it as

  .CreateAlias('DocScStList', 'd')
  .CreateAlias('d.DocSettId', 'set')
  .Add(TLinq.Eq('', Id)).UniqueResult;

or so.

Hi, thanks Ferrari
Yes, many times, simply does not work, the list "DocSct.DocScStList" still empty, just when do "DocSct.DocScStList.count" an extra query is executed (but this is not the issue), then i need to do eagerload DocSct.DocScStList.DocSett one by one, with the for cicle.

Result := Manager.Find<TDocSct>.Add(TLinq.Eq(Dic.DocSct.Id.PropName, Id)) .UniqueResult;

Thanks again

Hi, again, copy json to "" and easily understand the structure.

{"$type":"DB.Schema.TDocSct","$id":1,"FDescription":"Some Description","FDocScStList":[{"$type":"DB.Schema.TDocScSt","$id":5,"FId":-1,"FEnabled":1,"FDocSettId":{"$type":"DB.Schema.TDocSett","$id":6,"FTypeCode":"FT","FId":-1},"FDocSctId":{"$proxy":"single","key":-1,"class":"TDocScSt","member":"FDocSctId"}},{"$type":"DB.Schema.TDocScSt","$id":7,"FId":-2,"FEnabled":1,"FDocSettId":{"$type":"DB.Schema.TDocSett","$id":8,"FTypeCode":"NC","FId":-3},"FDocSctId":{"$proxy":"single","key":-1,"class":"TDocScSt","member":"FDocSctId"}}]}

Thanks, thanks, thanks

That's unfortunately how it is. The many-valued associations are always retrieved separately, because they are a different SELECT statement. And at that point, the fetch eager information about sub associations is not available.

The workaround is the one I mentioned here: How to load optimized Master-Detail - #2 by wlandgraf

hi Wagner
As a suggestion if somehow aurelius execute one query for the master table and one query for the detail table with eager load to the association, we just don't need the for cicle. But that's what it's
Thanks for your patience :)

1 Like

I consider it important to reopen this post, because latest version of aurelius has a fix, but i can make this work

 "Fixed: TFetchMode.Eager now forces eager loading of many-valued associations as well"

Consider the above sample, Master-Detail-Association. I need to Load the Association without a for cicle,

fCriteria.CreateAlias('DocScStList','AliasDocScStList', TFetchMode.Eager);
fCriteria.CreateAlias('AliasDocScStList.DocSettId','AliasDocSettId', TFetchMode.Eager);//This does not work

Please, check JSON to easily understand the schema


The fix refers to loading the many-valued association at the time they are loaded from the database. But it sill will execute one SQL statement for each list.

Ok, Thanks