模型优先 - 存储过程全文检索 - 返回而不是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.

下面是code存储过程：

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

我没接触这个项目先做模型。我将自己的存储过程来我的模型通过右击和pressing：添加新的＆GT;功能导入...

我设定功能进口的名称，所选择的存储过程，选择了返回一个集合来实体：（所需的数据类型在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＆LT;用户＆GT; 。它说，返回类型为存储过程的int。我收到以下错误：

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

无法键入'诠释'隐式转换为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:

更新：埃迪在指定的DBML在存储过程中的阻力后的评论，你可以设置的返回类型（我选择的是用户对象类型）。这工作。我宁愿不这样做虽然。我将它作为一个临时的解决方案，但我有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的返回类型并没有什么。它没有下降downlist，什么都没有。

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

的类型为用户实体，然后也许写的搜索语句

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.

请让我知道。

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