How to: Define a Model with a Stored Procedure

A stored procedure that is defined in a database can be exposed in a conceptual model in two ways:

• You can create a function import in the conceptual model that maps to a stored procedure. This allows you to define a method on your ObjectContext that executes the stored procedure in the database. Depending on how the stored procedure is defined, the corresponding function import can return entity types or complex types.

• You can map the insert, update, and delete operations for an entity type to stored procedures. This allows you to define custom insert, update, and delete behaviors for entity types.

  Note

  If you do not map all three of the insert, update, and delete operations of an entity type to stored procedures, the unmapped operations will fail if executed at runtime, and an UpdateException will be thrown.

This topic describes how to create a function import in a conceptual model that maps to a stored procedure in the database. For information about mapping the insert, update, and delete operations for entity types to stored procedures, see How to: Define a Model with Modification Stored Procedures.

The recommended way to create a function import that maps to a stored procedure is to use the ADO.NET Entity Data Model Tools. For information about using the Entity Data Model Tools, see the following topics:

• How to: Import a Stored Procedure

• How to: Edit a Function Import

• How to: Map a Function Import to a Complex Type

• Walkthrough: Retrieving Entity Types with a Stored Procedure

You can also manually edit the model and mapping files to create a function import that maps to a stored procedure. The example in this topic shows how to do this and is based on the School sample database and the School model. For information about generating the School model from the School database, see EDM Generator (EdmGen.exe). Assuming that you have defined a stored procedure in the database, the basic steps for creating a function import that maps to the stored procedure are as follows:

1. Add a Function element to the storage model that specifies the stored procedure in the database.

2. Add a FunctionImport element to the conceptual model that specifies the function import in the conceptual model.

3. Add a FunctionImportMapping element to the mapping file that maps the function import in the conceptual model to the function in the storage model.

The procedures in this topic describe how to create the Function, FunctionImport, and FunctionImportMapping elements to import the following stored procedure into the conceptual model:

IF NOT EXISTS (SELECT * FROM sys.objects 
            WHERE object_id = OBJECT_ID(N'[dbo].[GetStudentGrades]') 
            AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[GetStudentGrades]
            @StudentID int
            AS
            SELECT EnrollmentID, Grade, CourseID, StudentID FROM dbo.StudentGrade
            WHERE StudentID = @StudentID
' 
END
GO

To add a Function element to the storage model

1. Open the storage model (the .ssdl file).

2. Add the following Function element as a child of the Schema element:

   <Function Name="GetStudentGrades" Aggregate="false" BuiltIn="false" 
             NiladicFunction="false" IsComposable="false" 
             ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
     <Parameter Name="StudentID" Type="int" Mode="In" />
   </Function>
   

To add a FunctionImport element to the conceptual model

1. Open the conceptual model (the .csdl file).

2. Add the following FunctionImport element as a child of the EntityContainer element.

   <FunctionImport Name="GetStudentGrades" EntitySet="StudentGrades" 
                   ReturnType="Collection(SchoolModel.StudentGrade)">
     <Parameter Name="StudentID" Mode="In" Type="Int32" />
   </FunctionImport>
   

To add a FunctionImportMapping element to the mapping file

1. Open the mapping file (the .msl file).

2. Add the following FunctionImportMapping element as a child of the EntityContainerMapping element.

   <FunctionImportMapping FunctionImportName="GetStudentGrades" 
                          FunctionName="SchoolModel.Store.GetStudentGrades" />
   

See Also

Other Resources

Defining Advanced Data Models
How to: Define Custom Functions in the Storage Model
CSDL, SSDL, and MSL Specifications