Entity SQL User-Defined Functions in Entity Framework 4

May 28, 2009

Entity SQL User-Defined Functions in Entity Framework 4

Continuing the tour aboutEntity SQL User-Defined Functions in Entity Framework 4
the new features in EF4, the
next stop is the new user-defined
function
capability of Entity SQL.

Entity SQL User-Defined Functions

There are two kinds of user-defined functions in EF4:

  • Functions that are being defined as part of the conceptual model
  • Functions that are being defined inline within a query

You should use the Entity SQL user-defined functions when you
want to encapsulate commonly used Entity SQL inside a function.

Entity SQL User-Defined Functions – Conceptual Model

This approach is called Model Defined Functions. The functions 
are declared in the conceptual model and not in the storage model
and are written in Entity SQL. In the Schema element of the CSDL
you insert a Function element with the relevant details like:

<Function Name="GetCourseTime" ReturnType="Edm.Int32">
  <Parameter Name="Course" Type="SchoolModel.Course" />                      
  <DefiningExpression>
    Edm.Hour(Course.CourseDetails.Time)
  </DefiningExpression>
</Function>

This function returns the hour which the course is occurring.

DefiningExpression element can hold any valid Entity SQL expression.

You can use the declared function in Entity SQL queries like in the

following code snippet which get the courses that occur after 12:00:

using (var context = new SchoolEntities())
{
    var qSql = "SELECT VALUE(c) FROM SchoolEntities.Courses " +
               "AS c WHERE SchoolModel.GetCourseTime(c) > 12 ";
    var afternoonCourse = context.CreateQuery<Course>(qSql);
 
    foreach (var course in afternoonCourse)
    {
        Console.WriteLine(course.Title);
    }
 
    Console.ReadLine();
}

If you want to use this function in LINQ to Entities you need

to provide a corresponding CLR method. That method needs

to be in the same signature like the declared function. Use the

EdmFunction attribute to “tell” Entity Framework that a mapping exists

between this function and your Model Defined Function. The following

code shows an example of how to do that:

public partial class SchoolEntities
{
    [EdmFunction("SchoolModel", "GetCourseTime")]
    public int GetCourseTime(Course course)
    {
        throw new NotSupportedException();
    }
}

Pay attention, that you don’t need to implement the function.

Here is the same query from earlier but in LINQ to Entities:

using (var context = new SchoolEntities())
{
    var afternoonCourse = from c in context.Courses
                          where context.GetCourseTime(c) > 12
                          select c;
 
    foreach (var course in afternoonCourse)
    {
        Console.WriteLine(course.Title);
    }
 
    Console.ReadLine();
}

Entity SQL User-Defined Functions – Inline Functions

You can also declare inline functions inside your queries.

Using this approach is less flexible because you declare the function

to be used inside the current Entity SQL expression you create.

The following code demonstrate the use of user-defined inline functions:

using (var context = new SchoolEntities())
{
    string qStr = "USING EF4; " +
                  "FUNCTION GetCourseCreditBiggerThenThree(c Course) AS " +
                  "(c.CourseDetails.Credits > 3) " +
                  "SELECT c.Title " +
                  "FROM SchoolEntities.Courses AS c " +
                  "WHERE GetCourseCreditBiggerThenThree(c)";
 
    var query = new ObjectQuery<DbDataRecord>(qStr, context);
 
    foreach (var record in query)
    {
        Console.WriteLine("{0}", record[0]);
    }
 
    Console.ReadLine();
}

What you see is a query that is constructed from a declaration of a

function which returns true if the course has more than 3 credits.

After the declaration I call another Entity SQL statement that uses

the declared function. This is a simple and not so useful function but

you can see how inline functions are built.

Summary

Lets sum up, one of the new features of EF4 is the Entity SQL

user-defined functions
. As in databases, we can define our own

functions inside the conceptual model or as inline functions inside

an Entity SQL query. This new feature enable more flexibility to

Entity Framework.

DotNetKicks Image
Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

*

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>