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

问题描述

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

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

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

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

我确实是先做 Model 来接触这个项目的.我通过右键单击并按下将我的存储过程添加到我的模型中:Add New >函数导入...

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...

我设置了函数导入的名称，选择了存储过程，为实体选择了返回集合":(SP 返回的所需数据类型，在本例中为用户).

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 是一个 IENumerable.它说返回类型是存储过程的 int.我收到以下错误:

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

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

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

我也尝试在声明 @SQLQuery 的正下方添加一个参数定义变量，如下所示:

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 是传入搜索的字符串.

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

我什至尝试将我的所有表拖到 DBML 文件中，因为我以前已经成功地以这种方式使用过存储过程.当我将 SP 拖到桌子上时，我收到以下消息:

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在注释中指定，在你拖入存储过程后的DBML中，可以设置返回类型(我选择了User对象类型).这有效.我宁愿不这样做.我将把它作为一个临时解决方案，但我有 15 个存储过程，我必须这样做.

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.

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

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);

并且

newSearch.Users

是 User Entity 类型，那么可能将搜索语句写为

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?

即:

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

如果它适用于后一种情况，那么上述更改可能会起作用.

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

请告诉我.

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