实体框架(数据库优先)存储过程返回的结果不正确 [英] 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.

背景1:我的存储过程LTM_Lease_DeleteSubFiles在顶部执行SET NOCOUNT ON，并在存储过程结束时使用RETURN命令设置返回值.

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

背景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(...)

推荐答案

原因-EF(包括v6)的模板构建器错误地将SP设置为返回包含行数而不是INT的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的飞越智能提示表明执行存储过程…； 丢弃该函数返回的所有结果；并返回受执行影响的行数"，而不是通常的"Executes存储过程….具有指定参数".

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.

为什么问题2是8 :由于Microsoft使用了错误的ExecuteFunction，SP返回了8行数据而不是返回值.

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

SP修复问题1 -您必须将SET NOCOUNT ON注释掉.

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

解决问题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后，从功能导入"文件夹和数据存储"的SP文件夹中删除SP. [此更改使EF模板生成器使SP调用成为可为空的INT的ObjectResult而不是单个INT结果.] 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屋！