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

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

Accepted Answer

To overcome this problem, take the following actions:

  1. The saved process must be imported as a function. Right-click your Entity model's workspace area and selectAdd -> Function Import .
  2. Enter the name your stored procedure should be known by in your model in the Add Function Import dialog, for instance.Search_Products select your process from the drop-down box, then select what the procedure's return value should be.Entities and decideProducts via the drop-down menu.
  3. Next, in the code after:

    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 explanation for why you-1 As a result, Entity Framework is unable to natively support Stored Procedure Return values. Support for saved procedure return values, in my opinion, relies on the Entity Framework version. Additionally, because Entity Framework is an ORM rather than a substitute for SQL, it lacks robust support for stored procedures.

30
9/3/2015 7:36:33 PM

Popular Answer

This has happened to me before while using stored procedures and dynamic SQL. If I put the line 'SET FMTONLY OFF;' (see https://msdn.microsoft.com/en-us/library/ms173839.aspx) to the top of my saved procedure before it is added to the EF model, I have had success with complex types. Make careful to remove this line after setting up your model with your complex type.

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow