在 Entity Framework Core 中执行 Oracle 存储过程。

Question

当我尝试使用Entity Framework Core执行Oracle存储过程时，我遇到了这个异常:

“No mapping to a relational type can be found for the CLR type 'OracleParameter[]'”

我尝试了ExecuteSqlCommand和FromSQL方法，但两者都导致同样的异常。

以下是Oracle存储过程的代码:

CREATE OR REPLACE Procedure GET_DEPT_PROC (
p_ParentSiteId number,
P_SiteId number,P_LoggedUserId number, P_curParam OUT sys_REFCURSOR) as
Begin
OPEN curParam FOR 
select 1 Id, 2 EntityAlmostOnRenewalTrans, 3 EntityVerifiedTrans, 4 
EntityCompletedTrans, 5 EntityClosedTrans, 6 MyVerifiedTrans, 7 
MyCreatedTrans, 8 MyDraftedTrans, 9 MyRejectedTrans FROM DUAL;

End;

C#方法执行此存储过程：

public virtual DashBoardData GetDashBoardData(short parentSiteId, short siteId, int loggedUserId)
{
     var parameters = new OracleParameter[]
            {                        
                    new OracleParameter("@p_ParentSiteId", parentSiteId),
                    new OracleParameter("@p_SiteId", siteId),
                    new OracleParameter("@p_LoggedUserId", loggedUserId),                        
                    new OracleParameter("@p_curParam", OracleDbType.RefCursor, ParameterDirection.Output)};

     string sql = "EXEC GET_DEPT_PROC(@p_ParentSiteId,@p_SiteId,@p_LoggedUserId:@p_curParam);";

     var res = XDB.FromSql<DashBoardData>(base.context, sql, parameters).ToList()[0];
}

public static IQueryable<TEntity> FromSql<TEntity>(DbContext EFContext,RawSqlString sql, IEnumerable<OracleParameter> op) where TEntity : class
{
     return EFContext.Set<TEntity>().FromSql(sql, op);
}

- Wael Mourad

Answer 1

以下是带有示例的答案:

.: Oracle存储过程:

create or replace Procedure GET_DEPT_PROC (
p_ParentSiteId number,
P_SiteId number,
P_LoggedUserId number, curParam OUT sys_REFCURSOR,curParam2 OUT sys_REFCURSOR )
as
Begin
OPEN curParam FOR 
select 1 Id, 2 EntityAlmostOnRenewalTrans, 3 EntityVerifiedTrans, 4 EntityCompletedTrans, 5 EntityClosedTrans, 6 MyVerifiedTrans, 7 MyCreatedTrans, 8 MyDraftedTrans, 9 MyRejectedTrans FROM DUAL;
OPEN curParam2 FOR
select 1 Id, 2 EntityAlmostOnRenewalTrans, 3 EntityVerifiedTrans, 4 EntityCompletedTrans, 5 EntityClosedTrans, 6 MyVerifiedTrans, 7 MyCreatedTrans, 8 MyDraftedTrans, 9 MyRejectedTrans FROM DUAL;
End; 

.: 在Oracle中执行存储过程:

DECLARE
CUR1 SYS_REFCURSOR;
CUR2 SYS_REFCURSOR;
BEGIN
TestOracleSP (1,2,3,CUR1,CUR2) ;
END;

.：使用FromSQL方法在代码中执行：

 public virtual DashBoardData GetDashBoardData(short parentSiteId, short siteId, int loggedUserId)
{

try
        {


            OracleParameter p1 = new OracleParameter("p_ParentSiteId", parentSiteId);
            OracleParameter p2 = new OracleParameter("p_SiteId", siteId);
            OracleParameter p3 = new OracleParameter("p_LoggedUserId", loggedUserId);
            OracleParameter p4 = new OracleParameter("CUR", OracleDbType.RefCursor, ParameterDirection.Output);
            OracleParameter p5 = new OracleParameter("CUR2", OracleDbType.RefCursor, ParameterDirection.Output);

            string sql = "BEGIN GET_DEPT_PROC(:p_ParentSiteId, :p_SiteId, :p_LoggedUserId, :CUR2, :CUR);END;";
            var res = context.Set<DashBoardData>().FromSql(sql, p1, p2, p3, p4, p5).ToList();

            return res[0];

        }
        catch (Exception x)
        {
            XLogger.Error("Exception : " + x);
            return null;
        }
  }

- Wael Mourad

Answer 2

你可以使用我写的存储过程脚手架工具：

EntityFramework.Oracle.StoreProcedure.Scaffolder

它会为您的上下文创建一个新的部分类，其中包含所有过程和UDDT（如果存在）。

- Moti Matatyahu