撰寫於 DB 端執行篩選、排序、與分頁的 LINQ 查詢

這篇筆記在整理如何撰寫具備篩選、排序、與分頁功能的 LINQ to Entities 查詢，而且這些查詢實際上都是在資料庫端執行（而不是把全部資料撈出來之後再進行篩選、排序、分頁）....

使用的工具／套件

Visual Studio 2013 或 2015
Entity Framework 6
PagedList.EntityFramework
LINQKit（PredicateBuilder）

簡介

假設我們正在寫一個 ASP.NET Web API 應用程式，它需要提供一個 API 來讓用戶端查詢客戶資料。用戶端可以透過 HTTP GET 方式來指定排序和篩選條件。

對此需求，我們通常會建立一個 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[]&gt 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

撰寫於 DB 端執行篩選、排序、與分頁的 LINQ 查詢

沒有留言:

臉書專頁

近 30 天熱門文章

文章分類

過往文章

追蹤者

總網頁瀏覽量

撰寫於 DB 端執行篩選、排序、與分頁的 LINQ 查詢

相關文章：

沒有留言:

臉書專頁

近 30 天熱門文章

文章分類

過往文章

追蹤者

總網頁瀏覽量