2

I have the following objects:

Parent
   public virtual Guid Id { get; set; }
   public virtual DateTime TimeStamp { get; set; }
   public virtual IList<Child> Childs { get; set; }

Child
   public virtual Guid Id { get; set; }
   public virtual string Name { get; set; }

I use Fluent to Map One To Many as follows:

.Override<Parent>(obj =>obj.HasMany(x => x.Childs)
                                .Cascade.All()
                                .Not.Inverse()
                                .Not.KeyNullable()
                                .Not.KeyUpdate())

I need to get up to all Parent with Childs between dates order by TimeStamp.

I am trying to do it as follows (maxCapacity is int):

QueryOver<Parent>().Where(x => x.TimeStamp > from)
            .And(x => x.TimeStamp < to).OrderBy(x => x.TimeStamp).Desc
            .Left.JoinQueryOver<Child>(x => x.Childs)
            .TransformUsing(new DistinctRootEntityResultTransformer())
            .Take(maxCapacity).List();

The result is not what I expected since the Take(maxCapacity) is not on the parent result but on the total query result which includes parent and child.

How can I get the latest X already transformed Parent rows?

Thanks.

Haimon
  • 227
  • 1
  • 14

1 Answers1

4

The way I would go here is:

  • load the list of root entity (Parent) and
  • let NHibernate load their children lazily - in separated SQL query.

To avoid 1 + N issue, we can use smart mapping feature:

19.1.5. Using batch fetching

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...

So, the query should be like this:

session.QueryOver<Parent>()
    .Where(x => x.TimeStamp > from)
    .And(x => x.TimeStamp < to).OrderBy(x => x.TimeStamp).Desc
    //.Left.JoinQueryOver<Child>(x => x.Childs)
    // .TransformUsing(new DistinctRootEntityResultTransformer())
    .Skip(start) // paging
    .Take(maxCapacity)
    .List<Parent>();

And the parent mapping should be like:

<class name="Parent">
    ...
    <bag name="Childs" batch-size="3">
        ...
    </bag>
</class>

Please, check also these:

Community
  • 1
  • 1
Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • Thanks for the answer. My Qs: 1. Does NHibernate support batch fetching for SQL CE Database? 2. Does the property "batch-size="3" means that it will eager load 3 children each time? – Haimon Dec 29 '14 at 17:15
  • 1
    I used 3 because that value is in the Documenation. So SORRY - correct setting would be 25 or 50. *(for some reasone, NHibernate is using the half of that setting, i.e. 12 or 25 - do not ask me why ;)* and the answer to SQL CE - YES batch fetching is ready for any DB engine... just try it please. With value 25. This is setting which I have on 1) every class and on 2) every bag: **`batch-size="25"`** – Radim Köhler Dec 29 '14 at 17:17
  • According to your answer I should not use Join. So how do you load the children? By LoadList: NHibernateUtil.Initialize(Parent.Childs) ? – Haimon Dec 29 '14 at 20:17
  • In continue to eager loading: what is the best way to eager load: Fetch, NHibernateUtil.Initialize, Future...? – Haimon Dec 29 '14 at 20:51
  • Honestly I do not care how to load children. I simply keep session open as long as possible. And because my world is web, that mean - keep it open through all the web request. If the collection(s) is(are) needed, NHibernate will loaded it (them). If not - all will be a proxy, which won't be ever loaded === very efficient. So I really do not care. That's the benefit. If you really need it, then the most effective way would be: ask for a count of the first item (parent) children collection. That will cause the complete load (thanks to batch fetching). Hope this help... – Radim Köhler Dec 30 '14 at 04:29