Kimera
Kimera

Reputation: 21

ENTITY FRAMEWORK CORE 5 - Return two oracle cursors from single stored procedure

I'm working on an API that calls an oracle stored procedure -I can't touch it- and returns two refcursors. The problem is that in my code, only the first cursor beign returned from the sp gets mapped, while the other one trows an InvalidOperationException.

My repository class

var sql = "BEGIN CCO_PKG_WHITDRAWREQUEST.OBT_OPCION_RET_DES_PRC(:pi_num_seguro_social , :pi_num_resolucion, :po_cur_valida_res, :po_cur_opciones); END;";

            var parameters = new OracleParameter[]
            {
                new OracleParameter("pi_num_seguro_social", request.NSS),
                new OracleParameter("pi_num_resolucion", request.NumResolucion),
                new OracleParameter("po_cur_valida_res", OracleDbType.RefCursor, ParameterDirection.Output),
                new OracleParameter("po_cur_opciones", OracleDbType.RefCursor, ParameterDirection.Output)
            };
            
            //This one gets mapped without problems.
            var validaResCursor = await _context.CursorValidaRes.FromSqlRaw(sql, parameters).ToListAsync();
            
            //This one throws the exception.
            var opcionesCursor = await _context.CursorOpciones.FromSqlRaw(sql, parameters).ToListAsync();

            var result = (opcionesCursor, validaResCursor);

The cursor's class that gets mapped

    [Keyless]
    public class ValidaResCursor
    {
        public string EsCVEValido { get; set; }
        public string Opcion { get; set; }
        public string Descripcion { get; set; }
        public double SaldoA { get; set; }
        public double SaldoB { get; set; }
        public string Vigencia { get; set; }
    }
}

The cursor's class that doesn't

    [Keyless]
    public class OpcionesCursor
    {
        public string cv { get; set; }
        public string tipo { get; set; }
        public string descripcion { get; set; }
        public double monto { get; set; }
        public double salario_bc { get; set; }
    }

Execution

The first cursor gets mapped correctly (It only has 1 row).

Error

The second cursor throws an error.

This is the first time I'm working with oracle in a dotnet enviroment, but I'm following the oficial guide from oracle, (see. https://github.com/oracle/dotnet-db-samples/blob/master/samples/dotnet-core/ef-core/stored-procedure/return-ref-cursor.cs) and as you can see, aparently it only works with sp's that returns only one cursor. Any ideas? Any help will be appreciated.

Upvotes: 2

Views: 1556

Answers (1)

Currently this is not supported by EF Core: https://github.com/dotnet/efcore/issues/8127 take a look at the link to find alternatives to this problem.

Upvotes: 1

Related Questions