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

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

使用的工具/套件

簡介

假設我們正在寫一個 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 )。

參考資料

Post Comments

技術提供:Blogger.