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

OData with Entity Framework

在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，然後透過WHERE與TOP來取出我們分頁所需的資料。這部分也是學員最擔心的，結果而言，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來排序），應進行資料表的正規化來避免問題。