Specify Association Depth in Nhibernate Criteria

Question

Okay, lets say I have A Many-To-Many, Student/Class objects. With the following mapping

      HasManyToMany(m => m.Classes)
                .Table("StudentClasses")
                .LazyLoad()
                .Cascade.None();

and query:

       return _session.CreateCriteria<Student>()
                               .Add(Restrictions.Eq("Id", studentId))
                               .SetCacheable(false)
                               .UniqueResult<Student>();

My Tables

    ID | Student           ID | Class              StudentId | ClassId
    ============           ===========             ===================
    1  | John              1 | Algebra             1         | 1
    2  | Sue               2 | Biology             1         | 2
    3  | Frank             3 | Speech              2         | 2
    4  | Jim               4 | Athletics           2         | 5
    5  | Frankenstein      5 | History             5         | 5

What I want is John, with Algebra and Biology This comes, But Biology comes with John and Sue, she in turn gets me Bio and History, which populates sue and Frankenstein. You don't want to get Frankenstein (or anything beyond biology). Also at any point you can start cycling back around in circles.

How do I specify not to populate that deeply? SetMaxRows obviously isn't want I'm looking for, as It cuts down on the total number of rows, I only want to hydrate the first level. I'm particularly confused because I thought LazyLoading would force me to specify exactly what I did want to hydrate

How do you know John and Sue are coming back too? – Daniel Schilling Aug 30 '13 at 00:39 — Daniel Schilling, Aug 30 '13 at 00:39

score 1 · Accepted Answer · answered Aug 30 '13 at 01:09

Lazy loading allows you to not have to specify what you want to hydrate. The collections will be populated "lazily" at the time you access them. Your above query will only fetch a single row: John. When you access john.Classes, NHibernate will execute another query behind the scenes to fetch John's classes. Then when you access the biology.Students property, NHibernate will execute another query to populate that collection... and so on.

You definitely do want lazy-loading on this relationship (which is the default, by the way). If you turned lazy loading off, then this would do exactly what you're saying you want to avoid - NHibernate would know that it's not allowed to lazily load these collections, so it would start working to load them as soon as you loaded a student, which could potentially end up loading all of the data in these three tables, and in a very inefficient manner with lots of round-trips to the database.

If you know that all you want is "John, with Algebra and Biology", then you should add these lines to your query:

    .SetFetchMode("Classes", FetchMode.Eager)
    .SetResultTransformer(Transformers.DistinctRootEntity)

The SetFetchMode tells NHibernate to use left outer joins to pre-populate the student.Classes collection. The DistintRootEntity tells NHibernate to not return the duplicate students that will be generated by the left outer join.

Specify Association Depth in Nhibernate Criteria

1 Answers1