Using stored functions in an entity structure

Question

I have an old Oracle DB that I am trying to use in an EntityFramework 4.1 application.
I read about the big limitation that the oracle has with EF: you cannot call the Oracle stored Function with EF unless you create a wrapper procedure .

I have thousands of stored functions in my database, is there any other way to solve it?
How to use raw Context.SqlQuery() ?

Until now, I could not find a solution for this ...

---

Oracle docs:

Oracle developers can use PL / SQL stored procedures with constraints within the entity through the Entity Framework Function Imports (used to invoke procedures explicitly) and stored procedure mappings (which are automatically called for object input, update, and delete operations).

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

Answer 1

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

}

Edition:

Pay attention to this dotConnect solution for Oracle (it may be useful to implement 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); }