Nhibernate - How do I get ordered distinct results with QueryOver?

Question

public class City { virtual public long Id { get; set; } virtual public string Name { get; set; } }

City table contains duplicated Names and I want to remove duplicates. I also want the results to be ordered by Id.

First I thought about the following query.

select distinct Name from City order by Id;

But this breaks with 'ORDER BY items must appear in the select list if SELECT DISTINCT is specified.' exception. After seeing http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by-error.aspx I think I should do:

select Name from City group by Name order by min(Id)

So my question is how can I do this query with QueryOver?

FlukeFan · Accepted Answer · 2010-09-27 07:58:33Z

This is possible in ICriteria:

var list = session.CreateCriteria<City>() .SetProjection(Projections.Group("Name")) .AddOrder(Order.Asc(Projections.Min("Id"))) .List<string>();

But this is not currently possible in QueryOver, because the .OrderBy(IProjection) overload is missing. Once the missing overload has been added it should look something like:

var list = s.QueryOver<City>() .Select(Projections.Group<City>(p => p.Name)) .OrderBy(Projections.Min<City>(c => c.Id)).Asc .List<string>();

Note that the Projections overloads are there just now, so you can write the following (typesafe) query in ICriteria:

var list = session.CreateCriteria<City>() .SetProjection(Projections.Group<City>(c => c.Name)) .AddOrder(Order.Asc(Projections.Min<City>(c => c.Id))) .List<string>();

FYI, the missing overload for .OrderBy has been added to the trunk, and will appear in RC1.
Hi FlukeFan => So the 'ORDER BY items must appear in the select list if SELECT DISTINCT is specified.' exception should no longer appear?

Andrew Gray · Accepted Answer · 2018-05-22 21:29:33Z

So, what I've found is pretty simple...

var query = session.QueryOver<MyModel>() // Conditions here .OrderBy(m => m.GetThisDistinctField).Desc() // ...or Asc()... .SelectList(m => m.SelectGroup(g => g.GetThisDistinctField)); var result = query.List<FieldDataType>().ToList(); return result;

To get an ordered query in queryover, start with a query that includes whatever criteria you need, but then add the SelectList/SelectGroup setup in order to get the distinct list. In other words, it's sort of like NHibernate can take a regular query, and then do special stuff to make it a select distinct query.

This is a solution I'm using in a current project I'm working on; I hope it helps someone else too.

Collectives™ on Stack Overflow

Nhibernate - How do I get ordered distinct results with QueryOver?

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related