EF core called the Procedure

Asked

Viewed 368 times

2

I’m trying to use the and I have a problem, I need to call a procedure:

var status = _context.Set<Usuario>().FromSql("exec LoginUsuario @chave='ROBr', @senha=null,@fonteConfiavel=0").ToList();

Error:

System.Invalidoperationexception: 'The required column 'ID' was not present in the Results of a 'Fromsql' Operation.'

  • I have a suspicion of what it is, but not sure, so I will add here in the comments, try as follows var status = _context.Set<Usuario>().FromSql("exec LoginUsuario @p0, @p1, @p2", parameters: new[] {"ROBr", null, "0" }).ToList();

  • By the error it seems that some parameter is missing in the procedure. Very specific it seems that the ID

  • So it hasn’t worked yet, but I’ve captured the Procedure call : exec sp_executesql N'exec Loginusuario @chave,@password,@fonteConfiavel ',N'@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000)',@P0=N'Robr',@P1=NULL,@P2=N'true

  • var status = _context. Set<Usuario>(). Fromsql("exec Loginusuario @key,@password,@fonteDescover knowed", Parameters: new[] { "Robr", null, "true" }). Tolist();

  • Two things: i) Try removing "exec" from FromSql FromSql(" LoginUsuario....; ii) Turning exec LoginUsuario "ROBr", null, 0 right in the database, of the right?

  • exec sp_executesql N'Loginusuario ',N'@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 int',@P0=N'@key=Rob',@P1=NULL,@P2=0 ,this is called and tested in the database and gives the same error Msg 201, Level 16, State 4, Procedure Loginusuario, Line 73 Procedure or Function 'Loginusuario' expects Parameter '@chave', which was not supplied.

  • is missing passing parameters to your procedure

  • put the Procedure on your question!?

Show 3 more comments

2 answers

0


To call a Stored Procedure in the Entity Framework Core there are two options in the method Fromsql and Database.Executesqlcommand. The Fromsql is used for return (SELECT) and already the Database.Executesqlcommand to insert, change and delete data from a table, basically that’s it.

By the question code there is error in the creation of the parameters that the Stored Procedure needed to work, so with a minimal example I’ll explain how to make the code:

  • FromSql (with feedback)

Store Procedure

CREATE PROCEDURE [dbo].[Proc_Login] 
    @UserName varchar(50)
AS
BEGIN
    SET NOCOUNT ON;    
    SELECT * FROM [Login] WHERE [Login].[UserName] = @UserName;
END

To use the code is very simple, create a SqlParameter with all the parameters you need to use and pass the corresponding value and then just call your Procedure by the method Fromsql the same way it was created in your bank:

using (DatabaseContext db = new DatabaseContext())
{              
    var param = new SqlParameter("@UserName", "Us1");
    var result = db.Login
                   .FromSql("Proc_Login @UserName", param)
                   .FirstOrDefault();
}

  • Database.ExecuteSqlCommand

Store Procedure

CREATE PROCEDURE Proc_Update_Passord_Login 
    @Password varchar(50),
    @Id int
AS
BEGIN           
    UPDATE [Login] SET [Login].[Password] = @Password WHERE [Login].[Id] = @Id;
END

To use this procedure update use in your DbContext the Database.Executesqlcommand as follows:

using (DatabaseContext db = new DatabaseContext())
{    
    var parameters = new[]
    {
        new SqlParameter("@Password", "abcd"),
        new SqlParameter("@Id", 1)
    };
    int count = db.Database
        .ExecuteSqlCommand("Proc_Update_Passord_Login @Password, @Id", parameters);

}

where the variable count returns the number of rows that were affected.


Returning your question based on this explanation is simple the solution:

var parameters = new[]
{
    new SqlParameter("@chave", "abcd"),
    new SqlParameter("@senha", null),
    new SqlParameter("@fonteConfiavel", 0)
};
var status = _context.Set<Usuario>()
                    .FromSql("LoginUsuario @chave,@senha,@fonteConfiavel", parameters)
                    .ToList();

Note: I don’t know how yours is Stored Procedure, so the parameters were used equal in asking, may need some adjustment for some unexpected error, but, the example giving earlier is functional.

References:

0

Good found a solution to call Procedure in Entity framework core,follow below the solution I found:

var cmd = _context.Database.GetDbConnection().CreateCommand();
//Abrir conexão com o banco.
_context.Database.GetDbConnection().Open();
//Chama a sua procedure.
cmd.CommandText = "LoginUsuario";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//Passar os parametros necessarios para chamar a procedure.
cmd.Parameters.Add(new SqlParameter("@chave", email));
cmd.Parameters.Add(new SqlParameter("@senha", senha));
cmd.Parameters.Add(new SqlParameter("@fonteConfiavel", false));
//Executar a chamada.
using (var rd = cmd.ExecuteReader())
{

   List<LoginUsuario> listobjet = new List<LoginUsuario>();  
   foreach (var item in rd)
   {

       LoginUsuario loginUsuario = new LoginUsuario();
       // Aqui voce coloca os pametro que retorna da procedure.
       loginUsuario.Cookie = rd[0].ToString();
       loginUsuario.LoginStatus = Convert.ToInt32(rd[1].ToString());
       loginUsuario.UsuarioID = Convert.ToInt32(rd[2].ToString());
       listobjet.Add(loginUsuario);

   }
   //Fechar conexão
   _context.Database.GetDbConnection().Close();   
   //retornar os valores
    return listobjet.FirstOrDefault();

}

Browser other questions tagged

You are not signed in. Login or sign up in order to post.