I'd need to load a list of very large objects with so many children and children of children. what's the best approach to take?
I'm using Oracle 11g database and I've written the below method but it results in cartesian product (duplicated results):
public IList<ARNomination> GetByEventId(long eventId)
{
var session = this._sessionFactory.Session;
var nominationQuery = session.Query<ARNomination>().Where(n => n.Event.Id == eventId);
using (var trans = session.Transaction)
{
trans.Begin();
// this will load the Contacts in one statement
nominationQuery
.FetchMany(n => n.Contacts)
.ToFuture();
// this will load the CustomAttributes in one statement
nominationQuery
.FetchMany(n => n.CustomAttributes)
.ToFuture();
// this will load the nominations but joins those two tables in one statement which results in cartesian product
nominationQuery
.FetchMany(n => n.CustomAttributes)
.FetchMany(n => n.Contacts)
.ToFuture();
trans.Commit();
}
return nominationQuery.ToList();
}
Fetching Collections is a difficult operation. It has many side effects (as you realized, when there are fetched more collections). But even with fetching one collection, we are loading many duplicated rows.
In general, for collections loading, I would suggest to use the batch processing. This will execute more SQL queries... but not so much, and what is more important, you can do paging on the root list ARNomination
.
See: 19.1.5. Using batch fetching you can find more details.
You have to mark your collections and/or entities with an attribute batch-szie="25"
.
xml:
<bag name="Contacts" ... batch-size="25">
...
fluent:
HasMany(x => x.Contacts)
...
.BatchSize(25)
Please, check few arguments here:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With