Usage of stored functions in entity framework

I have an old Oracle DB which I'm trying to use within EntityFramework 4.1 application.
I've read about the big limitation that oracle has with EF- you can't call Oracle stored Function with EF unless you create a wrapping Procedure.

I got thousands of stored functions in my DB, is there any other way to solve it?
Like using raw Context.SqlQuery()?

So far I couldn't find a solution for it...

Oracle docs:

Oracle developers can leverage PL/SQL stored procedures, with limitations, within the entity framework via Entity Framework Function Imports (used to call the procedures explicitly) and stored procedure mappings (which are automatically called for entity Insert, Update, and Delete operations).

Only Oracle stored procedures can be called by Entity Framework, not stored functions. (Oracle stored functions can be used if they are wrapped inside of a stored procedure that uses an OUT parameter for the stored function return value.)

If you are working with Entity Framework 4.1 Code First, you can try using the Database.SqlQuery Method . For example, for this function

CREATE OR REPLACE FUNCTION USERNAME_CTX.FUNCTION1 (param number)
 RETURN number
AS
BEGIN
 return param + 1;
END;

you can use this code:

using (var ctx = new Model()) {
   var result =  ctx.Database.SqlQuery<int>("select username_ctx.FUNCTION1(:p0) from dual",1).FirstOrDefault();

}

EDITED:

Please note this solution for dotConnect for Oracle (maybe it will be useful for implementing a similar solution for ODP.NET)

For this function:

CREATE OR REPLACE FUNCTION USERNAME_CTX.FUNCTION2 (param number, int_param out number, str_param out varchar2)
  RETURN number
AS
BEGIN
   int_param := param + 2;
   str_param := 'value';
   return param + 1;
END;

You can use the following code:

   using (var ctx = new Model()) {
       var firstParam = new Devart.Data.Oracle.OracleParameter("p0", OracleDbType.Number, 1, ParameterDirection.Input);
       var secondParam = new Devart.Data.Oracle.OracleParameter("p1", OracleDbType.Number, ParameterDirection.Output);
       var thirdParam = new Devart.Data.Oracle.OracleParameter("p2", OracleDbType.VarChar, ParameterDirection.Output);
       var cursorParam = new Devart.Data.Oracle.OracleParameter("cursor_param", OracleDbType.Cursor, ParameterDirection.Output);

       var result = ctx.Database.SqlQuery<int>(
        @"declare
             res number;  
        begin
             res := username_ctx.FUNCTION2(:p0, :p1, :p2);
             open :cursor_param for select res from dual;
        end;",  firstParam, secondParam, thirdParam, cursorParam).FirstOrDefault();

       Console.WriteLine("Return value: {0}; int_param: {1}; str_param: '{2}'", result, secondParam.Value, thirdParam.Value);
      }

EDITED 2

or use this code:

     using (var ctx = new Model()) {
       var firstParam = new Devart.Data.Oracle.OracleParameter("p0", OracleDbType.Number, 1, ParameterDirection.Input);
       var secondParam = new Devart.Data.Oracle.OracleParameter("p1", OracleDbType.Number, ParameterDirection.Output);
       var thirdParam = new Devart.Data.Oracle.OracleParameter("p2", OracleDbType.VarChar, ParameterDirection.Output);
       var resultParam = new Devart.Data.Oracle.OracleParameter("res", OracleDbType.Number, 1, ParameterDirection.Output);
       ctx.Database.ExecuteSqlCommand(@"begin  :res := username_ctx.FUNCTION2(:p0, :p1, :p2);  end;", firstParam, secondParam, thirdParam, resultParam);
       Console.WriteLine("Return value: {0}; int_param: {1}; str_param: '{2}'", resultParam.Value, secondParam.Value, thirdParam.Value);
 }

Recommended topics

Hot tags