關於IQueryable<T>特性的小實驗
1 | 45,190 |
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
二者的差異頗大,而我們可以形成以下結論:
- T-SQL分別在17及22行的IEnumerable<T>.Count()及IQueryable<T>.Count()才執行,證明不管是IEnumerable<T>或IQueryable<T>都是"要列舉內容前夕才執行SQL"。
- IEnumerable<T>版本丟出的T-SQL會取回單價<20商品的所有欄位,相當於GetCheapProductsE()執行完畢時的查詢範圍,換句話說,在傳回IEnumerable<T>的當下T-SQL就已確定不再更動,即使直到.Count()才執行,T-SQL內容也不會受到之後加上Where()條件及Count()的影響。
- 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 好乖
好乖