Model-Defined Functions in Entity Developer

This article explains how the support of Entity Framework Model-Defined Functions is implemented in Entity Developer ORM Designer and illustrates creating such methods on simple examples. The Entity model for Entity Developer used in the samples below can be downloaded here.

A model-defined function is a function, declared in the conceptual part of the model (csdl) and mapped to Entity SQL queries instead of usual mapping to a stored function from the storage part (like Function Import). When executing such a method, an Entity SQL query, specified as the defining expression of the method, is executed. The method can have zero or more parameters and method parameters are referenced directly by Name in the DefiningExpression, no parameter denoting prefix (like @) is used. This means you must be careful to choose parameter names that don’t coincide with other identifiers you need to use in the eSQL expression. Such methods support only IN bound parameters. The method must have a return type and return type can be any of the following:

• A scalar type or a collection of scalar types.
• An entity type or a collection of entity types.
• A complex type or a collection of complex types.

Method with a DefiningExpression do not require mapping, because the eSQL expression is composed out of eSQL fragments that are already mapped.

The model we use in this tutorial needs only two entities, based on the Sales.SalesOrderDetail and Production.Product tables. You can either download the model with these two classes here or generate it from the AdventureWorks database with Entity Developer.

1) Model-Defined Functions, Returning Scalar Value

To create a model-defined function, returning a scalar value, we perform the following actions:

1. In the Model Explorer window right-click the Methods node and select New Method… in the shortcut menu.
2. Tweak the method as shown on the following screenshot:Note: The cleared Collection Result check box means that the result of this method will be a single value of decimal type, not the collection IQueryable<decimal>.
3. Switch to the Parameters tab. Click the Add button to add a new parameter.
4. Specify all the settings for the new parameter as shown on the following screenshot:
5. Switch to the Defining Expression tab and enter the following code to the Entity SQL box:

   SUM(SELECT VALUE((s.UnitPrice - s.UnitPriceDiscount) * s.OrderQty)
   FROM AdventureWorksEntities.SalesOrderDetails as s
   WHERE s.ProductID = productID)

   

   Note that parameter name is specified and used without any prefix like ‘@’ or ‘:’ on both Parameters tab and Defining Expression tab. Also it is necessary to use the context name as a prefix when referencing conceptual model objects in Entity SQL text.

6. Click OK and save the model. If you are using the standalone version of Entity Developer, start the code generation by clicking the Generate Code button on the Model toolbar.

The following code will be generated in the .csdl mapping file:

<Function Name="GetProductRevenue" ReturnType="Decimal">
    <Parameter Name="productID" Type="Int32" />
    <DefiningExpression>SUM(SELECT VALUE((s.UnitPrice - s.UnitPriceDiscount) * s.OrderQty)
FROM AdventureWorksEntities.SalesOrderDetails as s
WHERE s.ProductID = productID)</DefiningExpression>
</Function>

The following method will be generated in the context class:

C#

        [EdmFunction(@"AdventureWorks", @"GetProductRevenue")]
        public global::System.Nullable<decimal> GetProductRevenue (global::System.Nullable<int> productID)
        {
            return this.QueryProvider.Execute<global::System.Nullable<decimal>>(Expression.Call(Expression.Constant(this),(MethodInfo)MethodInfo.GetCurrentMethod(),Expression.Constant(productID, typeof(global::System.Nullable<int>))));
        }

Visual Basic

        <EdmFunction("AdventureWorks", "GetProductRevenue")> _
        Public Function GetProductRevenue (ByVal productID As Global.System.Nullable(Of Integer)) As Global.System.Nullable(Of Decimal)
            Return MyBase.QueryProvider.Execute(Of Global.System.Nullable(Of Decimal))(Expression.Call(Expression.Constant(Me), DirectCast(MethodBase.GetCurrentMethod, MethodInfo), New Expression() { Expression.Constant(productID, GetType(Global.System.Nullable(Of Integer))) }))
        End Function

You can call this method in your code in the following way:

C#

      using (AdventureWorksEntities context = new AdventureWorksEntities()) {

        decimal? productRevenue = context.GetProductRevenue(776);
      }

Visual Basic

        Using context As AdventureWorksEntities = New AdventureWorksEntities

            Dim productRevenue As Nullable(Of Decimal) = context.GetProductRevenue(776)
        End Using

2) Model-Defined Function, Returning Collection of Scalar Values

To create a model-defined function, returning a collection of scalar values, we perform the following actions:

1. In the Model Explorer window right-click the Methods node and select New Method… in the shortcut menu.
2. Tweak the method as shown on the following screenshot:Note: The selected Collection Result check box means that the result of this method will be a collection IQueryable<string>, not a single string value.
3. Switch to the Parameters tab. Click the Add button to add a new parameter.
4. Specify all the settings for the new parameter as shown on the following screenshot:
5. Switch to the Defining Expression tab and enter the following code to the Entity SQL box:

   SELECT VALUE p.Name FROM AdventureWorksEntities.Products as p
   WHERE p.Color = color

   

   Note that parameter name is specified and used without any prefix like ‘@’ or ‘:’ on both Parameters tab and Defining Expression tab. Also it is necessary to use the context name as a prefix when referencing conceptual model objects in Entity SQL text.

6. Click OK and save the model. If you are using the standalone version of Entity Developer, start the code generation by clicking the Generate Code button on the Model toolbar.

The following code will be generated in the .csdl mapping file:

  <Function Name="GetProductNameByColor" ReturnType="Collection(String)">
    <Parameter Name="color" Type="String" />
    <DefiningExpression>SELECT VALUE p.Name FROM AdventureWorksEntities.Products as p
WHERE p.Color = color</DefiningExpression>
  </Function>

The following method will be generated in the context class:

C#

        [EdmFunction(@"AdventureWorks", @"GetProductNameByColor")]
        public IQueryable<string> GetProductNameByColor (string color)
        {
            return this.QueryProvider.CreateQuery<string>(Expression.Call(Expression.Constant(this),(MethodInfo)MethodInfo.GetCurrentMethod(),Expression.Constant(color, typeof(string))));
        }

Visual Basic

        <EdmFunction("AdventureWorks", "GetProductNameByColor")> _
        Public Function GetProductNameByColor (ByVal color As String) As IQueryable(Of String)
            Return MyBase.QueryProvider.CreateQuery(Of String)(Expression.Call(Expression.Constant(Me), DirectCast(MethodBase.GetCurrentMethod, MethodInfo), New Expression() { Expression.Constant(color, GetType(String)) }))
        End Function

You can call this method in your code in the following way:

C#

      using (AdventureWorksEntities context = new AdventureWorksEntities()) {

        IList<string> productNameByColor = context.GetProductNameByColor("silver").ToList();
      }

Visual Basic

        Using context As AdventureWorksEntities = New AdventureWorksEntities

            Dim productNameByColor As List(Of String) = context.GetProductNameByColor("silver").ToList()
        End Using

3) Model-Defined Function, Returning Collection of Entities

To create a model-defined function, returning a collection of entities, we perform the following actions:

1. In the Model Explorer window right-click the Methods node and select New Method… in the shortcut menu.
2. Tweak the method as shown on the following screenshot:Note: The selected Collection Result check box means that the result of this method will be a collection IQueryable<SalesOrderDetail>, not a single SalesOrderDetail entity.
3. Switch to the Parameters tab. Click the Add button to add a new parameter.
4. Specify all the settings for the new parameter as shown on the following screenshot:
5. Switch to the Defining Expression tab and enter the following code to the Entity SQL box:

   SELECT VALUE s FROM AdventureWorksEntities.SalesOrderDetails as s
   WHERE s.UnitPrice = price

   

   Note that parameter name is specified and used without any prefix like ‘@’ or ‘:’ on both Parameters tab and Defining Expression tab. Also it is necessary to use the context name as a prefix when referencing conceptual model objects in Entity SQL text.

6. Click OK and save the model. If you are using the standalone version of Entity Developer, start the code generation by clicking the Generate Code button on the Model toolbar.

The following code will be generated in the .csdl mapping file:

  <Function Name="GetSalesOrderDetailByPrice" ReturnType="Collection(AdventureWorks.SalesOrderDetail)">
    <Parameter Name="price" Type="Decimal" />
    <DefiningExpression>SELECT VALUE s FROM AdventureWorksEntities.SalesOrderDetails as s
WHERE s.UnitPrice = price</DefiningExpression>
  </Function>

The following method will be generated in the context class:

C#

        [EdmFunction(@"AdventureWorks", @"GetSalesOrderDetailByPrice")]
        public IQueryable<SalesOrderDetail> GetSalesOrderDetailByPrice (global::System.Nullable<decimal> price)
        {
            return this.QueryProvider.CreateQuery<SalesOrderDetail>(Expression.Call(Expression.Constant(this),(MethodInfo)MethodInfo.GetCurrentMethod(),Expression.Constant(price, typeof(global::System.Nullable<decimal>))));
        }

Visual Basic

        <EdmFunction("AdventureWorks", "GetSalesOrderDetailByPrice")> _
        Public Function GetSalesOrderDetailByPrice (ByVal price As Global.System.Nullable(Of Decimal)) As IQueryable(Of SalesOrderDetail)
            Return MyBase.QueryProvider.CreateQuery(Of SalesOrderDetail)(Expression.Call(Expression.Constant(Me), DirectCast(MethodBase.GetCurrentMethod, MethodInfo), New Expression() { Expression.Constant(price, GetType(Global.System.Nullable(Of Decimal))) }))
        End Function

You can call this method in your code in the following way:

C#

      using (AdventureWorksEntities context = new AdventureWorksEntities()) {

        IList<SalesOrderDetail> orderDetailsByPrice = context.GetSalesOrderDetailByPrice(2024.9940M).ToList();
      }

Visual Basic

        Using context As AdventureWorksEntities = New AdventureWorksEntities

            Dim orderDetailsByPrice As List(Of SalesOrderDetail) = context.GetSalesOrderDetailByPrice(2024.994).ToList()
        End Using

Calling Model-Defined Functions from LINQ Queries

It is possible to use model-defined functions in LINQ, but this requires an extra step to create an appropriate stub method in the CLR.

To call such method from LINQ queries do the following:

1. Add a common language runtime (CLR) method, mapped to the function defined in the conceptual model, to your application. To map the method, you must apply EdmFunctionAttribute to this method. Note that the NamespaceName and FunctionName parameters of the attribute are the namespace name of the conceptual model and the function name in the conceptual model respectively. Function names in LINQ are case sensitive.Here is the example of such method.C#

       [EdmFunction(@"AdventureWorks", @"GetProductRevenue")]
       public static global::System.Nullable<decimal> GetProductRevenue(global::System.Nullable<int> productID) {
   
         throw new NotSupportedException("Direct calls are not supported.");
       }

   Visual Basic

       <EdmFunction("AdventureWorks", "GetProductRevenue")> _
       Public Shared Function GetProductRevenue(ByVal productID As Global.System.Nullable(Of Integer)) As Global.System.Nullable(Of Decimal)
   
           Throw New NotSupportedException("Direct calls are not supported.")
       End Function

2. After this you may use this method in LINQ to Entities queries.
   C#

         using (AdventureWorksEntities context = new AdventureWorksEntities()) {
   
           var products = from p in context.Products
                             where GetProductRevenue(p.ProductID) > 5000
                             select p;
         }

   Visual Basic

           Using context As AdventureWorksEntities = New AdventureWorksEntities
   
               Dim products As IQueryable(Of Product) = From p In context.Products Where (GetProductRevenue(p.ProductID) > 5000) Select p
           End Using

CLR methods 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.