Model Defined Functions

Today the Entity Framework, and more specifically the Entity Data Model, have a limited notion of Functions.

We are currently restricted to Function Imports that allow stored procedures to be invoked, and Canonical / Store Functions for database independent and database specific functions respectively.

Now however we want to support functions defined, not just declared, in the EDM (aka. the CSDL).

An example would be:

<Function Name="GetAge" ReturnType="Edm.Int32">
      <Parameter Name="Person" Type="Model.Person" />
            Edm.DiffYears(Edm.CurrentDateTime(), Person.Birthday)

Here are some things to notice:

  • The DefiningExpression is eSQL.

  • The function can have zero or more parameters.

  • The Function must have a return type.

  • Function Parameters are referenced directly by Name in the DefiningExpression: meaning there is no parameter denoting prefix like @. This means you must be careful to choose parameter names that don't collide with identifiers you need to use in the rest of eSQL expression.

  • Unlike functions in SSDL, functions in CSDL only support In bound Parameters (i.e Mode="In") because otherwise they become non-composable.

  • For this reason the Mode of a parameter cannot be set in CSDL (it is always Mode="In").

  • Functions are declared as Global Items and are declared within the <Schema> element. As such there identity is made up of the Schema's namespace and the function name.

  • The function parameters and return type can be any of the following:

    • A scalar type or collection of scalar types.
    • An entity type or collection of entity types.
    • A complex type or collection of complex types.
    • A row type or collection of row types (See below).
    • A ref type or collection of ref types.
  • Functions with a DefiningExpression do not require mapping, since the eSQL expression is composed out of eSQL fragments that are already mapped.

  • Functions without a DefiningExpression are simply declarations. Today the Entity Framework doesn't complain when loading a CSDL with such a function, but you can't invoke it. In the future these functions might be used to support mapping Table Value Functions in the store to functions in the Conceptual Model.

  • Since it is trivial in eSQL to create arbitrary un-named types, imagine a projection that projects 3 of the properties from an Entity, we now need a mechanism for defining these "RowTypes" inline, so that they can be used when defining Function Parameters and ReturnTypes. For example:

    <Parameter Name="Coordinate">
    <Property Name="X" Type="int" Nullable="false"/>
    <Property Name="Y" Type="int" Nullable="false"/>
    <Property Name="Z" Type="int" Nullable="false"/>

  • Since eSQL is primarily set based, we also need a way of defining parameters and return types that are collections of RowTypes:

    <Parameter Name="Coordinates">
    <Property Name="X" Type="int" Nullable="false"/>
    <Property Name="Y" Type="int" Nullable="false"/>
    <Property Name="Z" Type="int" Nullable="false"/>

Using the Function via eSQL:

It is trivial to use the function via eSQL. For example:

SELECT Namespace.GetAge(p)
FROM Container.People AS P
WHERE P.Firstname = ‘Jim’

Here we get Jim's age, assuming of course there is only one Jim!

It is also possible to compose functions together, you must simply ensure return types and target parameter types are the same (in the case of named types) or structurally equivalent (in the case of row types or collections of row types).

Things get a little trickier when you are dealing with functions that return sets, for example imagine a function that returns someone's friends, used in conjunction with the GetAge function:

FROM Container.People AS P
CROSS APPLY Namespace.GetFriends(P) AS F
WHERE Namespace.GetAge(P) > 21

Here we get all the friends of people older than 21.

As you can see to do this sort of thing you need a crash course in eSQL.

Using the Function via LINQ:

It is also possible to use these functions in LINQ, but this does require a extra step to create an appropriate stub function in the CLR.

This solution is based on the techniques described here, and involves creating a Stub function in the CLR language of your choice, and annotating it something like this:

[EdmFunction("Namespace", "GetAge")]
public static int GetAge(Person p)
    throw new NotSupportedException(…);

The Entity Framework uses the signature of the function and the EdmFunction attribute to map calls to this function when encountered to the appropriate Model Defined Function.

Once you have this stub it is then trivial to use it in a LINQ query like this:

var peopleOver21 =
     from p in ctx.People
     where GetAge(p) < 21
     select p;

Indeed if you are familiar with LINQ you will probably find composing functions together a lot easier too:

var friendOfPeopleOver21 =
     from p in ctx.People
     from f in GetFriends(p)
where GetAge(p) < 21
     select f;

Notice that the CLR functions don't need to be directly callable, in the example above the CLR stub throws an exception if called directly.

However the existence of the stub allows you to create LINQ expressions that compile correctly, and then at runtime, when used in a LINQ to Entities query, the function call is simply translated by the entity framework into a query that runs in the database.


As you can see Model Defined Function's are very powerful, and this post has barely scratched the surface of possibilities they open up.

The Entity Framework team would love to hear your comments.

Alex James  
Program Manager,
Entity Framework Team

This post is part of the transparent design exercise in the Entity Framework Team. To understand how it works and how your feedback will be used please look at this post.