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!