CODEWITHSUNDEEP
sqlsql-serverpaginationnhibernatenhibernate-criteria
dythim
dythim

Reputation: 930

NHibernate SetFirstResult causes duplicate results

We are having trouble with the way NHibernate (version 4.0.0.4000 AND 4.0.4.4000 tested) returns duplicate results. In the sample below, I get 566 results (the correct number of results), but only 549 are unique, meaning there are 17 duplicates. 

#region Get Record IDs
public IList<string> GetRecordIds(string user, string agency, DateTime utcFrom, DateTime utcTo, SearchDateRangeType dateRangeType, IEnumerable<string> status, IEnumerable<string> billingStatus, IEnumerable<string> qaStatus, IEnumerable<string> transmissionStatus, IEnumerable<string> scheduledTransmissions, int pageSize = -1, int pageNumber = -1)
{
    using (ISession session = NHibernateHelper.OpenSession())
    {
        ICriteria crit = session.CreateCriteria<Metadata>();

        var dateDisjunction = Restrictions.Disjunction();

        dateDisjunction.Add(Restrictions.Between("IncidentDate", utcFrom, utcTo));

        crit.Add(dateDisjunction);

        if (string.IsNullOrEmpty(agency) == false)
        {
            crit.CreateAlias("Ownership._entities.AsIList", "entities");
            crit.Add(Restrictions.Eq("entities._entityName._value", agency));
            crit.Add(Restrictions.Eq("entities._isDeleted._value", false) || Restrictions.IsNull("entities._isDeleted._value"));
        }

        crit.AddOrder(Order.Asc(Projections.Property("RecordId")));

        crit.SetProjection(Projections.Property("RecordId"));

        if (pageSize > 0 && pageNumber > 0)
        {
            crit.SetFirstResult(pageSize * (pageNumber - 1)).SetMaxResults(pageSize);
        }

        var ret = crit.List<string>();

        return ret;
    }
}
#endregion

SQL Sample 1 is the generated first iteration code from NHibernate. Subsequent pages (second page onward) use ROW_NUMBER() OVER. SQL Sample 2 is a manually-created first page, which uses ROW_NUMBER() OVER as if it was a subsequent page. NHibernate has apparently "optimized" away the ROW_NUMBER() OVER for the first page and that seems(?) to be the cause of our issues.

SQL Sample 1: Generated by NHibernate. Causes duplicates.

SELECT
    TOP (100) this_.RecordId as y0_ 
FROM
    PcrMetadata this_ 
inner join
    PcrEntities entities1_ 
        on this_.Id=entities1_.ListKey 
WHERE
    (
        this_.IncidentDate between '0001-01-01 00:00:00.0000000' and '9999-01-01 00:00:00.0000000'
    ) 
    and entities1_.Name = 'ClientIDNumber' 
    and (
        entities1_.Entities_IsDeleted = 0 
        or entities1_.Entities_IsDeleted is null
    )

SQL Sample 2: Manually created based on NHibernate second page on. Does not cause duplicates.

SELECT
    TOP (100) this_.RecordId as y0_  
FROM
    (SELECT
        this_.Record as y0_,
        ROW_NUMBER() OVER(
    ORDER BY
        CURRENT_TIMESTAMP) as __hibernate_sort_row 
    FROM
        PcrMetadata this_ 
    inner join
        PcrEntities entities1_ 
            on this_.Id=entities1_.ListKey 
    WHERE
        (
            this_.IncidentDate between '0001-01-01 00:00:00.0000000' and '9999-01-01 00:00:00.0000000'
        ) 
        and entities1_.Name = 'ClientIDNumber' 
        and (
            entities1_.Entities_IsDeleted = 0 
            or entities1_.Entities_IsDeleted is null
        )) as query 
WHERE
    query.__hibernate_sort_row > 0 -- CHANGE THIS NUMBER

Am I doing something wrong? Or there anything I can do to force NHibernate to use ROW_NUMBER? Thanks in advance for any help!

Upvotes: 0

Views: 295

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123891

We cannot JOIN collections and apply paging. Because we are getting cartesian product, which is paged (experience described above).

The solution I would suggest, is to (my way NEVER) join collection. To get the similar results, we should:

  • use subquery to apply WHERE
  • use fetch batching to later recieve all collection items without 1 + N issue

There is detailed answer about this issue.

see also:

There is more about making result distinct, but this could not help here:

Upvotes: 1

Related Questions