Entity Framework 6 Recipes 2nd Edition（11-2）译 -> 为一个”模型定义”函数返回一个计算列

11-3. 为一个”模型定义”函数返回一个计算列

问题

想从”模型定义”函数里返回一个计算列

解决方案

假设我们有一个员工(Employee)实体,属性有: FirstName, LastName,和BirthDate, 如

Figure 11-3所示.

 

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

我们想要创建一个”模型定义”函数,让它返回FirstName 和LastName 合并后的full name

. 我们想要创建另一个”模型定义”函数,让它返回根据BirthDate 列计算出的age

按以下操作:

1. 右击解决方案中的.edmx 文件, 打开方式 ➤ XML 编辑器.

2. 把 Listing 11-5 里的代码插入到.edmx文件的概念模型节点的<Schema> 标签下.这样就在模型里定义好了函数.

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. 插入和查询模型的代码,如Listing 11-6所示的代码:.

Listing 11-6. 用eSQL 和LINQ两种方式调用(“模型定义”函数)来查询模型

    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 are not supported.");

        }

        [EdmFunction("EFRecipesModel1103", "Age")]

        public static int Age(Employee employee)

        {

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

        }

}

上述Listing 11-6执行结果如下:

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

 

它是如何工作的?

我们的”模型定义”函数FullName()返回的是Edm.String类型, Age()返回的是Edm.Int32.这些函数定义在概念层,所以它们不直接引用实体数据模型系统以外的类型. 这些原始的类型容易转化为运行时系统的类型.

在 <DefiningExpression> 里或”模型定义”函数的函数体里,我们直接访问参数接收到的实体的属性.这里没必要用select 语句. 然后,结果表达式必须要有一个类型来匹配函数返回的类型.

在插入一些employee到我们的模型之后,我们的第一个查询用 eSQL. 我们构造一个eSQL 表达式来调用我们的两个”模型定义”函数,并且把结果投射到 Name 和Age列  . 我们的 eSQL 表达式的结果为一个包含Name和Age成员的匿名类型的集合.因为我们不是返回一个在模型里定义好的类型, 所以我们CreateQuery<T>()中的类型声明为DbDataRecord.然后我们遍历查询结果集,并把所有employee的name和age打印出来

在 LINQ 查询里, 我们从Employee实体集中检索,正如本章前面章节一样, .因为我们返回的也是一个标量值,所以我们需要定义一个相应的运行时方法.这些方法只是利用它的签名而不会被调用.

我们可以在Employee实体中为计算所得的full name 和age分别声明只读的属性,然而这样会使得这些计算在实体被取回时都要进行,但我们用”模型定义”函数,只有在我们需要用到这些属性时才进行计算

 

附：创建示例用到的数据库的脚本文件