Model Defined Functions are a great addition to EF4. It allows you to add functions directly into your model rather than having to place the additional logic into business classes. This not only allows the functions to be “just there”, but you can use them in queries, something that you cannot do with properties that are defined in the classes.
Note that in the MSDN docs, these are called “Conceptual Model Functions” and also “User Defined Functions”.
The basics for MDFs are simple enough and have been covered in a few blog posts, but there are only hints at how to use them when you want to go beyond the basics.
But first the basics with a simple example that I have wanted to do since the first time I saw an EDM: concatenate First and Last Name properties.
Functions have been part of the conceptual schema since we were first introduced to the EDM. There is a new function element called DefiningExpression. Here, you can write some Entity SQL to perform a function and return the results. You don’t write an entire query expression however, just the function.
The complete query expression to return the full name could look like this.
SELECT c.contactId, Trim(c.FirstName) + “ “ + c.LastName
FROM MyEntities.Contacts AS c
If you wanted to enapsulate that full name forumla in a defining expression you need only
<DefiningExpression>
Trim(c.FirstName) + “ “ + c.LastName
</DefiningExpression>
The function also requires you to pass in the parameter the function will be preformed on, in this case, the Contact.
Here's what the function would look like. NOte that it takes a parameter.
<Function Name="FullName" ReturnType="Edm.String">
<Parameter Name="c" Type="TestModel.Contact">
</Parameter>
<DefiningExpression>
Trim(c.FirstName) + " " + c.LastName
</DefiningExpression>
</Function>
As of Beta 1, this features is not supported in the designer, so you have to go into the XML to add the new function. You need to place the function in the CSDL so that it is a sibling of the Entity elements.
The function is readily available to Entity SQL queries. The contact variable is passed into the function as a parameter.
SELECT c.contactId, FullName(c)
FROM MyEntities.Contacts as c
But because LINQ to Entities works against classes, not the model itself, you need to make that function available in code. The function is not generated into any of the classes by default. You can change the T4 template of course.
The function is not added as a method of a particular entity. I have been creating a separate class called Functions. The method needs attributes to wire it up to the model and can only be used in queries, not called directly. I’ve copied the exception text that is demonstrated in the MSDN docs.
public static class Functions
{
[EdmFunction("TestModel", "FullName")]
public static string FullName(Person p)
{
throw new NotSupportedException
("This function can only be used in a query");
}
}
Now you can use the function in a LINQ query:
from p in context.People
select new {p.PersonID, FullName=Functions.FullName(p)}
Okay, that’s the easy one.
In both the Model Defined Functions blog post from Alex James and the documentation, there are hints about returning more than a scalar value from the function. You can return scalars, entities, types or collections. You can even define a type on the fly. I wanted to figure out how to do this. It took a lot of experimenting to figure out, but now that I did figure it out, it makes perfect sense.
Imagine that I wanted to create a new type that returned a number of calculated values, for example, Age and FullName.
If I did that in an EntitySQL query it would look like this:
SELECT c.contactID, Trim(c.FirstName) + “ “ + c.LastName, DiffYears(c.BirthDate,CurrentDateTime())
FROM MyEntities.Contacts AS c
If you look at the function above, you can see that I defined the ReturnType as an attribute of the function.
I could also define it as an element
<Function Name="FullName">
<ReturnType Type="Edm.String" />
For an in-line type, I can then define a RowType with properties.
<Function Name="CalculatedDetails">
<ReturnType>
<RowType>
<Property Name="Age" Type="Int32" />
<Property Name="FullName" Type="String"/>
</RowType>
</ReturnType>
Then the parameter and defining expression.
Here’s the kicker for the DefiningExpression. I kept trying to just use the subset of the ESQL expression above.
<DefiningExpression>
DiffYears(Person.PersonalDetail.BirthDate,CurrentDateTime()),Trim(Person.FirstName) + " " + Person.LastName
</DefiningExpression>
It failed over and over. You don’t want to know how many things I tried and how much time I spent. But you can thank me for getting this out of the way so you don’t have to do what I did. ;-)
I was missing the fact that the type I defined above was a Row. I was only returning some scalars. They needed to be wrapped in a row which is an Entity SQL function I have known but never used.
Here is the complete correct function that returns a Row.
<Function Name="CalculatedDetails">
<ReturnType>
<RowType>
<Property Name="Age" Type="Int32" />
<Property Name="FullName" Type="String"/>
</RowType>
</ReturnType>
<Parameter Name="Person" Type="TestModel.Person">
</Parameter>
<DefiningExpression>
Row(DiffYears(Person.PersonalDetail.BirthDate,CurrentDateTime()),Trim(Person.FirstName) + " " + Person.LastName)
</DefiningExpression>
</Function>
You could also return a collection, perhaps your own view of orders on a customer. To do that you need to wrap the RowType element inside a CollectionType.
<ReturnType>
<CollectionType>
<RowType>
<Property ….. />
<Property ….. />
</RowType>
</CollectionType>
</ReturnType>
Using the inline type is not as straightforward as using one that returns a scalar. If you have already worked with ESQL and DbDataRecords, this won’t be painful at all.
An Entity SQL expression that uses the function could looke like this
string esql= “SELECT p.PersonID, TestModel.CalculatedDetails(p) “ +
“from TestEntities.People as p”; ObjectQuery<DbDataRecord> detailsQuery =
context.CreateQuery<DbDataRecord>(esql);
var detailsList = detailsQuery.ToList();
You can then dig through the resulting DbDataRecords the same as you would for a row of a DataReader.
detailsList[0][0] will return the PersonId of the first result.
detailsList[0][1] will return the type as a MaterializedDataRecord with two fields.
detailsList[0][1][0] will contain the Age and detailsList[0][1][1] will contain the Full Name.
What about LINQ?
I have experimented with this a lot and can only seem to succeed by returning dbDataRecords for LINQ also. I even tried defining a type with those two properties but can’t directly get that back.
So for now, I’m stuck with
[EdmFunction("TestModel", "CalculatedDetails")]
public static DbDataRecord CalculatedDetails(Person p)
{
throw new NotSupportedException
("This function can only be used in a query");
}
The results are anonymous types whose first field is an int called PersonID and second field is a MaterializedDataRecord called Details. Again, I have to dig through the MaterializedDataRecord to get the values.
So with this example, the value of the Model Defined Function might have gotten a bit blurry because of the extra effort of dealing with the DbDataRecord, therefore let me reiterate. :-)
These functions logically fit in the model and it’s been a drag to have to have to build them into the classes. That means if you wanted to reuse them you have to distribute the classes as well as the model.
Also having the functions in the model makes it possible to use them in queries. That’s huge.
DefiningExpression is only one way to leverage the functions. I imagine that will see more ways to embed logic into those functions as time goes on. The only thing I worry about which is the same worry that we have with SqlCLR is that they will be misunderstood and misused. Developers will have to apply some thought behind what logic belongs in the model vs. in the business classes.
For more details on MDFs, looks for the terms “Conceptual Model Functions” in the MSDN docs.
Here are the key articles in there so far.
- Conceptual Model Functions (Entity Data Model)
- How to: Call Model-Defined Functions in Queries (LINQ to Entities)
- How to: Call Model-Defined Functions as Object Methods (LINQ to Entities)
There is a whole ‘nother related feature to look at which is the ability to define functions inline in your code.
Hope this helps! I *hate* not knowing how to use a feature that looks promising.
