Return scalar value from parameterized SQL statement using DB2 EF Core

Question

In a class library targeting .NET 5.0, EntityFramework Core, I can use the following code to return a scalar value from parameterized SQL statement using EF Core for SQL Server:

public object GetResult()
{
    var context = _contextFactory.CreateDbContext();
    var commandText = "SELECT @p0 = (SELECT Case When Exists(SELECT null WHERE @p1 != 'B') THEN 1 Else 0 End)";
    var p0 = new SqlParameter
    {
        ParameterName = "@p0",
        SqlDbType = SqlDbType.Bit,
        Direction = ParameterDirection.Output,
    };

    var p1 = new SqlParameter
    {
        ParameterName = "@p1",
        SqlDbType = SqlDbType.VarChar,
        Size = 1,
        Direction = ParameterDirection.Input,
        Value = 'A'
    };

    _ = context.Database.ExecuteSqlRaw(commandText, new[] { p0, p1 });

    return p0.Value;
}

I would like to be able to do something similar using EF Core for DB2. I am using the IBM.EntityFrameworkCore 5.0.0.300 Nuget package. The DB2 EF Core provider does not support named parameters, so must replace parameter names with ? markers in command string...however, this statement is not supported on DB2 and throws an error.

public object GetResult()
{
    var context = _contextFactory.CreateDbContext();
    var commandText = "SELECT ? = (SELECT Case When Exists(SELECT 1 FROM sysibm.sysdummy1 WHERE ? != 'R') THEN 1 Else 0 End FROM sysibm.sysdummy1)";
    var p0 = new DB2Parameter
    {
        ParameterName = "@p0",
        SqlDbType = DB2Type.Boolean,
        Direction = ParameterDirection.Output,
    };

    var p1 = new DB2Parameter
    {
        ParameterName = "@p1",
        SqlDbType = DB2Type.VarChar,
        Size = 1,
        Direction = ParameterDirection.Input,
        Value = 'A'
    };

    _ = context.Database.ExecuteSqlRaw(commandText, new[] { p0, p1 });

    return p0.Value;
}

I could use the context to get the ConnectionString and create a new DB2Connection and a parameterized DB2Command and call ExecuteScalar to get the result, but would like to try to return value as a strongly typed parameter rather than the ExecuteScalar which returns the value as an object.

Does anyone know how I may craft a SQL statement for DB2 to return a scalar value as an output or result parameter using EF Core and ExecuteSqlRaw??

Answer 1

could use the context to get the ConnectionString and create a new DB2Connection

There's no need to do that. To use ADO.NET directly with your DbContext, just do this:

var con = context.Database.GetDbConnection();
con.Open();
. . .