NHibernate get first x distinct results does not get me x results?

Question

I have an ICriteria query like so:

var contentCriteria = DetachedCriteria.For<InvoiceItem>();
var countCriteria = DetachedCriteria.For<InvoiceItem>();

if (model.CurrentPage <= 0) model.CurrentPage = 1;

if (model.OnlyShowErrors)
{
    contentCriteria.Add(Restrictions.Not(Restrictions.Eq("TroubleClass", TroubleClasses.Success)));
    countCriteria.Add(Restrictions.Not(Restrictions.Eq("TroubleClass", TroubleClasses.Success)));
}

if (!string.IsNullOrEmpty(model.BatchId))
{
    contentCriteria.Add(Restrictions.Eq("BatchId", model.BatchId));
    countCriteria.Add(Restrictions.Eq("BatchId", model.BatchId));
}

if (model.DocumentStartDate != null)
{
    contentCriteria.Add(Restrictions.Ge("DocumentDate", model.DocumentStartDate));
    countCriteria.Add(Restrictions.Ge("DocumentDate", model.DocumentStartDate));
}

if (model.DocumentEndDate != null)
{
    contentCriteria.Add(Restrictions.Le("DocumentDate", model.DocumentEndDate));
    countCriteria.Add(Restrictions.Le("DocumentDate", model.DocumentEndDate));
}

if (!string.IsNullOrEmpty(model.VendorId))
{
    contentCriteria.Add(Restrictions.Eq("VendorId", model.VendorId));
    countCriteria.Add(Restrictions.Eq("VendorId", model.VendorId));
}


using (var session = GetSession())
{

    var countC = countCriteria.GetExecutableCriteria(session)
        .SetProjection(Projections.CountDistinct("RecordId"));

    var contentC = contentCriteria
        .AddOrder(Order.Desc("PersistedTimeStamp"))
        .GetExecutableCriteria(session)
        .SetResultTransformer(Transformers.DistinctRootEntity)
        .SetFirstResult((model.CurrentPage * model.ItemsPerPage) - model.ItemsPerPage)
        .SetMaxResults(model.ItemsPerPage);

    var mq = session.CreateMultiCriteria()
        .Add("total", countC)
        .Add<InvoiceItem>("paged", contentC);

    model.Invoices = ((IEnumerable<InvoiceItem>)mq.GetResult("paged"));
    model.Invoices = model.Invoices
        .OrderBy(x => x.PersistedTimeStamp);

    model.TotalItems = (int)(mq.GetResult("total") as System.Collections.ArrayList)[0];
}
return model;

This returns results, but where I would expect the results to be in groups of model.ItemsPerPage, it rarely is. I think that the .SetResultTransformer(Transformers.DistinctRootEntity) transform is being run after the .SetMaxResults(model.ItemsPerPage) limit, and I don't know why or how to fix it. Can someone please enlighten me?

Answer 1

So this is directly related to what was written in this blog post. Additionally, I had the platform-specific complication of PostgreSQL not allowing a DISTINCT ordered set ordered by something not in the SELECT list. Ultimately, I had to make two calls to the database, like so:

using (var session = GetSession())
{
    //I honestly hope I never have to reverse engineer this mess.  Pagination in NHibernate
    //when ordering by an additional column is a nightmare.
    var countC = countCriteria.GetExecutableCriteria(session)
        .SetProjection(Projections.CountDistinct("RecordId"));

    var contentOrdered = contentCriteria
        .SetProjection(Projections.Distinct(
            Projections.ProjectionList()
                .Add(Projections.Id())
                .Add(Projections.Property("PersistedTimeStamp"))
                ))
        .AddOrder(Order.Desc("PersistedTimeStamp"))
        .SetFirstResult((model.CurrentPage * model.ItemsPerPage) - model.ItemsPerPage)
        .SetMaxResults(model.ItemsPerPage);

    var contentIds = contentOrdered.GetExecutableCriteria(session)
        .List().OfType<IEnumerable<object>>()
        .Select(s => (Guid)s.First())
        .ToList();

    var contentC = DetachedCriteria.For<InvoiceItem>()
        .Add(Restrictions.In("RecordId", contentIds))
        .SetResultTransformer(Transformers.DistinctRootEntity);

    var mq = session.CreateMultiCriteria()
        .Add("total", countC)
        .Add("paged", contentC);

    model.Invoices = (mq.GetResult("paged") as System.Collections.ArrayList)
        .OfType<InvoiceItem>()
        .OrderBy(x => x.PersistedTimeStamp);

    model.TotalItems = (int)(mq.GetResult("total") as System.Collections.ArrayList)[0];
}
return model;

This is not pretty, but it worked; I think the folks over at NHibernate need to work on this and make it a tad bit easier.