觀察OData透過Entity Framework所產生的T-SQL

OData with Entity Framework

WebServices開發實戰 - 使用ASP.NET Web API 2

在M-V-C架構之下,Model可以採用傳統ADO.NET或ORM,而ORM我們當然是採用Entity Framework,在週四的MSDN講座有學員問到,採用OData之後的T-SQL是如何?例如分頁,是在資料庫分好,還是把資料取回再分。以下使用SQL Profiler側錄並整理。

OData - $orderby - 排序

ASC

   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]
      ORDER BY [Extent1].[ProductName] ASC
  

DESC

   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]
      ORDER BY [Extent1].[ProductName] DESC
  

OData - $top - 取前幾筆

   exec sp_executesql N'SELECT TOP (@p__linq__0) 
      [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]
      ORDER BY [Extent1].[ProductID] ASC',N'@p__linq__0 int',@p__linq__0=10
  

$top會對應到T-SQL的TOP使用,特別注意到最後的Order By,它會使用PK排序,這在某些情境下會有些問題,例如,使用GUID來當 PK,我想就會出事了。這部份請小心。

OData - $skip

   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 ( 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], row_number() OVER (ORDER BY [Extent1].[ProductID] ASC) AS [row_number]
         FROM [dbo].[Products] AS [Extent1]
      )  AS [Extent1]
      WHERE [Extent1].[row_number] > @p__linq__0
      ORDER BY [Extent1].[ProductID] ASC',N'@p__linq__0 int',@p__linq__0=10
  

子查詢先產出重要的 row_number,查詢再使用 row_number 來跳過一定筆數。Order By問題同上。

OData - paging

   exec sp_executesql N'SELECT TOP (@p__linq__1) 
      [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 ( 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], row_number() OVER (ORDER BY [Extent1].[ProductID] ASC) AS [row_number]
         FROM [dbo].[Products] AS [Extent1]
      )  AS [Extent1]
      WHERE [Extent1].[row_number] > @p__linq__0
      ORDER BY [Extent1].[ProductID] ASC',N'@p__linq__0 int,@p__linq__1 int',@p__linq__0=10,@p__linq__1=10
  

分頁是使用$skip$top,由子查詢產生 row_number,然後透過WHERETOP來取出我們分頁所需的資料。這部分也是學員最擔心的,結果而言,Entity Framework會幫我們與資料庫進行溝通,還好沒有笨到全部取回來再進行分頁。

OData - $filter

$filter主要對應的是WHERE條件。

   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) AND ([Extent1].[UnitPrice] < @p__linq__1)',N'@p__linq__0 decimal(2,0),@p__linq__1 decimal(2,0)',@p__linq__0=40,@p__linq__1=50
  

OData - $filter - startswith()

   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 (((CASE WHEN ([Extent1].[ProductName] LIKE @p__linq__0 ESCAPE N''~'') THEN cast(1 as bit) WHEN ( NOT ([Extent1].[ProductName] LIKE @p__linq__0 ESCAPE N''~'')) THEN cast(0 as bit) END) = @p__linq__1) AND ( NOT ((CASE WHEN ([Extent1].[ProductName] LIKE @p__linq__0 ESCAPE N''~'') THEN cast(1 as bit) WHEN ( NOT ([Extent1].[ProductName] LIKE @p__linq__0 ESCAPE N''~'')) THEN cast(0 as bit) END IS NULL) OR (@p__linq__1 IS NULL)))) OR ((CASE WHEN ([Extent1].[ProductName] LIKE @p__linq__0 ESCAPE N''~'') THEN cast(1 as bit) WHEN ( NOT ([Extent1].[ProductName] LIKE @p__linq__0 ESCAPE N''~'')) THEN cast(0 as bit) END IS NULL) AND (@p__linq__1 IS NULL))',N'@p__linq__0 nvarchar(4000),@p__linq__1 bit',@p__linq__0=N'A%',@p__linq__1=1
  

OData<-->Entity Framework<-->Database;從這裡看的出來,各位的資料表不能亂設計(尤其是PK,大家都有看到Order By會拿PK來排序),應進行資料表的正規化來避免問題。

5 則留言:

  1. 請問週四的講課有影片檔了嗎?分享十分精彩,很多在TechDays聽不懂了都迎刃而解。

    回覆刪除
    回覆
    1. 官方說明:https://www.facebook.com/photo.php?fbid=637791236259050&set=a.199337396771105.43932.199182533453258&type=1

      刪除
  2. 你好,目前官方仍未放上投影片,因為近日內想在組織內做個這部分的分享,是否方便先取得當日的投影片資料?

    回覆刪除
    回覆
    1. 投影片版權為官方所有,不方便提供。
      如有需要,我也有是供企業內訓服務 ^^。

      刪除

感謝您的留言,如果我的文章你喜歡或對你有幫助,按個「讚」或「分享」它,我會很高興的。