Have Geek Brain

Retrieving table data from stored procedure using entity structure

I am 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 query result SELECT

in typeList<CountryName> 

List<CountryName> countryList = null;

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

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

+3


source to share


2 answers


  • You need to import the stored procedure as a function. Right-click the workspace area of ​​your Entity model and select Add -> Function Import

    .
  • In the Add Import Function dialog box, enter the name that your stored procedure will be referred to in your model, for example GetCountryListSP

    , select your procedure from the dropdown list and select the return value of the procedure to be Entities

    and select CountryName

    from the dropdown list.

  • Then in code:

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

    With this approach, you cannot return a -1

    stored procedure. Please see this for more details on stored procedure issues in Entity Framework.



+9


source


You can do this without importing. Something like that:

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


EntityFramework sometimes doesn't recognize or import SP))) So that's why I'm saving my clock with this snippet.

+3


source






More articles:


All Articles