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")

                });

                    Name = "Roger Smith",

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

                });

                    Name = "James Jordan",

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

                    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))

                foreach (var reservation in query1)

輸出結果如下:

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...

它是如何工作的

在内部寫條件,如在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''~''))

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

WHERE (@p__linq__0 IS NULL OR

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

((@p__linq__2 = @p__linq__3) OR

kid1412聲明：轉載請把此段聲明完整地置于文章頁面明顯處，并保留個人在部落格園的連結：http://www.cnblogs.com/kid1412/(可點選跳轉）。