NHibernate Nested List Query

Question

I have the following models

public class Customer 
{
    public virtual long Id { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<Invoice> Invoices { get; set; }
    public virtual XDocument AlotOfData { get; set; }
}

public class Invoice
{
    public virtual long CustomerId { get; set; }
    public virtual long Id { get; set; }
    public virtual IList<LineItem> LineItems { get; set; }
    public virtual XDocument AlotOfData { get; set; }
}

public class LineItem
{
    public virtual long InvoiceId { get; set; }
    public virtual long Id { get; set; }
    public virtual double Amount { get; set; }
    public virtual XDocument AlotOfData { get; set; }
}

If i do just a normal nhibernate query to get a customer it would look like below.

long customerId = 1;
Customer customer = Session.QueryOver<Customer>().Where(x => x.Id == customerId).SingleOrDefault();

if i have lazy="false" in my mapping files for all child collections it will result in the following Sql Queries being executed

1 query to get the Customer (only selecting columns from Customer table)

1 query to get all the Invoices for that customer (only selecting columns from Invoice table)

n queries to the LineItems table (one query for each Invoice only selecting columns from LineItems table)

What i would like to know is how do i keep the same queries that are currently being executed but instead of nhibernate making n seperate queries to the LineItems table it will only make 1. Is there a way that this can be done either by an in clause or a join to the invoice table but not selecting the columns from the invoice table.

Thanks Jeremy

Have a look at the following http://stackoverflow.com/questions/2917490/nhibernate-join-and-projection-properties — MHOOS, Sep 12 '14 at 15:50

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

Yes NHibernate supports this functionality, it is called:

19.1.5. Using batch fetching

Let me cite from docs:

NHibernate can make efficient use of batch fetching, that is, NHibernate can load several uninitialized proxies if one proxy is accessed (or collections. Batch fetching is an optimization of the lazy select fetching strategy. There are two ways you can tune batch fetching: on the class and the collection level.

Batch fetching for classes/entities is easier to understand. Imagine you have the following situation at runtime: You have 25 Cat instances loaded in an ISession, each Cat has a reference to its Owner, a Person. The Person class is mapped with a proxy, lazy="true". If you now iterate through all cats and call cat.Owner on each, NHibernate will by default execute 25 SELECT statements, to retrieve the proxied owners. You can tune this behavior by specifying a batch-size in the mapping of Person:

<class name="Person" batch-size="10">...</class>

NHibernate will now execute only three queries, the pattern is 10, 10, 5. ...

That setting batch-size="25" could be used also for collection mapping:

You may also enable batch fetching of collections. For example, if each Person has a lazy collection of Cats, and 10 persons are currently loaded in the ISesssion, iterating through all persons will generate 10 SELECTs, one for every call to person.Cats. If you enable batch fetching for the Cats collection in the mapping of Person, NHibernate can pre-fetch collections:

<class name="Person">
    <set name="Cats" batch-size="3">
        ...
    </set>
</class>

I would strongly suggest to use it. I do have that mapping on every class and collection

See also:

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 12 '14 at 15:51

Radim Köhler

122,561
47
239
335

Batch only groups gets when you are accessing a child list from the parent, What i want to do is get all child lists from child lists from parent. Thanks though – Jeremy Barnet Sep 12 '14 at 15:58
In fact, in your case, your 1 + 1 + n queries, will be replaced with 1 + 1 + 1 * (count/25). It is significant improvement. And in fact, this is what you can get with NHibernate – Radim Köhler Sep 12 '14 at 16:00
So i would just put batch-Size="25" on my bag of line items? – Jeremy Barnet Sep 12 '14 at 16:06
Yes, sir! That's it. Surprisingly simple, unbelievably efficient. But the mapping on collection is not enough - do the same on your ``... The more you will play, the more you will see what it can do for you... *(mapping on collection is enough, but if there will be some `many-to-one` on the `LinetItem` - it will not issue n queries...)* – Radim Köhler Sep 12 '14 at 16:07
I am trying this out right now i will let you know how it worked out. – Jeremy Barnet Sep 12 '14 at 16:12
Just tried this out and i am seeing that even though i have set my batch size to 25 the in clause only has 11 items? do you know if they have a max of 11 in nhibernate? – Jeremy Barnet Sep 12 '14 at 16:18
;) I do not have any explanation! ;) but yes... weird. Great you are testing that ;) But other words, if you like to get 25 set it to 50. Important is not the *count* but the functionality... really good job man ;) – Radim Köhler Sep 12 '14 at 16:23
How right you are it seems to half what ever number i put in for the batch size. Thanks for you help. – Jeremy Barnet Sep 12 '14 at 16:33
Great to see that. Enjoy NHibernate ;) – Radim Köhler Sep 12 '14 at 16:34

NHibernate Nested List Query

1 Answers1

19.1.5. Using batch fetching