Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

NHibernate QueryOver order by first non-null value (coalescing)

What I'm trying to come up is something that's expressed like this:

var result = Session.QueryOver<Foo>().OrderBy(f => f.UpdatedAt ?? f.CreatedAt);

Sure enough, this doesn't work. Rough equivalent of this in T-SQL is

... order by coalesce(f.UpdatedAt, f.CreatedAt)

What's the kosher way to do "coalescing" in NHibernate QueryOver?

like image 635
Anton Gogolev Avatar asked Apr 27 '11 13:04

Anton Gogolev


Video Answer


2 Answers

.OrderBy(Projections.SqlFunction("coalesce",
                                 NHibernateUtil.DateTime,
                                 Projections.Property<Foo>(x => x.UpdatedAt),
                                 Projections.Property<Foo>(x => x.CreatedAt)))
like image 84
Diego Mijelshon Avatar answered Sep 21 '22 03:09

Diego Mijelshon


Diego's answer is the way I came up with, but it seemed to be too verbose to me, so I asked a question, and got an excellent answer. Basically, you can register your own extensions and then just do

.OrderBy(f => f.UpdatedAt.IfNull(f.CreatedAt));

where IfNull is your new extension. I've even submitted an improvement proposal to NH Jira, but got no response yet.

like image 41
Sergei Tachenov Avatar answered Sep 24 '22 03:09

Sergei Tachenov