在 nHibernate 的 CreateSQLQuery 中使用 SQL Server 2008 Geography 类型

2022-01-02 00:00:00 sql-server nhibernate

我正在尝试使用 nHibernate (2.0.1GA) 发出 SQL 更新语句，如下所示:

I am trying to issue a SQL update statement with nHibernate (2.0.1GA) like this:

sqlstring = string.Format("set nocount on;update myusers set geo=geography::Point({0}, {1}, 4326) where userid={2};", mlat, mlong, userid);
_session.CreateSQLQuery(sqlstring).ExecuteUpdate();

但是我收到以下错误:geography@p0"不是可识别的内置函数名称.

However I receive the following error: 'geography@p0' is not a recognized built-in function name.

我以为 CreateSQLQuery 只会传递我给它的 SQL 并执行它...你猜不是.关于如何在 nHibernate 的上下文中做到这一点的任何想法?

I thought CreateSQLQuery would just pass the SQL I gave it and execute it...guess not. Any ideas on how I can do that within the context of nHibernate?

推荐答案

我很确定我可以告诉你发生了什么，但我不知道是否有解决方案.

I'm pretty sure I can tell you what is happening, but I don't know if there is a fix for it.

我认为问题在于 ':' 字符被 NHibernate 用于创建命名参数.你的表情变成了:

I think the problem is that the ':' character is used by NHibernate to create a named parameter. Your expression is getting changed to:

set nocount on;update myusers set geo=geography@p0({0}, {1}, 4326) where userid={2};

@p0 将是一个 SQL 变量.不幸的是，我找不到任何用于转义冒号的文档，因此它们不被视为命名参数.

And @p0 is going to be a SQL variable. Unfortunately I can't find any documentation for escaping colons so they are not treated as a named parameter.

如果存在转义字符(我对 NHibernate 源代码的快速浏览没有找到；如果您想花更多时间搜索，命名参数在 NHibernate.Engine.Query.ParameterParser 中处理)，那么您可以使用

If an escape character exists (my quick skim of the NHibernate source didn't find one; Named parameters are handled in NHibernate.Engine.Query.ParameterParser if you want to spend a little more time searching), then you could use that.

其他解决方案:

  • 向源添加转义字符.然后您可以使用 NHibernate 的修改版本.如果你这样做，你应该将你的补丁提交给团队，这样它就可以包含在真实的东西中，而你不必维护源代码的修改版本(没意思).
  • 在您的数据库中创建一个返回 geography::Point 的用户定义函数，然后调用您的函数而不是标准 SQL 函数.这似乎是最快/最简单的启动和运行方式，但也有点像创可贴.
  • 看看 NHibernate Spatial 中是否有什么东西可以让您以编程方式添加geography::Point() [或编辑该项目的代码以添加一个并将补丁提交给该团队].
  • Add an escape character to the source. You can then use a modified version of NHibernate. If you do this, you should submit your patch to the team so it can be included in the real thing and you don't have to maintain a modified version of the source (no fun).
  • Create a user defined function in your DB that returns a geography::Point, then call your function instead of the standard SQL function. This seems like the quickest/easiest way to get up and running, but also feels a bit like a band-aid.
  • See if there is something in NHibernate Spatial that will let you programmatically add the geography::Point() [or edit the code for that project to add one and submit the patch to that team].

相关文章