Entity Framework (Database first) has incorrect return result from stored procedure

user_1970642 0
Viewed 2603

Environment:

  • Visual Studio 2017
  • SQL Server 2016
  • EF v6 with a database-first approach

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

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

var spResults = context.LTM_Lease_DeleteSubFiles(...)

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

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

var spResults = context.DOIOwnerChanges_Apply(...)
entity-framework-6 stored-procedures entity-framework sql-server c#
1 Answers

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

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

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: The SP returned 8 rows of data rather than the return value because Microsoft used the wrong ExecuteFunction.

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

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.

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.

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.

user_19706420
Related