Development With A Dot

Blog on development in general, and specifically on .NET

Sponsors

News

My Friends

My Links

Permanent Posts

Portuguese Communities

LINQ to NHibernate Extensions

Giorgetti Alessandro, aka, Guardian, author of the blog PrimordialCode, has published recently some great posts on extending LINQ to NHibernate. You can read them here, here and here.

Inspired by these posts, I set out to implement some other features that LINQ to NHibernate is lacking, namely, support for the SQL CAST and COALESCE operators. It wasn’t difficult at all. Here’s what I came up with:

First, some extensions for the Object type:

   1: public static class ObjectExtensions
   2: {
   3:     public static T Coalesce<T>(this T obj, T fallback) where T: class
   4:     {
   5:         return(obj == null ? fallback : obj);
   6:     }
   7:  
   8:     public static TTarget Cast<TTarget>(this Object source)
   9:     {
  10:         return ((TTarget)source);
  11:     }
  12: }

Next, the registration for the custom extensions:

   1: public class CustomLinqToHqlGeneratorsRegistry: DefaultLinqToHqlGeneratorsRegistry
   2: {
   3:     public CustomLinqToHqlGeneratorsRegistry()
   4:     {
   5:         this.RegisterGenerator(ReflectionHelper.GetMethodDefinition(() => ObjectExtensions.Coalesce<Object>(null, null)), new CoalesceHqlGeneratorForMethod());
   6:         this.RegisterGenerator(ReflectionHelper.GetMethodDefinition(() => ObjectExtensions.Cast<Object>(null)), new CastHqlGeneratorForMethod());
   7:     }
   8: }

The loquacious configuration which includes the registry:

   1: static Configuration LoquaciousBuildConfiguration<TDriver, TDialect>(String connectionStringName)
   2:             where TDriver : IDriver
   3:             where TDialect : Dialect
   4: {
   5:     Configuration cfg = new Configuration()
   6:     //.SetProperty(NHibernate.Cfg.Environment.LinqToHqlGeneratorsRegistry, typeof(CustomLinqToHqlGeneratorsRegistry).AssemblyQualifiedName)
   7:     .SetProperty(NHibernate.Cfg.Environment.GenerateStatistics, Boolean.FalseString)
   8:     .SetProperty(NHibernate.Cfg.Environment.UseQueryCache, Boolean.TrueString)
   9:     .SetProperty(NHibernate.Cfg.Environment.UseSecondLevelCache, Boolean.FalseString)
  10:     .SetProperty(NHibernate.Cfg.Environment.ShowSql, Boolean.TrueString)
  11:     .SetProperty(NHibernate.Cfg.Environment.FormatSql, Boolean.TrueString)
  12:     .SetProperty(NHibernate.Cfg.Environment.PrepareSql, Boolean.TrueString)
  13:     .SetProperty(NHibernate.Cfg.Environment.PropertyBytecodeProvider, "lcg")
  14:     .SetProperty(NHibernate.Cfg.Environment.PropertyUseReflectionOptimizer, Boolean.TrueString)
  15:     .SetProperty(NHibernate.Cfg.Environment.UseProxyValidator, Boolean.TrueString)
  16:     .Proxy(p => p.ProxyFactoryFactory<DefaultProxyFactoryFactory>())
  17:     .LinqToHqlGeneratorsRegistry<CustomLinqToHqlGeneratorsRegistry>()
  18:     .DataBaseIntegration(db =>
  19:     {
  20:         db.KeywordsAutoImport = Hbm2DDLKeyWords.AutoQuote;
  21:         db.IsolationLevel = System.Data.IsolationLevel.ReadCommitted;
  22:         db.ConnectionStringName = connectionStringName;
  23:         db.Dialect<TDialect>();
  24:         db.BatchSize = 500;
  25:         db.HqlToSqlSubstitutions = "true 1, false 0, yes 'Y', no 'N'";
  26:         db.Driver<TDriver>();
  27:     });
  28:  
  29:     return (cfg);
  30: }

Now, the AST implementation for the CAST operator:

   1: class CastHqlGeneratorForMethod : BaseHqlGeneratorForMethod
   2: {
   3:     public CastHqlGeneratorForMethod()
   4:     {
   5:         this.SupportedMethods = new MethodInfo[] { ReflectionHelper.GetMethodDefinition(() => ObjectExtensions.Cast<Object>(null)) };
   6:     }
   7:  
   8:     public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject, ReadOnlyCollection<Expression> arguments, HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
   9:     {
  10:         return (treeBuilder.Cast(visitor.Visit(arguments[0]).AsExpression(), method.ReturnType));
  11:     }
  12: }

And the implementation for COALESCE:

   1: class CoalesceHqlGeneratorForMethod : BaseHqlGeneratorForMethod
   2: {
   3:     public CoalesceHqlGeneratorForMethod()
   4:     {
   5:         this.SupportedMethods = new MethodInfo[] { ReflectionHelper.GetMethodDefinition(() => ObjectExtensions.Coalesce<Object>(null, null)) };
   6:     }
   7:  
   8:     public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject, ReadOnlyCollection<Expression> arguments, HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
   9:     {
  10:         return (treeBuilder.Coalesce(visitor.Visit(arguments[0]).AsExpression(), visitor.Visit(arguments[1]).AsExpression()));
  11:     }
  12: }

Now let’s try it:

   1: Customer customerNamedSomething1 = session.Query<Customer>().Where(x => x.Name.Coalesce("SOMETHING") == "SOMETHING").FirstOrDefault();
   2: Customer customerNamedSomething2 = session.Query<Customer>().Where(x => x.Name.Cast<String>() == "SOMETHING").FirstOrDefault();

You can see that the LINQ queries are being translated to proper SQL for both cases (only tried it with SQL Server, mind you!).

OK, the examples are a bit silly, but I couldn’t think of anything else. I think you’ll get the picture, though!

Thanks to Guardian for the initial examples!

Bookmark and Share

Posted: Jun 14 2011, 11:55 AM by Ricardo Peres | with 2 comment(s) |
Filed under: , , ,

Comments

Jarrett said:

This looks really valuable for SOUNDEX() and NEWID() (to order by random). I've written these in Criteria format, but not in LINQ format. (I prefer the LINQ format.) Thanks!

# June 15, 2011 5:34 AM

NHibernate String Comparison « devioblog said:

Pingback from  NHibernate String Comparison &laquo;  devioblog

# August 20, 2012 3:06 PM