LINQ to SQL — Can't modify return type of stor

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

Okay, I found the problem... kind of. I had changed the name of the table "Assignments" and forgot to update the stored procudure, so the DBML designer was confused. BUT even after I updated the stored procedure, deleted it from the DBML designer and readded it, it wasn't working!

This is nearly the same problem discussed here: http://forums.asp.net/t/1231821.aspx.

It only worked when I deleted the stored procedure from the database and recreated it, and deleted it from the DBML designer, recompiled, restarted Visual Studio, and added it again. This is the second time I've run into "refresh" problems with the Visual Studio DBML designer...

Answer 2

OK, I didnt want to be changing anything in my Designer.cs code, I knew there was a different problem and it wasnt related to my stored procedure (I wasnt using temp table anyway).

Simply deleting the sp from the database and updating the model wasnt helping at all. New model created still had the same problems...

What I found is that for some reason a copies of my sp were created in DatabaseModel -> Function Imports.

What I did, I deleted the duplicated objects in Function Imports and updated the model. It worked!

Regards, Chris

Answer 3

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 4

Thanks @Rubenz, I was also using FTS (Full-Text Search) in a stored procedure and your steps worked.

I commented the FTS section from the stored procedure, added the stored procedure to .dbml, and then uncommented the FTS section back to original.

Answer 5

I realize that this is an old question but the above suggestions pointed me in the right direction but did not work in my case. I ended up editing the dbml file with the XML editor in Visual Studio as suggested above.

Once in the file, look for the Function section for the stored procedure. You will most likely not see the section – ElementType – which defines the return type. I began to edit the fields from another Function (stored procedure) and found that this was too tedious and may introduce issues.

I decided to delete all the Column definitions from the ElementType - but leave the ElementType section and save the file. I then deleted the stored procedure from the designer and re-added it. It then filled in the correct columns within the ElementType. Worked beautifully.

Answer 6

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;