Entity Framework 6 Recipes 2nd Edition（13-4）译 -> 有效地创建一个搜索查询

问题

你想用LINQ写一个搜索查询,能被转换成更有效率的SQL.另外,你想用EF的CodeFirst方式实现.

解决方案

假设你有如下Figure 13-6所示的模型

 

Figure 13-6. A simple model with a Reservation entity

 

         首先,这个例子用EF的CodeFirst方式实现,在Listing 13-10,我们创建实体类Reservation

Listing 13-10. The Reservation Entity Object

    public class Reservation

    {

        public int ReservationId { get; set; }

        public System.DateTime ResDate { get; set; }

        public string Name { get; set; }

}

接下来,在Listing 13-11,我们创建用CodeFirst方式访问EF功能的DbContext对象.

Listing 13-11. DbContext Object

    public class Recipe5Context : DbContext

    {

        public Recipe5Context()

            : base("Recipe4ConnectionString")

        {

            // disable Entity Framework Model Compatibility

            Database.SetInitializer<Recipe5Context>(null);

        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)

        {

            modelBuilder.Entity<Reservation>().ToTable("Chapter13.Reservation");

        }

        public DbSet<Reservation> Reservations { get; set; }

}

         接下来在项目中添加App.config,并把Listing 13-12的代码添加到ConnectionStrings节下.

Listing 13-12. Connection String

<connectionStrings>

<add name="Recipe4ConnectionString"

connectionString="Data Source=.;

Initial Catalog=EFRecipes;

Integrated Security=True;

MultipleActiveResultSets=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

         你想用LINMQ写一个搜索查询,通过指定特定的人,或特定日期,或两者一起来获取Reservations(预定),你可能会用let关键字,像我们在Listing 13-13中的第一个查询那样,使得LINQ查询表达式更清晰可读,然而,let关键字会被转换成更复杂效率更低的SQL语句.为替换它,我们明确地在where子句里创建两个条件,像我们在Listing 13-13的第二个查询那样.

Listing 13-13. Using Both the let Keyword and Explicit Conditions in the Query

     using (var context = new Recipe4Context())

            {

                context.Reservations.Add(new Reservation { Name = "James Jordan", ResDate = DateTime.Parse("4/18/10") });

                context.Reservations.Add(new Reservation

                {

                    Name = "Katie Marlowe",

                    ResDate = DateTime.Parse("3/22/10")

                });

                context.Reservations.Add(new Reservation

                {

                    Name = "Roger Smith",

                    ResDate = DateTime.Parse("4/18/10")

                });

                context.Reservations.Add(new Reservation

                {

                    Name = "James Jordan",

                    ResDate = DateTime.Parse("5/12/10")

                });

                context.Reservations.Add(new Reservation

                {

                    Name = "James Jordan",

                    ResDate = DateTime.Parse("6/22/10")

                });

                context.SaveChanges();

            }

            using (var context = new Recipe4Context())

            {

                DateTime? searchDate = null;

                var searchName = "James Jordan";

                Console.WriteLine("More complex SQL...");

                var query2 = from reservation in context.Reservations

                             let dateMatches = searchDate == null || reservation.ResDate == searchDate

                             let nameMatches = searchName == string.Empty || reservation.Name.Contains(searchName)

                             where dateMatches && nameMatches

                             select reservation;

                foreach (var reservation in query2)

                {

                    Console.WriteLine("Found reservation for {0} on {1}", reservation.Name,

                    reservation.ResDate.ToShortDateString());

                }

                Console.WriteLine("Cleaner SQL...");

                var query1 = from reservation in context.Reservations

                             where (searchDate == null || reservation.ResDate == searchDate)

                             &&

                             (searchName == string.Empty || reservation.Name.Contains(searchName))

                             select reservation;

                foreach (var reservation in query1)

                {

                    Console.WriteLine("Found reservation for {0} on {1}", reservation.Name,

                    reservation.ResDate.ToShortDateString());

                }

            }

输出结果如下:

More complex SQL...

Found reservation for James Jordan on 4/18/2010

Found reservation for James Jordan on 5/12/2010

Found reservation for James Jordan on 6/22/2010

Cleaner SQL...

Found reservation for James Jordan on 4/18/2010

Found reservation for James Jordan on 5/12/2010

Found reservation for James Jordan on 6/22/2010

 

它是如何工作的

在内部写条件,如在Listing 13-13的第二个查询那样,可读性和可维护性不是很好.更具代表性地,可能用let关键字使代码更清晰可读,即使在一些情况下,会导致更复杂和低效的SQL代码.

         让我们看一下这两种方式生成的SQL语句.Listing 13-14显示的是第一个查询的SQL语句.注意,在where子句里包含了case语句和一些cast语句等,如果我们的查询有更多的条件,产生的SQL语句会更复杂.

Listing 13-14. SQL Generated When let Is Used in the LINQ Query

SELECT

[Extent1].[ReservationId] AS [ReservationId],

[Extent1].[ResDate] AS [ResDate],

[Extent1].[Name] AS [Name]

FROM [Chapter13].[Reservation] AS [Extent1]

WHERE (

(CASE WHEN (@p__linq__0 IS NULL OR

@p__linq__1 = CAST( [Extent1].[ResDate] AS datetime2))

THEN cast(1 as bit)

WHEN ( NOT (@p__linq__0 IS NULL OR

@p__linq__1 = CAST( [Extent1].[ResDate] AS datetime2)))

THEN cast(0 as bit) END) = 1) AND

((CASE WHEN ((@p__linq__2 = @p__linq__3) OR

([Extent1].[Name] LIKE @p__linq__4 ESCAPE N''~''))

THEN cast(1 as bit)

WHEN ( NOT ((@p__linq__2 = @p__linq__3) OR

([Extent1].[Name] LIKE @p__linq__4 ESCAPE N''~'')))

THEN cast(0 as bit) END) = 1)

 

         Listing 13-15显示了第二个查询产生的SQL语句,我们在where内使用条件,这个查询更简单并且在运行时更高效.

Listing 13-15. Cleaner, More Efficient SQL Generated When Not Using let in a LINQ Query

SELECT

[Extent1].[ReservationId] AS [ReservationId],

[Extent1].[ResDate] AS [ResDate],

[Extent1].[Name] AS [Name]

FROM [Chapter13].[Reservation] AS [Extent1]

WHERE (@p__linq__0 IS NULL OR

@p__linq__1 = CAST( [Extent1].[ResDate] AS datetime2)) AND

((@p__linq__2 = @p__linq__3) OR

([Extent1].[Name] LIKE @p__linq__4 ESCAPE N''~''))