Clash Royale CLAN TAG

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

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:

Department

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?

16 Answers
16

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

DbContext

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


Here is an example on how to call a stored procedure with input and output parameters and that it returns a FormularioVentasTO (YourEntityType)

public FormularioVentasTO GetFormularioVentas(string idUsuario, string idFormulario)
{
FormularioVentasTO formVentas;
string sqlQuery;
SqlParameter sqlParams;

formVentas = null;

try
{
sqlQuery = "sp_ConsultarSolicitud @idUsuario, @idSolicitud, codError";

sqlParams = new SqlParameter
{
new SqlParameter { ParameterName = "@idUsuario", Value =idUsuario , Direction = System.Data.ParameterDirection.Input},
new SqlParameter { ParameterName = "@idSolicitud", Value =idFormulario, Direction = System.Data.ParameterDirection.Input },
new SqlParameter { ParameterName = "@codError", Value =-99, Direction = System.Data.ParameterDirection.Output },
};

using (PortalFinandinaPriv dbContext = new PortalFinandinaPriv())
{
formVentas = dbContext.Database.SqlQuery<FormularioVentasTO>(sqlQuery, sqlParams).SingleOrDefault();
}
}
catch (Exception ex)
{
//handle, Log or throw exception
}

return formVentas;
}


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

Stored Procedures with Multiple Result Sets

"storedProcedureName @param1, @param2"

params

System.Data.SqlClient.SqlParameter

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

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.

ResultForCampaign

SqlQuery

I solved it with ExecuteSqlCommand

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)
{
int resault;
try
{
using (DBContex reposit = new DBContex())
{
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

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.

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)

MapToStoredProcedures()

context

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

SqlQuery

.MapToStoredProcedures(s =>

Add

.Insert(i => i.HasName("insert_department")

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.

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

_db

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!

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;


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();
}


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();


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>();
}


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();


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);
}


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.


This post cound help you:

Execute Stored Procedure using DBContext

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.