Microsoft.Data.SqlClient.SqlException encountered while trying to call/execute a stored procedure in a .NET Core project

I am trying to call a stored procedure (that needs an input parameter) through ADO.NET code.

The exception I get is as follows:

Microsoft.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure 'EXEC FindRecipesByTitle'.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)

My stored procedure:

CREATE procedure [dbo].[FindRecipesByTitle]
    @title nvarchar(max)
AS
    SET NOCOUNT ON;
BEGIN
    SELECT
        Recipes.Id,
        Recipes.Name AS 'RecipeName',
        Recipes.PostedById,
        CASE
            WHEN Recipes.DishTypeId = 1 THEN 'Appetizers, Beverages'
            WHEN Recipes.DishTypeId = 2 THEN 'Soups, Salads'
            WHEN Recipes.DishTypeId = 3 THEN 'Vegetables'
            ELSE Recipes.DishTypeId 
        END AS DishTypeId,
        CASE
            WHEN Recipes.VegNonVeg = 0 THEN 'Vegetarian'
            WHEN Recipes.VegNonVeg = 1 THEN 'Non Vegetarian'
            ELSE Recipes.VegNonVeg 
        END AS VegNonVeg,
        Recipes.EstimatedTimeInMinutes,
        Recipes.Ingredients,
        Recipes.Description,
        Recipes.ImagePath,
        Recipes.CreatedAt,
        DishTypes.Name AS 'DishName'
    FROM
        [Recipes]
    JOIN
        [DishTypes] ON Recipes.DishTypeId = DishTypes.Id
    WHERE
        Recipes.Name LIKE '%' + @title + '%'
END

Code where I am calling the stored procedure:

public async Task<List<RecipeViewModel>> GetRecipeByTitleFromSP(string title)
{
    List<RecipeViewModel> lstRcpVM = new List<RecipeViewModel>();

    using (var cmd=_dbContext.Database.GetDbConnection().CreateCommand())
    {
        cmd.CommandText = $@"EXEC FindRecipesByTitle";
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(new SqlParameter("@title", title));

        _dbContext.Database.OpenConnection();

        using (var reader = cmd.ExecuteReader())
        {
            while(reader.Read())
            {
                lstRcpVM.Add(new RecipeViewModel
                {
                    RecipeName = reader.GetString("RecipeName"),
                    PostedById = reader.GetString("PostedById"),
                    DishTypeId = reader.GetString("DishTypeId"),
                    VegNonVeg = reader.GetString("VegNonVeg"),
                    EstimatedTimeInMinutes = reader.GetInt32("EstimatedTimeInMinutes"),
                    Ingredients = reader.GetString("Ingredients"),
                    Description = reader.GetString("Description"),
                    ImagePath = reader.GetString("ImagePath"),
                    CreatedAt = Convert.ToDateTime(reader["CreatedAt"]),
                    DishName = reader.GetString("DishName")
                });
            }
        }

        _dbContext.Database.CloseConnection();
    }

    return lstRcpVM;
}

Where _dbContext is my Entity Framework Core dbContext and RecipeViewModel is a DTO, which I haven't posted for sake of brevity.

When I try to run the code I am getting that exception I posted above, which is really strange, because I can see that the stored procedure is very well there.

What am I doing wrong? could you please tell me? What am I missing? Is it the way I am passing the parameter to the stored procedure? Or is it something else?

Can you help me sort this out?

Thanks people

With CommandType.StoredProcedure, you don't include 'EXEC'. The whole point of that mode is that it builds out the RPC call for you, rather than you providing a TSQL batch that includes 'EXEC' and the parameter markers.

So just make CommandText the name of the stored procedure.

cmd.CommandText = $@"FindRecipesByTitle";
cmd.CommandType = CommandType.StoredProcedure;