In this post, I’ll go over how to use functions built into the database engine. This can be useful when you want to do some work inside of your SQL query rather than do post-processing on the result set you get back.
This article is part of an ongoing series on NHibernate Queryover. Click here to see the table of contents.
Dialects in NHibernate
To understand how to use and later build SQL functions, it’s helpful to understand how the default SQL functions are registered with NHibernate to begin with.
NHibernate has the concept of a SQL dialect, a vendor-specific flavor of SQL. As you probably know, many dialects are supported out of the box. NHibernate represents dialects with a class per supported dialect.
Dialect base class registers required functions for a dialect using ANSI-92 standards. If a dialect implements a function differently, that dialect must overwrite the base class’ implementation with its own.
For example, SQL Server doesn’t implement the ANSI-92
TRIM function, so the
MsSql2000 dialect class uses a different implementation than the base class (which ultimately calls
ltrim to simulate the ANSI standard).
It’s worth looking over the
Dialect base class and possibly the dialect class for the database engine you’re using to see what functions are already available to you.
Calling functions from your queries
There are two ways to actually use SQL functions inside of your queries.
Using already registered SQL functions is fairly simple, using
Projections.SqlFunction. For example, here’s a query that gets every
Person’s middle name, or “Not Applicable” if
null, using the
1 2 3 4 5 6 7
1 2 3 4
This same pattern applies to all SQL functions that you’d like to call using
Inside of a QueryOver query, there’s actually a better way to call many of the most common SQL functions. The
ProjectionsExtensions class inside of the
NHibernate.Criterion namespace contains extension methods that are parsed into SQL function calls.
For example, here’s a query using the
.Upper extension method. Note that these extension methods are actually on the object’s properties:
1 2 3
1 2 3 4
This is much cleaner than the alternative using
1 2 3 4 5 6 7
Using your own functions
In most cases, functions you want to use will already be registered in the dialect you’re using. In some cases, however, you’ll want to add a function that’s not been registered. In this section of the post, I’ll go over how to add the
checksum function in SQL Server. There are a few steps involved in using your own function, I’ll go over each one in detail.
There are actually two ways to invoke a custom SQL function from your queries. You can either add the function “statically” to a custom dialect, or invoke a brand new function “dynamically” at runtime that’s not registered with the dialect.
Adding your own dialect
As I discussed earlier, functions are registered in the dialect class representing the database flavor you’re using. Since we can’t modify those classes directly to register our function, we’ll create a new dialect that’s a subclass of the one we’re using.
Since I’m using SQL Server in this example, I’ll create a custom dialect that’s a subclass of
1 2 3 4 5 6 7 8 9 10 11
Then, we need to make sure our application is using the new dialect. We can do this either in our configuration code:
1 2 3 4 5 6
Or, in our config file:
1 2 3 4 5 6 7 8
Calling the function using
If all you want to do is call a function using
Projections.SqlFunction, you’re basically done. All you need to do is call the function:
1 2 3 4 5 6 7
This will yield the following SQL:
1 2 3 4
Creating a custom projections class
Projections.SqlFunction isn’t quite satisfactory, especially after seeing the built-in
ProjectionExtensions. We can easily create a
CustomProjections class that provides some syntactic sugar for calling our custom function:
1 2 3 4 5 6 7 8 9 10 11 12
Notice that we have two overloads of
Checksum, one that takes an array of
Expression<Func<object>>s and another that takes an array of
Expression<Func<object>> overload is convenient when we don’t need to combine the use of
checksum with other functions, for example:
1 2 3 4 5
IProjection overload is useful when we need to supply
checksum with the result of calling another function, say
1 2 3 4 5 6 7 8 9 10
If you look through NHibernate’s implementations of various SQL functions, you might notice that many use
SQLFunctionTemplate. These should take care of most of your custom function needs. If not, you can always implement
ISQLFunction and create your own implementation.
StandardSQLFunction to implement our
checksum example. Basically,
StandardSQLFunction allows you to implement a SQL function that takes an arbitrary number of arguments and returns a scalar value.
SQLFunctionTemplate is a bit more sophisticated, and you can use it to implement SQL functions with a template, like the name implies. This is typically useful when you want to require a function to have a specific number of arguments.
An example of this would be the
stuff function in SQL Server. This function inserts one string into another string, deleting the specified number of characters from the first string at a start index, then inserts the second string.
For example, here’s how you could use
stuff to replace “C++” with “C#”:
stuff has a fixed number of parameters, it’s a good candidate for
SQLFunctionTemplate. All we have to do to register it in our dialect is add the following line:
Here, we’re basically just saying that
stuff is a function whose syntax is invoking the
stuff function with exactly four parameters.
We’ll add a few more static methods to our
CustomProjections class, since there are several ways we might want to call this function, we’ll provide several overloads:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
Here’s an example of how it would be used:
1 2 3 4 5 6
Invoking new functions at runtime
If, for some reason, you don’t want to create a custom dialect and register functions there, you can still invoke an unregistered SQL function. There’s an overload of
Projections.SqlFunction that takes an
ISQLFunction that you can define at runtime. For example, if we had not registered our
checksum function, you could call it dynamically like this:
1 2 3 4 5
Here, we’re defining and using the
checksum function in one shot.
There is a disadvantage to using this method. When you register a function with the dialect instead, NHibernate adds the function to an internal cache and reuses the function definition whenever you access it by name.
Creating a new
checksum function every time we needed to call the SQL Server
checksum function would be wasteful—it would be better to define the function once and have NHibernate cache and reuse it.
However, we may want to leverage invoking a function dynamically to take care of special SQL functions, like SQL Server’s
Implementing SQL Server’s
SQL Server has a function called
datediff that returns the number of “date parts” between a given start and end date.
At first glance, it seems like we could register
The problem here is that
datediff’s first parameter is a SQL server keyword and cannot be supplied as a variable. According to MSDN:
These dateparts and abbreviations cannot be supplied as a user-declared variable.
So that means we can’t call
datediff and supply the
datepart dynamically. We could register a function for every possible version of
datediff and name them all slightly differently:
1 2 3
I’m not sure about you but this makes me cringe. Luckily there’s a better solution. We can use NHibernate’s ability to run an arbitrary, unregistered SQL function to dynamically create and execute the various versions of
datediff. Here’s the code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Now, we’re able to write queries using any date part we want without having to register a separate function for each date part. For example, here’s a query that gets the
datediff in days, quarters, and months:
1 2 3 4 5 6
This still isn’t perfect. You might have realized that we’re still at a disadvantage since we’re not using cached versions of our function definitions. One good solution to this is to use our own cache for the various
datediff flavors. Here’s what our class looks like with that modification:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
Now we’re caching our function definitions so that we’re not redefining versions of
Another enhancement that probably should be made is to make the
datepart argument of
DateProjections.DateDiff strongly typed. A good solution there would be to use an
enum defining the possible
datepart values. Then you could use a
Dictionary<DatePart, string> to map from
enum values to strings.
Calling built-in SQL functions from NHibernate queries has been written about many times before, but hopefully I was able to shed some light on how those functions are registered and invoked. In summary:
- You can either register a function by using a custom dialect and invoke it by name later, or define and invoke the function in one step.
- Registering a function with a custom dialect is often the best option since the function definition is cached and reused automatically by NHibernate.
SQLFunctionTemplateare implementations of
ISQLFunctionthat enable easily defining SQL functions.
- Using a custom projections class is a useful abstraction to lay on top of
Projections.SqlFunctionto make code easier to read and more robust.
- You can use NHibernate’s ability to call SQL functions at runtime to implement the
datedifffunction in a clean way.