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.

In this chapter we consider an usage of stored functions with ORM tools.

• Function creation
• Devart Entity Developer
• Microsoft EDM Wizard and EDM Designer
• Using the Method

Function creation

CREATE FUNCTION GET_DEPT_FUNC RETURN SYS_REFCURSOR
AS
cur SYS_REFCURSOR;
BEGIN
  OPEN cur FOR
  SELECT *
    FROM DEPT
    ORDER BY DEPTNO;
  RETURN cur;
END;

Devart Entity Developer

Expand the connection node in the Database Explorer, then your schema node and then the Functions node. Drag the GET_DEPT_FUNC to the diagram sheet. The function will be created in the storage model, and its following attributes will be set:

• IsComposable="false" - This attribute indicates if the function returns some value.
• IsConcealedFunction="true" - This attribute indicates that the function returns value, but the value is invisible for the Entity Framework.

Corresponding method will be automatically created in the conceptual model. However, you need to set the return type for it manually. To do this perform the following actions:

1. Double-click the method in the Model Explorer.
2. Click Entities and then select DEPT from the Entities drop-down list.
   
   
   
3. Click OK.

You also may drag the function from Database Explorer to the storage model node in the Model Explorer. In such case only the storage model is updated, the corresponding method is not created. To create corresponding method, drag the function from the Model Explorer to the diagram sheet or right-click it in the Model Explorer and select Create Method from the popup menu.

Microsoft EDM Wizard and EDM Designer

<Function Name="GET_DEPT_FUNC" ReturnType="REF CURSOR" Aggregate="false"
BuiltIn="false" NiladicFunction="false" IsComposable="true"
ParameterTypeSemantics="AllowImplicitConversion" Schema="SCOTT" />

1. Right-click the model and select Open With from the popup menu.
2. Click XML Editor in the Choose the program you want to use to open this file box and then click OK.
3. If the model was already opened in the model designer, Visual Studio will ask you whether to close the designer. Click Yes.
4. Add the devart:IsConcealedFunction="true" attribute to the FUNCTION tag of the GET_DEPT_FUNC function.
5. Replace the ReturnType="REF CURSOR" attribute with the devart:ReturnType="REF CURSOR"
6. Change value of the IsComposable attribute from "true" to "false". The result XML for the function should look like this:
   
   

   <Function Name="GET_DEPT_FUNC" devart:IsConcealedFunction="true"
   devart:ReturnType="REF CURSOR" Aggregate="false" BuiltIn="false"
   NiladicFunction="false" IsComposable="false"
   ParameterTypeSemantics="AllowImplicitConversion" Schema="SCOTT" />

7. Save the file and close it.
8. Reopen it in the ADO.NET EDM Designer.
9. Locate the GET_DEPT_FUNC in the Model Browser. Right-click it and select Create Function Import from the popup menu.
10. In the Add Function Import dialog box click Entities and then select DEPT from the Entities drop-down list. You also may change the Function Import Name.
    
    
    
    
11. Click OK.

Using the Method

This example illustrates call of the method, retrieving the first element of the collection of entities, and output to console. Object context name and case of the Depts class and its fields may differ in your case.

      using (SCOTTEntities context = new SCOTTEntities()) {
         Dept dept = context.GetDeptFunc().First();
         Console.WriteLine("Dept {0}: '{1}', '{2}'", dept.Deptno, dept.Dname, dept.Loc);
      }

	  
	Using context = New SCOTTEntities
		Dim dept As Dept =  context.GetDeptFunc().First()
		Console.WriteLine("Dept {0}: '{1}', '{2}'", dept.Deptno, dept.Dname, dept.Loc)
	End Using
	  

See Also

Entity Framework Tutorial  | Using Entity Data Model Wizard  | Stored Routines in Entity Framework Model  | Creating Database and Model  | Stored Procedure Returning Result Set using REF CURSOR Out Parameter  | Mapping CUD Operations to Stored Routines