Strongly Typed Delete With NHibernate

To my great satisfaction, LINQ is nowadays present everywhere, from XML processing, to database querying, including SharePoint. The “Q” in it standing for Query, it’s not a surprise that is mainly being used for querying, but it would be interesting to use it for other scenarios, such as updating and deleting.

I would like to be able to do this in NHibernate:

   1: session.Query<Product>().Where(x => x.IsDiscontinued == true).Delete();

The only alternatives I have at the moment is HQL or SQL, but, as we know, it is not strongly-typed, and thus not refactor-friendly. I even suggested it to the NHibernate team, for the new major versions coming on.

It would be easy to achieve this by resorting to LINQ to Objects, I would just have to force materialization of every entity coming from the query, and then issue a ISession.Delete on each them. Not very wise, though. I decided to write my own solution.

Consider this extension method:

   1: public static class QueryableExtensions
   2: {
   3:     #region Private static readonly fields
   4:     private static readonly PropertyInfo sessionProperty = typeof(DefaultQueryProvider).GetProperty("Session", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.GetProperty);
   5:     private static readonly FieldInfo batcherInterceptorField = typeof(AbstractBatcher).GetField("_interceptor", BindingFlags.NonPublic | BindingFlags.Instance);
   6:     #endregion
   7:  
   8:     #region Public extension methods
   9:     public static void Delete<T>(this IQueryable<T> queryable)
  10:     {
  11:         if (queryable.GetType().GetGenericTypeDefinition() == typeof(NhQueryable<>))
  12:         {
  13:             ISessionImplementor impl = sessionProperty.GetValue(queryable.Provider, null) as ISessionImplementor;
  14:             IInterceptor oldInterceptor = sessionImplInterceptorField.GetValue(impl) as IInterceptor;
  15:             IInterceptor deleteInterceptor = new DeleteInterceptor();
  16:  
  17:             batcherInterceptorField.SetValue(impl.Batcher, deleteInterceptor);
  18:  
  19:             queryable.Any();
  20:  
  21:             batcherInterceptorField.SetValue(impl.Batcher, oldInterceptor);
  22:         }
  23:         else
  24:         {
  25:             throw (new ArgumentException("Invalid type", "queryable"));
  26:         }
  27:     }
  28:     #endregion
  29: }

What it does is:

  1. Checks if the IQueryable<T> object is an NHibernate implementation;
  2. Get access to its underlying ISessionImplementor;
  3. Saves the current IInterceptor (if any) for the current IBatcher instance;
  4. Sets it to a new instance of our new interceptor, DeleteInterceptor, which is where all the fun actually happens;
  5. Executes the query;
  6. Restores the old IInterceptor to the IBatcher.

So, next is the DeleteInterceptor class:

   1: class DeleteInterceptor : EmptyInterceptor
   2: {
   3:     private static readonly Regex regex = new Regex("\\s+from\\s+([^\\s]+)\\s+([^\\s]+)\\s+");
   4:  
   5:     public override SqlString OnPrepareStatement(SqlString sql)
   6:     {
   7:         Match match = regex.Match(sql.ToString());
   8:         String tableName = match.Groups[1].Value;
   9:         String tableAlias = match.Groups[2].Value;
  10:  
  11:         sql = sql.Substring(match.Groups[2].Index);
  12:         sql = sql.Replace(tableAlias, tableName);
  13:         sql = sql.Insert(0, "delete from ");
  14:  
  15:         Int32 orderByIndex = sql.IndexOfCaseInsensitive(" order by ");
  16:  
  17:         if (orderByIndex > 0)
  18:         {
  19:             sql = sql.Substring(0, orderByIndex);
  20:         }
  21:  
  22:         return (sql);
  23:     }
  24: }

Pretty easy, don’t you think? A basic NHibernate interceptor that just replaces the “SELECT” part of the query for a “DELETE” and removes any possible “ORDER BY”.

My solution is based upon replacing the current interceptor – if one exists – for a temporary one, just for the execution of this query, and setting the previous one back afterwards. It should be safe, because ISessions are meant for single-threaded use only. At the current stage, this should be regarded as a hack! There is plenty of room for other solutions and improvements, but it’s fun, though! Let me hear your thoughts and suggestions.

                             

No Comments