This topic is applicable to Entity Framework v1 - v6. It is not applicable to Entity Framework Core because Entity Developer generates ADO.NET code for invoking stored procedures in this case.

Oracle pipelined functions are stored functions that return a collection type result iteratively by elements. There are two ways to run Oracle pipelined function via Entity Framework - creating an entity based on such function or creating a Function Import. They will be demonstrated using the following sample pipelined function:

CREATE TYPE ARRAY AS TABLE OF NUMBER;

CREATE FUNCTION gen_numbers(n IN NUMBER DEFAULT NULL)
RETURN ARRAY
PIPELINED
AS
BEGIN
  FOR i IN 1 .. nvl(n,999999999)
  LOOP
    PIPE ROW(i);
  END LOOP;
  RETURN;
END;

Creating Entity Based on Oracle Pipelined Function

1. Right-click the Tables/Views node in Model Explorer and select Defining Query from the Add submenu of the shortcut menu.

   

2. Type SELECT * FROM TABLE(GEN_NUMBERS(100)) in Defining Query Editor. Replace 100 here with your own value. Be aware that this value will be obfuscated.

   
3. Click Execute and make sure that correct data are returned (a grid will be displayed).
4. Switch to the Columns tab to generate the collection of columns.
5. Close Defining Query Editor by clicking OK - Entity1 will be created.
6. Drag Entity1 from the storage model in Model Explorer to the diagram surface - this creates the corresponding entity in the conceptual part of the model.
7. Save the model to generate code.

Now you can use this pipelined function as a usual entity.

using (PipelinedFunctionDemoModel.PipelinedFunctionDemoEntities context = 
    new PipelinedFunctionDemoModel.PipelinedFunctionDemoEntities())
{
    var query1 = context.Entity1s
        .Select(e1 => e1.COLUMNVALUE)
        .ToList();

    var query2 = context.Entity1s
        .Where(e2 => e2.COLUMNVALUE > 5)
        .Take(10)
        .OrderByDescending(e2 => e2.COLUMNVALUE)
        .Select(e2 => e2.COLUMNVALUE)
        .ToList();
}

Using context As New PipelinedFunctionDemoModel.PipelinedFunctionDemoEntities()
	  Dim query1 = context.Entity1s.[Select](Function(e1) e1.COLUMNVALUE).ToList()

	  Dim query2 = context.Entity1s.Where( _
        Function(e2) e2.COLUMNVALUE > 5 _
    ) _
        .Take(10) _
        .OrderByDescending(Function(e2) e2.COLUMNVALUE)
        .[Select](Function(e2) e2.COLUMNVALUE).ToList()
End Using

Advantages:

• You can work with this function like with a usual entity - apply conditions, use ".Where", join it with other entities, order data, etc., and all of this is performed on the server side.

Disadvantages:

• You cannot specify different parameter values for a function. The only way to do it is to create other entities with a different defining query.
• Applying Where condition is not as effective as in case of selecting data from a table. Thought everything is performed on the server side, the function still produces all the data, though the part of it that does not match to the condition is not sent to the client.
• If a result set, returned by a pipelined function, does not contain a set of columns that can uniquely identify each row, this method is not applicable.

Creating Function Import Based on Oracle Pipelined Function

1. Drag the stored function to the Stored Procedures node in Model Explorer. The model will become not valid.
2. Set the Concealed property of this function to true.
3. Drag this function from the Model Explorer to the diagram surface to create the corresponding method in conceptual model (Function Import). Click Yes when dialog asks you to obtain metadata of procedure result set to generate complex type automatically
   
4. Save the model to generate code.

Now you can use this pipelined function in the following way.

using (PipelinedFunctionDemoModel.PipelinedFunctionDemoEntities context = 
    new PipelinedFunctionDemoModel.PipelinedFunctionDemoEntities()) {
var results = context.GENNUMBER(14).Select(e => e.MyProperty).ToList();
}

Using context As New PipelinedFunctionDemoModel.PipelinedFunctionDemoEntities()
	Dim results = context.GENNUMBER(14).[Select](Function(e) e.MyProperty).ToList()
End Using

Advantages:

• It is possible to use different parameter values.

Disadvantages:

• You cannot work with this function as with entity - use where conditions, etc.