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

Navigation
4

I'm 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

Code C # 

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

Throws me: 

Must declare the scalar variable "@fecha".

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

    
asked by Tuco 11.01.2016 в 17:44
source

4 answers

11

You have to pass the parameters as a SqlParameter object, the values are not enough. 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 fecha must be assigned to the parameter @fecha .

    
answered by 11.01.2016 / 17:50
source
5

The parameters must be specified as instances of SqlParameter 

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)
);
    
answered by 11.01.2016 в 17:56
4

Instead of using the SqlQuery () it would not be better to map the procedure to an entity using the MapToStoredProcedures

Code First Insert / Update / Delete Stored Procedures

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

The article explains how to perform this type of mapping

greetings

    
answered by 11.01.2016 в 17:50
0

I apply this way to implement an SP using EF: 

empresaEntities _empleados = new empresaEntities();

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

            _empleados.SaveChanges();

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

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

Last but not least. Entities are called and changes are saved with SavesChanges ();

That way it works for me.

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

    
answered by 26.02.2018 в 20:44
Show text when hover in an image Putting text on an edge