如何在名为IQuery参数的NHibernate上设置C＃可空值类型值？

I am using NHibernate and calling a stored procedure via a named query:

我正在使用NHibernate并通过命名查询调用存储过程:

<sql-query name="SearchStuff" read-only="true" cacheable="true">
  <return class="ResultEntity" />
  EXEC [SearchStuff] ?, ?, ?    </sql-query>

Many of the stored procedure parameters are deliberately nullable - this cannot be changed.

许多存储过程参数都是故意可以为空的 - 这是无法更改的。

The C#:

IQuery listQuery = this.Session.GetNamedQuery("SearchStuff");
listQuery.SetInt32(0, param1);
listQuery.SetDateTime(1, param2);
listQuery.SetString(2, param3);
IList<ResultEntity> results = listQuery.List<ResultEntity>();

Unfortunately, NHibernate does not provide any SetXyz() methods for nullable value types so I tried adding some extension methods to compensate:

不幸的是,NHibernate没有为可为空的值类型提供任何SetXyz()方法,所以我尝试添加一些扩展方法来补偿:

public static class QueryExtensions
{
    public static void SetInt32(this IQuery query, int position, int? val)
    {
        if (val.HasValue)
        {
            query.SetInt32(position, val.Value);
        }
        else
        {
            query.SetParameter(position, null);
        }
    }

    public static void SetInt32(this IQuery query, string name, int? val)
    {
        if (val.HasValue)
        {
            query.SetInt32(name, val.Value);
        }
        else
        {
            query.SetParameter(name, null);
        }
    }

    public static void SetDateTime(this IQuery query, int position, DateTime? val)
    {
        if (val.HasValue)
        {
            query.SetDateTime(position, val.Value);
        }
        else
        {
            query.SetParameter(position, null);
        }
    }

    public static void SetDateTime(this IQuery query, string name, DateTime? val)
    {
        if (val.HasValue)
        {
            query.SetDateTime(name, val.Value);
        }
        else
        {
            query.SetParameter(name, null);
        }
    }
}

I've tried various versions of these but none work. The code above fails with the error:

我尝试了各种版本,但都没有用。上面的代码失败并出现错误:

System.ArgumentNullException : A type specific Set(position, val) should be called because the Type can not be guessed from a null value.

I also tried simply not setting the parameter but NHibernate requires every parameter to be set. I've tried using both positional and named versions with the same results.

我也尝试过不设置参数,但NHibernate需要设置每个参数。我尝试使用位置和命名版本具有相同的结果。

Is there any way to assign null values to value typed parameters in NHibernate named queries?

有没有办法在NHibernate命名查询中为值类型参数赋值空值?

3 个解决方案

#1

OK, it turns out there are some overrides on SetParameter that allow the type to be set explicitly. For example:

好的,事实证明在SetParameter上有一些覆盖允许明确设置类型。例如:

query.SetParameter(position, null, NHibernateUtil.Int32);

The full extension methods (for Int32 and DateTime only) are now:

完整的扩展方法(仅限Int32和DateTime)现在是:

public static class QueryExtensions
{
    public static void SetInt32(this IQuery query, int position, int? val)
    {
        if (val.HasValue)
        {
            query.SetInt32(position, val.Value);
        }
        else
        {
            query.SetParameter(position, null, NHibernateUtil.Int32);
        }
    }

    public static void SetInt32(this IQuery query, string name, int? val)
    {
        if (val.HasValue)
        {
            query.SetInt32(name, val.Value);
        }
        else
        {
            query.SetParameter(name, null, NHibernateUtil.Int32);
        }
    }

    public static void SetDateTime(this IQuery query, int position, DateTime? val)
    {
        if (val.HasValue)
        {
            query.SetDateTime(position, val.Value);
        }
        else
        {
            query.SetParameter(position, null, NHibernateUtil.DateTime);
        }
    }

    public static void SetDateTime(this IQuery query, string name, DateTime? val)
    {
        if (val.HasValue)
        {
            query.SetDateTime(name, val.Value);
        }
        else
        {
            query.SetParameter(name, null, NHibernateUtil.DateTime);
        }
    }
}

#2

Another way to accomplish it is:

另一种方法是:

query.SetParameter<int?>(0, null);
query.SetParameter<DateTime?>(1, null);
...

And so on...

等等...

Notice the ? symbol that makes the primitive type nullable.

请注意?使基本类型可为空的符号。

#3

The full extension methods (for Int32 and DateTime only) with chaining are now:

现在,链接的完整扩展方法(仅适用于Int32和DateTime):

public static class QueryExtensions
{
    public static IQuery SetInt32(this IQuery __query, int __position, int? __val)
    {
        var _query = __val.HasValue ? __query.SetInt32(__position, __val.Value) : __query.SetParameter(__position, null, NHibernateUtil.Int32);

        return _query;
    }

    public static IQuery SetInt32(this IQuery __query, string __name, int? __val)
    {
        var _query = __val.HasValue ? __query.SetInt32(__name, __val.Value) : __query.SetParameter(__name, null, NHibernateUtil.Int32);

        return _query;
    }

    public static IQuery SetDateTime(this IQuery __query, int __position, DateTime? __val)
    {
        var _query = __val.HasValue ? __query.SetDateTime(__position, __val.Value) : __query.SetParameter(__position, null, NHibernateUtil.DateTime);

        return _query;
    }

    public static IQuery SetDateTime(this IQuery __query, string __name, DateTime? __val)
    {
        var _query = __val.HasValue ? __query.SetDateTime(__name, __val.Value) : __query.SetParameter(__name, null, NHibernateUtil.DateTime);

        return _query;
    }
}

#1