How to call Stored Procedure in .Net using Entity Framework Core?

 In Entity Framework Core, you can call a stored procedure using the FromSqlRaw method of the DbSet class. Here are the steps to call a stored procedure in Entity Framework Core:

1. Define a model class that represents the result set of the stored procedure. The properties of the model class should match the column names of the stored procedure result set.

csharp
public class CustomerOrder
{
    public int CustomerId { get; set; }
    public string CustomerName { get; set; }
    public int OrderId { get; set; }
    public DateTime OrderDate { get; set; }
}

2. Define a DbContext class that includes a DbSet property for the model class.

csharp
public class MyDbContext : DbContext
{
    public DbSet<CustomerOrder> CustomerOrders { get; set; }

    // ...
}

3. Call the stored procedure using the FromSqlRaw method of the DbSet class.

csharp
var customerOrders = dbContext.CustomerOrders.FromSqlRaw("EXECUTE dbo.GetCustomerOrders").ToList();

Replace GetCustomerOrders with the name of your stored procedure. This will execute the stored procedure and map the result set to a list of CustomerOrder objects.

4. You can also pass parameters to the stored procedure by including them in the SQL query.

csharp
var customerId = 123;
var customerOrders = dbContext.CustomerOrders.FromSqlRaw("EXECUTE dbo.GetCustomerOrders @CustomerId", customerId).ToList();

This will execute the stored procedure with the @CustomerId parameter set to 123.

That's it! You have now called a stored procedure in Entity Framework Core and mapped the result set to a list of model objects.