Few days ago I’ve faced with duplication of data in XML we send to vendors and it leaded my to basic understanding how fetching works within NHibernate.

Database

So to make things clear we will start with ever simplest database layout and few datarows as on picture below:

I’m using following code to test our activities.

        private void FetchCustomerUseOrders(ISession session, FetchMode fetchMode)
        {
            var criteria = session.CreateCriteria(typeof(Customer))
                .SetFetchMode(“Orders”, fetchMode)
                .Add(Restrictions.Eq(“CustomerId”, 10));
            var customers = criteria.List<Customer>();
            Console.WriteLine(customers[0].Orders.Count);
        }

It fetches Customer with passed in FetchMode and then uses Orders to examine how lazy loading works.

Then I’m calling method for each of the FetchMode in separate sessions like below:

            using (var session = sessionFactory.OpenSession())
            {
                FetchCustomerUseOrders(session, FetchMode.Default);
            }
            using (var session = sessionFactory.OpenSession())
            {
                FetchCustomerUseOrders(session, FetchMode.Eager);
            }
            using (var session = sessionFactory.OpenSession())
            {
                FetchCustomerUseOrders(session, FetchMode.Join);
            }
            using (var session = sessionFactory.OpenSession())
            {
                FetchCustomerUseOrders(session, FetchMode.Lazy);
            }
            using (var session = sessionFactory.OpenSession())
            {
                FetchCustomerUseOrders(session, FetchMode.Select);
            }

Mapping of the Customer

            HasMany(x => x.Orders)
                .Access.AsCamelCaseField(Prefix.Underscore)
                .WithKeyColumn(“Customer”)
                .FetchType.Join()
                .LazyLoad();

Generated SQL-s

NHibernate can generate two types of fetching: either join or either separate select statements. So our code generated three join statements for Default, Eager, Join. Also two calls to database with two selects for the Lazy and Select. So all FetchMode except of Default are overriding our Customer mapping.

Join SQL

There is one thing you should remember about such kind of fetching – you will get duplication of the entities in your collections. You should apply joining when you are going to have some specific restrictions in your query.

Duplication of entities:

On the picture above you see two customers, which are the same references and represent same customer with same id = 10. To eliminate this issue you should apply one of the fixes: use distinct, use subquery or filter entities in client code. This article should make this more clear.

My recommendation is never use “.FetchType.Join()” in your One-To-Many relations as I did that. Better let NHibernate do it by default. Nhibernate applies Join to Many-To-One relations and Select to One-To-Many.

In my case better use following:

            HasMany(x => x.Orders)
                .Access.AsCamelCaseField(Prefix.Underscore)
                .WithKeyColumn(“Customer”);

The less you write code – the less mistakes you do. :)

Two SELECT-s

This type of query is generated if you have Select() fetch type. If you have LazyLoad in your mapping second select will be executed only on accesing of the Orders property of Customer. In other case (Not.LazyLoad) it will be executed immediately on fetching Customer.

Hope this brings some light. At least for me it does.

If you haven't subsribed yet, you can subsribe below: