How to Call Stored Procedure in Entity Framework 6 (Code-First)

Call stored procedure from Entity Framework 6.1.3 code-first

Use without exec :

using (Model1 ent = new Models.Model1())
{
    var regionID= new SqlParameter("@RegionID", 100);
    var regionDesc= new SqlParameter("@RegionDesc", "Nima");

    ent.Database.SqlQuery<Region>("InsertRegion @RegionID ,@RegionDesc", regionID ,regionDesc);
}

but in your sp not returning any recored so you can use this also :

using (Model1 ent = new Models.Model1())
{
    var regionID= new SqlParameter("@RegionID", 100);
    var regionDesc= new SqlParameter("@RegionDesc", "Nima");

    ent.Database.ExecuteSqlCommand("InsertRegion @RegionID ,@RegionDesc", regionID ,regionDesc);
}

How to call stored procedure using Entity Framework code-first?

You can do it with the ExecuteSqlCommand method on the database.

this.Database.ExecuteSqlCommand(
   "[dbo].[DeleteUserById] @UserId", 
   new SqlParameter("@UserId", id));

UPDATE

(answer to comment)

[Table("Users")]
public class ApplicationUser
{
}

How to MAP select stored procedure in Entity Framework 6 code-first approach?

Since this question brought some new insights, I've researched a little and marc_s answered helped. Basically Knowing EF Code-First, when you do not have any database it is kinda impossible to create a SP for Select, since you have to create the database first then insert data within then select what is there (which makes more sense), you have to create the Stored Procedure within the SQL and use code below to run it:
You can call a stored procedure in your DbContext class as follows.

this.Database.SqlQuery<YourEntityType>("storedProcedure‌​Name",params);

Credit goes to @Marc_S and Source

MVC 5, EF 6, and Code First from Database Stored Procedures

EDIT: Tarun mentioned the stored procedures aren't showing up due to lack of permissions.

Entity Framework provide APIs to call stored procedures though, you can even map a stored procedure to a model.

See this link:
https://msdn.microsoft.com/en-us/data/dn468673.aspx

To bind a model to a stored procedure:

 modelBuilder.Entity<Blog>().MapToStoredProcedures();

If you are not interested in mapping a stored procedure see this post:

How to call Stored Procedure in Entity Framework 6 (Code-First)?

Calling existing Stored procedure using Entity Framework 6 with C#

One thing I can tell an issue with is in your method where you call the procedure. You are setting all elements of the array equal to param but you are constantly changing param. All of your elements will be equal to the final state of param. Try this instead:

public void GetEmployeeDataUsingProcedure()
{
    object[] parameters = new SqlParameter[4];
    List<EmployeeResultSet> lstEmployees = new List<EmployeeResultSet>();
    try
    {
        using (var db = new DevelopmentTestDatabaseContext())
        {
            parameters[0] = new SqlParameter("@departmentname", "IT");
            parameters[1] = new SqlParameter("@sortCol", "ID");
            parameters[2] = new SqlParameter("@sortdir", "asc");
            parameters[3] = new SqlParameter("@searchString", "ope");

            var results = db.Database.SqlQuery<EmployeeResultSet>("proc_getEmployees @departmentname, @sortCol, @sortdir, @searchString", parameters);
            db.Database.Log = query => System.Diagnostics.Debug.Write(query);
            lstEmployees = results.ToList();
        }
    }
    catch (Exception ex)
    {
        //log it or something
    }
}

There may be other issues but without looking too much into it I'd need more information about specific errors or behavior you are experiencing.

You may also try typing out the full name of your database:

"MyDatabase.MySchema.proc_getEmployees @departmentname, @sortCol, @sortdir, @searchString"

EDIT Per your comments:

A quick ducking and I found this. Essentially it states that if you cast your numbers as int in your query you should be fine. So instead of:

select * from employeetable

Try:

select CAST(RowNumber as int) as RowNumber, 
    CAST(TotalRecords as int) as TotalRecords, 
    CAST(ID as int) as ID, 
    FirstName, 
    LastName, 
    Designation, 
    DepartmentName, 
    Contact, 
    EmailAddress, 
    Location 
from employeetable

EF 6 Code First From Database - Calling existing stored procedure

You need to define a DTO for each stored procedure. You just create simple classes where the propery names of the classes match the name of the columns that are returned by the stored procedure. So, when you call the Stored Procedure from EF, your objects are built and populated. The performance is great!
Here I'm pasting an example:

dbContext.Database.CommandTimeout = 3600;
List<CarsDTO> objs = dbContext.Database.SqlQuery<CarsDTO>(
         "spGetCars @carMakeId", new SqlParameter("carMakeId", id)
).ToList();

Execute stored procedure using entity framework

You can call SqlQuery from your Entity Framework data context.

context.Database.SqlQuery<YourType>("exec usp_StoredProcedure").ToList()

You would need a class to map the query results back, as an example:

public class YourType
{
   public string Property1 { get; set; }
   public string Property2 { get; set; }
}

You can also specify parameters to the query as shown below:

SqlParameter parameter1 = new SqlParameter("@Parameter1", "Value");
context.Database.SqlQuery<YourType>("exec usp_StoredProcedure @Parameter1", parameter1).ToList()

---

Related Topics