实体框架(数据库优先)从存储过程返回不正确的结果 [英] Entity Framework (Database first) has incorrect return result from stored procedure

问题描述

环境:

• Visual Studio 2017
• SQL Server 2016
• 采用数据库优先方法的 EF v6

背景:存储过程在 EDMX 中.如果什么都没发生，我的存储过程将返回值设置为 0，如果有影响则设置为 1，如果有错误，则设置为 @@ERROR.

Background: the stored procedure is in the EDMX. My stored procedure sets the return value to 0 if nothing happened, 1 if something affected and value of @@ERROR if errors.

BACKGROUND 1: 我的存储过程 LTM_Lease_DeleteSubFiles 在顶部执行 SET NOCOUNT ON 并使用 RETURN<设置返回值/code> 存储过程末尾的命令.

BACKGROUND 1: my stored procedure, LTM_Lease_DeleteSubFiles, does SET NOCOUNT ON at the top and sets return value with RETURN command at the end of the stored procedure.

问题 1: 我的调用返回 -1，它甚至不在存储过程中:

PROBLEM 1: my call returns -1 which is not even in the stored procedure:

var spResults = context.LTM_Lease_DeleteSubFiles(...)

BACKGROUND 2:我的存储过程 DOIOwnerChanges_Apply 在存储过程结束时使用 RETURN 命令设置返回值.

BACKGROUND 2: my stored procedure DOIOwnerChanges_Apply sets return value with RETURN command at the end of the stored procedure.

问题 2:我的调用返回的值 8 甚至在存储过程中都找不到:

PROBLEM 2: my call returns the value of 8 which is not even found in the stored procedure:

var spResults = context.DOIOwnerChanges_Apply(...)

推荐答案

REASON - EF(包括 v6)的模板构建器错误地将 SP 设置为返回包含行数的 INT，而不是返回值，因为它错误地调用了错误的 ObjectContext.ExecuteFunction(在模板生成的类 YourDatabaseEntities 中找到，它是 DBContext 的子类).

REASON - The template builder for EF (including v6) incorrectly sets the SP up as returning an INT containing the row count rather than the return value because it incorrectly calls the wrong ObjectContext.ExecuteFunction (found in the template-generated class YourDatabaseEntities that is the child of the DBContext).

为什么错误的ExecuteFunction?- 结果集错误地表示更改行的行数，而不是返回值或输出参数，因为它调用了丢弃结果的不同 ExecuteFunction.ObjectContext.ExecuteFunction 的天桥智能提示说执行存储过程……；丢弃从函数返回的任何结果；并返回受执行影响的行数"而不是通常的执行一个存储过程……带有指定的参数".

Why wrong ExecuteFunction? - The result set incorrectly says the row count of changed rows rather than the return value or output parameters because it calls a different ExecuteFunction that discards the results. The flyover intellisense hint of the ObjectContext.ExecuteFunction says "Executes a stored procedure ….; discards any results returned from the function; and returns the number of rows affected by the execution" rather than the usual "Executes a stored procedure …. with the specified parameters".

为什么问题 1 是 -1:我认为 SET NOCOUNT ON 导致 SP 不返回任何计数结果，并且 Microsoft 的 ExecuteFunction 将其作为错误代码返回.

WHY PROBLEM 1 IS -1: I believe the SET NOCOUNT ON is causing the SP to return no count result and that Microsoft's ExecuteFunction returns that as error code.

WHY PROBLEM 2 IS 8:SP 返回 8 行数据而不是返回值，因为 Microsoft 使用了错误的 ExecuteFunction.

WHY PROBLEM 2 IS 8: The SP returned 8 rows of data rather than the return value because Microsoft used the wrong ExecuteFunction.

SP FIX TO PROBLEM 1 - 你必须注释掉 SET NOCOUNT ON .

SP FIX TO PROBLEM 1 - You have to comment out SET NOCOUNT ON .

SP 修复问题 1 和 2 - 您必须更改存储过程以将 SELECT 命令作为最后一条语句而不是 RETURN 命令.

SP FIX TO PROBLEM 1 and 2 - You have to change stored procedure to do the SELECT command as last statement instead of the RETURN command.

解决方案修复 - 1) 修复 SP 后，从 Function Imports 文件夹和 Data Store 的 SP 文件夹中删除 SP.[此更改导致 EF 模板生成器使 SP 调用成为可为空 INT 而不是单个 INT 结果的 ObjectResult.] 2) 使用从数据库更新模型"将 SP 重新加载到 EDMX 3) 重建所有数据EDMX 所在的项目.4) 退出 Visual Studio 并返回.5) 重建整体解决方案.

SOLUTION FIX - 1) After fixing SP, delete SP from Function Imports folder and the Data Store's SP folder. [This change causes EF template generator to make the SP call become an ObjectResult of nullable INT rather than a single INT result.] 2) Reload the SP into the EDMX by using the "Update Model from Database" 3) Rebuild all of your data project where the EDMX resides. 4) Exit Visual Studio and return. 5) Rebuild overall solution.

其他解决方法 - 来源:Microsoft 的 David Browne - 1) 重写 SP 创建一个 OUTPUT 参数并在那里返回结果.2) 遇到错误时执行 THROW 或 RAISERROR.

OTHER WORKAROUNDS - Source: David Browne of Microsoft - 1) Rewrite the SP creating an OUTPUT parameter and return the result there. 2) Do a THROW or RAISERROR when an error is hit.

这篇关于实体框架(数据库优先)从存储过程返回不正确的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！