0

I have a complex query to execute and I want to execute it in SQL instead of using linq query because in SQL is too much fast.

I have been searching in the net and I do not have too much clear if EF6 supports Stored Procedures with CodeFirst.

I have a Procedure like this ( Get resultset from oracle stored procedure ) to get the results of a Select

CREATE OR REPLACE PROCEDURE CENTER_FROM_LOCATION(LOC_OID IN NUMBER, LOC_CENTER OUT SYS_REFCURSOR) AS
  BEGIN
    OPEN LOC_CENTER FOR
    SELECT * FROM (
      .....
    )WHERE ROWNUM = 1;
  END;

And my C# code 

public ObjectResult<TYPE_IN_MODEL> CENTER_FROM_LOCATION(decimal LocationOID)
{
    var Parameter_LocationOID = new ObjectParameter("LOC_OID", LocationOID);
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<TYPE_IN_MODEL>("CENTER_FROM_LOCATION", Parameter_LocationOID);
}

The first curious thing is that It gives me an Exception if I put the parameter as Int32 and atribute OID is defined in the Model as Int32. With decimal works but throws an exception because number of parameters.

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> Oracle.ManagedDataAccess.Client.OracleException: ORA-06550: línea 1, columna 8:
PLS-00306: número o tipos de argumentos erróneos al llamar a 'CENTER_FROM_LOCATION'
ORA-06550: línea 1, columna 8:
PL/SQL: Statement ignored
...

Can anybody show me an example of one Stored Procedure in Oracle to get the results of some select and theway to map the Stored Procedure in CodeFirst with EF6.

Community
  • 1
  • 1
JuanDYB
  • 590
  • 3
  • 9
  • 23
  • I created this: https://blog.3d-logic.com/2014/04/09/support-for-store-functions-tvfs-and-stored-procs-in-entity-framework-6-1/ a while ago. Have not tried with Oracle but heard from they were able to make it work – Pawel Oct 01 '16 at 05:14
  • Your reply helps me but it doesn't work or I don't know how to get it working with Oracle Procedure returning a Cursor. I have added my code and my oracle procedure example. – JuanDYB Oct 02 '16 at 11:04

0 Answers0