Database Reference
In-Depth Information
In the first query example, we build an eSQL statement that calls the
AverageUnitPrice()
function. We create
and execute the query. For each row in the results, we pull out the data for the first column, which is the category
name, and the data for the second column, which is the average unit price for the category. We display them for
each row.
The second query example is a little more interesting. Here we use the
AverageUnitPrice()
function in
a LINQ query. To do this, we need to add a stub method in a separate class. The method is decorated with the
[EdmFunction()]
attribute, which marks it as an implementation of a model-defined function. This CLR method will
not actually be called, which is evident by the exception we throw in the body of the method. Because we return a
scalar value, the method's implementation here is simply for the signature (the parameter number, types, and return
type). In the LINQ query, we grab each category and reshape the results into an anonymous type that holds the
category name and the result of calling the
AverageUnitPrice()
method in the
MyFunction
class. This is the stub we
created that is tied to the
AverageUnitPrice()
model-defined function. For each of the resulting objects, we display
the category name and the category's average unit price.
DbContext is the light version of ObjectContext. Whenever a CreateQuery is to be used to execute a Sql
(Entity SQL), then ObjectContext is required. Thus ObjectContext is fetched through DbContext using (context as
IObjectContextAdapter) ObjectContext.
The parameters for model-defined functions can be scalar, entity types, complex types, anonymous types, or
collections of these. In many of the recipes in this chapter, we'll show you how to create and use model-defined
functions with these parameter types.
The parameters for model-defined functions don't show direction. There are no “out” parameters, only implied
“in” parameters. The reason for this is that model-defined functions are composable and can be used as part of LINQ
queries. This prevents them from returning values in output parameters.
In this example, we returned a single scalar decimal value. To do this, we had to explicitly return a scalar using the
AnyElement operator. Entity Framework does not know how to map a collection to a scalar value. We help out here by
using the AnyElement operator, which signals that only a single value will result from the query. It just so happens that
we return a collection of just one element from which the AnyElement operator selects just one element.
Best Practice
Model-defined functions provide a clean and practical way to implement parts of a conceptual model that would be
tedious if not impossible any other way. Here are some best practices and uses for model-defined functions.
Model-defined functions are written in eSQL and defined at the conceptual layer. This
provides a level of abstraction from the details of the store layer and allows you to leverage a
more complete model independent of the store layer.
You can define functions for expressions that you commonly use in your LINQ or eSQL
queries. This provides better code organization and allows code reuse. Also, if you use
LINQ, then due to the nature of IntelliSense and compile-time checks, there will be fewer
code issues because of typos.
Model-defined functions are composable, which allows you to implement functions that
serve as building blocks for more complex expressions. This can both simplify your code
and make it more maintainable.
Model-defined functions can be used in places where you have computed properties.
A
computed property
, like a function, is a read-only value. For properties, you incur the
cost of computing the value when the entity is materialized, whether or not you need the
computed property. With a model-defined function, the cost of computing the value is
incurred only when you actually need the value.
