How to retrieve multiple rows from a stored procedure with Linq to SQL?

Question

I've recently started to work with Linq to SQL and wondered how to get multiple rows as a result of executing a stored procedure,here's a simple sp i want to work with:

CREATE PROCEDURE gsp_ftsmultiple
  @SearchKey varchar(100)
AS

BEGIN

  SET NOCOUNT ON;

  SELECT Label, theContent 
    FROM FtsTest 
   WHERE FREETEXT( theContent, @SearchKey )

END

Executing this sp from management studio is fine,but the corresponding method in the generated context class returns a int value, and I don't know how to get the actual rows, tried using the OUT parameters, but they can return only one result.

Answer 1

You'd might be better off just running the query in itself, seeing as you're not really doing anything that requires a procedure. Just make sure you escape the search key properly.

Answer 2

Try creating the fulltext predicates as wrapper functions instead of sprocs, like this. Worked for me.

Answer 3

I think all you need to do is make a class that matches your result schema and make sure the function in the dbml has it as a return type.

see here on msdn

Answer 4

When sqlmetal generates the code for this procedure, it doesn't pass a valid argument for the query to work.

Try to put something like this on the top of you SP:

IF @SearchKey IS NULL OR LEN(@SearchKey) = 0
BEGIN
    SELECT N'' AS Label, N'' AS theContent WHERE 1 = 0
END

[...]

It will now deduce that 2 NVARCHAR columns are the usual output and generate code accordingly

PS. Code off my head, untested

Mike

Answer 5

I tested the following code snippet and it worked fine. If you put the generated code here, I can take a look and check where is the problem. Anyway, you can create a partial class as same name as your generated data context class and add the desired method manually as follows.

public partial class FooDataContext: System.Data.Linq.DataContext
{

    [System.Data.Linq.Mapping.Function(Name = "GetFoo")]
    public System.Data.Linq.ISingleResult<Foo> GetFoo()
    {
        System.Data.Linq.IExecuteResult res = base.ExecuteMethodCall(this, (System.Reflection.MethodInfo)System.Reflection.MethodInfo.GetCurrentMethod());
        return (System.Data.Linq.ISingleResult<Foo>)res.ReturnValue;
    }

}