Question 252236 in ASP.NET Question posted in
The official documentation can be found here.

Asp.net – Exception while running stored procedure from EF Core

My migration code is as in the picture. It forms smoothly.

SELECT * 
FROM user_topic_total_score_procedure('e4811f68-91b9-4fa2-802c-b2eb36abf6af', 'f23a157d-6116-46b6-0020-3f9bdc850001')

It works when I call it like this in Navicat. But when I run it on the .NET side, I get an error. I also put the .NET code between the images.

The error I get is:

Parameter ‘score’ referenced in SQL but is an out-only parameter

I tried many methods on the internet, but it got stuck on something but I couldn’t notice it. Thank you in advance for your help.
migration
method call

I scanned various codes on the internet, but I could not find this type of code for Postgres stored procedure.

Migration Code:
protected override void Up(MigrationBuilder migrationBuilder)
{

        migrationBuilder.Sql($@"CREATE OR REPLACE FUNCTION user_topic_total_score_procedure(
                                par_user_id VARCHAR(36),    
                                par_topic_id VARCHAR(36),
                                OUT score NUMERIC
                              ) AS $$
                            BEGIN
                                SELECT SUM(uqa.score)
                                INTO score
                                FROM user_question_answers uqa
                                INNER JOIN questions q ON q.ID = uqa.question_id
                                WHERE uqa.answer_cancelled = 'f' AND uqa.user_id = par_user_id AND q.topic_id = par_topic_id
                                GROUP BY uqa.user_id, q.topic_id;
                            END;
                            $$ LANGUAGE plpgsql;");

    }

Calling Method:

public async Task GetUserTopicTotalScoreAsync(string user_id, string topic_id){
try
{

    var scoreParameter = new NpgsqlParameter("@score", NpgsqlDbType.Numeric)
    {
        Direction = ParameterDirection.Output
    };

    var sql = "SELECT * from user_topic_total_score_procedure(@par_user_id, @par_topic_id,@score)";

    await _context.Database.ExecuteSqlRawAsync(
        sql,
        new NpgsqlParameter("@par_user_id", NpgsqlDbType.Varchar) { Value = user_id },
        new NpgsqlParameter("@par_topic_id", NpgsqlDbType.Varchar) { Value = topic_id },
        scoreParameter
    );

    var result = (decimal)scoreParameter.Value;
    return result;
    } catch (Exception)
    {
        return 0;
    }
}

Tags:

2

Answers


  1. 0

    There is a difference between the plain SQL call to your function, and the SQL call used in your .NET code: Two parameters vs three parameters.

    FROM user_topic_total_score_procedure('e4811f68-91b9-4fa2-802c-b2eb36abf6af', 'f23a157d-6116-46b6-0020-3f9bdc850001')

    And

    from user_topic_total_score_procedure(@par_user_id, @par_topic_id,@score)

    And since score is labeled output from your SQL function, you can’t use this as input.

    Solution:

    var sql = "SELECT user_topic_total_score_procedure(@par_user_id, @par_topic_id)";

    There is no need to use * FROM because you only get a single item in return, not a record.

    And besides this, there is no need for plpgsql either. This is better done using the SQL language:

    CREATE OR REPLACE FUNCTION user_topic_total_score_procedure( -- it's not a procedure!
    par_user_id VARCHAR(36),
    par_topic_id VARCHAR(36),
    OUT score NUMERIC
) AS
$$
SELECT SUM(uqa.score)
FROM user_question_answers    uqa
         INNER JOIN questions q ON q.id = uqa.question_id
WHERE uqa.answer_cancelled = 'f'
  AND uqa.user_id = par_user_id
  AND q.topic_id = par_topic_id;
$$ LANGUAGE sql;

    I would also use a different name for this function, something without the suggestion that is is a procedure.

    Login or Signup to reply.
  2. 0

    u should handle the OUT parameter. You can do this by using the ExecuteFunctionAsync method in Entity Framework Core, which is designed for calling stored procedures with output parameters. Here’s how you can modify your code:

    public async Task<decimal> GetUserTopicTotalScoreAsync(string user_id, string topic_id)
{
    try
    {
        var scoreParameter = new NpgsqlParameter("@score", NpgsqlDbType.Numeric)
        {
            Direction = ParameterDirection.Output
        };

        await _context.Database.ExecuteSqlInterpolatedAsync(
            $"SELECT * from user_topic_total_score_procedure({user_id}, {topic_id}, {scoreParameter})"
        );

        var result = (decimal)scoreParameter.Value;
        return result;
    }
    catch (Exception)
    {
        return 0;
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search