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

Question

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

c# entity-framework entity-framework-6 sql-server stored-procedures

Question

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

Eric Wood

Popular Answer

I have come across this before with stored procedures using dynamic SQL. I have had success using complex types if I add the line 'SET FMTONLY OFF;' (see https://msdn.microsoft.com/en-us/library/ms173839.aspx) to the top of my stored procedure before it is added to the EF model. Once you have your model setup with your complex type, be sure to remove this line.

Example:

ALTER PROCEDURE dbo.SearchProducts
  @SearchTerm VARCHAR(max)
AS
BEGIN
  SET FMTONLY OFF;
  DECLARE @query VARCHAR(max)
  SET @query = 'SELECT * FROM dbo.Products WHERE Name LIKE ''%' + @SearchTerm + '%'''
  EXEC(@query)
END

gotmilk13531

View more on Stack Overflow

Salah Akbari · Accepted Answer · 9/3/2015 7:36:33 PM

Use the following steps to solve this issue:

You need to Import the stored procedure as a Function. Right-click on the workspace area of your Entity model and choose Add -> Function Import.
In the Add Function Import dialog, enter the name you want your stored procedure to be referred to in your model for example Search_Products, choose your procedure from the drop down list, and choose the return value of the procedure to be Entities and choose Products from the drop down list.

Then in the code behind:

var db = new MyEntities();
var TEST_SEARCH_TERM = "product";
var result = db.Search_Products(TEST_SEARCH_TERM);//Search_Products is the name that you specified in Function Import dialog

MyGridView.DataSource = result;
MyGridView.DataBind();

The reason that you get -1 for result is that Entity Framework cannot support Stored Procedure Return values out of the box. I think support of stored procedure return values depends on version of Entity framework. Also Entity Framework doesn't have rich stored procedure support because its an ORM, not a SQL replacement.

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

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related