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.
精彩评论