By Jaan


2014-01-03 10:23:54 8 Comments

I am very new to Entity Framework 6 and I want to implement stored procedures in my project. I have a stored procedure as follows:

ALTER PROCEDURE [dbo].[insert_department]
    @Name [varchar](100)
AS
BEGIN
    INSERT [dbo].[Departments]([Name])
    VALUES (@Name)

    DECLARE @DeptId int

    SELECT @DeptId = [DeptId]
    FROM [dbo].[Departments]
    WHERE @@ROWCOUNT > 0 AND [DeptId] = SCOPE_IDENTITY()

    SELECT t0.[DeptId]
    FROM [dbo].[Departments] AS t0
    WHERE @@ROWCOUNT > 0 AND t0.[DeptId] = @DeptId
END

Department class:

public class Department
{
    public int DepartmentId { get; set; }       
    public string Name { get; set; }
}

modelBuilder 
.Entity<Department>() 
.MapToStoredProcedures(s => 
s.Update(u => u.HasName("modify_department") 
               .Parameter(b => b.Department, "department_id") 
               .Parameter(b => b.Name, "department_name")) 
 .Delete(d => d.HasName("delete_department") 
               .Parameter(b => b.DepartmentId, "department_id")) 
 .Insert(i => i.HasName("insert_department") 
               .Parameter(b => b.Name, "department_name")));

protected void btnSave_Click(object sender, EventArgs e)
{
    string department = txtDepartment.text.trim();

    // here I want to call the stored procedure to insert values
}

My problem is: how can I call the stored procedure and pass parameters into it?

19 comments

@Hari Lakkakula 2019-05-22 09:26:24

Using MySql and Entity framework code first Approach:

public class Vw_EMIcount
{
    public int EmiCount { get; set; }
    public string Satus { get; set; }
}

var result = context.Database.SqlQuery<Vw_EMIcount>("call EMIStatus('2018-3-01' ,'2019-05-30')").ToList();

@SHUBHASIS 2019-04-16 13:46:28

Nothing have to do... when you are creating dbcontext for code first approach initialize namespace below the fluent API area make list of sp and use it another place where you want.

public partial class JobScheduleSmsEntities : DbContext
{
    public JobScheduleSmsEntities()
        : base("name=JobScheduleSmsEntities")
    {
        Database.SetInitializer<JobScheduleSmsEntities>(new CreateDatabaseIfNotExists<JobScheduleSmsEntities>());
    }

    public virtual DbSet<Customer> Customers { get; set; }
    public virtual DbSet<ReachargeDetail> ReachargeDetails { get; set; }
    public virtual DbSet<RoleMaster> RoleMasters { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //modelBuilder.Types().Configure(t => t.MapToStoredProcedures());

        //modelBuilder.Entity<RoleMaster>()
        //     .HasMany(e => e.Customers)
        //     .WithRequired(e => e.RoleMaster)
        //     .HasForeignKey(e => e.RoleID)
        //     .WillCascadeOnDelete(false);
    }
    public virtual List<Sp_CustomerDetails02> Sp_CustomerDetails()
    {
        //return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Sp_CustomerDetails02>("Sp_CustomerDetails");
        //  this.Database.SqlQuery<Sp_CustomerDetails02>("Sp_CustomerDetails");
        using (JobScheduleSmsEntities db = new JobScheduleSmsEntities())
        {
           return db.Database.SqlQuery<Sp_CustomerDetails02>("Sp_CustomerDetails").ToList();

        }

    }

}

}

public partial class Sp_CustomerDetails02
{
    public long? ID { get; set; }
    public string Name { get; set; }
    public string CustomerID { get; set; }
    public long? CustID { get; set; }
    public long? Customer_ID { get; set; }
    public decimal? Amount { get; set; }
    public DateTime? StartDate { get; set; }
    public DateTime? EndDate { get; set; }
    public int? CountDay { get; set; }
    public int? EndDateCountDay { get; set; }
    public DateTime? RenewDate { get; set; }
    public bool? IsSMS { get; set; }
    public bool? IsActive { get; set; }
    public string Contact { get; set; }
}

@user1883961 2019-04-10 23:08:24

public static string ToSqlParamsString(this IDictionary<string, string> dict)
        {
            string result = string.Empty;
            foreach (var kvp in dict)
            {
                result += $"@{kvp.Key}='{kvp.Value}',";
            }
            return result.Trim(',', ' ');
        }

public static List<T> RunSproc<T>(string sprocName, IDictionary<string, string> parameters)
        {
            string command = $"exec {sprocName} {parameters.ToSqlParamsString()}";
            return Context.Database.SqlQuery<T>(command).ToList();
        }

@Mahdi ghafoorian 2014-05-07 06:07:27

I solved it with ExecuteSqlCommand

Put your own method like mine in DbContext as your own instances:

public void addmessage(<yourEntity> _msg)
{
    var date = new SqlParameter("@date", _msg.MDate);
    var subject = new SqlParameter("@subject", _msg.MSubject);
    var body = new SqlParameter("@body", _msg.MBody);
    var fid = new SqlParameter("@fid", _msg.FID);
    this.Database.ExecuteSqlCommand("exec messageinsert @Date , @Subject , @Body , @Fid", date,subject,body,fid);
}

so you can have a method in your code-behind like this :

[WebMethod] //this method is static and i use web method because i call this method from client side
public static void AddMessage(string Date, string Subject, string Body, string Follower, string Department)
{
    try
    {
        using (DBContext reposit = new DBContext())
        {
            msge <yourEntity> Newmsg = new msge();
            Newmsg.MDate = Date;
            Newmsg.MSubject = Subject.Trim();
            Newmsg.MBody = Body.Trim();
            Newmsg.FID= 5;
            reposit.addmessage(Newmsg);
        }
    }
    catch (Exception)
    {
        throw;
    }
}

this is my SP :

Create PROCEDURE dbo.MessageInsert

    @Date nchar["size"],
    @Subject nchar["size"],
    @Body nchar["size"],
    @Fid int
AS
    insert into Msg (MDate,MSubject,MBody,FID) values (@Date,@Subject,@Body,@Fid)
    RETURN

hope helped you

@Dave W 2014-07-22 08:41:00

You need to specify a length on the nchar parameters to your stored procedure - otherwise they are just one character long, as you've found.

@Mahdi ghafoorian 2014-09-24 12:50:27

sure , what a silly mistake , thanks ..

@Komengem 2015-03-06 18:30:56

@Mahdighafoorian This is a very useful answer, thanks alot! :)

@GoldBishop 2015-10-16 12:52:19

This syntax requires no modification to the order of the SProc's Parameters, in other words Ordinal Positioning.

@Alborz 2014-01-03 19:10:01

You can call a stored procedure in your DbContext class as follows.

this.Database.SqlQuery<YourEntityType>("storedProcedureName",params);

But if your stored procedure returns multiple result sets as your sample code, then you can see this helpful article on MSDN

Stored Procedures with Multiple Result Sets

@Jaan 2014-01-03 20:21:46

Thanks @Alborz. can you please provide me some links regarding various implementation of Stored Procedure in Entity Framework 6 Code First. I searched everywhere on the web but didn't get any article where i can directly call a stored procedure for IN and OUT parameters. Thanks for your valuable time.

@Alborz 2014-01-03 20:33:06

@ebram khalil 2014-06-12 08:40:27

Is that the same as creating SQL command and then execute it?

@Mark 2015-04-14 14:49:32

This doesn't appear to work with parameters. It seems to need to explicitly list the parameters as part of the query.

@Oppa Gingham Style 2015-05-13 00:41:28

Yes you do need to specify the params as part of the query - "storedProcedureName @param1, @param2". Also the type of params is System.Data.SqlClient.SqlParameter[].

@Brian Vander Plaats 2015-11-05 17:12:46

This answer is correct, but doesn't address that you CAN use stored procedure mapping to avoid using Database.SQLQuery(). See my answer or qujck's

@Ppp 2017-05-05 04:19:52

this.Database.SqlQuery<YourEntityType>("storedProcedureName @param1", new System.Data.SqlClient.SqlParameter("@param1", YourParam));

@Mauricio Gracia Gutierrez 2018-05-04 15:24:25

I have included the usefull comments to improve this answer

@Marshall 2018-07-12 14:23:27

Hi, I can't find dbContext.Database.SqlQuery<>, I have dbContext.TableName.SqlQuery.

@Lankymart 2018-10-24 11:06:10

@MauricioGraciaGutierrez how is modifying the existing answer with an example (while useful) that has no context to the original question relevant? What are FormularioVentasTO, sp_ConsultarSolicitud @idUsuario, @idSolicitud, codError and PortalFinandinaPriv? Looks like you have just added cut/paste a piece of code that would be better as a separate answer and should also be in English which is one of the requires of Stack Overflow. Rolling Back.

@Mauricio Gracia Gutierrez 2018-10-24 16:38:58

@Lankymart so now code variable names have to be in english ?

@Lankymart 2018-10-24 16:55:37

@MauricioGraciaGutierrez well yeah, along with the example being relevant to the OPs code would be nice.

@Mauricio Gracia Gutierrez 2018-10-24 18:13:26

I have seen plenty of examples that dont use variables in english but whatever

@Lankymart 2018-10-24 18:44:28

@MauricioGraciaGutierrez it’s more the fact that the example wasn’t relevant to the OP question context.

@AminGolmahalle 2019-02-28 17:35:22

YourContext.Database.SqlQuery<YourViewModel>("dbo.procname @p1, ...", value1,...)

@Vladislav Furdak 2018-08-11 21:36:18

I found that calling of Stored Procedures in Code First approach is not convenient. I prefer to use Dapper instead

The following code was written with Entity Framework:

var clientIdParameter = new SqlParameter("@ClientId", 4);

var result = context.Database
.SqlQuery<ResultForCampaign>("GetResultsForCampaign @ClientId", clientIdParameter)
.ToList();

The following code was written with Dapper:

return Database.Connection.Query<ResultForCampaign>(
            "GetResultsForCampaign ",
            new
            {
                ClientId = 4
            },
            commandType: CommandType.StoredProcedure);

I believe the second piece of code is simpler to understand.

@reza.cse08 2017-12-19 08:37:39

It work for me at code first. It return a list with matching property of view model(StudentChapterCompletionViewModel)

var studentIdParameter = new SqlParameter
{
     ParameterName = "studentId",
     Direction = ParameterDirection.Input,
     SqlDbType = SqlDbType.BigInt,
     Value = studentId
 };

 var results = Context.Database.SqlQuery<StudentChapterCompletionViewModel>(
                "exec dbo.sp_StudentComplettion @studentId",
                 studentIdParameter
                ).ToList();

Updated for Context

Context is the instance of the class that Inherit DbContext like below.

public class ApplicationDbContext : DbContext
{
    public DbSet<City> City { get; set; }
}

var Context = new  ApplicationDbContext();

@Marshall 2018-07-12 14:26:55

Hi, I can't find this Context.Database.SqlQuery<Model> , where as I can do this Context.TableName.SqlQuery(ProcName). which is giving me issues

@reza.cse08 2018-07-12 18:36:15

@Marshall, maybe you are using the database first design. please check this link stackoverflow.com/questions/11792018/…

@Shafiq Rabbi 2018-04-25 10:47:19

When EDMX create this time if you select stored procedured in table select option then just call store procedured using procedured name...

var num1 = 1; 
var num2 = 2; 

var result = context.proc_name(num1,num2).tolist();// list or single you get here.. using same thing you can call insert,update or delete procedured.

@Md. Delower Hossain 2017-11-14 10:25:56

public IList<Models.StandardRecipeDetail> GetRequisitionDetailBySearchCriteria(Guid subGroupItemId, Guid groupItemId)
{
    var query = this.UnitOfWork.Context.Database.SqlQuery<Models.StandardRecipeDetail>("SP_GetRequisitionDetailBySearchCriteria @SubGroupItemId,@GroupItemId",
    new System.Data.SqlClient.SqlParameter("@SubGroupItemId", subGroupItemId),
    new System.Data.SqlClient.SqlParameter("@GroupItemId", groupItemId));
    return query.ToList();
}

@Tom Stickel 2017-02-16 00:50:46

This works for me by pulling back data from a stored procedure while passing in a parameter.

var param = new SqlParameter("@datetime", combinedTime);
var result = 
        _db.Database.SqlQuery<QAList>("dbo.GetQAListByDateTime @datetime", param).ToList();

_db is the dbContext

@Shiraj Momin 2016-02-25 09:31:25

object[] xparams = {
            new SqlParameter("@ParametterWithNummvalue", DBNull.Value),
            new SqlParameter("@In_Parameter", "Value"),
            new SqlParameter("@Out_Parameter", SqlDbType.Int) {Direction = ParameterDirection.Output}};

        YourDbContext.Database.ExecuteSqlCommand("exec StoreProcedure_Name @ParametterWithNummvalue, @In_Parameter, @Out_Parameter", xparams);
        var ReturnValue = ((SqlParameter)params[2]).Value;  

@yogihosting 2016-06-23 13:30:15

params is an identifier use a different name.

@Xavier Poinas 2016-08-18 09:27:42

The SaveChanges() here isn't necessary. Changes are committed at the ExecuteSqlCommand() call.

@qujck 2014-01-03 14:12:49

You are using MapToStoredProcedures() which indicates that you are mapping your entities to stored procedures, when doing this you need to let go of the fact that there is a stored procedure and use the context as normal. Something like this (written into the browser so not tested)

using(MyContext context = new MyContext())
{
    Department department = new Department()
    {
        Name = txtDepartment.text.trim()
    };
    context.Set<Department>().Add(department);
}

If all you really trying to do is call a stored procedure directly then use SqlQuery

@Jaan 2014-01-03 15:02:11

Thanks qujck. But i want to use stored procedure. I have given just a sample code for convenient to understand.

@qujck 2014-01-03 15:03:28

@Jaan - The code above will use the stored procedure. Do you mean you want to directly call the stored procedure?

@Jaan 2014-01-03 15:15:00

yes. Can you please tell me which way is the better. Calling directly the stored procedure or the above code you have given?

@qujck 2014-01-03 15:18:22

@Jaan use the code I have shown - the ORM is meant to hide the underlying implementation - using the code above ensures that it doesn't matter to the rest of your code whether there's a stored procedure or not. You can even change the model mapping to another stored procedure or to not be a stored procedure without changing anything else.

@Chazt3n 2015-04-16 12:30:12

Where are you calling a sproc in this answer?

@qujck 2015-04-16 13:12:18

@Chazt3n The question shows the stored procedures being configured from the line .MapToStoredProcedures(s => . A call to Add should resolve to .Insert(i => i.HasName("insert_department")

@Stryder 2015-06-04 00:20:37

Trying to go from DataTables to using EF, thanks for the smack in the back of the head for this answer.

@IngoB 2017-02-19 19:01:26

This is what EF (DB first) generates in the DbContext class:

public ObjectResult<int> Insert_Department(string department)
{
    var departmentParameter = new ObjectParameter("department", department);

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<int>("insert_department", departmentParameter);
}

@trueboroda 2016-11-17 08:49:03

if you wanna pass table params into stored procedure, you must necessary set TypeName property for your table params.

SqlParameter codesParam = new SqlParameter(CODES_PARAM, SqlDbType.Structured);
            SqlParameter factoriesParam = new SqlParameter(FACTORIES_PARAM, SqlDbType.Structured);

            codesParam.Value = tbCodes;
            codesParam.TypeName = "[dbo].[MES_CodesType]";
            factoriesParam.Value = tbfactories;
            factoriesParam.TypeName = "[dbo].[MES_FactoriesType]";


            var list = _context.Database.SqlQuery<MESGoodsRemain>($"{SP_NAME} {CODES_PARAM}, {FACTORIES_PARAM}"
                , new SqlParameter[] {
                   codesParam,
                   factoriesParam
                }
                ).ToList();

@Brian Vander Plaats 2015-11-05 17:11:59

Using your example, here are two ways to accomplish this:

1 - Use Stored procedure mapping

Note that this code will work with or without mapping. If you turn off mapping on the entity, EF will generate an insert + select statement.

protected void btnSave_Click(object sender, EventArgs e)
{
     using (var db = DepartmentContext() )
     {
        var department = new Department();

        department.Name = txtDepartment.text.trim();

        db.Departments.add(department);
        db.SaveChanges();

        // EF will populate department.DepartmentId
        int departmentID = department.DepartmentId;
     }
}

2 - Call the stored procedure directly

protected void btnSave_Click(object sender, EventArgs e)
{
     using (var db = DepartmentContext() )
     {
        var name = new SqlParameter("@name, txtDepartment.text.trim());

        //to get this to work, you will need to change your select inside dbo.insert_department to include name in the resultset
        var department = db.Database.SqlQuery<Department>("dbo.insert_department @name", name).SingleOrDefault();

       //alternately, you can invoke SqlQuery on the DbSet itself:
       //var department = db.Departments.SqlQuery("dbo.insert_department @name", name).SingleOrDefault();

        int departmentID = department.DepartmentId;
     }
}

I recommend using the first approach, as you can work with the department object directly and not have to create a bunch of SqlParameter objects.

@edtruant 2016-04-19 09:57:27

Be careful, is the second example the change is not tracked by the dbContext

@edtruant 2016-04-19 10:03:49

EDIT.Use the System.Data.Entity.DbSet<TEntity>.SqlQuery(String, Object[]) instead.

@Brian Vander Plaats 2016-04-19 14:46:17

@edtruant The dbContext does appear to track the change. To test, I looked at db.<DbSet>.Count() before and after the insert statement. In both methods, the count increased by one. For completeness I added the alternate method to the example.

@xr280xr 2017-09-28 14:43:11

I don't see any reference to the stored procedure in the first example.

@Brian Vander Plaats 2017-09-28 21:00:46

@xr280xr the insert_department is referenced in the modelBuilder expression in the OP's question. That's the advantage to mapping things this way because it effectively functions the same way as if you were letting EF generate the insert/update/delete statements

@binki 2018-05-04 23:55:26

@xr280xr There’s no reason to use EF if you’re going to call the stored procedure manually.

@Gabriel Andrés Brancolini 2014-01-22 13:13:52

Take a look to this link that shows how works the mapping of EF 6 with Stored Procedures to make an Insert, Update and Delete: http://msdn.microsoft.com/en-us/data/dn468673

Addition

Here is a great example to call a stored procedure from Code First:

Lets say you have to execute an Stored Procedure with a single parameter, and that Stored Procedure returns a set of data that match with the Entity States, so we will have this:

var countryIso = "AR"; //Argentina

var statesFromArgentina = context.Countries.SqlQuery(
                                      "dbo.GetStatesFromCountry @p0", countryIso
                                                    );

Now lets say that we whant to execute another stored procedure with two parameters:

var countryIso = "AR"; //Argentina
var stateIso = "RN"; //Río Negro

var citiesFromRioNegro = context.States.SqlQuery(
                            "dbo.GetCitiesFromState @p0, @p1", countryIso, stateIso
                          );

Notice that we are using index-based naming for parameters. This is because Entity Framework will wrap these parameters up as DbParameter objects fro you to avoid any SQL injection issues.

Hope this example helps!

@Dib 2015-08-30 08:25:58

Mindless passenger has a project that allows for multiple results sets to be returned from a stored proc using entity framework. One of his examples below....

using (testentities te = new testentities())
{
    //-------------------------------------------------------------
    // Simple stored proc
    //-------------------------------------------------------------
    var parms1 = new testone() { inparm = "abcd" };
    var results1 = te.CallStoredProc<testone>(te.testoneproc, parms1);
    var r1 = results1.ToList<TestOneResultSet>();
}

@Pawel 2014-05-07 06:16:11

You can now also use a convention I created which enables invoking stored procedures (including stored procedures returning multiple resultsets), TVFs and scalar UDFs natively from EF.

Until Entity Framework 6.1 was released store functions (i.e. Table Valued Functions and Stored Procedures) could be used in EF only when doing Database First. There were some workarounds which made it possible to invoke store functions in Code First apps but you still could not use TVFs in Linq queries which was one of the biggest limitations. In EF 6.1 the mapping API was made public which (along with some additional tweaks) made it possible to use store functions in your Code First apps.

Read more

I pushed quite hard for the past two weeks and here it is – the beta version of the convention that enables using store functions (i.e. stored procedures, table valued functions etc.) in applications that use Code First approach and Entity Framework 6.1.1 (or newer). I am more than happy with the fixes and new features that are included in this release.

Read more.

@GoldBishop 2016-07-22 17:18:43

Actually since 4.0, you could execute SProcs without the Model. You needed to execute Raw SQL statements instead of object property. Even with 6.1.x, you have to use either SqlQuery<T> or ExecuteSqlCommand to obtain a similar effect.

@Filipe Leite 2014-08-21 14:30:11

All you have to do is create an object that has the same property names as the results returned by the stored procedure. For the following stored procedure:

    CREATE PROCEDURE [dbo].[GetResultsForCampaign]  
    @ClientId int   
    AS
    BEGIN
    SET NOCOUNT ON;

    SELECT AgeGroup, Gender, Payout
    FROM IntegrationResult
    WHERE ClientId = @ClientId
    END

create a class that looks like:

    public class ResultForCampaign
    {
        public string AgeGroup { get; set; }

        public string Gender { get; set; }

        public decimal Payout { get; set; }
    }

and then call the procedure by doing the following:

    using(var context = new DatabaseContext())
    {
            var clientIdParameter = new SqlParameter("@ClientId", 4);

            var result = context.Database
                .SqlQuery<ResultForCampaign>("GetResultsForCampaign @ClientId", clientIdParameter)
                .ToList();
    }

The result will contain a list of ResultForCampaign objects. You can call SqlQuery using as many parameters as needed.

@GoldBishop 2016-07-22 17:17:02

For one off situations, this would work great. I find that the SProc definition should be tightly coupled with the class that inherits from DBContext, instead of out in the "wheat fields" of the product.

Related Questions

Sponsored Content

27 Answered Questions

22 Answered Questions

[SOLVED] Search text in stored procedure in SQL Server

11 Answered Questions

[SOLVED] How can I get Id of inserted entity in Entity framework?

18 Answered Questions

[SOLVED] Function vs. Stored Procedure in SQL Server

28 Answered Questions

[SOLVED] Fastest Way of Inserting in Entity Framework

5 Answered Questions

16 Answered Questions

[SOLVED] Entity Framework vs LINQ to SQL

1 Answered Questions

7 Answered Questions

[SOLVED] Entity Framework 5 Updating a Record

0 Answered Questions

Sponsored Content