Mapping Ref Cursor out parameter to a Entity

[rdwlaz]

Take this simple example:

Let's say I have an entity called Customer with id (key), name, address. I have a Oracle stored procedure called get_all_customers.

Code: Select all

PROCEDURE get_all_customers(  
    p_cursor OUT t_cursor  
)  
IS 
BEGIN 
    OPEN p_cursor FOR 
    SELECT   
        id, name, address  
    FROM   
        customer        
    ORDER BY   
        name;  
END get_all_customers; 

Using the "Create Function Import" I created a Function called GetAllCustomers and set the return type to the Customer entity.
How can I map the columns from the cursor to the Customer entity.

Is this possible in EF?

I get Error 11009: Property '' is not mapped.

Thanks in advance,

[AndreyR]

It is possible in EF.
You should have an entity corresponding to the result set of stored procedure in your model.
It should contain the same set of columns with the same names and types.
If you don't have such table, create a view.

[rdwlaz]

It is possible in EF.
You should have an entity corresponding to the result set of stored procedure in your model.
It should contain the same set of columns with the same names and types.
If you don't have such table, create a view.

Andrey,

I used the simple example just to paint a clear picture. However, what I doing is more complex. I'm trying to use existing sp's with business logic and map them to entities that I created in the conceptual model. I'll then expose the data using ADO.NET Data Service as AtomPub feeds. There are no physical tables to map the entites to and creating views is not an option. Calls must be dynamic based on input paramters.

So getting back to my simple example from above:

I created a entity called Customer in my model with the same columns and types that the ref cursor returns. Now, lets say the sp takes an input parameter of p_name (where p_name is 'Smith' and it can return multilpe customers) and returns the p_cursor as the output parameter.

When I expand the sp under the Function Imports I only see my output p_cursor and my input p_name parameters.

So how do I map the columns from the p_cursor to the columns in my entity? Is this possible w/o having the entity mapped to a physical table or view?

I'm currently evaluating your product for my company so any advice would be great since I have the trial version.

[AndreyR]

You can manually edit your .edmx file. There are two options - creating an entity from a scratch or removing all unnecessary properties of existing entity. After this simply create function import and map its output to MyCustomer.
Anyway, you should obtain something like this:

Code: Select all

...

...

  
    
  
  
  
  

...

...

...

  
    
  
  
  
  

...

...

  
    
      
      
      
    
  
  ...
  

[rdwlaz]

Thanks that worked.