Share via

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

P. G. Choudhury 101 Reputation points
2025-01-06T17:02:27.0333333+00:00

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
Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
770 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
109 questions
Entity Framework Core Training
Entity Framework Core Training
Entity Framework Core: A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.Training: Instruction to develop new skills.
5 questions
Accepted answer
  1. Hongrui Yu-MSFT 3,730 Reputation points Microsoft Vendor
    2025-01-07T09:18:53.5266667+00:00

    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:Picture1 Test Results:

    Picture2

    Picture3

    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.

    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bruce (SqlWork.com) 69,276 Reputation points
    2025-01-06T18:24:17.9066667+00:00

    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().

  2. AgaveJoe 29,281 Reputation points
    2025-01-06T19:44:23.2133333+00:00

    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}");
}
    0 comments
  3. AgaveJoe 29,281 Reputation points
    2025-01-06T20:00:50.15+00:00

    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.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.