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

Question

When I drag a particular stored procedure into the VS 2008 dbml designer, it shows up with Return Type set to "none", and it's read only so I can't change it. The designer code shows it as returning an int, and if I change that manually, it just gets undone on the next build.

But with another (nearly identical) stored procedure, I can change the return type just fine (from "Auto Generated Type" to what I want.)

I've run into this problem on two separate machines. Any idea what's going on?

Here's the stored procedure that works:

USE [studio] GO /****** Object:  StoredProcedure [dbo].[GetCourseAnnouncements]    Script Date: 05/29/2009 09:44:51 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[GetCourseAnnouncements]     @course int AS SELECT * FROM Announcements WHERE Announcements.course = @course RETURN 

And this one doesn't:

USE [studio] GO /****** Object:  StoredProcedure [dbo].[GetCourseAssignments]    Script Date: 05/29/2009 09:45:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[GetCourseAssignments]     @course int AS SELECT * FROM Assignments WHERE Assignments.course = @course ORDER BY date_due ASC RETURN 

Answer 1

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.

Answer 2

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) )