How to get a distinct result with nHibernate and QueryOver API?

Question

I have this Repository method

    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);
        }            

        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();
    }

You supply a free text search string and a list of Tags. The problem is that if a message has more then one tag it is listed duplicated times. I want a distinct result based on the Message entity. I've looked at

Projections.Distinct

But it requires a list of Properties to to the distinct question on. This Message is my entity root there most be a way of getting this behaviour without supplying all of the entity properties?

Thanks in advance, Anders

Sly · Answer 1 · 2023-04-20T18:02:37.840

69

If you're using the ICriteria API, you need:

.SetResultTransformer(new DistinctEntityRootTransformer())

If you're using the QueryOver API, you need:

.TransformUsing(Transformers.DistinctRootEntity)

But beware, this all occurs on client side, so all the duplicate rows are still pulled from SQL database.

edited Apr 20 '23 at 18:02

answered Jan 06 '11 at 14:58

Sly

15,046
12
60
89

5

The ICriteria API is not very fluent with QUeryOver, but .TransformUsing(Transformers.DistinctRootEntity); works perfect. – Anders Jan 06 '11 at 16:07
.TransformUsing(Transformers.DistinctRootEntity) Did not work at all with Paging :/ Any other ideas? – Anders Jan 06 '11 at 18:28
7

It won't work with paging. For paging queries you will need to use Projections – Sly Jan 06 '11 at 19:50
2

Does this mean you cannot select to an alias AND do a distinct (as they are both transformers and you only seem to be able to use one transformer)? – Sam Oct 10 '13 at 05:13
2

I get regular upvotes on this question so I revisit it sometimes. Are there no better solution today? In EF this just works. (They have other problems though) – Anders Sep 15 '15 at 10:52

score 30 · Answer 2 · answered Feb 24 '11 at 00:33

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);
}

Works but... tedious. Instead of As("xxx") you can call the WithAlias extension method instead for a little less magic strings. Thanks — Sam, Oct 10 '13 at 05:17
WithAlias extension method example: .WithAlias(() => entity.PropertyName) — xhafan, Jun 22 '17 at 14:54

score 13 · Answer 3 · answered Apr 06 '11 at 15:49

You can use SelectList and GroupBy, e.g:

tags.SelectList(t => t.SelectGroup(x => x.Id))

Should work and produce the same query plan as distinct.

If you need multiple items in the group, do something like:

tags.SelectList(t => t.SelectGroup(x => x.Id)
                      .SelectGroup(x => x.Name)
               )

score 2 · Answer 4 · answered Aug 12 '11 at 13:45

I have recently created a method to apply select distinct based on a mapped object type. It applies this to an IQueryOver object (property of class). Method also has access to the nhibernate config. You could add these as method parameters. Needs work for production, but method is working great in dev, only used it for one entity so far.

This method was created because I am trying to page my data at the server level and a distinct result transformer would not work.

After you get your object collection (query.List()) you may have to reload the objects to populate one to many child objects. Many to one mappings will be proxied for lazy loads.

 public void DistinctRootProjectionList<E>()
    {
        var classMapping = Context.Config.GetClassMapping(typeof(E));
        var propertyIterator = classMapping.UnjoinedPropertyIterator;
        List<IProjection> projections = new List<IProjection>();
        ProjectionList list = Projections.ProjectionList();

        list.Add(Projections.Property(classMapping.IdentifierProperty.Name), classMapping.IdentifierProperty.Name);

        foreach (var item in propertyIterator)
        {
            if (item.Value.IsSimpleValue || item.Value.Type.IsEntityType)
            {
                list.Add(Projections.Property(item.Name), item.Name);
            }
        }
        query.UnderlyingCriteria.SetProjection(Projections.Distinct(list));
        query.TransformUsing(Transformers.AliasToBean<E>());
    }

Code I used to load one to many relations... T is the entity type.

for (int i = 0; i < resp.Data.Count; i++)
        {
            resp.Data[i] = session.Load<T>(GetInstanceIdValue(resp.Data[i]));
        }

How to get a distinct result with nHibernate and QueryOver API?

4 Answers4

Linked