CODEWITHSUNDEEP
sql.netnhibernate
Jeremy Barnet
Jeremy Barnet

Reputation: 53

NHibernate Nested List Query

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

Upvotes: 2

Views: 626

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123901

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:

Upvotes: 1

Related Questions