Creating Model-Defined Function, Returning Collection of Scalar Values

This topic demonstrates how to create a model-defined function returning a scalar result. It uses the Product entity, generated from the Production.Product table in the Microsoft standard AdventureWorks database. The function will return the names of the products having the specified color. 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 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:

Method Editor - General tab

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:

images_GetProductNameByColor2

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

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

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

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

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

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

Send feedback on this topic