Child collection and one to one

Question

My entity "TimeRecord" has a collection of "WayPoints" and two one-to-one properties "Location" and "WayData".

Each property can be null.

I need to export all Time Records with initialized properties for a specific User.

I actually had a working solution but then I started to use NHibernateProiler and first I noticed that this code results in a ridiculous number of query’s against db.

var query = (from timeRecord in Session.Query<TimeRecord>()
                .Where(tr => tr.User.Id == userid)
            select timeRecord);

Then I changed my code to:

var query = (from post in Session.Query<TimeRecord>()
                .Fetch(x => x.Location)
                .Fetch(x => x.WayData)
                .FetchMany(x => x.WayPoints)
                .Where(tr => tr.User.Id == userid)
            select post);

Which lead me to the Cartesian product problem.

Right now I’m experimenting with this piece of code:

 var sql1 = "from TimeRecord b left outer join fetch b.Location where b.User.Id=:User_id";
 var sql2 = "from TimeRecord b left outer join fetch b.WayData where b.User.Id=:User_id";
 var sql3 = "from TimeRecord b left inner join fetch b.WayPoints where b.User.Id=:User_id";


var result = Session.CreateMultiQuery()
             .Add(Session.CreateQuery(sql1))
             .Add(Session.CreateQuery(sql2))
             .Add(Session.CreateQuery(sql3))
             .SetParameter("User_id", userid)
             .List();

But I can’t say if this is the correct approach or if this is even possible with nHibernate. Can someone help me with that?

Answer 1

The 1+N issue is a usual with the entity/collection mapping and ORM tools. But NHibernate has a very nice solution how to manage it properly. It is called:

• 19.1.5. Using batch fetching

This setting will allow:

• To continue querying the root entity (TimeRecord in our case)
• No fetching inside of the query (Session.Query<TimeRecord>()). That means we do have support for correct paging. (Take(), Skip() will be executed over the flat root table)
• All the collections will be loaded with their own SELECT statements (could seem as disadvantage but se below)
• There will be much more less SELECTs then 1+N. All of them will be batched. E.g. by 25 records
• all the native mapping (lazy loading of collections) will still be in place...

The xml mapping example:

-- class level
<class name="Location" batch-size="25 ...

-- collection level
<batch name="Locations" batch-size="25" ...

I would suggest to apply that over all your collections/classes. With Fluent mapping it could be done also with conventions

The fluent mapping:

// class
public LocationMap()
{
    Id(x => x....
    ...
    BatchSize(25);

// collection
HasMany(x => x.Locations)
  ...
  .BatchSize(25);