Defining Custom Functions in Entity Framework

Introduction

During the SDP conference, I have been asked about the use of functions inside the Entity Data Model.
This article will try to answer one such question of how to define a custom function inside EF.

Custom Functions in Entity Framework

One of the capabilities of EF since EF1 was the creation of custom functions inside the SSDL part of the model. After their creation, we could consume them like other imported functions. In that way, we could define functions in the model which were acting like stored procedures but without being a part of the database. This, of course, means that in order to use this feature, we needed to write some XML code inside the SSDL.

Defining a Custom Function

The first thing to do is to open the model in XML editor and to add a Function element inside the Schema element in the SSDL part of the XML. Inside the Function element, we add a CommandText element which will hold our T-SQL expression. We can also add to the function parameters.

The following XML fragment shows an example of a custom function:

<Function Name="GetCoursesByCredit" IsComposable="false">
  <CommandText>
    SELECT *
    FROM Course
    WHERE Credits = @Credits
  </CommandText>  
  <Parameter Name="Credits" Type="int" Mode="In">
  </Parameter>
</Function>

After the creation of that function, we can use the Add Function Import wizard to import that function to the ObjectContext:

and then to run a test like this one to see the function in action:

using (var context = new SchoolEntities())
{
    var query = context.GetCoursesByCredit(3);
    foreach (var course in query)
    {
        Console.WriteLine(course.Title);
    }
    Console.ReadLine();
}

Summary

EF enables us to add custom SSDL functions to our model. These functions can be handled like other imported functions (UDFs or stored procedures). This feature should be used rarely in my opinion and only when there is a T-SQL functionality that we must have but EF doesn’t support.

History

• 26^th July, 2010: Initial post