How to Retrieve Data from a Stored Procedure Using Entity Framework Without Getting an Empty Dataset?

Retrieving Data from Stored Procedures Using Entity Framework

Problem:
Developers encounter difficulties retrieving data from a stored procedure when using Entity Framework (EF), resulting in an empty dataset (-1) instead of the expected data.

Stored Procedure:

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

C# Code:

var db = new MyEntities();
var TEST_SEARCH_TERM = "product";
var result = db.SearchProducts(TEST_SEARCH_TERM);

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

Solution:
To resolve this issue, follow these steps:

1. Import the Stored Procedure as a Function:

   • Right-click on the workspace area of your Entity model and select "Add" -> "Function Import."
   • Enter the desired name for the stored procedure (e.g., Search_Products) and select the procedure from the drop-down list.
   • Set the return value to "Entities" and choose "Products" as the entity type.

2. Update the Code Behind:

   • Replace the existing code with the following:

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

By importing the stored procedure as a function, Entity Framework can properly retrieve and return the data from the stored procedure.

The above is the detailed content of How to Retrieve Data from a Stored Procedure Using Entity Framework Without Getting an Empty Dataset?. For more information, please follow other related articles on the PHP Chinese website!