Using Sql Server 2008 DataTable parameters with NHibernate

by Michael Henstock 8. December 2012 11:47

Hitting the maximum parameter limit of 2100 for an Sql Server query generally points to deeper issues in the design of your application and should be a sign for the need of refactoring than anything else. However, there are some rare cases where this really is the best solution and you need to get around this limit.

With Sql Server 2008+ we can use table valued parameters to a query (http://msdn.microsoft.com/en-us/library/bb510489.aspx) when we need to pass in a large set of parameter queries. While great for direct queries, this feature isn't supported in NHibernate due to the specifics around actually implementing such a feature make it nigh impossible to do so without breaking the generic wrapper interface around all of the databases that NHibernate supports.

NOTE: While the following does allow usage of table valued parameters with NHibernate, it is a hack that works around and with NHibernates plugin infrastructure. While NHibernate is beautifully extensible, I wasn't able to find a way to implement this without the hacks as described in the following. If anyone knows of a better way to implement this, I would love to know.
 
So to begin, we need to create a table type within the target database:
CREATE TYPE [dbo].[IdentifierTable] AS TABLE([ID] [uniqueidentifier] NULL);
Here we create a table type with a field that is of the same data type as the fields we wish to query in volume. Next, we create an
extension method working off the data type that we want to query (in this case a Guid):
public static bool IsIn(this Guid source, IEnumerable<Guid> idList)
{
     throw new NotImplementedException();
}
This gives us a method to call in our NHibernate Linq query to use when we want to override the default NHibernate behaviour to use the
table type parameters. Next we need a generator class which generate the HQL used by NHibernate for this query.
There's no method (that I know of) that allows us to call the table type parameter with a DataTable within HQL. As such, we're just putting
the default code that NHibernate generates for a standard Contains HQL filter and adding a wrapper around this (the hacks begin!):
 
public class IsInDataTableGenerator : BaseHqlGeneratorForMethod
{
    public const string DATA_TABLE_PARAMETER_VALUE = "DATA_TABLE_PARAMETER_VALUE";
    public IsInDataTableGenerator()
    {
        SupportedMethods = new[] { ReflectionHelper.GetMethodDefinition(() => MyNHibernateExensions.IsIn(Guid.Empty, null)) };
    }

    public override HqlTreeNode BuildHql(
        MethodInfo method,
        Expression targetObject,
        ReadOnlyCollection<Expression> arguments,
        HqlTreeBuilder treeBuilder,
        IHqlExpressionVisitor visitor)
    {
        return
            treeBuilder.BooleanAnd(
                treeBuilder.BooleanAnd(
                    treeBuilder.Equality(treeBuilder.Constant(DATA_TABLE_PARAMETER_VALUE), treeBuilder.Constant(DATA_TABLE_PARAMETER_VALUE)),
                    treeBuilder.In(
                        visitor.Visit(arguments[0]).AsExpression(),
                        visitor.Visit(arguments[1]).AsExpression())),
                treeBuilder.Equality(treeBuilder.Constant(DATA_TABLE_PARAMETER_VALUE), treeBuilder.Constant(DATA_TABLE_PARAMETER_VALUE)))
            ;
    }
}
This adds in the standard HQL for value IN (value list) queries, and wraps the start and end with 'DATA_TABLE_PARAMETER_VALUE' = 'DATA_TABLE_PARAMETER_VALUE' filters. Next we need to register the extension method with NHibernate:
class LinqToHqlGeneratorsRegistry : DefaultLinqToHqlGeneratorsRegistry
{
     public LinqToHqlGeneratorsRegistry()
     {
         RegisterGenerator(
             ReflectionHelper.GetMethodDefinition(() => MyNHibernateExensions.IsIn(Guid.Empty, null)),
             new IsInDataTableGenerator());
     }
}
This simply registers the extension with NHibernate and lets it know to use the IsInDataTableGenerator for the Linq queries that use that
function. Next up, we need to really get our hacks in full motion. We create a new class that inherits from the default NHibernate
Sql2008ClientDriver and override the OnBeforePrepare method. This takes in the final IDbCommand object that will be sent to the database,
so we start by inspecting the actual Sql and play around with that to inject our Sql to filter against the table type.
class MySql2008ClientDriver : Sql2008ClientDriver
{
    private static readonly Regex SubstitutionRegEx =
        new Regex(
            "'" + IsInDataTableGenerator.DATA_TABLE_PARAMETER_VALUE + "'='" + IsInDataTableGenerator.DATA_TABLE_PARAMETER_VALUE + 
            "'.+?'" + IsInDataTableGenerator.DATA_TABLE_PARAMETER_VALUE + "'='" + IsInDataTableGenerator.DATA_TABLE_PARAMETER_VALUE + "'",
            RegexOptions.Compiled);
    private static readonly Regex ParameterMapRegEx = new Regex(@"(\@p.*?)\b\)", RegexOptions.Compiled);
    private static readonly Regex FieldRegEx = new Regex(@"\(.*?\(", RegexOptions.Compiled);

    protected override void OnBeforePrepare(IDbCommand command)
    {
        foreach (var substitution in SubstitutionRegEx.Matches(command.CommandText).Cast<Match>().Select(x => x.Value))
        {
            // Grab a string array of parameter names being passed into the filter
            var parameters = ParameterMapRegEx.Match(substitution).Value.Replace(')', ' ').Replace(" ", "").Split(',');
            // prepare the replacement filter statement
            var filterStatement = FieldRegEx.Match(substitution).Value + "SELECT ID FROM " + parameters[0] + "))";

            // Add the list of parameter values into a DataTable and remove them from the command.
            var table = new DataTable();
            table.Columns.Add(new DataColumn("ID", typeof(Guid)));
            foreach (var sqlParameter in parameters.Select(parameter => ((SqlParameter)command.Parameters[parameter])))
            {
                table.Rows.Add(sqlParameter.Value);
                command.Parameters.Remove(sqlParameter);
            }

            // Add the substitute parameter with the datatable value
            command.Parameters.Add(
                new SqlParameter
                {
                    SqlDbType = SqlDbType.Structured,
                    TypeName = "IdentifierTable",
                    Value = table,
                    ParameterName = parameters[0]
                });
            // replace the substitution string with the replacement filter
            command.CommandText = command.CommandText.Replace(substitution, filterStatement);
        }
    }
}
This uses regular expressions to grab out all the where conditions with the "DATA_TABLE_PARAMETER_VALUE" wrapper. Then gets the field name it's filtering on plus all the Sql parameters being passed into the command for that filter statement. Then builds up a DataTable object with the same structure as the table type, inserts rows with all the values in the sql parameters, and removes the parameters from the query. Then finally adds in a new parameter with the TypeName pointing to the table type, and the DataTable created as the value. Finally we replace the old Sql IN filter with a new one that only checks the field IN the newly added table type parameter.
 
Lastly, we just need to update the NHibernate configuration to use the override classes show above. When we configure your NHibernate SessionFactory, we need to add the following:
config.Properties["connection.driver_class"] = typeof (MySql2008ClientDriver).AssemblyQualifiedName;
config.LinqToHqlGeneratorsRegistry<LinqToHqlGeneratorsRegistry>();
The first line simply overrides the default client driver class with our overridden class. The second just adds our Linq to HQL registration class to the list of registry classes.
 
Once this has all been done, our queries simply become a case of calling the Linq extension method in our NHibernate IQueryables like the following:
var filterIds = new Guid[3000];
// Populate filterIds with values
session.Query<MyTest>().Where(r => r.id.IsIn(filterIds));

Tags:

C# | NHibernate