使用内置的SQL“转换"；休眠标准中的功能 [英] Using in-built sql "Convert" function in nhibernate criteria

问题描述

我想利用SQL Server 2008中的Convert函数，以便可以在DateTime列上进行搜索.

I want to make use of the Convert function in SQL Server 2008 so I can search on a DateTime column.

建议的SQL看起来像这样:

The proposed SQL would look something like this:

SELECT (list of fields) FROM aTable
WHERE CONVERT(VARCHAR(25), theColumn) LIKE '%2009%'

这是尝试模拟转化调用的条件的一部分:

Here is part of the criteria that tries to emulate the call to convert:

Projections.SqlFunction("CONVERT", 
  NHibernateUtil.String, 
  Projections.Constant("varchar(25)"), 
  Projections.Property(searchCol))

搜索列将被动态选择，因此无法在查询中进行硬编码.

The search column would be dynamically selected so it cannot be hard coded in a query.

问题在于，当SQL由nhibernate生成时，它以字符串形式传入数据类型时，它周围应该没有引号.

The problem is that when the SQL is generated by nhibernate, its passing in the data type as a string, when there shouldn't be any quotes around it.

因此生成的sql如下:

So the generated sql looks like:

(convert(@p3, this_.theColumn) LIKE @p4

何时需要:

(convert(varchar(25), this_.theColumn) LIKE @p4

我可以肯定问题出在Projections.Constant("varchar(25)")上，但是我不知道正确的投影语法可以使它工作.

I am definitely sure the problem is with Projections.Constant("varchar(25)") but I do not know the correct projections syntax to make it work.

推荐答案

如果您可以接受CAST而不是CONVERT (而且我几乎可以肯定)，那么那里是更直接的解决方案.

If you could accept the CAST instead of CONVERT (And I am almost sure that you can), then there is more straightforward solution.

让我们调用抽象，而不是调用与SQL Server相关" 函数，该抽象应该适用于大多数数据库服务器(基于受支持的NHibernate dilacts)

Instead of calling "SQL Server related" function, let's call the abstraction, which should be working on most DB Servers (based on supported NHibernate dilacts)

Projections.Cast(NHibernateUtil.String, Projections.Property(searchCol))

所以WHERE子句中使用的Restriction可能看起来像这样:

So the Restriction used in a WHERE clause could look like this:

Restrictions
    .Like (
        Projections.Cast(NHibernateUtil.String, Projections.Property(searchCol))
        , "2009"
        , MatchMode.Anywhere
    )

NHibernate使用SQL Server方言生成的结果将是:

And the result generated by NHibernate, using the SQL Server dialect would be:

WHERE cast( this_.theColumn as NVARCHAR(255)) like @p1 ... @p1=N'%2009%'

这篇关于使用内置的SQL“转换"；休眠标准中的功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！