How to: Define Custom Functions in the Storage Model

  • Article

[This page is specific to the latest version of the Entity Framework. The latest version is available as the 'Entity Framework' NuGet package. For more information, see Entity Framework Releases and Versioning.]

You can define a custom function in the storage model by adding a Function element that contains a CommandText element to the storage schema definition language (SSDL) of an .edmx file. A CommandText element is commonly used to provide functionality similar to that provided by stored procedures, but the stored procedure is defined in the .edmx file, not the database. You can define any SQL statement, including parameterized statements, in a CommandText element.

Note

Changes made to the SSDL section of an .edmx file, as suggested in the procedure below, will be overwritten if you use the Update Model Wizard to update your model.

Defining a Custom Function in the Storage Model

The following procedure assumes that you have an .edmx file open in the XML Editor in Visual Studio. The procedure provides a high-level outline of adding a custom function in the storage model. The example that follows provides more detail about steps in the procedure.

To define a custom function in the storage model

  1. Add a Function element to the Schema element in the Storage Model Content section of the .edmx file.

    For more information, see Function Element (SSDL) and Schema Element (SSDL).

  2. Add a CommandText element to the new Function element.

  3. Define an SQL statement in the CommandText element.

    The statement can be a parameterized statement.

  4. For each parameter in the SQL query, add a Parameter element to the Function element. Set the Name , Mode , and Type attributes of each Parameter element.

  5. Save and close the .edmx file.

  6. Open the .edmx file in the ADO.NET Entity Data Model Designer (Entity Designer).

    The newly added function will now appear as a stored procedure in the Model Browser window.

  7. Create a FunctionImport for the stored procedure. For more information, see How to: Import a Stored Procedure.

Example

The following is an example of a Function element, which you can add to the Schema element in the Storage Model Content section of an .edmx file to define a custom function.

<Function Name="UpdateOfficeAssignment" IsComposable="false">
  <CommandText>
    UPDATE OfficeAssignment
    SET Location = @location
    WHERE InstructorID = @id;
  </CommandText>
  <Parameter Name="location"
             Mode="In"
             Type="nvarchar"/>
  <Parameter Name="id"
             Mode="In"
             Type="int"/>
</Function>

To make this function available on the ObjectContext, you must create a corresponding function import in the conceptual model. For more information, see How to: Import a Stored Procedure.

See Also

Concepts

Entity Framework Designer

Other Resources

Editing an .edmx File Manually
How to: Add a Defining Query
ADO.NET Entity Data Model Designer

Build Date:

2013-06-19