Click here to Skip to main content
15,867,956 members
Articles / Programming Languages / C#

Defining Custom Functions in Entity Framework

Rate me:
Please Sign up or sign in to vote.
4.47/5 (7 votes)
26 Jul 2010CPOL1 min read 44.7K   14   2
The article explains how to define 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:

XML
<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:

C#
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

  • 26th July, 2010: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead sparXys
Israel Israel
Gil Fink is a web development expert and ASP.Net/IIS Microsoft MVP. He is the founder and owner of sparXys. He is currently consulting for various enterprises and companies, where he helps to develop Web and RIA-based solutions. He conducts lectures and workshops for individuals and enterprises who want to specialize in infrastructure and web development. He is also co-author of several Microsoft Official Courses (MOCs) and training kits, co-author of "Pro Single Page Application Development" book (Apress) and the founder of Front-End.IL Meetup. You can read his publications at his website: http://www.gilfink.net

Comments and Discussions

 
GeneralGreat article Pin
Michał Zalewski2-Aug-10 22:08
Michał Zalewski2-Aug-10 22:08 
Great article!
Is there a possibility of support HierarchyID SQL Server data type in EF ?
I found solution by using stored procedures, but is there possible to use HierarchyID (Microsoft.SqlServer.Types.SqlHierarchyId) in EF.
GeneralRe: Great article Pin
Gil Fink3-Aug-10 1:52
Gil Fink3-Aug-10 1:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.