ASP.NET Web API支援OData,可以藉由URL加註$filter、$orderby、$skip、$top參數,對資料進行條件篩選、排序、分頁等加工(關於ASP.NET Web API OData特性,可參見KKBruce的文章)。而要啟用OData,有項重要前題是回傳資料型別必須為IQueryable<T>,而非一般LINQ常用的IEnumerable<T>。

對於List<T>、T[]等保存在記憶體的資料結構,傳回IQueryable<T>充其量只是要多一層.AsQueryable()的轉換程序,感覺不出要求IQueryable<T>的特殊用意。但在LINQ to SQL、Entity Framework等查詢資料庫的情境,回傳IQueryable<T>還是IEnumerable<T>,差別可就大了!!

IQueryable<T>繼承自IEumerable<T>,一樣具有"可列舉"特性,IEnumerable有的功能它都有。那麼,IQueryable<T>又有何特異功能? 若試著用淺白的文字說明,應在於IQueryable背後要有一個Query Provider(例如LINQ to SQL、Oracle EF Data Provider...)、且它能保存Query Expression,允許稍後繼續加工調整查詢邏輯,直到最後要列舉成具體資料時,再將最後版本的Query Expression交由Query Provider轉換成實際可在資料庫執行的SQL語法,執行後取得資料,產生列舉結果。

OData的例子,恰巧就是一個足以突顯IQueryable<T>優勢的典型案例。

在Web API Action中,我們完全不需考量篩選、排序、分頁等OData層次的需求,只需專心以IQueryable<T>傳回完整查詢結果,之後會由ASP.NET MVC機制接手,依據QueryString中的$filter、$orderby、$skip/$top等參數為IQueryable<T>的Query Expression再加上Where、OrderBy、Skip/Take等限制。當最後查詢結果要經JSON序列化傳回Client端時,Query Provider才開始將Query Expression轉成SQL語法,只取回限定篩選條件、特定分頁所在筆數、且依指定欄位排序過的最精簡資料,提升網站伺服器與DB伺服器間的傳輸效率。換個角度,若傳回查詢結果時使用的不是IQueryable<T>而是IEnumerable<T>,則在Action將結果交給ASP.NET MVC機制時,SQL查詢語法就已確定不再受到後續Where、OrderBy、Skip/Take影響,雖然最終結果相同,實際的運作,卻是未經篩選/分頁/排序的資料全部由DB載入記憶體,之後再以操作List<T>的方式於記憶中進行篩選、排序及分頁,白白浪費了DB I/O、網路頻寬、CPU及記憶體資源。

我們用一個實驗來驗證這一點:

排版顯示純文字
   1:  using System;
   2:  using System.Collections.Generic;
   3:  using System.Linq;
   4:  using System.Text;
   5:   
   6:  namespace IQueryableTest
   7:  {
   8:      class Program
   9:      {
  10:          static void Main(string[] args)
  11:          {
  12:              //測試1 IEnumerable
  13:              //取得<20的低單價商品
  14:              IEnumerable<Product> cheap1 = GetCheapProductsE(20);
  15:              //進行第二次篩選,從中挑出肉品類
  16:              IEnumerable<Product> meat1 = cheap1.Where(o => o.CategoryID == 6);
  17:              Console.WriteLine("Count={0}", meat1.Count());
  18:   
  19:              //測試2
  20:              IQueryable<Product> cheap2 = GetCheapProductsQ(20);
  21:              IQueryable<Product> meat2 = cheap2.Where(o => o.CategoryID == 6);
  22:              Console.WriteLine("Count={0}", meat2.Count());
  23:   
  24:              Console.Read();
  25:          }
  26:   
  27:          static NWEntities ctx = new NWEntities();
  28:          static IEnumerable<Product> GetCheapProductsE(decimal przLimit)
  29:          {
  30:              return ctx.Products.Where(o => o.UnitPrice < przLimit);
  31:          }
  32:          static IQueryable<Product> GetCheapProductsQ(decimal przLimit) 
  33:          {
  34:              return ctx.Products.Where(o => o.UnitPrice < przLimit);
  35:          }
  36:   
  37:      }
  38:  }

以SQL Server北風資料庫的產品資料當成資料來源,建立Entity Framework Model,接著寫兩個方法,以LINQ .Where(o => o.UnitPrice < 價格)查詢單價小於指定價格的商品,再分別以IEnumerable<Product>及IQueryable<Product>型別傳回結果。呼叫端接著會再加上.Where(o => o.CategoryID == 6)篩選肉品類商品,最後只有一筆吻合。

使用Visual Stuio的逐行偵錯,配合SQL Profiler觀察T-SQL執行記錄,我們發現在第17行,程式會執行以下查詢:

排版顯示純文字
exec sp_executesql N'SELECT 
[Extent1].[ProductID] AS [ProductID], 
[Extent1].[ProductName] AS [ProductName], 
[Extent1].[SupplierID] AS [SupplierID], 
[Extent1].[CategoryID] AS [CategoryID], 
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit], 
[Extent1].[UnitPrice] AS [UnitPrice], 
[Extent1].[UnitsInStock] AS [UnitsInStock], 
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder], 
[Extent1].[ReorderLevel] AS [ReorderLevel], 
[Extent1].[Discontinued] AS [Discontinued]
FROM [dbo].[Products] AS [Extent1]
WHERE [Extent1].[UnitPrice] < @p__linq__0',N'@p__linq__0 decimal(2,0)',@p__linq__0=20

接著在第22行,IQueryabl<Product>版本則是執行以下T-SQL指令:

排版顯示純文字
exec sp_executesql N'SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[Products] AS [Extent1]
    WHERE ([Extent1].[UnitPrice] < @p__linq__0) AND (6 = [Extent1].[CategoryID])
)  AS [GroupBy1]',N'@p__linq__0 decimal(2,0)',@p__linq__0=20

二者的差異頗大,而我們可以形成以下結論:

  1. T-SQL分別在17及22行的IEnumerable<T>.Count()及IQueryable<T>.Count()才執行,證明不管是IEnumerable<T>或IQueryable<T>都是"要列舉內容前夕才執行SQL"。
  2. IEnumerable<T>版本丟出的T-SQL會取回單價<20商品的所有欄位,相當於GetCheapProductsE()執行完畢時的查詢範圍,換句話說,在傳回IEnumerable<T>的當下T-SQL就已確定不再更動,即使直到.Count()才執行,T-SQL內容也不會受到之後加上Where()條件及Count()的影響。
  3. IQueryable<T>版本丟出的T-SQL,WHERE條件中包含了UnitPrice及CategoryID,而且只取COUNT(1)計筆數,未取回Product資料表的任何欄位內容,相對有效率許多。而此一T-SQL,可視為Where(o => o.UnitPrice < 20), Where(o => o.CategoryID == 6), Count()三者融合後的結果。

依此結論,在DB相關查詢應用情境中,可善加利用IQueryable<T>提升查詢效率。


Comments

# by 好乖

好乖

Post a comment