Retrieve table data from stored procedure using entity framework

Question

I'm using Entity Framework v6. I have a stored procedure as shown below

CREATE PROCEDURE [dbo].[GetCountryList] 
(
    @CustomerName VARCHAR(MAX), 
    @SearchCriteria VARCHAR(MAX)
)
AS
    BEGIN
    SET NOCOUNT ON

        SELECT CountryID, CountryName FROM dbo.Table1 
        WHERE CustomerName = @CustomerName AND CountryName = @SearchCriteria
    END

Now I have a model class

public class CountryName
{
    public int CountryId { get; set; }
    public string CountryName { get; set; }
}

So I want to get the result of the SELECT query in a List<CountryName> type

List<CountryName> countryList = null;

using (DbEntities dbContext = new DbEntities())
{
    countryList = //my code to collect the result
}

Well, I could have run a LINQ to SQL directly on the table but unfortunately my requirement in to get the data from stored procedure. So, how can I do it?

Solution 1

1. 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.
2. In the Add Function Import dialog, enter the name you want your stored procedure to be referred to in your model for example GetCountryListSP, choose your procedure from the drop down list, and choose the return value of the procedure to be Entities and choose CountryName from the drop down list.

3. Then in the code:

   var result = db.GetCountryListSP();//Send parameters too
   

   With this approach you prevent returning -1 of the stored procedure. Please check this post for more details about stored procedure problem with Entity Framework.

Solution 2

You can do it without importing. Something like that:

var countryList = dbContext.Database.SqlQuery<CountryName>("[GetCountryList]").ToList();

EntityFramework sometimes won't recognize or import SPs ))) So, that's why I saving my hours with this snippet.