I tried code to execute the Stored Procedure, but I'm facing some issues on my code. help me to solve this error.

Controller

using Employee_API.Data;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Employee_API.Models;
using MySqlConnector;

namespace Employee_API.Controllers
{
  [ApiController]
  [Route("api/[controller]")]
  public class EmpController : Controller
  {

    private readonly EmpDbContext _empDbContext;

    public EmpController(EmpDbContext empDbContext)
    {

        _empDbContext = empDbContext;
    }
            
    [HttpPost("AddEmp")]    
    public async Task<IActionResult> AddEmp(Employee empRequest)
    {
        var name = new MySqlParameter("@Name", empRequest.Name);
        var empid = new MySqlParameter("@EmpId", empRequest.EmpId);
        var design = new MySqlParameter("@Designation", empRequest.Designation);
        var dob = new MySqlParameter("@DOB", empRequest.DOB);
        var mobile = new MySqlParameter("@Mobile", empRequest.Mobile);
        var mail = new MySqlParameter("@Mail", empRequest.Mail);

        await _empDbContext.emp_data.ExecuteSqlRaw("EXEC Add_New_Employee @Name, @EmpId, @Designation, @DOB, @Mobile, @Mail", name, empid, design, dob, mobile, mail).ToListAsync();

       return Ok(empRequest);

    }
  }
}

DbContext

using Employee_API.Models;
using Microsoft.EntityFrameworkCore;

namespace Employee_API.Data
{
  public class EmpDbContext : DbContext
  {
    public EmpDbContext(DbContextOptions options) : base(options)
    {
    }

    public DbSet<Employee> emp_data { get; set; } 
   }  
 }

Packages

[]

(https://i.stack.imgur.com/JN8Qo.png)

I've tried on many online search, but I couldn't find any solution for this code.

Thanks in Advance.

CodePudding user response：

In Net core 6, the method name has changed to FromSqlRaw.

1. Add package Microsoft.EntityFrameworkCore.Relational

2.Try:

 await _empDbContext.emp_data.FromSqlRaw("....");

CodePudding user response：

Running raw SQL is quite the opposite of what's EntityFramework for.

In your code, you're calling ExecureSqlRaw against DBSet, but you should execute it against Database, like this

_empDbContext.Database.ExecuteSqlRaw("...")