Entity Framework 6 Recipes 2nd Edition (11-2) for a "model definition" function returns a computed column

11-3. Returns a computed column for a "model definition" function

Problem

To return a computed column from the model definition function

Solution Solutions

Suppose we have an employee entity with attributes: FirstName, LastName, and birthdate, such as

Figure 11-3.

Figure 11-3. An Employee entity with a few typical properties

We want to create a "model definition" function that returns the merged full name of FirstName and LastName

. We want to create another model definition function that returns the age that is calculated from the Birthdate column

Follow these steps:

1. Right-click the. edmx file in the solution, open with? XML Editor.

2. Insert the code in Listing 11-5 under the <Schema> tab of the conceptual model node of the. edmx file. This defines the function in the model.

Listing 11-5. Code for model-defined Functions

<function name= "FullName" returntype= "edm.string" >

<parameter name= "emp" type= "Efrecipesmodel1103.employee"/>

<DefiningExpression>

Trim (EMP. FirstName) + "" + Trim (emp. LastName)

</DefiningExpression>

</Function>

<function name= "Age" returntype= "Edm.int32" >

<parameter name= "emp" type= "Efrecipesmodel1103.employee"/>

<DefiningExpression>

Year (Currentdatetime ())-year (EMP. BirthDate)

</DefiningExpression>

</Function>

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

Listing 11-6. Querying models with ESQL and LINQ two-way invocation (model definition function)

Class Program

{

static void Main (string[] args)

{

Runexample ();

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

Console.readkey ();

}

static void Runexample ()

{

using (var context = new EFRecipesEntities1103 ())

{

Context. Database.executesqlcommand ("Delete from Chapter11.employee");

Context. Employees.add (New Employee

{

FirstName = "Jill",

LastName = "Robins",

Birthdate = DateTime.Parse ("3/2/1976")

});

Context. Employees.add (New Employee

{

FirstName = "Michael",

LastName = "Kirk",

Birthdate = DateTime.Parse ("4/12/1985")

});

Context. Employees.add (New Employee

{

FirstName = "Karen",

LastName = "Stanford",

Birthdate = DateTime.Parse ("7/6/1963")

});

Context. SaveChanges ();

}

using (var context = new EFRecipesEntities1103 ())

{

Console.WriteLine ("Query using ESQL");

var esql = @ "Select Efrecipesmodel1103.fullname (E) as Name,

Efrecipesmodel1103.age (e) as age from

Efrecipesentities1103.employees as E ";

var ObjectContext = (context as Iobjectcontextadapter). ObjectContext;

var emps = objectcontext.createquery<dbdatarecord> (esql);

foreach (var emp in emps)

{

Console.WriteLine ("Employee: {0}, Age: {1}", emp["Name"],emp["Age"]);

}

}

using (var context = new EFRecipesEntities1103 ())

{

Console.WriteLine ("\nquery using LINQ");

var emps = from E in context. Employees

Select New

{

Name = Myfunctions.fullname (e),

Age = Myfunctions.age (e)

};

foreach (var emp in emps)

{

Console.WriteLine ("Employee: {0}, Age: {1}", Emp. Name, EMP. Age.tostring ());

}

}

}

}

public class Myfunctions

{

[Edmfunction ("EFRecipesModel1103", "FullName")]

public static string FullName (Employee employee)

{

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

}

[Edmfunction ("EFRecipesModel1103", "age")]

public static int age (employee employee)

{

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

}

}

The results of the above listing 11-6 implementation are as follows:

Query using ESQL

Employee:jill Robins, age:37

Employee:michael Kirk, age:28

Employee:karen Stanford, age:50

Query using LINQ

Employee:jill Robins, age:37

Employee:michael Kirk, age:28

Employee:karen Stanford, age:50

How does it work?

Our model definition function fullname () returns the Edm.string type, and age () returns EDM.INT32. These functions are defined at the conceptual level, so they do not directly refer to types other than the Entity Data Model system. These primitive types are easily converted to the type of the runtime system.

In the function body of <DefiningExpression> or the "model definition" function, we directly access the attributes of the entity to which the parameter is received. There is no need to use the SELECT statement here. The resulting expression must then have a type that matches the type returned by the function.

After inserting some of the employee into our model, our first query was with ESQL. We construct an esql expression to invoke our two "model definition" functions and project the results into the Name and the Age column. The result of our ESQL expression is a collection of anonymous types that contain the name and age members. Because we are not returning a type defined in the model, so we createquery<t> () The type declared in is DbDataRecord. Then we traverse the query result set and print out the name and age of all the employee

In LINQ queries, we retrieve from the employee entity centrally, as in the previous section of this chapter. Because we are returning a scalar value, we need to define a corresponding runtime method. These methods only use its signature and are not called.

We can declare read-only properties in the employee entity for the computed full name and age, but this will make the calculations happen when the entity is retrieved, but we use the model definition function, which is only evaluated when we need to use these properties.

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

Entity Framework 6 Recipes 2nd Edition (11-2) for a "model definition" function returns a computed column