How to: Define Custom Functions in the Storage Model (Entity Framework)

  • Article

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.

NoteNote

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 (Entity Data Model Tools).

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. Adding this Function element to the School model provides functionality for updating the office assignment of a specified instructor. For information about the School model example, see Quickstart (Entity Framework).

<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 (Entity Data Model Tools).

See Also

Tasks

How to: Add a Defining Query (Entity Framework)

Concepts

ADO.NET Entity Data Model Designer

Other Resources

Editing an .edmx File Manually (Entity Framework)
ADO.NET Entity Data Model Tools