How to get the result of SELECT COUNT(*) from a stored procedure using Pomelo/Entity

Question

I'm using ASP NET core 5.0 and using Pomelo to work with a MariaDB database

I have a stored procedure that takes a bunch of parameters and returns a SELECT COUNT(*), so a nice simple integer. I thought this would be fairly trivial to do, but it turns out it's actually really difficult

I can do an extremely ugly workaround by doing something like:

Database.GetDbConnection().Open();

using (var x = Database.GetDbConnection().CreateCommand())
{
    x.CommandText = "test";
    x.CommandType = System.Data.CommandType.StoredProcedure;

    var q = await x.ExecuteScalarAsync();

    Console.WriteLine(q);
}

But I don't really want to have to do connection opening and closing, because this is just janky

I thought I could use Database.ExecuteSqlRaw[async], but the return value for that is rows affected, not the actual output of the query. I've tried various attempts at getting a return value, most of them throwing exceptions and the others doing nothing.

So, is there a tidy way of doing this or do I have the fudge a clunky workaround?

Answer 1

That is nearly the recommended way to do it, just replace Database.GetDbConnection().Open() with Database.OpenConnectionAsync(), so that EF Core manages the connection for you and will close it when not needed anymore (otherwise, if the connection hasn't been opened by EF Core yet, you are suddenly responsible for closing it later).

Put everything in an extension method for easy reusability and move on:

public static async Task<T> GetScalarStoredProcedureResultAsync<T>(
    this DbContext context,
    string name)
{
    await context.Database.OpenConnectionAsync();
    var connection = context.Database.GetDbConnection();

    using var command = connection.CreateCommand();
    command.CommandText = name;
    command.CommandType = System.Data.CommandType.StoredProcedure;

    return (T) await command.ExecuteScalarAsync();
}

The call would be:

var count = yourContext.GetScalarStoredProcedureResultAsync<long>("test");
Console.WriteLine(count);

Answer 2

EDIT

Wow, just realized you're doing that already. But yeah, you're not opening a new connection. EF is still managing the connection. You could modify your stored procedure to use an output parameter, or keep it as is.

Original Answer

You don't have to open a new connection manually. Use the connection object attached to the context. This was pulled from another SO answer:

using (var cmd = _context.Database.GetDbConnection().CreateCommand()) {
    cmd.CommandText = "[Production].[Select_TicketQuantity]";
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    if (cmd.Connection.State != System.Data.ConnectionState.Open) cmd.Connection.Open();
    cmd.Parameters.Add(new SqlParameter("Ticket", ticket));
    cmd.Parameters.Add(new SqlParameter("Reference", reference));
    quantity = (int)cmd.ExecuteScalar();
}

See the answer here: https://stackoverflow.com/a/59627588/12431728