Solution: My generated LINQ to SQL stored procedure returns an int when it should return a table!

There are various scenarios where LINQ to SQL will not recognise the results of a stored procedure, and so not generate the correct return type. If the stored procedure is a composite stored procedure that involves a lot of temporary tables, select intos, multiple resultsets based on parameters input, or control of flow statements, then it could well be that the results returned to the code generator are not consistent with the live execution of the stored procedure. 

The usual scenario is that you call a CREATE TABLE #TempTable within your stored procedure, but when the stored proc is dragged from the Server tab to the LINQ to SQL designer, it returns an int data type, and not the generated table object that you were expecting.

There are a couple of ways around this. What you could do is create a dummy SELECT that returns a representation of the data. For example, if I wanted to return a report resultset, I might type the following:


ALTER PROCEDURE myschema.mystoredproc

@MyArg uniqueidentifier

as

begin
-- Start code added temporarily
SELECT Description=cast('Test data' as varchar(100)), Month1=cast(1 as decimal(4,1)), Month2 = cast(1 as decimal(4,1)), Month3 = cast(1 as decimal(4,1));
return;
-- End code added temporarily

CREATE #TempTable

(

Description varchar(100),

Month1 decimal(4,1),

Month2 decimal(4,1),

Month3 decimal(4,1)

)

...

more content goes here

...

end

The SELECT statement and the RETURN statement are inserted temporarily so that there are valid results returned from the stored proc that will be recognised by the LINQ to SQL generator. It works, but there is a danger in that you may get the returned types wrong.

Instead of doing this, I have found that the best thing to do is to put the following statement, SET FMTONLY OFF, into my stored procedure.


ALTER PROCEDURE myschema.mystoredproc

@MyArg uniqueidentifier

as

begin
-- Start code added temporarily
SET FMTONLY OFF;
-- End code added temporarily

CREATE #TempTable

(

Description varchar(100),

Month1 decimal(4,1),

Month2 decimal(4,1),

Month3 decimal(4,1)

)

...

more content goes here

...

end

What this now does is it returns the dataset metadata by executing the actual stored procedure. It will be recognised by LINQ to SQL and will now generate the correct return table object type.

When LINQ to SQL calls the stored procedure for generation, LINQ to SQL doesn’t want to actually execute statements or cause persistent changes to the database. So to ensure that no changes of are made to the database, it executes a

SET FMTONLY ON

So this is by design.

You can override this in your code, which is what I’ve done, to return the type you want.

But this does a full execution of your stored procedure. So if you are doing more than just performing a custom SELECT out of a temp table, you should probably remove or comment out this line after use. Otherwise, every time you try to recreate the stored proc in the LINQ to SQL designer, it will execute the stored procedure and potentially update data in your database!

5 Responses to Solution: My generated LINQ to SQL stored procedure returns an int when it should return a table!

  1. stevecat says:

    Great tip. It worked fine for me. Thanks.

  2. suyakodyazmak says:

    Thank you my friend :)

  3. deedeenyc says:

    If I had found this yesterday, it would have saved my hours!!! There are many other fixes I found that DID NOT WORK.
    Thank You….

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: