NHibernate RowCountInt64 returns wrong count with transformed query

Question

I am experiencing a weird error while executing an NHibernate Query. I have a query of type IQueryOver<ExternalUser, ExternalUser> which is filtered and transformed (using DistinctRootEntity, which i am guessing is causing the problem). I create the query like this:

List<Guid> companyList = /* some guids */
Company company = null;
var query = _session.QueryOver<ExternalUser>()
                    .JoinAlias(x => x.Companies, () => company)
                    .Where(() => company.Id.IsIn(companyList))
                    .TransformUsing(Transformers.DistinctRootEntity);

When i execute query.RowCountInt64() i get 4.

When i execute query.List() i get 3 items back.

I have also tried query.ToRowCountInt64Query().List<long>().Sum() which also gives me 4.

I also tried query.ToRowCountInt64Query().FutureValue<long>().Valuewhich also gives me 4.

Any ideas how to solve this?

carl · Answer 1 · 2014-03-11T14:36:04.810

I found a solution that works:

totalCount = query.Clone()
                  .Select(Projections.CountDistinct<User>(x => x.Id))
                  .SingleOrDefault<int>();

...but my solution restricts me to Int32 which i'm not happy about. It will probably suffice in the implementation where i'm using it, but there might be cases elsewhere where long is needed, so any other suggestions are appreciated.

EDIT: The only thing i didn't like with the solution above was that it returned an int, so with some digging, i managed to fix that with another projection class:

public class Int64CountProjection : CountProjection
{
    protected internal Int64CountProjection(string prop) : base(prop) {}
    protected internal Int64CountProjection(IProjection projection) : base(projection) {}

    public override IType[] GetTypes(ICriteria criteria, ICriteriaQuery criteriaQuery)
    {
        return new IType[] { NHibernateUtil.Int64 };
    }

    public static CountProjection Distinct<T>(Expression<Func<T, object>> expression)
    {
        return new Int64CountProjection(ExpressionProcessor.FindMemberExpression(expression.Body)).SetDistinct();
    }
}

...and with this class i can get my count like this (which could be refined further with an extension method, but this is enough for me):

totalCount = query.Clone()
                  .Select(Int64CountProjection.Distinct<User>(x => x.Id))
                  .SingleOrDefault<long>();

EDIT #2 I couldn't leave well alone, so i implemented an extension method aswell:

     public static long CorrectRowCount<TRoot>(this IQueryOver<TRoot> query) where TRoot : IEntity
     {
         return query.Clone()
                     .Select(Int64CountProjection.Distinct<TRoot>(x => x.Id))
                     .ClearOrders()
                     .Skip(0)
                     .Take(RowSelection.NoValue)
                     .SingleOrDefault<long>();
     }

score 0 · Answer 2 · answered Mar 10 '14 at 18:07

The problems seems be Transformers.DistinctRootEntity is natural you get 4 in row count, and 3 on a list. The DistinctRootEntity works when the query has been executed retuning 4 rows and removing duplicated in memory.

You can use query.RowCountInt64() with the correct query.

//
// Summary:
//     Clones the QueryOver, removes orders and paging, and projects the row-count
//     (Int64) for the query
IQueryOver<TRoot, TRoot> ToRowCountInt64Query();

//
// Summary:
//     Short for ToRowCountInt64Query().SingleOrDefault<long>()
long RowCountInt64();

The problem seems to be that the row count methods you describe remove any distinct projections i have done, which means that the count i get is not the same as the number of items i get. I need the row count to be accurate (not counting cartesian products) since i'm using this in a paged list. — carl, Mar 11 '14 at 14:12

NHibernate RowCountInt64 returns wrong count with transformed query

2 Answers2