Entity Framework 6 Recipes 2nd Edition（11-9）译 -> 在LINQ中使用规范函数

11-9. 在LINQ中使用规范函数

问题

想在一个LINQ查询中使用规范函数

解决方案

假设我们已经有一个影片租赁(MovieRental )实体,它保存某个影片什么时候租出及还回来,以及滞纳金等,如Figure 11-9. 所示:

 

Figure 11-9. The MovieRental entity that has the dates for a rental period along with any late fees

我们想取得所有租期超过10天的影片

如何创建和使用查询,如Listing 11-16所示:

Listing 11-16. Retrieving the Late Movies using the DateDiff() Function

    class Program

    {

        static void Main(string[] args)

        {

            RunExample();

        }

 

        private static void RunExample()

        {

            using (var context=new EFRecipesEntities())

            {

                var mr1 = new MovieRental

                {

                    Title = "A Day in the Life",

                    RentalDate = DateTime.Parse("2/19/2013"),

                    ReturnedDate = DateTime.Parse("3/4/2013"),

                    LateFees = 3M

                };

                var mr2 = new MovieRental

                {

                    Title = "The Shortest Yard",

                    RentalDate = DateTime.Parse("3/15/2013"),

                    ReturnedDate = DateTime.Parse("3/20/2013"),

                    LateFees = 0M

                };

                var mr3 = new MovieRental

                {

                    Title = "Jim's Story",

                    RentalDate = DateTime.Parse("3/2/2013"),

                    ReturnedDate = DateTime.Parse("3/19/2013"),

                    LateFees = 3M

                };

                context.MovieRentals.Add(mr1);

                context.MovieRentals.Add(mr2);

                context.MovieRentals.Add(mr3);

                context.SaveChanges();

 

            }

            using (var context = new EFRecipesEntities())

            {

                Console.WriteLine("Movie rentals late returns");

                Console.WriteLine("==========================");

                var late = from r in context.MovieRentals

                           where DbFunctions.DiffDays(r.RentalDate, r.ReturnedDate) > 10

                           select r;

                foreach (var rental in late)

                {

                    Console.WriteLine("{0} was {1} days late, fee: {2}", rental.Title,

                    (rental.ReturnedDate - rental.RentalDate).Days - 10,

                    rental.LateFees.ToString("C"));

                }

            }

 

        }

}

上述Listing 11-16代码输出结果如下:

Movie rentals late returns

==========================

A Day in the Life was 3 days late, fee: $3.00

Jim's Story was 7 days late, fee: $3.00

它是如何工作的?

定义在EF里的规范函数, 它可以不被数据源所知道,而且能被数据提供器支持.规范函数返回的数据类型是根据实体数据模型的类型定义的.

在本小节里,我们使用DiffDays() 函数来计算两个日期的所差的天数,由于DiffDays()是一个规范函数,所以它能被数据所有提供者执行.

最佳实践

你可能会问自己“什么时候应该用实体函数?”. EF提供者把一些表达式翻译成规范函数, 但是这种翻译能力是有限的.不是每个运行时方法都能翻译成相应的规范函数,这里是一些最佳实践,如果可以翻译,那么就使用它,这样能使代码易读.如果不能翻译,那么就明确地使用EntityFunction类来调用规范函数,就像下列的代码片段:

var laterentals = from r in context.MovieRentals

where (r.ReturnedDate - r.RentalDate).Days > 10

select r;

上面这句,不能翻译成规范函数,所以你应该使用下列这句:

var laterentals = from r in context.MovieRentals

where EntityFunctions.DiffDays(r.RentalDate,

r.ReturnedDate) > 10

select r;

 

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