Chain LINQ IQueryable, and end with Stored Procedure

Question

I'm chaining search criteria in my application through IQueryable extension methods, e.g.:

public static IQueryable<Fish> AtAge (this IQueryable<Fish> fish, Int32 age)
{ 
    return fish.Where(f => f.Age == age);
}

However, I also have a full text search stored procedure:

CREATE PROCEDURE [dbo].[Fishes_FullTextSearch]
@searchtext nvarchar(4000),
@limitcount int
AS
SELECT Fishes.* FROM Fishes
  INNER JOIN CONTAINSTABLE(Fishes, *, @searchtext, @limitcount)
  AS KEY_TBL ON Fishes.Id = KEY_TBL.[KEY]
  ORDER BY KEY_TBL.[Rank]

The stored procedure obviously doesn't return IQueryable, however, is it possible to somehow limit the result set for the stored procedure using IQueryable's?

I'm envisioning something like .AtAge(5).AboveWeight(100).Fishes_FulltextSearch("abc").

In this case, the fulltext search should execute on a smaller subset of my Fishes table (narrowed by Age and Weight).

Is something like this possible? Sample code?

Answer 1

I think that it might work if you

• create another extension method for Fishes_FulltextSearch("abc")
• inside it, you insert the result set of the previos methods (AtAge and AboveWeight) to a temporal table
• work with this temporal table inside the stored procedure
• return from the stored procedure the desired result set
• and return from the new extension method the result set of the stored procedure.

Hope this helps.

Answer 2

If you can use ContainsTable in a View, -that- returns IQueryable...