C# 存储过程返回 int 而不是结果集

Stored procedure returns int instead of result set

提问by Rod

I have a stored procedure that contains dynamic select. Something like this:

我有一个包含动态选择的存储过程。像这样的东西：

ALTER PROCEDURE [dbo].[usp_GetTestRecords] 
    --@p1 int = 0, 
    --@p2 int = 0
    @groupId nvarchar(10) = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @query  NVARCHAR(max)

    SET @query = 'SELECT * FROM CUSTOMERS WHERE Id = ' + @groupId
    /* This actually contains a dynamic pivot select statement */

    EXECUTE(@query);
END

In SSMS the stored procedure runs fine and shows result set.

在 SSMS 中，存储过程运行良好并显示结果集。

In C# using Entity Framework it shows returning an intinstead of IEnumerable?

在使用实体框架的 C# 中，它显示返回一个int而不是IEnumerable?

private void LoadTestRecords()
{
    TestRecordsDBEntities dataContext = new TestRecordsDBEntities();
    string id = ddlGroupId.SelectedValue;

    List<TestRecord> list = dataContext.usp_GetTestRecords(id); //This part doesn't work returns int
    GridView1.DataSource = list;
}

Generated function for usp_GetTestRecords

生成的函数 usp_GetTestRecords

public virtual int usp_GetTestRecords(string groupId)
{
    var groupIdParameter = groupId != null ?
        new ObjectParameter("groupId", groupId) :
        new ObjectParameter("groupId", typeof(string));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("usp_GetTestRecords", groupIdParameter);
}

采纳答案by Rob

Entity Framework can't tell what your stored procedure is returning. I've had success creating a table variable that mirrors the data from your SELECT statement. Just insert into the table variable then do a select from that table variable. EF should pick it up.

实体框架无法判断您的存储过程返回什么。我已经成功创建了一个表变量，该变量反映了 SELECT 语句中的数据。只需插入表变量，然后从该表变量中进行选择。EF 应该把它捡起来。

回答by Claies

When you generated your model class for your stored procedure, you chose scalar return result by mistake. you should remove your stored procedure from your entity model, then re-add the stored procedure. In the dialog for the stored procedure, you can choose the return type you are expecting. Do notjust edit the generated code.. this may work now, but the generated code can be replaced if you make other changes to your model.

当您为存储过程生成模型类时，您错误地选择了标量返回结果。您应该从实体模型中删除存储过程，然后重新添加存储过程。在存储过程的对话框中，您可以选择期望的返回类型。 不要只编辑生成的代码。这现在可能会起作用，但是如果您对模型进行其他更改，则可以替换生成的代码。

回答by Yelnic

I get this when I have a stored procedure that includes an "exec" call into a temporary table, such as:

当我有一个包含对临时表的“exec”调用的存储过程时，我会得到这个，例如：

insert into #codes (Code, ActionCodes, Description)
exec TreatmentCodes_sps 0

It appears that Entity Framework gets confused as to what should be returned by the procedure. The solution I've come across is to add this at the top of the sproc:

实体框架似乎对程序应该返回什么感到困惑。我遇到的解决方案是在 sproc 的顶部添加它：

SET FMTONLY OFF

After this, all is well.

在此之后，一切都很好。

回答by Nezam

Just change to

只需更改为

ALTER PROCEDURE [dbo].[usp_GetTestRecords] 
    --@p1 int = 0, 
    --@p2 int = 0
    @groupId nvarchar(10) = 0
AS
BEGIN
    SET NOCOUNT ON;


    SELECT * FROM CUSTOMERS WHERE Id =  @groupId

END

回答by Circle Hsiao

I got the same problem, and found solution here

我遇到了同样的问题，并在这里找到了解决方案

1. Move to your .edmx
2. At Model Browser Window/Function Imports find your procedure then double click it
3. Change the return type to you want
4. Save .edmx and check the return type again.

1. 移至您的 .edmx
2. 在模型浏览器窗口/函数导入中找到您的程序，然后双击它
3. 将返回类型更改为您想要的
4. 保存 .edmx 并再次检查返回类型。

It should be what you need now.

它应该是你现在需要的。

回答by Rob White

I know this is an old thread but in case someone has the same problems I'll tell my woes.

我知道这是一个旧线程，但如果有人遇到同样的问题，我会说出我的困境。

As a help to find the issue, run sql profiler when you add your stored proc. Then you can see what entity framework is passing as parameters to generate your resultset. I imagine nearly always it will pass null parameter values. If you are generating sql on the fly by concatenating string values and parameter values and some are null then the sql will break and you wont get a return set.

作为查找问题的帮助，请在添加存储过程时运行 sql profiler。然后你可以看到什么实体框架作为参数传递来生成你的结果集。我想它几乎总是会传递空参数值。如果您通过连接字符串值和参数值动态生成 sql 并且一些为空，那么 sql 将中断并且您不会获得返回集。

I haven't needed to generate temp tables or anything just an exec command.

我不需要生成临时表或任何只是一个 exec 命令。

Hope it helps

希望能帮助到你

回答by Armen Hovsepian

I had the same problem, I changed the name of return fields by 'AS' keyword and addressed my problem. One reason for this problem is naming column names with SQL Server reserved keywords.

我遇到了同样的问题，我通过“AS”关键字更改了返回字段的名称并解决了我的问题。此问题的一个原因是使用 SQL Server 保留关键字命名列名。

The example is fallows:

这个例子是休耕：

ALTER PROCEDURE [dbo].[usp_GetProducts]

AS
BEGIN

 SET NOCOUNT ON;

 SELECT 
      , p.Id
      , p.Title
      , p.Description AS 'Description'

    FROM dbo.Products AS p
END

回答by hoangvu

Entity Framework will automatically return a scalar value if your stored procedure doesn't have a primary key in your result set. Thus, you'd have to include a primary key column in your select statement, or create a temp table with a primary key in order for Entity Framework to return a result set for your stored procedure.

如果您的存储过程在结果集中没有主键，实体框架将自动返回一个标量值。因此，您必须在 select 语句中包含一个主键列，或者创建一个带有主键的临时表，以便实体框架为您的存储过程返回结果集。

回答by wode

See Ladislav Mrnka's answer in this Stack Overflow post https://stackoverflow.com/a/7131344/4318324

参见 Ladislav Mrnka 在这篇 Stack Overflow 帖子中的回答 https://stackoverflow.com/a/7131344/4318324

I had the same basic problem.

我有同样的基本问题。

Adding

添加

SET FMTONLY OFF

To a procedure you are trying to import during the import will address this problem. It's a good practice to remove the line afterwards unless the purpose of the database is solely to provide schema for EF (Entity Framework).

在导入过程中尝试导入的过程将解决此问题。除非数据库的目的仅仅是为 EF（实体框架）提供架构，否则最好在之后删除该行。

The main reason for caution is that EF uses this setting to prevent data mutations when trying to obtain metadata.

谨慎的主要原因是 EF 在尝试获取元数据时使用此设置来防止数据突变。

If you refresh your entity model from a database any procedures with this line in them can potentially update the data in that database just by trying to obtain the schema.

如果您从数据库刷新实体模型，其中包含此行的任何过程都可能仅通过尝试获取模式来更新该数据库中的数据。

I wanted to add a further note on this so it's not needed to fully scan through the other link.

我想对此添加进一步的注释，因此不需要完全扫描另一个链接。

if you want to try to use FMTONLY here are a couple things to keep in mind.

如果您想尝试使用 FMTONLY，请记住以下几点。

when FMTONLY is on:
  1) only the schema is returned (no) rows.
     similar to adding a blanket false statement to your where clause (ie "where 1=0")
  2) flow control statements are ignored

Example

例子

set fmtonly on

if 1=1
begin
    select 1 a
end
else
begin
    select 1 a,2 b
end

while 1=1
select 1 c

The above returns NO rows whatsoever and the metadata for each of the three queries

以上不返回任何行和三个查询中的每一个的元数据

For this reason some people suggest toggling it off in a way that takes advantage of it's non-observance of flow control

出于这个原因，有些人建议以一种利用它不遵守流量控制的方式关闭它

if 1=0
begin
    set fmtonly off
end

In fact you could use this to introduce logic that tracks this

事实上，您可以使用它来引入跟踪此的逻辑

set fmtonly off
declare @g varchar(30)
set @g = 'fmtonly was set to off'

if 1=0
begin
    set fmtonly off
    set @g = 'fmtonly was set to on' 
end

select @g

Think VERY CAREFULLY before trying to use this feature as it is both deprecated and potentially makes sql extremely hard to follow

在尝试使用此功能之前要非常仔细地考虑，因为它既已弃用，又可能使 sql 极难遵循

the MAIN concepts that need to be understood are the following

需要理解的主要概念如下

1. EF turns FMTONLY on to prevent MUTATING data from executing stored procedures
   when it executes them during a model update.
   (from which it follows)

2. setting FMTONLY off in any procedure that EF will attempt to do a schema scan
   (potentially ANY and EACHONE) introduces the potential to mutate database
   data whenever *anyone* attempts to update their database model.

回答by wode

I have pondered this a bit and I think I have a better/simpler answer

我考虑了一下，我想我有一个更好/更简单的答案

If you have a complex stored that gives entity framework some difficultly (for current versions of Entity Framework that are using the FMTONLY tag to aquire schema)

如果您有一个复杂的存储，这给实体框架带来了一些困难（对于使用 FMTONLY 标签获取架构的实体框架的当前版本）

consider doing the folowing at the beginning of your stored procedure.

考虑在存储过程开始时执行以下操作。

--where [columnlist] matches the schema you want EF to pick up for your stored procedure

if 1=0
begin
    select 
       [columnlist]
    from [table list and joins]
    where 1=0
end

if you are okay loading your result set into a table variable you can do the following to help keep your schema in sync

如果您可以将结果集加载到表变量中，您可以执行以下操作以帮助您的架构保持同步

declare @tablevar as table
(
    blah int
    ,moreblah varchar(20)
)

if 1=0
begin
    select * from @tablevar
end

...
-- load data into @tablevar
select * from @tablevar