How to call stored procedure in .net 6 using EF Core

Question

I have a procedure I need to call it from my code using EFCore

Below is my procedure structure

Create PROCEDURE [dbo].[BusinessOrders]  
(
    @StatusID INT ,
    @BusinessBranchID INT,
    @LangID INT ,
    @PurchasedOn NVARCHAR(MAX) ,
    @Search NVARCHAR(MAX),
    @PageSize INT,
    @PageNo INT 
)
AS
BEGIN

END

This is my code function

public IEnumerable<BusinessOrderEntity.BODB> GetBusinessOrders(int StatusID, int BusinessBranchID, int LangID, string PurchasedOn = "", string Search = "", int PageSize = 10, int PageNo = 1)
{
    // here need to call procedure.
}

I've find different solution but it does not working with my context class

Answer 1

EF Core supports the old style for calling stored procedures:

private async Task ExecuteStoredProc()
{
    DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();

    cmd.CommandText = "dbo.sp_DoSomething";
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar) { Value = "Steve" });
    cmd.Parameters.Add(new SqlParameter("@lastName", SqlDbType.VarChar) { Value = "Smith" });

    cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.BigInt) { Direction = ParameterDirection.Output });

    if (cmd.Connection.State != ConnectionState.Open)
    {
        cmd.Connection.Open();
    }

    await cmd.ExecuteNonQueryAsync();

    long id = (long)cmd.Parameters["@Id"].Value;
}

You can check similar questions here:

• How to run stored procedures in Entity Framework Core?

• How do I call stored procedures in EF Core 6 using named parameters?

Answer 2

You can use FromSqlInterpolated method.

context.BODB.FromSqlInterpolated(
    $"[dbo].[BusinessOrders] {statusId}, {businessBranchId}, {langId}, {purchasedOn}, {search}, {pageSize}, {pageNo}");

See SQL Queries for more info.