How to: Define Custom Functions in the Storage Model

You can define a custom function in the storage model by adding a Function element that contains a CommandText element to the store 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 function is defined in the .edmx file, not the database. You can define any SQL statement, including parameterized statements, in a CommandText element.

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 new stored procedure. For more information, see How to: Import a Stored Procedure.

   Note

   If the return type for the FunctionImport is not an entity type, object layer code will not be automatically generated. To expose the FunctionImport on the ObjectContext, you must write custom object layer code. For more information, see Customizing Objects (Entity Framework).

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 first and last names of a specified person. For information about the School model example, see Quickstart (Entity Framework).

<Function Name="UpdatePersonName" IsComposable="false">
          <CommandText>UPDATE Person
                       SET LastName = @lname,
                           FirstName = @fname
                       WHERE CustomerID = @id;
                       SELECT @@ROWCOUNT
          </CommandText>
          <Parameter Name="lname"
                     Mode="In"
                     Type="nvarchar"/>
          <Parameter Name="fname"
                     Mode="In"
                     Type="nvarchar" />
          <Parameter Name="id"
                     Mode="In"
                     Type="int"/>
        </Function>

You can make the FunctionImport available on the ObjectContext by adding the following method to the SchoolEntities partial class that is generated by the Entity Framework. For more information, see Customizing Objects (Entity Framework).

Public Function UpdatePersonName(ByVal firstName As String, _
    ByVal lastName As String, ByVal id As Integer) As Integer
    Dim command As DbCommand = Me.Connection.CreateCommand()
    command.CommandType = CommandType.StoredProcedure
    command.CommandText = Me.DefaultContainerName + _
                          "."c & "UpdatePersonName"

    Dim p1 As New EntityParameter("fname", DbType.[String])
    p1.Value = firstName
    Dim p2 As New EntityParameter("lname", DbType.[String])
    p2.Value = lastName
    Dim p3 As New EntityParameter("id", DbType.Int32)
    p3.Value = id
    Dim parameters As Object() = New Object() {p1, p2, p3}

    If command.Connection.State = ConnectionState.Closed Then
        command.Connection.Open()
    End If
    Try
        command.Parameters.AddRange(parameters)
        Return CType(command.ExecuteScalar(), Integer)
    Finally
        command.Connection.Close()
    End Try
End Function

public int UpdatePersonName(string firstName, 
                            string lastName, 
                            int id)
{
    DbCommand command = this.Connection.CreateCommand();
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = this.DefaultContainerName + 
        '.' + "UpdatePersonName";

    EntityParameter p1 = new EntityParameter("fname", DbType.String) 
        { Value = firstName };
    EntityParameter p2 = new EntityParameter("lname", DbType.String) 
        { Value = lastName };
    EntityParameter p3 = new EntityParameter("id", DbType.Int32) 
        { Value = id };
    object[] parameters = new object[] { p1, p2, p3 };

    if (command.Connection.State == ConnectionState.Closed)
    {
        command.Connection.Open();
    }
    try
    {
        command.Parameters.AddRange(parameters);
        return (int)command.ExecuteScalar();
    }
    finally
    {
        command.Connection.Close();
    }
}

See Also

Tasks

How to: Add a Defining Query

Other Resources

Editing an .edmx File Manually
ADO.NET Entity Data Model Designer
Entity Data Model Tools