views:

108

answers:

1

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

+1  A: 

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.

Raj Kaimal
The sproc was erroring on a null input, I hadn't considered that. I made it return the normal result list on null input, and now an auto-generated result type gets generated when I add the sproc to the O/R designer. Thanks!
Stephen Jennings