Retrieve table data from stored procedure using entity framework

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


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

CREATE PROCEDURE [dbo].[GetCountryList] 
    @CustomerName VARCHAR(MAX), 
    @SearchCriteria VARCHAR(MAX)

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

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?

9/24/2015 9:26:13 AM

Accepted Answer

  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.

5/23/2017 12:25:51 PM

Popular Answer

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.

Related Questions


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