Problem with nHibernate Distinct and paging

Question

I have this Repository method which uses QueryOver API

    public IList<Message> ListMessagesBy(string text, IList<Tag> tags, int pageIndex, out int count, out int pageSize)
    {
        pageSize = 10;
        var likeString = string.Format("%{0}%", text);
        var query = session.QueryOver<Message>()
            .Where(Restrictions.On<Message>(m => m.Text).IsLike(likeString) ||
            Restrictions.On<Message>(m => m.Fullname).IsLike(likeString));

        if (tags.Count > 0)
        {
            var tagIds = tags.Select(t => t.Id).ToList();
            query
                .JoinQueryOver<Tag>(m => m.Tags)
                .WhereRestrictionOn(t => t.Id).IsInG(tagIds)
                .TransformUsing(Transformers.DistinctRootEntity);
        }                        

        count = 0;
        if(pageIndex < 0)
        {
            count = query.ToRowCountQuery().FutureValue<int>().Value;
            pageIndex = 0;
        }
        return query.OrderBy(m => m.Created).Desc.Skip(pageIndex * pageSize).Take(pageSize).List();
    }

I've tried both

.TransformUsing(Transformers.DistinctRootEntity);

and

.RootCriteria.SetResultTransformer(new DistinctEntityRootTransformer())

It screws up both the total count (It returns result none distinct) and the actual paging (Skip / Take)

How can I fix this?

Thanks in advance, Anders

Answer 1

Try something like this

public IPagedList<Client> Find(int pageIndex, int pageSize)
{
    Client clientAlias = null;

    var query = Session.QueryOver<Client>(() => clientAlias)

        .Select(
            Projections.Distinct(
                Projections.ProjectionList()
                    .Add(Projections.Property<Client>(x => x.Id).As("Id"))
                    .Add(Projections.Property<Client>(x => x.Name).As("Name"))
                    .Add(Projections.Property<Client>(x => x.Surname).As("Surname"))
                    .Add(Projections.Property<Client>(x => x.GivenName).As("GivenName"))
                    .Add(Projections.Property<Client>(x => x.EmailAddress).As("EmailAddress"))
                    .Add(Projections.Property<Client>(x => x.MobilePhone).As("MobilePhone"))
            )
        )
        .TransformUsing(Transformers.AliasToBean<Client>())

        .OrderBy(() => clientAlias.Surname).Asc
        .ThenBy(() => clientAlias.GivenName).Asc;

    var count = query
        .ToRowCountQuery()
        .FutureValue<int>();

    return query
        .Take(pageSize)
        .Skip(Pagination.FirstResult(pageIndex, pageSize))
        .List<Client>()
        .ToPagedList(pageIndex, pageSize, count.Value);
}

Answer 2

I had a issue with this aswell. Firstly the Distinct does work, but only after the QueryOver.List.ToList() method was called, so the query.skip wouldn't work properly, paging over the duplicates, creating the list, then reducing my paged amount because of the duplicates.

Easiest thing i found to do was.. simply create a list of unique ids first, then do your pagination on the Ids themselves..

Then on your result set you can simply perform a Id and retrieve the ids only in your newly paginated id result set.

//Create your query as usual.. apply criteria.. do what ever you want.

//Get a unique set of ids from the result set.
var idList = query.
.Select(x => x.Id)
.List<long>().Distinct().ToList();

//Do your pagination here over those ids
List<long> pagedIds = idList.Skip(0).Take(10).ToList();

//Here what used to be non distinct resultset, now is..
List<T> resultquery.Where(() => 
item.Id.IsIn(pagedIds))
.List<Person>()
.ToList();

Special thanks to.. https://julianjelfs.wordpress.com/2009/04/03/nhibernate-removing-duplicates-combined-with-paging/