LINQ to SQL -- Can't modify return type of stored procedure

I've also seen this problem several times and while I don't know what causes it, I've come across a pretty easy way to get past it. It involves manually editing the xml within the .dbml file, but it's a pretty simple edit.

Right-click on your Data Context's .dbml file in the Solution Explorer (not the .layout file nor the designer.cs file) and open it with the XML Editor. You should find your stored procedure listed in a <Function> ... </Function> block. You should also find the custom class you would like to set as the Return Type listed in a <Type> ... </Type> block.

Step one is to give your custom class an identifier. You do so by adding an "Id" tag, like this, making sure that it's unique within the dbml file:

<Type Name="MyCustomClass" Id="ID1">

Step two is to tell your function to use the newly ID'd type as the Return Type. You do so by replacing the line in your <Function> block that looks like

<Return Type="System.Int32" />

with

<ElementType IdRef="ID1" />

Save the file, exit, and rebuild. Done. Re-open the .dbml file in design mode to verify: Your procedure will now have the custom class set as the Return Type.

I had a similar mapping problem, but I found the culprit in my case.

If your procedure or any subprocedure that gets called has temporary objects like

CREATE TABLE #result (
   ID INT,
   Message VARCHAR(50)
)

then you're in trouble, even if you don't select anything of these temporaries.

The mapper has a general problem with these temporary objects, because the type can be changed outside the procedure in the session context. Temporary objetcs are not typesafe for the mapper and he refuses the usage os them.

Replace them by table variables and you're back in business

DECLARE @result AS TABLE (
   ID INT,
   Message VARCHAR(50)
)

I followed the link provided by Tony for a better solution (same answer as Arash's)

• do read the blog, especially the last part, for there is a thing to consider when adding SET FMTONLY OFF in your stored procedure.

When you add

 SET FMTONLY OFF

in the beginning of the stored procedure and load it to DBML,
LINQ2SQL would execute the actual stored procedure.

To get the correct return table object type,
said stored procedure must return something when called w/o parameter(s).
That means:
1. Have default value for all input parameters
2. Make sure SP returns at least a row of data -- this is where I stumbled

create table #test ( text varchar(50) );
insert into #test (text) values ('X'); -- w/o this line, return type would be Int32
select * from #test; -- SP returns something, proper object type would be generated
return;