Linq to SQL Select in Stored Proc

Question

I have a stored proc called via LINQ to SQL the stored proc calls another stored proc within it and has a SELECT to output the result. the SELECT result doesnt' get returned to my LINQ to SQL, I can only get the int result value. How can I get the select result of a stored proc with is within a stored proc

Answer 1

The LINQ designer is not able to accurately parse your stored procedure to determine the schema. One work around is to temporarily change your stored proc to a simple select of the proper schema. Remap it in the designer. Then, change the stored proc back to the original. This way the designer is able to figure out the schema and properly map it to the correct entity.

Answer 2

When you call the stored procedure from Sql Server Management Studio, does it return multiple rowsets?

If that's the case, using the stored procedure from LINQ is not trivial, see this article.

Reading multiple results is easy using the NextResult method from DataReader:

var com = yourConnection.CreateCommand();
com.CommandText = "execute dbo.YourSp 'par1'";
var read = com.ExecuteReader();
// Move to second result set and read it
read.NextResult();
while (read.Read()) {
    Console.WriteLine(read["MyField"]);
}