模型第一 - 全文搜索存储过程 - 返回Int而不是List / IENumerable [英] Model First - Full Text Search Stored Procedure - Returns Int instead of List/IENumerable

问题描述

我已经寻找了几天的解决方案，但找不到一个。

我正在创建一个存储过程来使用全文搜索来搜索表。然后，我将这些存储过程中的15个结果合并到按排名排列的列表中。

这是存储过程的代码：

  SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
 
 ALTER PROCEDURE [dbo ] [SpAdvancedSearchICAPUsers] 
 @searching nvarchar（100）=''
 AS 
 BEGIN 
 SET NOCOUNT ON; 
设置FMTONLY OFF; 
 
 DECLARE @SQLQuery AS NVarchar（max）
 
 IF @searching IS NULL OR @searching =''
 BEGIN 
 SET @SQLQuery ='SELECT （所有字段名称以逗号分隔）FROM用户
 END 
 ELSE 
 BEGIN 
 SET @SQLQuery ='SELECT（所有字段名称以逗号分隔），fts.Rank FROM用户作为p JOIN 
 FREETEXTTABLE（用户，（搜索列表用逗号分隔的字段），@searching）
作为fts在p.userID = fts。[KEY] WHERE 1 = 1'
 END 
 
 EXEC @SQLQuery 
 END 
  

接近这个项目做模范先。我通过右键单击并添加我的存储过程到我的模型：添加新>函数导入...

我设置函数名称的import，选择存储过程，选择返回集合到实体：（SP返回的所需数据类型，在这种情况下为用户）。

当我使用这样的存储过程：

  newSearch.Users = db.SpAdvancedSearchICAPUsers（search）; // search是一个字符串
  

newSearch.Users 是 IENumerable< Users> 。它表示返回类型是存储过程的int。我收到以下错误：

不能将类型'int'隐式转换为'System.Collections.Generic.IEnumerable;

我也尝试添加一个参数定义变量，就像下面这样声明 @SQLQuery

 声明@ParamDefinition作为NVarchar（4000）
  

然后我设置它，并尝试运行如下：

  Set @ ParamDefinition ='@searchingnvarchar（100）'
 
执行sp_executesql @SQLQuery，@ParamDefinition，@searching 
  

@searching 是传入搜索的字符串。

我甚至试过将我的所有表拖到一个DBML文件中，因为我以前使用过这样的存储过程。当我将SP拖到表格上时，我收到以下消息：

更新：Eddie在注释中指出，在DBML中拖动存储过程之后，您可以设置返回类型（我选择了User对象类型）。这个工作。我宁愿不这样做。我会把它作为一个临时解决方案，但我有15个存储过程我必须这样做。

有谁知道如何使用EDMX文件而不是DBML从存储过程获取正确的输出？当我在EDMX中右键单击后查看属性，并选择映射详细信息>存储过程/函数。 EDMX中的返回类型没有任何东西。它没有下拉列表，没有。

解决方案

我不知道我是否打在这里，但如果你正在做一些类似

 newSearch.Users = db.SpAdvancedSearchICAPUsers（search）; 

和

 newSearch.Users 

的类型为用户实体，然后可能将搜索语句写入

 将@SQLQuery ='SELECT p。* FROM Users as p JOIN 
 FREETEXTTABLE用户（（搜索列表用逗号分隔的字段），@searching）
作为fts在p.userID = fts。[KEY] WHERE 1 = 1 
 ORDER BY fts。[Rank]'
  

将用于简单的事实，它返回字段用户实体期望（即：无等级字段）。

为了更好地诊断是否符合这种情况，问题是：

发送一个空的搜索字段时会发生同样的问题吗？

ie：

  newSearch.Users = db.SpAdvancedSearchICAPUsers（''）; 
  

如果它适用于后一种情况，那么上述的改变也许会。 b
$ b

请让我知道。

I have looked for a few days for a solution but can't find one.

I am creating a stored procedure to search a table using fulltext search. I will then combine the result from 15 of these stored procedures into a list ordered by their ranking.

Here is the code for the stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SpAdvancedSearchICAPUsers]
    @searching nvarchar(100) = ''
AS
BEGIN
    SET NOCOUNT ON;
    Set FMTONLY OFF;

    DECLARE @SQLQuery AS NVarchar(max)

    IF @searching IS NULL OR @searching = ''
    BEGIN
        SET @SQLQuery = 'SELECT (all field names listed separated by commas) FROM Users'
    END
    ELSE
    BEGIN
        SET @SQLQuery = 'SELECT (all field names listed separated by commas), fts.Rank FROM Users as p JOIN
                    FREETEXTTABLE(Users, (fields to search for listed separated by commas), @searching) 
                    as fts on p.userID = fts.[KEY] WHERE 1=1'
    END

    EXEC @SQLQuery
END

I did approached this project doing Model first. I added my stored procedures to my model by right clicking and pressing: Add New > Function Import...

I set the name of the function import, selected the stored procedure, selected the "Returns a Collection Of" to Entities: (desired data type the SP returns, in this case, Users).

When I use the stored procedure like this:

newSearch.Users = db.SpAdvancedSearchICAPUsers(search); //search is a string

newSearch.Users is an IENumerable<Users>. It says the return type is an int for the stored procedure. I get the following error:

Cannot implicitly convert type 'int' to 'System.Collections.Generic.IEnumerable;

I have also tried adding a parameter definition variable as such right below declaring @SQLQuery like this:

Declare @ParamDefinition As NVarchar(4000)

and then I set it and try and run it like this:

Set @ParamDefinition = '@searchingnvarchar(100)'

Exec sp_executesql @SQLQuery, @ParamDefinition, @searching

@searching is the string which is passed in to search.

I have even tried dragging all of my tables into a DBML file, because I've used stored procedures this way successfully before. When I dragged in the SP to the table I get this message:

UPDATE: Eddie in the comments specified that in the DBML after your drag in the stored procedure, you can set the return type (I chose the User object type). This works. I would rather not do this though. I'll have it as a temporary solution but I have 15 stored procedures I'd have to do that to.

Does anyone know how to get the correct output from the stored procedure using the EDMX file instead of a DBML? When I look at the properties after right clicking in the EDMX and selection "Mapping Details > Stored Procedures / Functions". The return type in the EDMX doesn't have anything. It has no drop downlist, nothing.

解决方案

I'm not sure if I'm hitting the spot here, but if you are doing something like

newSearch.Users = db.SpAdvancedSearchICAPUsers(search);

and

newSearch.Users

is of type User Entity, then perhaps writing the search statement as

    Set @SQLQuery = 'SELECT p.* FROM Users as p JOIN
                     FREETEXTTABLE(Users, (fields to search for listed separated by commas), @searching) 
                     as fts on p.userID = fts.[KEY] WHERE 1=1
                     ORDER BY fts.[Rank]'

would work for the simple fact that it is returning the fields a User Entity expects (i.e.: no rank field).

To better diagnose whether this is the case or not, the question turns to:

Does the same problem happen when you send an empty search field?

i.e.:

newSearch.Users = db.SpAdvancedSearchICAPUsers('');

If it works for the latter case, then perhaps the aforementioned change would do.

Please let me know.

这篇关于模型第一 - 全文搜索存储过程 - 返回Int而不是List / IENumerable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！