Custom LINQ Extensions for NHibernate

With extensibility virtually everywhere, NHibernate is nice to work with! Consider, for example, a need to call a database-specific function in a LINQ query – a typical request.

In SQL Server there is the SOUNDEX function, which can be used to generate a hash for a string that is based on the sound that it makes when spoken. It is a simple algorithm that was once applied manually by clerks, and can read more about it on Wikipedia. With T-SQL, it is used like this:

   1: SELECT SOUNDEX('ricardo')    --R263
   2: SELECT SOUNDEX('riicardo')   --R263
   3: SELECT SOUNDEX('rycardo')    --R263

In order to use this function from LINQ, we need to have a .NET method with an appropriate signature:

   1: public static class StringExtensions
   2: {
   3:     [LinqExtensionMethod]
   4:     public static String Soundex(this String input)
   5:     {
   6:         throw new NotImplementedException();
   7:     }
   8: }

Did you notice the [LinqExtensionMethod] attribute? Well, that’s all it takes! Smile We can have code like this:

   1: String nameHash = session.Query<Customer>().Select(x => x.Name.Soundex()).First();

And NHibernate will translate this to the appropriate function call!

Of course, this will make the LINQ expression run in the database, if you want it to run as well on the client side, here’s a possible implementation of the SOUNDEX algorithm: 

   1: public static class StringExtensions
   2: {
   3:     private const String values = "01230120022455012623010202";
   4:     private const Int32 encodingLength = 4;
   5:  
   6:     [LinqExtensionMethod]
   7:     public static String Soundex(this String input)
   8:     {
   9:         Char prevChar = ' ';
  10:  
  11:         input = Normalize(input);
  12:         
  13:         if (input.Length == 0)
  14:         {
  15:             return (input);
  16:         }
  17:  
  18:         StringBuilder builder = new StringBuilder();
  19:         builder.Append(input[0]);
  20:  
  21:         for (Int32 i = 1; ((i < input.Length) && (builder.Length < encodingLength)); ++i)
  22:         {
  23:             Char c = values[input[i] - 'A'];
  24:  
  25:             if ((c != '0') && (c != prevChar))
  26:             {
  27:                 builder.Append(c);
  28:                 prevChar = c;
  29:             }
  30:         }
  31:  
  32:         while (builder.Length < encodingLength)
  33:         {
  34:             builder.Append('0');
  35:         }
  36:  
  37:         return (builder.ToString());
  38:     }
  39:  
  40:  
  41:     private static String Normalize(String text)
  42:     {
  43:         StringBuilder builder = new StringBuilder();
  44:  
  45:         foreach (Char c in text)
  46:         {
  47:             if (Char.IsLetter(c) == true)
  48:             {
  49:                 builder.Append(Char.ToUpper(c));
  50:             }
  51:         }
  52:  
  53:         return (builder.ToString());
  54:     }
  55: }

As always, hope this helps! Winking smile

                             

No Comments