LINQ to SQL - 找不到“选择”

I have been looking to solve this for hours with no luck.

我一直在寻找解决这个问题几个小时没有运气。

I have a very simple scenario - I am trying the following in LINQ-to-SQL

我有一个非常简单的场景 - 我在LINQ-to-SQL中尝试以下内容

 var qry = (from row 
   in DBConnect.SEARCHhash("test", "1234")  
    select row);

Where the SEARCHhash is a stored procedure - Unfortunately, I can't even put .ToList() at the end because IntelliSense shows the following error

SEARCHhash是一个存储过程 - 不幸的是,我甚至无法将.ToList()放在最后,因为IntelliSense显示以下错误

Could not find an implementation of the query pattern for source type 'void'. 'Select' not found.

找不到源类型'void'的查询模式的实现。找不到“选择”。

All my other stored procedures work fine, just this one doesn't work.

我的所有其他存储过程都可以正常工作,只是这个不起作用。

Then I remember that when I dragged and dropped the stored procedure to Designer I got the following warning

然后我记得当我将存储过程拖放到Designer时,我得到了以下警告

The return types for the following stored procedures could not be detected. Set the return type for each stored procedure in the Properties window.

无法检测到以下存储过程的返回类型。在“属性”窗口中设置每个存储过程的返回类型。

So, I opened the properties and clicked on dropdown and only available option was int(int32) so selected int

所以,我打开属性并单击下拉列表,只有可用选项为int(int32),因此选择了int

But now then I started getting the following error

但是现在我开始收到以下错误

Could not find an implementation of the query pattern for source type 'int'. 'Select' not found

无法找到源类型“int”的查询模式的实现。找不到“选择”

The following is the code from Designer

以下是Designer的代码

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.SEARCHhash")]
        public int SEARCHhash([global::System.Data.Linq.Mapping.ParameterAttribute(Name="Keywords", DbType="VarChar(540)")] string keywords, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="UserId", DbType="NVarChar(128)")] string userId)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), keywords, userId);
            return ((int)(result.ReturnValue));
        }

I also started comparing my stored procedures and I noticed that this stored procedure has JOIN in it and of course it is using `FREETEXT'

我也开始比较我的存储过程,我注意到这个存储过程中有JOIN,当然它使用`FREETEXT'

Here is my stored procedure code:

这是我的存储过程代码:

SELECT 
    [dbo].[SEARCHMLinksData].PageID, 
    [dbo].[SEARCHMLinksData].PageTitle, 
    [dbo].[SEARCHMLinksData].PageDescription, 
    [dbo].[SEARCHMLinksData].PageType
FROM 
    [dbo].[SEARCHMLinksData] 
LEFT JOIN 
    [dbo].[SEARCHMLinksTags] ON [SEARCHMLinksData].PageID = [SEARCHMLinksTags].PageID
WHERE 
    (FREETEXT([SEARCHMLinksData].*, @Keywords) OR  
     FREETEXT([SEARCHMLinksTags].*,@Keywords) ) 
    AND
    ([SEARCHMLinksData].UserID = @UserId OR [SEARCHMLinksTags].[user_id] = @UserId)

Any idea or help will be appreciated

任何想法或帮助将不胜感激

Cheers

In addition, I tried so many things including the following https://www.mssqltips.com/sqlservertip/1542/using-stored-procedures-with-linq-to-sql/ The return types for the following stored procedures could not be detected Could not find an implementation of the query pattern for source type 'System.Data.Entity.DbSet'

此外,我尝试了很多东西,包括以下https://www.mssqltips.com/sqlservertip/1542/using-stored-procedures-with-linq-to-sql/以下存储过程的返回类型不能检测到无法找到源类型'System.Data.Entity.DbSet'的查询模式的实现

1 个解决方案

#1

Just in case if anyone is looking for the solution

以防万一有人在寻找解决方案

It is not possible to use FREETEXT in LINQ and that was the reason I created the Stored Procedure although in my case it seems like it was not possible to use the Stored Procedure either for some reason (i don't know why) Although the solution I found was to

在LINQ中不可能使用FREETEXT,这就是我创建存储过程的原因,尽管在我的情况下,由于某种原因(我不知道为什么)似乎无法使用存储过程尽管解决方案我发现是

create a function and call the function using LINQ

创建一个函数并使用LINQ调用该函数

It works for me now

它现在对我有用

One thing I had to do in the function though that - Instead of using

我不得不在函数中做的一件事 - 而不是使用

(FREETEXT([SEARCHMLinksData].*,@Keywords)

I had to use

我不得不使用

(FREETEXT([SEARCHMLinksData].Title,[SEARCHMLinksData].Description,@Keywords)

Means, list all the columns getting indexed by their [names] as it didn't take wildcard

意味着,列出由[name]索引的所有列,因为它没有使用通配符

Why all this? I have no idea but that's the only solution I could found

为什么这一切?我不知道,但这是我能找到的唯一解决方案

Hope this helps someone.

希望这有助于某人。

#1