LINQ to SQL - 无法修改存储过程的返回类型

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.

当我将特定存储过程拖到VS 2008 dbml设计器中时,它会显示返回类型设置为“none”,并且它是只读的,因此我无法更改它。设计器代码将其显示为返回int,如果我手动更改它,它将在下一个构建时撤消。

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

11 个解决方案

#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.

我也曾多次看到这个问题,虽然我不知道是什么原因造成的,但我遇到了一个很简单的方法。它涉及在.dbml文件中手动编辑xml,但这是一个非常简单的编辑。

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.

右键单击解决方案资源管理器中的数据上下文的.dbml文件(不是.layout文件,也不是designer.cs文件),然后使用XML编辑器打开它。您应该在 ... 块中找到列出的存储过程。您还应该找到要设置为 ... 块中列出的返回类型的自定义类。

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:

第一步是为您的自定义类提供标识符。您可以通过添加“Id”标记来实现此目的,确保它在dbml文件中是唯一的:

<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

第二步是告诉你的函数使用新的ID类型作为返回类型。您可以通过替换块中看起来像这样的行来实现

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

保存文件,退出并重建。完成。在设计模式下重新打开.dbml文件以验证:您的过程现在将自定义类设置为返回类型。

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

#3

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

我按照Tony提供的链接获得了更好的解决方案(和Arash一样的答案)

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

请阅读博客,特别是最后一部分,因为在您的存储过程中添加SET FMTONLY OFF时需要考虑一下。

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.

在存储过程的开头并将其加载到DBML,LINQ2SQL将执行实际的存储过程。

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

要获得正确的返回表对象类型,所述存储过程必须在调用w / o参数时返回一些内容。这意味着:1。具有所有输入参数的默认值2.确保SP至少返回一行数据 - 这是我偶然发现的地方

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;

#4

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!

好的,我发现了问题......有点儿。我更改了“Assignments”表的名称并忘记更新存储的procudure,因此DBML设计者感到困惑。即使我更新了存储过程后,将其从DBML设计器中删除并重新读取,它也无法正常工作!

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

这几乎与此处讨论的问题相同: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...

它只在我从数据库中删除存储过程并重新创建它,并从DBML设计器中删除它,重新编译,重新启动Visual Studio并再次添加它时才起作用。这是我第二次遇到Visual Studio DBML设计器的“刷新”问题......

#5

I managed to work out an easier way, which just wasn't obvious at the time, but sounds straight forward when written down:

我设法制定了一种更简单的方法,当时并不明显,但在写下来时听起来很直接:

Delete the stored procedure from the design surface of the .dbml file

从.dbml文件的设计图面中删除存储过程

Click Save All files

单击保存所有文件

Click Refresh in Server Explorer on the list of Stored Procedures

单击存储过程列表中的服务器资源管理器中的刷新

Add (drag) the stored procedure back onto the design surface of the .dbml file

将存储过程添加(拖动)回到.dbml文件的设计图面上

Click Save All

单击全部保存

Click Build
Check the designer.cs code file and you will have the updated C# code for the new version of the stored procedure

检查designer.cs代码文件,您将获得新版本存储过程的更新C#代码

check http://www.high-flying.co.uk/C-Sharp/linq-to-sql-can-t-update-dbml-file.html

#6

I had the same problem, but only happens if my sp uses FTS, what i did was "cheat" the dbml designer, I remove the fts language stuff and works perfectly, now i can change the return type. Later i go to the sp and add the fts again and works pefectly!. Hope this help.

我遇到了同样的问题,但只有当我的sp使用FTS时才会发生,我所做的是“欺骗”dbml设计器,我删除了fts语言的东西并且工作完美,现在我可以更改返回类型。后来我去sp并再次添加fts并且完美地工作!希望这有帮助。

#7

The way to get around this issue is:

解决这个问题的方法是:

Add "set fmtonly off;" to the beginning of your stored procedure.

添加“set fmtonly off;”到存储过程的开头。

After adding that statement get DBML generate the code for your stored procedure.

添加该语句后,获取DBML将为您的存储过程生成代码。

If your stored procedure's return type is still 'int' in your DBML code, comment the entire code of stored procedure, create a new SELECT statement whose returning fields types and names match the original's SELECT statement and get DBML regenerate the code again. It has to work!

如果存储过程的返回类型在DBML代码中仍为“int”,则注释存储过程的整个代码,创建一个新的SELECT语句,其返回的字段类型和名称与原始的SELECT语句匹配,并获得DBML再次重新生成代码。它必须工作!

#8

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

谢谢@Rubenz,我也在存储过程中使用FTS(全文搜索),你的步骤也很有效。

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

我从存储过程中注释了FTS部分,将存储过程添加到.dbml,然后将FTS部分取消注释回原始版本。

#9

This also happens when using sql user-defined types as parameters in stored procedures

当使用sql用户定义类型作为存储过程中的参数时,也会发生这种情况

http://alejandrobog.wordpress.com/2009/09/23/linq-to-sql-%e2%80%94-can%e2%80%99t-modify-return-type-of-stored-procedure/

#10

Better solution found here: http://tonesdotnetblog.wordpress.com/2010/06/02/solution-my-generated-linq-to-sql-stored-procedure-returns-an-int-when-it-should-return-a-table/

这里有更好的解决方案:http://tonesdotnetblog.wordpress.com/2010/06/02/solution-my-generated-linq-to-sql-stored-procedure-returns-an-int-when-it-should-return -一张桌子/

#11

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

好吧,我不想在Designer.cs代码中更改任何内容,我知道有一个不同的问题,它与我的存储过程无关(我还没有使用临时表)。

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

简单地从数据库中删除sp并更新模型根本没有帮助。创建的新模型仍然存在同样的问题......

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

我发现由于某种原因,我的sp的副本是在DatabaseModel - > Function Imports中创建的。

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

我做了什么,删除了功能导入中的重复对象并更新了模型。有效!

Regards, Chris

#1