实体框架核心 5 - 从单个存储过程返回两个 oracle 游标 [英] ENTITY FRAMEWORK CORE 5 - Return two oracle cursors from single stored procedure

问题描述

我正在开发一个调用 oracle 存储过程的 API - 我无法触及它 - 并返回两个 refcursors.问题是，在我的代码中，只有从 sp 返回的第一个游标 beign 被映射，而另一个游标引发 InvalidOperationException.

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.

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);

被映射的游标类

    [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; }
    }
}

没有的游标类

    [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; }
    }

执行

第一个游标被正确映射(它只有 1 行).

第二个光标抛出错误.

这是我第一次在 dotnet 环境中使用 oracle，但我正在遵循 oracle 的官方指南，(参见.https://github.com/oracle/dotnet-db-samples/blob/master/samples/dotnet-core/ef-core/stored-procedure/return-ref-cursor.cs)，正如您所见，显然它仅适用于仅返回一个游标的 sp.有任何想法吗?任何帮助将不胜感激.

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.

推荐答案

目前 EF Core 不支持:https://github.com/dotnet/efcore/issues/8127 查看链接以找到此问题的替代方案.

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.

这篇关于实体框架核心 5 - 从单个存储过程返回两个 oracle 游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！