Using SQL Functions in Criteria Restrictions
Posted by Davy Brion on April 30th, 2009
A coworker needed to use a SQL function in the where clause of a query that he was creating with NHibernate’s ICriteria API. Most examples of this on the web use HQL instead of the ICriteria API and since we primarily use the ICriteria API we looked into how to do this.
Turns out it is pretty simple to do, though the syntax isn’t really straightforward. Suppose you want to query all of your employees who are born in a specific year. You could mess around with some DateTime parameters, but most databases have SQL functions to get the year from a date. Using the ICriteria API, this would look like this:
var employeesBornIn81 = session.CreateCriteria<Employee>()
.Add(Restrictions.Eq(Projections.SqlFunction("year", NHibernateUtil.DateTime, Projections.Property("BirthDate")), 1981))
.List<Employee>();
which adds the following where clause to the SQL statement (on SQL Server 2005):
WHERE datepart(year, this_.BirthDate) = @p0;
April 30th, 2009 at 2:46 pm
1981 was a good year…
April 30th, 2009 at 2:49 pm
i used to think so
April 30th, 2009 at 3:42 pm
get off my lawn you 2!!
May 1st, 2009 at 8:36 am
[...] Using SQL Functions in Criteria Restrictions – Davy Brion shows how despite the slightly unfriendly syntax it is possible to build criteria based queries in NHibernate that use the the built in functions of the database. [...]
May 4th, 2009 at 5:05 pm
Hi Davy,
what if the db doesn’t support the ‘year’ function?
Doesn’t your code become db-specific when you use this kind of syntax?
Thanks
May 4th, 2009 at 5:08 pm
it’s not entirely db-specific, because these functions are registered per dialect and then map to their DB-specific implementation
if you use a function that’s only supported by one (or a couple of) dialect, then yes, you lose some db portability.
you can find the supported functions, and the db-specific implementation they map to, in the constructor of each dialect
May 5th, 2009 at 10:57 am
what about functions that need additional parameters, such as DATEPART or SUBSTRING? are those possible?
May 5th, 2009 at 11:08 am
there are some functions that are registered per dialect that support multiple parameters though the way to use them with the Criteria API is probably a bit different… haven’t tried those yet