7

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?

Salah Akbari
  • 39,330
  • 10
  • 79
  • 109
Rahul Chakrabarty
  • 2,149
  • 7
  • 39
  • 70

2 Answers2

12
  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.

Community
  • 1
  • 1
Salah Akbari
  • 39,330
  • 10
  • 79
  • 109
7

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.

Anton Norko
  • 2,166
  • 1
  • 15
  • 20
  • i used this but some time it does not work , the ef does not execute the stored procedure on live environment but when i execute proc on sql management studio or in my local visual studio then it works does it a some kind of bug in ef? – Dragon May 23 '17 at 13:24
  • I now changed my code to what @S.Akbari suggested, db.procedure(); and its working uptill now , i am also monitoring it if any thing wrong happens. – Dragon May 23 '17 at 13:25