0

I am working on an Asp.net MVC web application dealing with an Oracle database, and I have a statistics dashboard page.

I am using Entity Framework code-first with ODP.Net, but I am facing a performance issue on this page because Entity Framework queries are so complex.

So I have to convert my Entity Framework queries to stored procedures.

For example: I created the following stored procedure in Toad 

CREATE OR REPLACE PROCEDURE Schema.DashBoardStatistics 
    (
     -- Add the parameters for the stored procedure here
     p_YearId number, cur OUT SYS_REFCURSOR
    )
AS
BEGIN
    -- select top 5 Fields By rating Rating 
    Open cur FOR Select * FROM   Schema."Logs" where Schema."Logs"."Id"= p_YearId ;

END;
/

How to add another select to this Sp to return a dataset to my code?

And how can I call this from my code and receive this dataset in model classes?

Notice : i want to use entity framework query to execute stored procedure not ADO.NET To be able to map return dataset to my entities

Thanks!

ahmed naguib
  • 145
  • 14
  • 2
    Possible duplicate of [Calling Oracle stored procedure from C#?](http://stackoverflow.com/questions/3940587/calling-oracle-stored-procedure-from-c) – Igor Feb 08 '17 at 20:09
  • 1. You can map entities to stored procs but no one is going to write the code for you. Your best bet is to follow a tutorial on Entity Framework and Stored Procedures (there are many out there). Give it a shot and come back with some code if you get stuck (*you have to make an attempt*). 2. If you want to manually execute the stored proc and map the results to an entity then the duplicate link is still valid to some degree. – Igor Feb 08 '17 at 21:52
  • EF is good for CRUD. For this - no so much. Write a model hydrator that can match resultset field to a property on a model. How to do 2 selects? - ` p_YearId number, cur1 OUT SYS_REFCURSOR, cur2 OUT SYS_REFCURSOR` – T.S. Feb 09 '17 at 03:07

1 Answers1

1

See the example in the middle of this walkthrough:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/2015/entityframework_linq_modelfirst/Entity%20Framework%20LINQ%20and%20Model%20First.html

The key is that you need a config file for the REF CURSOR metadata. You should install Oracle Developer Tools for Visual Studio and then "Run" the stored procedure using the menu off Server Explorer. This will give you an option to auto generate the required config file that contains the metadata for the ref cursor.

You then follow the walkthrough to fill in the Import Function dialog which maps the SP to a Entity Function.

Christian Shay
  • 2,570
  • 14
  • 24