NHibernate Succinctly®
CHAPTER 11
Extending NHibernate
Extending LINQ
One common request is for the ability to have strongly typed LINQ expressions that take functions and have these functions run on the database. This is indeed possible with NHibernate. Let’s take a look at how this is possible.
First, let’s define a function prototype for something that we would like to call. In this example, I chose the SQL Server SOUNDEX function, for which you may find more information at http://msdn.microsoft.com/en-us/library/ms187384.aspx. Basically, this function returns a hash based on the sound that a string makes, thus allowing the successful comparison of badly written words. As far as the SOUNDEX algorithm is concerned, the following strings are the same:
- Ricardo.
- Riicardo.
- Ryicardo.
You can see it for yourself:
SELECT SOUNDEX('Ricardo') --R263 SELECT SOUNDEX('Riicardo') --R263 SELECT SOUNDEX('Rycardo') --R263 |
If we wanted to call SOUNDEX in a LINQ query, we might define the following extension method on the String class:
public static class StringExtensions { [LinqExtensionMethod] public static String Soundex(this String input) { throw new NotImplementedException(); } } |
Tip: Add a reference to the NHibernate.Linq namespace.
All we need to do to make it callable by NHibernate is to decorate it with the [LinqExtensionMethod] attribute from the NHibernate.Linq namespace. We can now write code like this and have NHibernate translate it to the appropriate SQL:
String soundexName = session.Query<Customer>().Select(x => x.Name.Soundex()).First(); |
Because the SOUNDEX method has no .NET implementation, we know that it is running in the database.
NHibernate already includes a useful extension for performing LIKE comparisons:
IEnumerable<Product> products = session.Query<Product>().Where(x => SqlMethods.Like(x.Name, "%phone%")) .ToList(); |
So, to clarify, we can apply the [LinqExtensionMethod] to any .NET method for which there is a corresponding SQL function with the same name and parameter signatures. There is no need to implement this method in .NET but, if you are curious, there is a possible implementation of the SOUNDEX algorithm in this book’s companion code. You can also find plenty of information about it on the Internet.
Extending HQL
Like LINQ, we can also extend HQL so that it knows any database-specific functions that we would like to use. It is normally done by extending a Dialect class and registering those functions there, but it is also very easy to achieve by reflection:
Tip: Reference namespaces System.Reflection, NHibernate, NHibernate.Dialect, NHibernate.Dialect.Function, NHibernate.Impl, and NHibernate.Dialect.
One reason why you would follow this approach instead of subclassing Dialect is, if you need to support multiple databases, you would have to subclass all of their dialects’ classes.
As an example, using SQL Server’s DATEADD and GETDATE functions:
- 1800+ high-performance UI components.
- Includes popular controls such as Grid, Chart, Scheduler, and more.
- 24x5 unlimited support by developers.