Problems implementing ordering and paging in NHibernate's QueryOver API for queries using joins and projections

Question

I'm still trying to get the hang of NHibernate. I'm doing some queries with projections, and so far I managed to come up with this:

var restrictions = Restrictions.Conjunction();
// add some restrictions

var qo = Session.QueryOver(() => tenantAlias)
    .JoinAlias(x => x.Customer, () => customerAlias)
    .Where(restrictions)                
    .SelectList(list => list
        .Select(() => tenantAlias.Id).WithAlias(() => item.TenantId)
        .Select(() => tenantAlias.DomainName.Value).WithAlias(() => item.DomainName)
        .Select(() => customerAlias.Code.Value).WithAlias(() => item.CustomerCode)
        .Select(() => customerAlias.DisplayName).WithAlias(() => item.CustomerName)
        .Select(() => tenantAlias.ActivationPeriod.From).WithAlias(() => item.ActivationPeriodFrom)
        .Select(() => tenantAlias.ActivationPeriod.Until).WithAlias(() => item.ActivationPeriodUntil)
        .Select(() => tenantAlias.PurchasedLicenses.Value).WithAlias(() => item.PurchasedLicenses)
    )
    .TransformUsing(Transformers.AliasToBean<TenantQueryResult.Item>());

    var items = await qo.ListAsync<TenantQueryResult.Item>();

Following a DDD approach, Tenant is an aggregate root and Customer an entity, and they have a one-to-one relationship. So far, this query is working beautifully.

Now, however, I would like to order the result set by customer's displayname property. I found there's an OrderBy method, so I thought it simple enough to add the following line:

.OrderBy(() => customerAlias.DisplayName)

But alas, this doesn't compile. The reason is that the query over always returns an instance of IQueryOver<T, T>, but the OrderBy clause returns IQueryOver<T> only. And IQueryOver<T> does not have a SelectList or TransformUsing or even ListAsync method. So I don't get how OrderBy clauses should work. The NHibernate documentation [1] doesn't provide an example, and Googling only got me simple examples with Cats, but never with joins or transforms or projections.

The same thing happens when I try to add paging to my query. Both Take and Skip methods are available, but they have the same problem, both return IQueryOver<T> instead of IQueryOver<T, T>.

The question is, then, how I'm supposed to add ordering and paging clauses with QueryOver API, when using joins and projections? Thanks in advance.

[1] http://nhibernate.info/doc/nhibernate-reference/queryqueryover.html

Radim Köhler Radim Köhler · Accepted Answer · 2018-04-05T06:13:43

We have to add direction ASC or DESC. So instead of this

.OrderBy(() => customerAlias.DisplayName)
.TransformUsing(Transformers.AliasToBean<TenantQueryResult.Item>());

We have to add .Asc or .Desc

.OrderBy(() => customerAlias.DisplayName)
    .Asc
.TransformUsing(Transformers.AliasToBean<TenantQueryResult.Item>());

Problems implementing ordering and paging in NHibernate's QueryOver API for queries using joins and projections

1 Answers