Defining Custom Functions in Entity Framework

January 1, 2010

no comments

Defining Custom Functions in Entity Framework

During the SDP conferenceDefining Custom Functions in Entity Framework
I have been asked about the  
use of functions inside the EDM.
This post will try to answer one
such question of how to define
a custom function in 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:

FunctionImport Wizard

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.

DotNetKicks Image
Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>