Model Defined Function with Entity Framework

Posted on: 2011-09-18

Entity Framework let you create Model Defined Function that is function that will be executed server side.

Let say that you you have a table with 3 columns : idProduct, Quantity, Price. You want to have the total amount for each rows. In Sql you would have done something like

SELECT idProduct, Quantity, Price, Quantity*Price as Total 
FROM Product 

You could also get this total with a Linq to Entity query like

 var allRows = db.Products.Select(prod=> new Product(prod.idProduct, prod.Quantity, prod.Price, prod.Quantity * prod.Price); 

The problem is that with bigger mathematics function that may require more than 2 fields on a lot of objects, this would require you to get a lot of data for the calculus. If you just need the total, instead of returning the price, quantity, etc you just need to use the model defined function and you are ready.

Defining the Model Defined Function

The first step is to open the Entity Model (.edmx) file in XML. The second step is to search for the CSDL section (you can search for "edmx:ConceptualModels"). Inside this, you need to add your function inside the schema tag.

Here is an example:

<Function Name="Total" ReturnType="Decimal">; 
  <Parameter Name="p" Type="NorthwindModel.Product" />; 
  <DefiningExpression>; (p.Price * p.Quantity) 
</DefiningExpression>; 
</Function>; 

The Parameter Name is the name used in the calculus and the Type is the type of this parameter. This must be a type of the Entity Model. The mapping will be then done to the table in the database later. In the DefiningExpression you could use some SQL function like CAST.

The third step is the add some C# code to be able to call this new function. This is where the EDMFunction attribute comes in action. This attribute need to be placed over a static method that will have the same return type of the one defined in the function attribute ReturnType. Also, the parameter need to be of the same type defined in the parameter element of the function.

[EdmFunction("Northwind", "Total")] 
public static decimal Total(Product product) { 
  throw new NotSupportedException("Totalcan only be used in a LINQ to Entities query"); 
} 

This static method can be placed where you want. You can insert this method in a class that hold all those EDMFunctions but the the best practice is to partial class the related class. This is perfect for a class generated by the Entity Framework because even if you synchronize it wont be erased. It's also good if you have POCO class because it won't dirty it with persisted functions. Do not forget to add the System.Data.Objects.DataClasses namespace.

How to use EDMFunction?

The Entity Model Function is defined inside the CSDL tag, it's also defined in the C# and finally we can call it. As you have seen, this method is only a shell and does not have any code inside. It can only be executed on the server side.

Here is an example:

var result = (from p in db.Products select new { p.ProductID, DetailTotal = MyStaticClass.Total(p) }).ToList(); 

And here you go.