Creating Model-Defined Function, Returning Collection of Entities

This topic demonstrates how to create a model-defined function returning a scalar result. It uses the SalesOrderDetail entity, generated from the Sales.SalesOrderDetails table in the Microsoft standard AdventureWorks database. The function will return a collection of the SalesOrderDetails entities having the specified price. 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 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:

Method Editor - General tab

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:

Method Editor - Parameters tab

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

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="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>

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

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

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

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

Send feedback on this topic