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
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.