Creating Model-Defined Function, Returning Scalar Value

This topic demonstrates how to create a model-defined function returning a scalar result. It uses the SalesOrderDetails entity, generated from the Sales.SalesOrderDetails table in the Microsoft standard AdventureWorks database. The function will return the revenue for the specified product ID. The following walkthrough assumes that the entity is already created. You may simply generate a model from the NorthwindEF database for this walkrthrough.

To create such 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:

Method Editor - General tab

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:

Method Editor - Parameters tab

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)

Method Editor - Defining Expression tab

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 is 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>

As a result of the code generation for the model, the corresponding context class method is generated:

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

Now it this method can be used in the application. For example:

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

Send feedback on this topic