开发者

How to get rank from full-text search query with Linq to SQL?

开发者 https://www.devze.com 2022-12-29 10:39 出处:网络
I am using Linq to SQL to call a stored procedure which runs a full-text search and returns the rank plus a few specific columns from the table Article.The rank column is the rank returned from the SQ

I am using Linq to SQL to call a stored procedure which runs a full-text search and returns the rank plus a few specific columns from the table Article. The rank column is the rank returned from the SQL function FREETEXTTABLE(). I've added this sproc to the O/R designer with return type Article.

This is working to get the columns I need; however, it discards the ranking of each search result. I'd like to get this information so I can display it to the user.

So far, I've tried creating a new class RankedArticle which inherits from Arti开发者_如何学Ccle and adds the column Rank, then changing the return type of my sproc mapping to RankedArticle. When I try this, an InvalidOperationException gets thrown:

Data member 'Int32 ArticleID' of type 'Heap.Models.Article' is not part of the mapping for type 'RankedArticle'. Is the member above the root of an inheritance hierarchy?

If I let the O/R designer set the sproc's own return type, it returns an int rather than a "SearchArticlesByKeywordResult" object. I'm not sure why this is, perhaps because the sproc is returning a union? Here is my procedure:

BEGIN
SET NOCOUNT ON;
(
    SELECT ftt.[rank] as [Rank], ArticleID, Subject
    FROM Article
    INNER JOIN FREETEXTTABLE( Article, (Subject, Body), @KeywordList ) AS ftt
    ON ftt.[key] = Article.ArticleID

    UNION

    SELECT ftt.[rank] as [Rank], Article.ArticleID as ArticleID, Article.Subject as Subject
    FROM Article
    INNER JOIN Solution ON Solution.ArticleID = Article.ArticleID
    INNER JOIN FREETEXTTABLE( Solution, Body, @KeywordList ) AS ftt
    ON ftt.[key] = Solution.SolutionID
)
ORDER BY [Rank] DESC
END

I can't seem to find any other questions or Google results from people trying to get the rank column, so I'm probably missing something obvious here.


Go to sql management studio and call your stored procedure with a @keywordlist as null. Do you see any error? Linq to SQL, in addition to other things, calls the sproc with null to discover the return columns.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号