Entity Framework 6 Recipes 2nd Edition（11-5）译 -> 从”模型定义”函数返回一个匿名类型

11-5. 从”模型定义”函数返回一个匿名类型

问题

想创建一个返回一个匿名类型的”模型定义”函数

解决方案

假设已有游客(Visitor) 预订(reservation)房间(hotel ) 的模型,如Figure 11-5所示.

 

Figure 11-5. A model for hotel reservations

想要返回每位游客房间预订条数和带来的总收入.因为很多地方需要这些信息,所以想要创建一个”模型定义”函数,接受一个查询参数,返回一个包含游客合计信息的匿名类型的集合:

2. 把Listing 11-9 中的代码,插入到.edmx文件的概念模型里<Schema> 标签下,这样我们就定义好了函数.

Listing 11-9. The VisitorSummary() Model-Defined Function

        <Function Name="VisitorSummary">

          <Parameter Name="StartDate" Type="Edm.DateTime" />

          <Parameter Name="Days" Type="Edm.Int32" />

          <ReturnType>

            <CollectionType>

              <RowType>

                <Property Name="Name" Type="Edm.String" />

                <Property Name="TotalReservations" Type="Edm.Int32" />

                <Property Name="BusinessEarned" Type="Edm.Decimal" />

              </RowType>

            </CollectionType>

          </ReturnType>

          <DefiningExpression>

            Select

            r.Visitor.Name,

            COUNT(r.ReservationId) as TotalReservations,

            SUM(r.Cost) as BusinessEarned

            from EFRecipesEntities.Reservations as r

            where r.ReservationDate between StartDate and

            AddDays(StartDate,Days)

            group by r.Visitor.Name

          </DefiningExpression>

        </Function>

3. 接下来插入和查询模型,代码如 Listing 11-10所示:.

Listing 11-10. Querying the Model Using the VistorySummary() Model-Defined Function

    class Program

    {

        static void Main(string[] args)

        {

            RunExample();

        }

        static void RunExample()

        {

            using (var context = new EFRecipesEntities())

            {

                var hotel = new Hotel { Name = "Five Seasons Resort" };

                var v1 = new Visitor { Name = "Alex Stevens" };

                var v2 = new Visitor { Name = "Joan Hills" };

                var r1 = new Reservation

                {

                    Cost = 79.99M,

                    Hotel = hotel,

                    ReservationDate = DateTime.Parse("2/19/2010"),

                    Visitor = v1

                };

                var r2 = new Reservation

                {

                    Cost = 99.99M,

                    Hotel = hotel,

                    ReservationDate = DateTime.Parse("2/17/2010"),

                    Visitor = v2

                };

                var r3 = new Reservation

                {

                    Cost = 109.99M,

                    Hotel = hotel,

                    ReservationDate = DateTime.Parse("2/18/2010"),

                    Visitor = v1

                };

                var r4 = new Reservation

                {

                    Cost = 89.99M,

                    Hotel = hotel,

                    ReservationDate = DateTime.Parse("2/17/2010"),

                    Visitor = v2

                };

                context.Hotels.Add(hotel);

                context.SaveChanges();

            }

            using (var context = new EFRecipesEntities())

            {

                Console.WriteLine("Using eSQL...");

                var esql = @"Select value v from

                        EFRecipesModel.VisitorSummary(DATETIME'2010-02-16 00:00', 7) as v";

                var objectContext = (context as IObjectContextAdapter).ObjectContext;

                var visitors = objectContext.CreateQuery<DbDataRecord>(esql);

                foreach (var visitor in visitors)

                {

                    Console.WriteLine("{0}, Total Reservations: {1}, Revenue: {2:C}",

                    visitor["Name"], visitor["TotalReservations"],

                    visitor["BusinessEarned"]);

                }

            }

            using (var context = new EFRecipesEntities())

            {

                Console.WriteLine();

                Console.WriteLine("Using LINQ...");

                               //译注:在我的EF6.1.1.3下,这句查询会出现异常

                var visitors = from v in

                                   context.VisitorSummary(DateTime.Parse("2/16/2010"), 7)

                               select v;

                foreach (var visitor in visitors)

                {

                    Console.WriteLine("{0}, Total Reservations: {1}, Revenue: {2:C}",

                    visitor["Name"], visitor["TotalReservations"],

                    visitor["BusinessEarned"]);

                }

            }

        }

    }

    partial class EFRecipesEntities

    {

        [EdmFunction("EFRecipesModel", "VisitorSummary")]

        public IQueryable<DbDataRecord> VisitorSummary(DateTime StartDate, int Days)

        {

            var objectContext = (this as IObjectContextAdapter).ObjectContext;

            return objectContext.CreateQuery<DbDataRecord>(

            Expression.Call(Expression.Constant(this),

            (MethodInfo)MethodInfo.GetCurrentMethod(),

            new Expression[] { Expression.Constant(StartDate),

                               Expression.Constant(Days) }

            ).ToString());

        }

}

上面Listing 11-10 的代码输出如下:

Using eSQL...

Alex Stevens, Total Reservations: 2, Revenue: $189.98

Joan Hills, Total Reservations: 2, Revenue: $189.98

Using LINQ...

Alex Stevens, Total Reservations: 2, Revenue: $189.98

Joan Hills, Total Reservations: 2, Revenue: $189.98

它是如何工作的?

在Listing 11-9里, 在VisitorSummary() 函数定义里,我们按实体的导航属性visitor进行分组,我们用eSQL Count() 的 函数计算每位游客的预订房间数量,用Sum() 把每位游客付的租金合计.

在这个函数里,我们把结果组织成:: Name, TotalReservations, 和BusinessEarned. 此处我们使用了<CollectionType> 和<RowType> 标签来指明返回类型. 在运行时里,用包含DbDataRecords的集合

为了使该函数能在LINQ查询中使用,我们创建了运行时方法(返回IQueryable<DbDataRecord>.) .和前面的小节一样,我们用EdmFunction()特性修饰该方法.. 因为需要返回一个IQueryable<T>, 所以我们需要在这个方法体中包含函数调用,以便它能用在查询表达式树中.

此外,我们需要在ObjectContext中访问QueryProvider来返回一个IQueryable<T>,所以我们需要在EFRecipesEntities类中实现这个方法.

 

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