Entity Framework 6 Recipes 2nd Edition (11-1), entityrecipes

Entity Framework 6 Recipes 2nd Edition (11-1), entityrecipes

The11ChapterFunction

Functions provide a powerful code reuse mechanism, and keep your code concise and easy to understand.

They are also the database-Layer Code that EF can use when running. Functions include Rowset Functions, Aggregate Functions, Ranking Functions, and scalar Functions.

The function is either determined or uncertain. When a function is called with some specified values and the results returned by the function are always the same, it is a definite function. When the same value is used, and the results returned by the function each time may be different, it is an uncertain function.

In the first seven sections, we explored "model definition" functions that allow us to create at the conceptual layer. These functions are defined according to the EF type and your model entity. In this way, they can be conveniently executed through data storage.

In the remaining sections, we will show you how to use functions defined by EF and functions at the database layer.

These functions allow you to influence existing code, at EF running or closer to the database layer of 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 entity instance, and return a scalar value.

Solution

Suppose there is a model shown in Figure 11-1.

 

Figure 11-1.A product and classification model

Next, create an instance that accepts a Category entity and return the average unit price of all products in the given Category:

1. In Solution Explorer, right-click the. edmx file and select Open Mode XML editor.

2. Insert the code in Listing 11-1 in the <Schema> label of the conceptual models node in the. edmx file to define the function 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 of 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.99 M,

Category = c1

};

Var p2 = new Product

{

ProductName = "Kraz ",

UnitPrice = 99.99 M,

Category = c1

};

Var p3 = new Product

{

ProductName = "Sundome ",

UnitPrice = 49.99 M,

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.99 M,

Category = c2

};

Var p5 = new Product

{

ProductName = "Montana ",

UnitPrice = 149.99 M,

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 an 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 an 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 CILS are not supported! ");

}

}

Listing 11-2.Insert and query a model using the AverageUnitPrice () function defined by the Model

 

The output result is as follows: Listing 11-2:

 

Using eSQL for the query...

 

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

Category 'family tents' has an average price of $159.99

 

Using LINQ for the query...

 

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

Category 'family tents' has an average price of $159.99

 

How does it work?

The "model definition" function is created at the concept layer and written in eSQL. of course, "model definition" allows you to reference entities in your model. Just as we have done here, Category entities and Product entities and their relationships are referenced in function implementation. The extra benefit of a function is that we will not be bound to a specified storage layer. Our program can also work by placing functions on lower layers, even database drivers.

The current designer does not support "model definition" functions. Unlike stored procedures, it can be supported by the designer, the "model definition" function will not be displayed by the model browser or appear elsewhere in the designer. The designer does not check the syntax errors in eSQL, and only reports errors at runtime, but can at least enable. edmx for definition.

In Listing 11-2, the code first inserts two categories and their respective products, and then queries 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 in the query result, we retrieve the first column of data (category name) and the second column of data (average unit price of each product category ). and output.

The second query example is more interesting. We use the AverageUnitPrice () function in the LINQ query, but we need to add a method stub in another class. The method uses [EdmFunction ()] feature decoration, marking it as a "model definition" function. the runtime method cannot be called (once called, an exception is explicitly thrown in the method ). because we only return a scalar value, the signature of this method is relatively simple (number of parameters, type, and return value type ). in the query In the LINQ query, we obtain each category and map the result (the category name, the result returned by calling the AverageUnitPrice () method In the MyFunction class) to an anonymous class. and output.

DbContext is a lightweight version of ObjectContext. You must use ObjectContext whenever you need to execute eSql (Entity SQL). because we need to get ObjectContext through DbContext (use :( context as IObjectContextAdapter) ObjectContext ).

The parameters of the "model definition" function can be scalar value, entity class, complex type, anonymous type, or a set of the above types ). in many sections of this chapter, we will demonstrate how to create and use the "model definition" function of these types of parameters.

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

In this example, we return a Single Scalar decimal type value. Because the Select query results are understood as a set, we need to explicitly use the AnyElement operator for the returned results. EF doesn't know how to map a set into a scalar value. So here we use the AnyElement operator to tell it that the returned result is just an element. When the Select result has only one element, we also use this operator to tell the caller that it is only one element.

Best practices

The "model definition" function provides an integral part of a pure and effective conceptual model. The following are some of its best practices :.

> The "model definition" function is defined in eSQL to the concept layer. We can abstract a more complete model from the details of the storage model.

> You can define commonly used expressions in LINQ or eSQL queries as functions. this makes the code structure better and reusable. of course, if you use the smart sensing and compile-time check provided by LINQ and VS, the code will be reduced due to incorrect input.

> The "model definition" function is a "component" that allows you to use it as a component in a more complex expression. This makes your code simpler and maintainability possible.

> The "model definition" function can be used where computing is needed. For example, if an attribute needs to be calculated is instantiated, it will consume computing resources. No matter whether this attribute is used, the "model definition" function calculates the attribute value only when you actually use this attribute.

 

Appendix: script file of the database used in the Creation example