EF core called the Procedure

Question

I’m trying to use the Entity-framework-core 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.'

Answer 1

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:

• Quick overview of the Entity Framework Core
• Working with Stored Procedure in Entity Framework Core
• How to execute Stored Procedure in Entity Framework Core

Answer 2

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

}