Can I use SQL functions in NHibernate QueryOver?

Question

I have been searching the internet and can't find an example on how to use the queryover of nhibernate 3.0 For example I would like to use the string functions on the where clause of the queryover ex:

var item = Query.Where(x => x.Name.ToLower() == name.ToLower()).FirstOrDefault();

But this doesn't work, because nhibernate can't understand the ToLower, so how can extend the dialect in a way that this becomes possible?

Doall

Answer 1

session.QueryOver<Foo>()
    .Where(Restrictions.Eq(
        Projections.SqlFunction("lower", NHibernateUtil.String, 
            Projections.Property<Foo>(x => x.Name)),
        name.ToLower()))

should get you SQL like where lower(Name) = @p0

Gastroenterostomy

Hi, thanks for the reply it worked indeed, you don't know how long and much I searched for this solution. Thanks – Doall

@Ruben Monteiro no problem, I find that sometimes you need to mix in some Criteria when working with QueryOver. – Gastroenterostomy

Answer 2

I believe it works at least in the build I am using (version 3.0.0.4000)... below is my example...

var reasons = _session.Query<Reason>();
var myReason = (from r in reasons 
                where r.IsCritical 
                   && r.ReasonCode.ToUpper() == reasonCode.ToUpper() 
               select r).FirstOrDefault();

Give it a shot and let me know if it works for you...

Dynamics

this results in a where clause that uses the UPPER() function in SQLServer... (which might be bad for performance... fyi) – Dynamics

I tried this var query = Session.GetISession().QueryOver<Budget>();var item = (from f in query where f.Description.ToLower() == description.ToLower() select f).List().FirstOrDefault(); and got this error Unrecognised method call in epression f.Description.ToUpper(), my version of nHibernate if the 3.1.0.4000, @Dynamics - I know the performance issue but I need to implement it this way – Doall

Interesting... your right it does not work when you use the QueryOver syntax... my guess is some day that will get fixed... Depending on your needs you could move to using the Query syntax for this and it should work... you code would be updated as follows... – Dynamics

var query = Session.GetISession().Query<Budget>();var item = (from f in query where f.Description.ToLower() == description.ToLower() select f).FirstOrDefault(); – Dynamics