這篇筆記在整理如何撰寫具備篩選、排序、與分頁功能的 LINQ to Entities 查詢,而且這些查詢實際上都是在資料庫端執行(而不是把全部資料撈出來之後再進行篩選、排序、分頁)....
使用的工具/套件
GetCustomersRequest 類別的定義如下:
其中的 Name 和 Country 都是用來作為篩選客戶資料的欄位。
底下是一個用戶端請求的範例:
http://[host-name]/api/Customer?page=0&pageSize=10&name=michael
最後,客戶資料是以一個名為 Customer 的類別來封裝。如下所示:
基本前提介紹完了,接著要在 GetCustomers 方法中加入篩選、排序、分頁等處理。
Version 1
一些注意事項:
你可以看到,在建立篩選條件時,其實蠻囉嗦的:必須先判斷用戶端有沒有指定某篩選欄位,如果有,才加入 LINQ 查詢的 where 子句。這個部分稍後會改進。
Version 2
針對剛才提到的缺點,同事 L 大大使用了 Viktor Mitev 的作法來避免寫一堆 if 條件判斷。其作法主要是為各種型別的篩選欄位分別撰寫對應的 IQueryable 擴充方法,並將條件判斷敘述放在這些擴充方法裡面。如下所示:
如此一來,對於字串型別的欄位,在建立 LINQ 查詢子句時就可以這樣寫:
Version 3:使用 LINQKit
如果需要讓用戶端更細緻地控制篩選條件的組合,例如訂單金額要大於、等於、還是小於某個數字,生日要介於某個日期區間...等等,先前的擴充方法就不夠用了,得自己另外寫輔助函式。或者,也可以考慮使用現成的 LINQKit。
若採用 LINQKit,此範例程式可以改寫如下:
關於 LINQKit 的進一步介紹,可以參考這篇文章:使用 LINQKit PredicateBuilder 解決動態OR條件查詢窘境。
實際產生的 SQL 命令
在執行前面的範例程式時,每一次查詢客戶資料的操作會產生兩個的 SQL 命令:先取得該次查詢結果的資料總筆數,然後取得指定頁次的資料集。
舉例來說,如果用戶端送出的查詢請求如下:
http://[host-name]/api/Customer?page=0&pageSize=10&country=ROC&sortOrder=name%20desc
利用 SQL Server Profiler 來監看實際產生的 SQL 命令,大概長得像這樣:
小結
本文對於建立篩選條件的作法著墨較多,而比較少討論排序的部分。如果要簡化排序的部分,同樣也可以自行撰寫擴充方法。
至於分頁,我覺得 PagedList.EntityFramework 有支援 async 呼叫,使用上沒有發現什麼問題,就直接用了(其實自己寫會是蠻好的練習,只是我太懶了 Orz )。
使用的工具/套件
- Visual Studio 2013 或 2015
- Entity Framework 6
- PagedList.EntityFramework
- LINQKit(PredicateBuilder)
簡介
假設我們正在寫一個 ASP.NET Web API 應用程式,它需要提供一個 API 來讓用戶端查詢客戶資料。用戶端可以透過 HTTP GET 方式來指定排序和篩選條件。
對此需求,我們通常會建立一個 CustomerController 類別,並且在其中加入一個 GetCustomers 方法來實作相關的查詢功能。此方法大概長這樣:
對此需求,我們通常會建立一個 CustomerController 類別,並且在其中加入一個 GetCustomers 方法來實作相關的查詢功能。此方法大概長這樣:
[HttpGet, Route("")] public async Task<CustomerViewModel[]> GetCustomers([FromUri] GetCustomersRequest request) { // TODO: 建立查詢(包含篩選、排序、分頁等子句) // TODO: 執行查詢,並將查詢結果轉換成 View Model,然後返回用戶端。 }
此方法的輸入參數與回傳型別分別是:
- GetCustomersRequest - 這是一個自訂類別,封裝了用戶端傳入的查詢參數。
- CustomerViewModel[] - 一個代表查詢結果的陣列。CustomerViewModel 與稍後提到的 Customer 類別雷同,且無關宏旨,故在此省略。
GetCustomersRequest 類別的定義如下:
public class GetCustomersRequest { // 分頁參數 public int Page { get; set; } public int PageSize { get; set; } public string SortOrder { get; set; } // Example: "Name desc" // 篩選條件 public string Name { get; set; } public string Country { get; set; } }
其中的 Name 和 Country 都是用來作為篩選客戶資料的欄位。
底下是一個用戶端請求的範例:
http://[host-name]/api/Customer?page=0&pageSize=10&name=michael
最後,客戶資料是以一個名為 Customer 的類別來封裝。如下所示:
public class Customer { public int Id { get; set; } public string Name { get; set; } public string Country { get; set; } public string Address { get; set; } }
基本前提介紹完了,接著要在 GetCustomers 方法中加入篩選、排序、分頁等處理。
Version 1
[HttpGet, Route("")] public async Task<CustomerViewModel[]> GetCustomers([FromUri] GetCustomersRequest request) { // 明白起見,把建立 DbContext 的程式碼放在這裡。實際上不會這樣做。 SalesContext salesContext = new SalesContext(); var customersQuery = salesContext.Customers.AsQueryable(); // 建立篩選條件 if (!string.IsNullOrWhiteSpace(request.Name)) { customersQuery = customersQuery.Where(c => c.Name.Contains(request.Name)); } if (!string.IsNullOrWhiteSpace(request.Country)) { customersQuery = customersQuery.Where(c => c.Country.Equals(request.Country)); } // 排序 switch (request.SortOrder.ToLower()) { case "country": case "country asc": customersQuery = customersQuery.OrderBy(c => c.Country); break; case "country desc": customersQuery = customersQuery.OrderByDescending(c => c.Country); break; case "name desc": customersQuery = customersQuery.OrderByDescending(c => c.Name); break; default: customersQuery = customersQuery.OrderBy(c => c.Name); break; } // 分頁(使用 PagedList.EntityFramework) var pagedResult = await customersQuery.ToPagedListAsync(request.Page, request.PageSize); // 轉換成 View Model var customerModels = pagedResult.Select(ConvertToCustomerViewModel); return customerModels.ToArray(); }
一些注意事項:
- 此 API 方法為 async 方法。
- 以此方式建立的多個篩選條件,彼此是以 "AND" 邏輯來組合。比如說,用戶端若同時指定了 name=mike&country=TW 參數,即表示要查詢「姓名 包含 "mike" 而且 國家 等於 "TW"」的客戶資料。這裡沒有更進一步考慮讓用戶端指定比對運算子要用包含、等於,而是採用內定的比對方式。
- 這裡的分頁處理是利用現有套件 PagedList.EntityFramework。它是基於 Troy Goode 的 PagedList 套件,主要是增加 async 支援。
你可以看到,在建立篩選條件時,其實蠻囉嗦的:必須先判斷用戶端有沒有指定某篩選欄位,如果有,才加入 LINQ 查詢的 where 子句。這個部分稍後會改進。
Version 2
針對剛才提到的缺點,同事 L 大大使用了 Viktor Mitev 的作法來避免寫一堆 if 條件判斷。其作法主要是為各種型別的篩選欄位分別撰寫對應的 IQueryable 擴充方法,並將條件判斷敘述放在這些擴充方法裡面。如下所示:
public static class QueryableWhereExtensions { // Where extension for filters of any nullable type public static IQueryable<TSource> Where<TSource, TFilter>(this IQueryable<TSource> source, TFilter? filter, Expression<Func<TSource, bool>> predicate) where TFilter : struct { if (filter.HasValue) { source = source.Where(predicate); } return source; } // Where extension for string filters public static IQueryable<TSource> Where<TSource>(this IQueryable<TSource> source, string filter, Expression<Func<TSource, bool>> predicate) { if (!string.IsNullOrWhiteSpace(filter)) { source = source.Where(predicate); } return source; } // Where extension for collection filters public static IQueryable<TSource> Where<TSource, TFilter>(this IQueryable<TSource> source, IEnumerable<TFilter> filter, Expression<Func<TSource, bool>> predicate) { if (filter != null && filter.Any()) { source = source.Where(predicate); } return source; } }
如此一來,對於字串型別的欄位,在建立 LINQ 查詢子句時就可以這樣寫:
public async Task<CustomerViewModel[]> GetCustomers([FromUri] GetCustomersRequest request) { // 明白起見,把建立 DbContext 的程式碼放在這裡。實際上不會這樣做。 SalesContext salesContext = new SalesContext(); // 建立篩選條件(使用 QueryableWhereExtension 裡面的擴充方法) var customersQuery = salesContext.Customers .Where(request.Name, c => c.Name.Contains(request.Name)) .Where(request.Country, c => c.Country.Equals(request.Country)); // 以下省略。 }也就是說,不管用戶端有沒有指定篩選條件,在建立 where 子句時一律把全部的篩選欄位都用自訂的 Where 擴充方法串起來就行了。
Version 3:使用 LINQKit
如果需要讓用戶端更細緻地控制篩選條件的組合,例如訂單金額要大於、等於、還是小於某個數字,生日要介於某個日期區間...等等,先前的擴充方法就不夠用了,得自己另外寫輔助函式。或者,也可以考慮使用現成的 LINQKit。
若採用 LINQKit,此範例程式可以改寫如下:
public async Task<CustomerViewModel[]> GetCustomers([FromUri] GetCustomersRequest request) { // 明白起見,把建立 DbContext 的程式碼放在這裡。實際上不會這樣做。 SalesContext salesContext = new SalesContext(); // 建立篩選條件(使用 LINQKit) var filterExpr = PredicateBuilder.True<Customer>(); if (!string.IsNullOrWhiteSpace(request.Name)) { filterExpr = filterExpr.And(c => c.Name.Contains(request.Name)); } if (!string.IsNullOrWhiteSpace(request.Country)) { filterExpr = filterExpr.And(c => c.Country.Equals(request.Country)); } var customersQuery = salesContext.Customers .AsExpandable() // 必須先呼叫此擴充方法(來自 LinqKit)。 .Where(filterExpr); // 以下省略。 }
關於 LINQKit 的進一步介紹,可以參考這篇文章:使用 LINQKit PredicateBuilder 解決動態OR條件查詢窘境。
實際產生的 SQL 命令
在執行前面的範例程式時,每一次查詢客戶資料的操作會產生兩個的 SQL 命令:先取得該次查詢結果的資料總筆數,然後取得指定頁次的資料集。
舉例來說,如果用戶端送出的查詢請求如下:
http://[host-name]/api/Customer?page=0&pageSize=10&country=ROC&sortOrder=name%20desc
利用 SQL Server Profiler 來監看實際產生的 SQL 命令,大概長得像這樣:
-- 取得查詢結果的資料總數 exec sp_executesql N'SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM [dbo].[Customers] AS [Extent1] WHERE ([Extent1].[Country] = @p__linq__0) OR (([Extent1].[Country] IS NULL) AND (@p__linq__0 IS NULL)) ) AS [GroupBy1]',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'ROC' -- 取得指定頁次的資料集合 exec sp_executesql N'SELECT [Project1].[Id] AS [Id], [Project1].[Name] AS [Name], [Project1].[Country] AS [Country], [Project1].[Address] AS [Address] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Country] AS [Country], [Extent1].[Address] AS [Address] FROM [dbo].[Customers] AS [Extent1] WHERE ([Extent1].[Country] = @p__linq__0) OR (([Extent1].[Country] IS NULL) AND (@p__linq__0 IS NULL)) ) AS [Project1] ORDER BY [Project1].[Name] DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY ',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'ROC'
小結
本文對於建立篩選條件的作法著墨較多,而比較少討論排序的部分。如果要簡化排序的部分,同樣也可以自行撰寫擴充方法。
至於分頁,我覺得 PagedList.EntityFramework 有支援 async 呼叫,使用上沒有發現什麼問題,就直接用了(其實自己寫會是蠻好的練習,只是我太懶了 Orz )。
參考資料
- Improving LINQ to Entities Queries by Viktor Mitev
- C# 5.0/4.0 in a Nutshell - LINQKit
沒有留言: