c#nhibernate pagination nhibernate-criteria

Fetching Paginated Entity with Collection

Consider two entities Person which has a one-to-many collection Vehicles

public class Person
{
    public IList<Vehicle> Vehicles { get; set;}
}

public class Vehicle 
{
   public string Name { get; set;}

   public Person Owner { get; set; }
}

I display a grid of Persons having vehicle and show the name of the first vehicle in the grid. The grid is paginated. I use the following criteria to fetch the data

I have a criteria for loading data for a grid view as

var criteria = DetachedCriteria.For<Person>()
    .CreateAlias("Vehicles","vehicle", JoinType.InnerJoin)
    .SetResultTransformer(new DistinctRootEntityResultTransformer())
    .SetMaxResults(pageSize)
    .SetFirstResult((page - 1) * pageSize)

criteria.Add(Restrictions.Eq("vehicle.Name", "super"));

where page and pageSize are calculated bits.

The problem is since max results and first results are calculated in the database and distinct root is done outside, the number of rows do not match.

Is there a way to resolve this issue ?

Solution

This kind of queries should always use subquery instead of any type of JOIN. That also means, that the colleciton item has reference to parent (as in our case).

So, here we create the inner select for Vehicle:

var vehicles = DetachedCriteria.For<Vehicle>();
// add any amount or kind of WHERE parts
vehicles.Add(Restrictions.Eq("vehicle.Name", "super"))
// and essential SELECT Person ID
vehicles.SetProjection( Projections.Property("Owner.ID"));

Now, we can adjust the above query, to work only on a root/parent level:

var criteria = DetachedCriteria.For<Person>()

// instead of this
// .CreateAlias("Vehicles","vehicle", JoinType.InnerJoin)

// we will use subquery
.Add(Subqueries.PropertyIn("ID", vehicles));

// Wrong to use this approach at all
//.SetResultTransformer(new DistinctRootEntityResultTransformer())

.SetMaxResults(pageSize)
.SetFirstResult((page - 1) * pageSize)

That will create SELECT like this:

SELECT p....
FROM Person AS p
WHERE p.ID IN (
  SELECT v.OwnerId
  FROM Vehcile AS v
    WHERE v.Name = 'super' ...
)