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:
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:
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:
Pay attention, that you don’t need to implement the function.
Here is the same query from earlier but in LINQ to Entities:
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:
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.
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