Entity Framework 6 Recipes 2nd Edition (11-1)---return a scalar value from the model definition function

Section One Chapters function

The function provides a powerful code reuse mechanism and keeps your code concise and understandable.

They are also the database layer code that EF can use when it runs. There are several classes of functions: Rowset Functions, aggregate functions, Ranking Functions, and scalar-valued functions.

The function is either deterministic or indeterminate. When a function is called with some specified value, and the result of the function is always the same, it is the deterministic function. When called even with some of the same values, and each time the function returns a different result, it is an indeterminate function.

In the first seven sections, we explored "model definition" functions that allow us to create on the conceptual layer. These functions are defined according to the EF type and your model entity. This allows them to be executed conveniently through the data store.

In the remaining sections, we show how to use functions that are defined by EF and the functions of the database layer.

These functions allow you to influence existing code at the database layer where the EF is running or closer to your data.

11-1. Return a scalar value from the model definition function

Problem

You want to define a function in the conceptual model, accept an instance of an entity, and return a scalar value.

Solution Solutions

Suppose you already have a model like figure 11-1.

Figure 11-1. a model of products and classifications

Next, create an average unit price that accepts a category entity instance and returns all product in a given category:

1. In Solution Explorer, right-click the. edmx file and select Open with? XML Editor.

2. In the conceptual model (conceptual models) node <Schema> tag of the. edmx file, insert the code in Listing 11-1 so that the function is defined in the model.

Listing 11-1. Definition of the Averageunitprice () Function in the Model

<function name= "Averageunitprice" returntype= "Edm.decimal" >

<parameter name= "category" Type= "Efrecipesmodel1101.category"/>

<DefiningExpression>

Anyelement (Select VALUE AVG (p.unitprice) from Efrecipesentities1101.products as P where p.category = = Category)

</DefiningExpression>

</Function>

3. Insert and query the code for this model, as shown in Listing 11-2.

Class Program

{

static void Main (string[] args)

{

Runexample ();

Console.WriteLine ("\npress any key to exit ...");

Console.readkey ();

}

static void Runexample ()

{

using (var context = new EFRecipesEntities1101 ())

{

Context. Database.executesqlcommand ("Delete from Chapter11.product;delete from Chapter11.category");

var C1 = new Category {CategoryName = "backpacking Tents"};

var p1 = new Product

{

ProductName = "Hooligan",

UnitPrice = 89.99M,

Category = C1

};

var P2 = new Product

{

ProductName = "KrAZ",

UnitPrice = 99.99M,

Category = C1

};

var p3 = new Product

{

ProductName = "Sundome",

UnitPrice = 49.99M,

Category = C1

};

Context. Categories.add (C1);

Context. Products.add (p1);

Context. Products.add (p2);

Context. Products.add (p3);

var C2 = new Category {CategoryName = "Family Tents"};

var P4 = new Product

{

ProductName = "Evanston",

UnitPrice = 169.99M,

Category = C2

};

var P5 = new Product

{

ProductName = "Montana",

UnitPrice = 149.99M,

Category = C2

};

Context. Categories.add (C2);

Context. Products.add (p4);

Context. Products.add (p5);

Context. SaveChanges ();

}

With ESQL

using (var context = new EFRecipesEntities1101 ())

{

Console.WriteLine ("Using ESQL for the query ...");

Console.WriteLine ();

String sql = @ "Select C.categoryname, EFRecipesModel1101

. Averageunitprice (c) as Averageprice from

Efrecipesentities1101.categories as C ";

var ObjectContext = (context as Iobjectcontextadapter). ObjectContext;

var cats = objectcontext.createquery<dbdatarecord> (SQL);

foreach (var cat in cats)

{

Console.WriteLine ("Category ' {0} ' has a average price of {1}",

cat["CategoryName"], ((decimal) cat["Averageprice"]). ToString ("C"));

}

}

With LINQ

using (var context = new EFRecipesEntities1101 ())

{

Console.WriteLine ();

Console.WriteLine ("Using LINQ for the query ...");

Console.WriteLine ();

var cats = from C in context. Categories

Select New

{

Name = C.categoryname,

Averageprice = Myfunctions.averageunitprice (c)

};

foreach (var cat in cats)

{

Console.WriteLine ("Category ' {0} ' has a average price of {1}",

Cat. Name, Cat. Averageprice.tostring ("C"));

}

}

}

}

public class Myfunctions

{

[Edmfunction ("EFRecipesModel1101", "Averageunitprice")]

public static decimal Averageunitprice (category category)

{

throw new NotSupportedException ("Direct calls is not supported!");

}

}

Listing 11-2. Inserting and querying models with the Averageunitprice () function of the model definition

The output results are shown in Listing 11-2 below:

Using ESQL for the query ...

Category ' backpacking tents ' has a average price of $79.99

Category ' Family tents ' has a average price of $159.99

Using LINQ for the query ...

Category ' backpacking tents ' has a average price of $79.99

Category ' Family tents ' has a average price of $159.99

How does it work?

A "model definition" function, created at the conceptual level, and written in esql. Of course, the model definition allows you to reference entities in your model, as we do here, referencing the category entity and the product entity and the relationships between them in the implementation of the function. The additional benefit of the function is that we are not bound to a specified storage tier. The function is placed on the lower layer, even the database driver, our program can also work.

The current designer does not support the model definition function, unlike stored procedures, which can be supported by the designer, and the model definition function will not be displayed by the model browser and will not appear elsewhere in the designer. The designer also does not check for syntax errors in esql, but only when it is run, but at least open an. edmx to define it.

In Listing 11-2, the code first inserts two categories (category) and some of their respective products (product). Then query the data in two slightly different ways. In the first query example, we create an ESQL statement to call the Averageunitprice () function. and execute the query. In each row of the query results, we take out the first column of data (category name) and the second column of data (average unit price per product category). and output.

The second query example, more interesting, we use the Averageunitprice () function in a LINQ query, but first we need to add a method stub to the other class, which is decorated with a [edmfunction ()] property and marked as a "model definition" function. The run-time method cannot invoke it (and, once called, explicitly throws an exception in the method). Because we just return a scalar value, this method signature is simpler (number of arguments, type, and return value type). In the LINQ query, we get each category and map the result (category name, call to the result returned by the Averageunitprice () method in the MyFunction Class) to an anonymous class. and output.

DbContext is the ObjectContext lightweight version. You must use ObjectContext whenever you need to execute esql (Entity SQL). Because we want to get ObjectContext through DbContext (using: (Context as Iobjectcontextadapter) ObjectContext).

The parameters of the model definition function can be: Scalar values, entity classes, complex types, anonymous types, or collections of the above types. In many sections of this chapter, we demonstrate how to create and use the model definition functions for these type parameters.

The parameters of the model definition function have no directionality, there is no output parameter, only the input parameter, because the model definition function is just a component and can be part of a LINQ query.

In this example, we return the value of a single scalar decimal type. Because the select query results are interpreted as a collection, we need to explicitly use the anyelement operator for the returned result. EF does not know how to map a collection to a scalar value, so we use the anyelement operator here to tell it that the result returned is just an element. When the select result has only one element, we also use the operator to tell the caller that it is just an element.

Best practices

The model definition function provides an integral part of a pure and efficient conceptual model. Here are a few of its best practices:.

The > Model definition function is defined with ESQL to the conceptual layer. This uses the ability to abstract a more complete model from the details of the storage model.

> You can define the expressions commonly used in LINQ or ESQL queries as functions. This makes the code more organized and reusable. Of course, if you use LINQ, VS provides IntelliSense and compile-time checking, it can reduce your code's problems due to incorrect input.

The > Model definition function is a "component" that allows you to use it as a component in more complex expressions. This will make your code easier and more maintainable.

The > "Model definition" function can be used where there is a need to calculate, such as a property that needs to be computed, and when the entity is instantiated it will consume the computation, no matter what you use it, and the "model definition" function only calculates the value of the property when you do use it.

Attached: Creating a script file for the database used by the sample

Entity Framework 6 Recipes 2nd Edition (11-1)---return a scalar value from the model definition function