Entity SQL User-Defined Functions in Entity Framework 4

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.