从 Subsonic.Select 调用 SQL 函数 [英] Calling an SQL function from a Subsonic.Select

问题描述

我在 subsonic 论坛上问了以下问题，但似乎只得到了一个回应，所以我想我也会在这里发帖，看看是否有人可以对这个问题有更多的了解...

I asked the following question on the subsonic forum, but only seemed to get one response, so I thought I'd post up here as well to see if anyone could shed some more light on the problem...

我希望使用选择工具(或查询工具)通过 SubSonic 创建以下 SQL 语句.它使用一个名为SPLIT()"的自定义函数:

I wish to create the following SQL statement through SubSonic using the Select tool (or Query tool) .. it uses a custom function called "SPLIT()":

SELECT * FROM VwPropertyList
哪里 VwPropertyList.idCreatedBy = 123
AND VwPropertyList.idCounty = 45
AND 29 IN (SELECT Item FROM SPLIT(DistrictGroupList, ','))

SELECT * FROM VwPropertyList
WHERE VwPropertyList.idCreatedBy = 123
AND VwPropertyList.idCounty = 45
AND 29 IN (SELECT Item FROM SPLIT(DistrictGroupList, ','))

(此 SQL 的最后一部分使用了 SPLIT 函数)

(the last part of this SQL uses the SPLIT function)

我的亚音速等效项如下所示...

My subsonic equivalent looks like the following...

将 mySelect 调暗为新的 SubSonic.Select
mySelect.From(VwPropertyList.Schema)
mySelect.Where(VwPropertyList.Columns.IdCreatedBy).IsEqualTo(123)
mySelect.And(VwPropertyList.Columns.IdCounty).IsEqualTo(45)
mySelect.And(29).In(New SubSonic.Select("Item").From("SPLIT("&VwPropertyList.Columns.DistrictGroupList&", ',')"))

Dim mySelect As New SubSonic.Select
mySelect.From(VwPropertyList.Schema)
mySelect.Where(VwPropertyList.Columns.IdCreatedBy).IsEqualTo(123)
mySelect.And(VwPropertyList.Columns.IdCounty).IsEqualTo(45)
mySelect.And(29).In(New SubSonic.Select("Item").From("SPLIT(" & VwPropertyList.Columns.DistrictGroupList & ", ',')"))

由于最后一部分，这不起作用..如何将AND 29 IN (SELECT Item FROM SPLIT(DistrictGroupList, ','))"添加到我的 Subsonic.Select 中?

This doesn't work though due to the last part .. how can I add "AND 29 IN (SELECT Item FROM SPLIT(DistrictGroupList, ','))" into my Subsonic.Select ?

我从 subsonic 论坛得到的回复建议我取消 Subsonic.Select 并用硬编码的 InlineQuery() 语句替换.. 像:

The response I got from the subsonic forum suggested I do away with Subsonic.Select and replace with hard-coded InlineQuery() statements .. like:

Dim SQL as String = "Select " &VwPropertyList.Columns.Item
SQL = SQL&从" &VwPropertyList.Schema.TableName
SQL =SQL &哪里" &VwPropertyList.Columns.IdCreatedBy &"= @CreatedBy "
SQL = SQL & " 和 " & VwPropertyList.Columns.IdCounty & " =@County "
SQL = SQL & " 并且@DistrictGroup IN(从中选择项目SPLIT(DistrictGroupList,',')"

Dim SQL as String = "Select " & VwPropertyList.Columns.Item
SQL = SQL & " From " & VwPropertyList.Schema.TableName
SQL = SQL & " Where " & VwPropertyList.Columns.IdCreatedBy & " = @CreatedBy "
SQL = SQL & " And " & VwPropertyList.Columns.IdCounty & " = @County "
SQL = SQL & " And @DistrictGroup IN (Select Item From SPLIT(DistrictGroupList,',')"

项目 =SubSonic.InlineQuery().ExecuteTypedList(Of我的项目)(SQL, 123,45,29)

Items = SubSonic.InlineQuery().ExecuteTypedList(Of MyItem)(SQL, 123,45,29)

如果可能的话，我更愿意使用 SubSonic.Select，以便我可以利用分页功能等.

I would prefer to use SubSonic.Select if possible though so that I can avail of the paging functionality etc.

有什么想法吗?

推荐答案

您可以执行 John 的建议，也可以使用我们的 InlineQuery 编写 SQL - 它允许您编写原始 SQL 并传入参数:

You could do John's suggestion or you could write the SQL using our InlineQuery - which allows you to write raw SQL and pass in params:

var qry=new InlineQuery("SELECT * FROM table WHERE column=@param",value)

var qry=new InlineQuery("SELECT * FROM table WHERE column=@param",value)

这篇关于从 Subsonic.Select 调用 SQL 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！