Invalid output when trying to invoke stored-procedure returning output from entityframework core -

Question

Hi gents, can you sort out the following for me. Let me explain the scenario in detail.

I have a MSSQL stored procedure that returns me output value. The output value is of int type.

Stored Procedure:

CREATE procedure [dbo].[RecipeAverageRating]
@RecipeId int,
@AverageRating int output
as
begin
	select @AverageRating=ISNULL(CAST(AVG(ReviewNumber * 1.0) AS int), 0)
						  from Reviews r
						  where r.RecipeId=@RecipeId
end

For a given i/p value of RecipeId, calculates and returns the average of ReviewNumber, and if no matching recipes found, returns 0.

Now I am trying to call & execute this SP from my .NETCORE 8.0 webAPI backend, using EntityFrameworkCore.

My backend code:

[HttpGet("{recipeId}")]
[AllowAnonymous]
public async Task

Answer 1

Hi, @P. G. Choudhury. Welcome to Microsoft Q&A. 

Solution: Just like DbSet, define your GetAverageRatingForRecipe in DbContext Reference code (Assume your DbContext is MyDbContext)

public partial class MyDbContext : DbContext
{
    public virtual DbSet<Review> Reviews { get; set; }

    public int GetRecipeAverageRating(int recipeId) 
    { 
        var averageRatingParam = new SqlParameter 
        {
            ParameterName = "@AverageRating", 
            SqlDbType = System.Data.SqlDbType.Int, 
            Direction = System.Data.ParameterDirection.Output 
        }; 


        Database.ExecuteSqlRaw("EXEC dbo.RecipeAverageRating @RecipeId, @AverageRating OUT", new SqlParameter("@RecipeId", recipeId), averageRatingParam); 
        return (int)averageRatingParam.Value; 
    }
    …
}

Call and get directly in [HttpGet("{recipeId}")]

 [HttpGet("{recipeId}")]

public async Task<ActionResult> GetAverageRatingForRecipe(int recipeId)
{
          return Ok(_dbContext.GetRecipeAverageRating(recipeId));
}

Assume your table structure and data are as follows: Test Results:

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

your SP is returns no rows, rather it update an output parameter. your EF call uses SqlQueryRaw(), which does not use the sql command parameters so your sql text is using undefined variables. as you are not using Dynamic SQL you can just use SqlQuery() which also does not use sql command parameters.

try:

  var sql = @"
    declare @AverageRating as int; 
	exec RecipeAverageRating {recipeId}, @AverageRating OUT; 
	select @AverageRating as AverageRating;
  ";
  var averageRatingFromSP = _dbContext.Database.SqlQuery<int>(sql).First();

note: SqlQuery() uses custom string interpolation so its injection safe, unlike SqlQueryRaw().

Answer 3

Entity Framework uses ADO.NET.

Example proc

DROP PROCEDURE IF EXISTS dbo.OutputParameterEx;
GO
CREATE PROCEDURE dbo.OutputParameterEx (
	@RecipeId int,
	@AverageRating int output
)
AS
BEGIN
	SET @AverageRating = @RecipeId * 2;
END
GO
--Test
DECLARE @AvgRating	INT
EXECUTE dbo.OutputParameterEx
	@RecipeId = 2,
	@AverageRating = @AvgRating OUTPUT;
SELECT @AvgRating;

Example ADO.NET

using var db = new DemoDbContext();
using (var command = db.Database.GetDbConnection().CreateCommand())
{
    command.CommandText = "dbo.OutputParameterEx";
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(new SqlParameter("@RecipeId", 2));
    SqlParameter AverageRating = new SqlParameter("@AverageRating", SqlDbType.Int)
    {
        Direction = ParameterDirection.Output,
    };
    command.Parameters.Add(AverageRating);
    db.Database.OpenConnection();
    var results = command.ExecuteNonQuery();
    db.Database.CloseConnection();
    Console.WriteLine($"AverageRating = {AverageRating.Value}");
}

Answer 4

Your procedure is fairly simple so if you want to return a scalar then...

DROP PROCEDURE IF EXISTS dbo.OutputParameterEx2;
GO
CREATE PROCEDURE dbo.OutputParameterEx2 (
	@RecipeId int
)
AS
BEGIN
	SELECT @RecipeId * 2;
END
GO
--Test
EXECUTE dbo.OutputParameterEx2
	@RecipeId = 2

C#

using var db = new DemoDbContext();
var result = db.Database.SqlQuery<int>($"EXECUTE dbo.OutputParameterEx2 @RecipeId = {2}").ToList();
Console.WriteLine($"AverageRating = {result.FirstOrDefault()}");

SqlQuery generates the following SQL

exec sp_executesql N'EXECUTE dbo.OutputParameterEx2 @RecipeId = @p0',N'@p0 int',@p0=2

This is covered in the official docs.