Linq to SQL (designer) does not understand stored procedure

Question

Why can the Linq to SQL designer not understand the following stored procedure and import it correctly?

CREATE PROCEDURE dbo.MartinTest
    @parameter1 INTEGER

AS
    SET NOCOUNT ON

    SELECT C1, C2, C3 
    INTO #myTempTable
    FROM MyTable

    SELECT C1, C2, C3
    FROM MyOtherTable INNER JOIN #myTempTable ON ....

    RETURN

When I use the designer to import this stored procedure, it sets the return type as "none", even though the SP clearly returns one or more rows of data.

Whereas, if I remove the first select statement and instead simply return the content of the original table...

CREATE PROCEDURE dbo.MartinTest
    @parameter1 INTEGER

AS
    SET NOCOUNT ON

    SELECT C1, C2, C3
    FROM MyTable

    RETURN

...the designer works fine and determines the correct return type.

BTW: I know I can manually code the call to the stored procedure so I am not looking for a work-around, I am more interested as to whether this is a bug in the designer for Linq to SQL or whether it is something else. I am using VS2008 SP1.

Thanks.

Answer 1

This is not technically an issue with the Linq to SQL designer, it is an issue with SQL Server. Linq to SQL designer uses SET FMTONLY ON to get the result set information of a stored procedure. Unfortunately, this method does not work when the stored procedure has a temporary table.

There is a small back and forth regarding this in the first few comments of ScottGu's blog post.

Answer 2

You could manually SET FMTONLY OFF at the beginning of the stored procedure. Probably comment it out for normal use, and only uncomment it when generating your LINQ classes.