Home / Questions / 2378
Accepted
The One
Asked: 2020-01-12 08:44:44 +0800 CST 2020-01-12 08:44:44 +0800 CST

How to call a stored procedure from Entity Framework 6 code first

  • 772

I am trying to call this stored procedure from C#

alter PROCEDURE GetLecturasEnRangoDeFechas
-- Add the parameters for the stored procedure here
@fecha as smalldatetime,
@idParteMaquina int,
@inicio int,
@inicioMasDuracion int,
@dia smalldatetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @inicioFecha as smalldatetime
set @inicioFecha=CONVERT(smalldatetime, DATEADD(s,@inicio,0))

declare @inicioMasDuracionFecha as smalldatetime
set @inicioMasDuracionFecha=CONVERT(smalldatetime, DATEADD(s,@inicioMasDuracion,0))

-- Insert statements for procedure here
select * from Lectura
where 
cast(Fecha as Date) =cast(@fecha As Date) and
IdParteMaquina=@idParteMaquina and
(Fecha>=@inicioFecha and
Fecha<=@inicioMasDuracionFecha)
or
(@inicioMasDuracionFecha>@dia and
(@inicioFecha+Fecha<=@inicioMasDuracionFecha))

END
GO

C# code

using(ProduccionContexto pc=new ProduccionContexto())
{
 var lects=pc.Database.SqlQuery<Lectura>("GetLecturasEnRangoDeFechas",fecha,partemaquina.Id, inicio.TotalSeconds, inicioMasDuracion.TotalSeconds, dia);
}

This throws me:

Procedure or function 'GetLecturasEnRangoDeFechas' expects parameter '@fecha', which was not supplied.

And if I do it like this:

using(ProduccionContexto pc=new ProduccionContexto())
{
 var lects=pc.Database.SqlQuery<Lectura>("GetLecturasEnRangoDeFechas @fecha,@idParteMaquina,@inicio,@inicioMasDuracion,@dia",fecha,partemaquina.Id, inicio.TotalSeconds, inicioMasDuracion.TotalSeconds, dia);
}

Throw me:

Must declare the scalar variable "@fecha".

What is the correct syntax to call a SP from C# EF 6 CodeFirst

c#

4 Answers

  1. Best Answer

    You have to pass the parameters as an object SqlParameter, it is not enough with the values. So:

    var lects = pc.Database.SqlQuery<Lectura>(
    "GetLecturasEnRangoDeFechas @fecha, @idParteMaquina, @inicio, @inicioMasDuracion, @dia",
    new SqlParameter("@fecha",fecha),
    new SqlParameter("@idParteMaquina",partemaquina.Id),
    new SqlParameter("@inicio",inicio.TotalSeconds),
    new SqlParameter("@inicioMasDuracion",inicioMasDuracion.TotalSeconds),
    new SqlParameter("@dia",dia));

    Entity Framework only has access to the value, it is not aware that the variable fechashould be assigned to the parameter @fecha.

    • 11

  2. Instead of using SqlQuery() wouldn't it be better to map the procedure to an entity using MapToStoredProcedures

    Code First Insert/Update/Delete Stored Procedures

    dee this form is much simpler to indicate which properties of the entity map with the parameters

    The article explains how to perform this type of mapping

    Cheers

    • 5

  3. Parameters must be specified as instances ofSqlParameter

    pc.Database.SqlQuery<Lectura>(
    "GetLecturasEnRangoDeFechas @fecha, @idParteMaquina, @inicio, @inicioMasDuracion, @dia",
    new SqlParameter("fecha", fecha),
    new SqlParameter("idParteMaquina", partemaquina.Id),
    new SqlParameter("inicio", inicio.TotalSeconds)
    new SqlParameter("inicioMasDuracion", inicioMasDuracion.TotalSeconds)
    new SqlParameter("dia", dia)
);
    • 5

  4. I apply this way to implement a SP using EF:

    empresaEntities _empleados = new empresaEntities();

            var Borrar_Empleado = _empleados.DeleteEmpleado(txtcedula.Text);

            _empleados.SaveChanges();

    I declare my entities instance, that is, my connection to the database.

    Then I declare a variable that will contain the SP: " DeleteEmployee ", followed by the entities + the name of the SP and then the parameter that it needs for its execution.

    Last but not least. The entities are called and the changes are saved with SavesChanges();

    That way it works for me.

    I hope this answer will be useful for future visits to the question asked.

    • 0