EF core procedure call

2

I'm trying to use the and I'm having 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. '

    
asked by anonymous 30.05.2018 / 19:15

2 answers

0

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

By the code of the question there is an error in creating the parameters that Stored Procedure needed to work, so with a minimum example

  • 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 that need to use and pass the corresponding value and after only calling its Procedure by method #

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 at DbContext o 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 have been affected.

Coming back Your question based on this explanation is simple to solve :

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 do not know how yours is Stored Procedure , so the parameters were used same in the query, which needs some adjustment by some unexpected error, but, the example given above is functional.

04.06.2018 / 16:14
0

Well I found a solution for calling the procedure in the entity framework core, here's 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();

}
    
01.06.2018 / 21:22