mrkt 的程式學習筆記: Oracle ODAC for Entity Framework 關連資料查詢的幾種不同方式

前篇文章「ODAC 11.2 Release 4 (11.2.0.3.0) - Includes support for Entity Framework and LINQ」，在文末的「其他的查詢運算操作」有介紹使用「join + group」的方式來查詢關連資料，而另外也測試了幾種不同的查詢方式，在本篇文章就來看看這幾種不同的查詢方式。

Join + Group

這就是上一篇文章所使用的查詢方式，來回顧一下：

轉換的SQL Script：

SELECT "Project1"."C2" AS "C1", "Project1"."OrderID" AS "OrderID", "Project1"."C1" AS "C2"FROM ( SELECT     "GroupBy1"."A1" AS "C1",     "GroupBy1"."K1" AS "OrderID",     1 AS "C2"    FROM ( SELECT         "Extent1"."K1" AS "K1",         SUM("Extent1"."A1") AS "A1"        FROM ( SELECT             "Extent1"."OrderID" AS "K1",             ( CAST( "Extent1"."Quantity" AS number(19,0))) * "Extent1"."UnitPrice" AS "A1"            FROM "NORTHWIND_USER"."OrderDetails" "Extent1"        )  "Extent1"        GROUP BY "K1"    )  "GroupBy1")  "Project1"ORDER BY "Project1"."OrderID" ASC

一開始的 query1 部分，先取得每筆訂單的OrderID以及Order所關連OrderDetail的金額（數量x單價）

單獨的執行者這個query1的部份：

而這一部分的操作可以用另外的方式來取得同樣的資料結果。

轉換的SQL Script：

SELECT 1 AS "C1", "Extent1"."OrderID" AS "OrderID", "Extent1"."UnitPrice" * ( CAST( "Extent1"."Quantity" AS number(19,0))) AS "C2"FROM "NORTHWIND_USER"."OrderDetails" "Extent1"

SelectMany

可以看到改使用SelectMany()的查詢方式，也是可以得到同樣的結果，而且時間上還比使用Join的方式要少一些。

轉換的SQL Script：

SELECT 1 AS "C1", "Extent1"."OrderID" AS "OrderID", "Extent1"."UnitPrice" * ( CAST( "Extent1"."Quantity" AS number(19,0))) AS "C2"FROM "NORTHWIND_USER"."OrderDetails" "Extent1"

可以看到這個使用SelectMany()查詢，轉換後的SQL Script是與使用Join方式查詢的是一樣的。

接著看看完整的查詢：

轉換的SQL Script：

SELECT "Project1"."C2" AS "C1", "Project1"."OrderID" AS "OrderID", "Project1"."C1" AS "C2"FROM ( SELECT     "GroupBy1"."A1" AS "C1",     "GroupBy1"."K1" AS "OrderID",     1 AS "C2"    FROM ( SELECT         "Extent1"."K1" AS "K1",         SUM("Extent1"."A1") AS "A1"        FROM ( SELECT             "Extent1"."OrderID" AS "K1",             "Extent1"."UnitPrice" * ( CAST( "Extent1"."Quantity" AS number(19,0))) AS "A1"            FROM "NORTHWIND_USER"."OrderDetails" "Extent1"        )  "Extent1"        GROUP BY "K1"    )  "GroupBy1")  "Project1"ORDER BY "Project1"."OrderID" ASC

其實使用SelectMany()的完整查詢所轉換的SQL Script與使用Join-Group的查詢所轉換的SQL Script是相同的。

因為是要取得Order資料以及關連的OrderDetail資料，如果只是單純的只有取出OrderID以及訂單總金額的話，那就還要再另外取得Order資料的其他欄位資料（因為上面的執行結果只有取出OrderID），而接下來的操作就是一併把Order的全部資料取出（一樣是在SelectMany的操作下完成）。

方式一：

var query1 = from o in Orders             from d in Orders.OrderBy(x=>x.OrderID).SelectMany(x=>x.OrderDetails)             where o.OrderID == d.OrderID             select new              {                 OrderID = o.OrderID,                Amount = d.UnitPrice * d.Quantity             };             var query2 = from a in query1             group a by a.OrderID into g             orderby g.Key             select new             {                 OrderID = g.Key,                Amount = g.Sum(x=>x.Amount)             };             var query3 = from b in query2             from c in Orders             where b.OrderID == c.OrderID             orderby b.OrderID             select new             {                 Order = c,                Amount = b.Amount             };

多了一個query3的部份，這裡的操作是把Order的資料給取出，

執行結果：

轉換的SQL Script：

SELECT "Project1"."C2" AS "C1", "Project1"."OrderID1" AS "OrderID", "Project1"."CustomerID" AS "CustomerID", "Project1"."EmployeeID" AS "EmployeeID", "Project1"."OrderDate" AS "OrderDate", "Project1"."RequiredDate" AS "RequiredDate", "Project1"."ShippedDate" AS "ShippedDate", "Project1"."ShipVia" AS "ShipVia", "Project1"."Freight" AS "Freight", "Project1"."ShipName" AS "ShipName", "Project1"."ShipAddress" AS "ShipAddress", "Project1"."ShipCity" AS "ShipCity", "Project1"."ShipRegion" AS "ShipRegion", "Project1"."ShipPostalCode" AS "ShipPostalCode", "Project1"."ShipCountry" AS "ShipCountry", "Project1"."C1" AS "C2"FROM ( SELECT     "GroupBy1"."A1" AS "C1",     "GroupBy1"."K1" AS "OrderID",     "Extent2"."OrderID" AS "OrderID1",     "Extent2"."CustomerID" AS "CustomerID",     "Extent2"."EmployeeID" AS "EmployeeID",     "Extent2"."OrderDate" AS "OrderDate",     "Extent2"."RequiredDate" AS "RequiredDate",     "Extent2"."ShippedDate" AS "ShippedDate",     "Extent2"."ShipVia" AS "ShipVia",     "Extent2"."Freight" AS "Freight",     "Extent2"."ShipName" AS "ShipName",     "Extent2"."ShipAddress" AS "ShipAddress",     "Extent2"."ShipCity" AS "ShipCity",     "Extent2"."ShipRegion" AS "ShipRegion",     "Extent2"."ShipPostalCode" AS "ShipPostalCode",     "Extent2"."ShipCountry" AS "ShipCountry",     1 AS "C2"    FROM   (SELECT         "Extent1"."K1" AS "K1",         SUM("Extent1"."A1") AS "A1"        FROM ( SELECT             "Extent1"."OrderID" AS "K1",             "Extent1"."UnitPrice" * ( CAST( "Extent1"."Quantity" AS number(19,0))) AS "A1"            FROM "NORTHWIND_USER"."OrderDetails" "Extent1"        )  "Extent1"        GROUP BY "K1" ) "GroupBy1"    INNER JOIN "NORTHWIND_USER"."Orders" "Extent2" ON "GroupBy1"."K1" = "Extent2"."OrderID")  "Project1"ORDER BY "Project1"."OrderID" ASC

方式二：

var query1 = from o in Orders             from d in Orders.OrderBy(x=>x.OrderID).SelectMany(x=>x.OrderDetails)             where o.OrderID == d.OrderID             select new              {                 OrderID = o.OrderID,                Amount = d.UnitPrice * d.Quantity             };             var query2 = from a in query1             group a by a.OrderID into g             orderby g.Key             select new             {                 Order = Orders.Where(x=>x.OrderID == g.Key),                Amount = g.Sum(x=>x.Amount)             };

而這一個方式則是不用多一個query3的查詢，而是在query2的Select()中直接把Order資料就取出，

執行結果：

轉換的SQL Script：

SELECT "Project1"."OrderID" AS "OrderID", "Project1"."C2" AS "C1", "Project1"."C1" AS "C2", "Project1"."C3" AS "C3", "Project1"."OrderID1" AS "OrderID1", "Project1"."CustomerID" AS "CustomerID", "Project1"."EmployeeID" AS "EmployeeID", "Project1"."OrderDate" AS "OrderDate", "Project1"."RequiredDate" AS "RequiredDate", "Project1"."ShippedDate" AS "ShippedDate", "Project1"."ShipVia" AS "ShipVia", "Project1"."Freight" AS "Freight", "Project1"."ShipName" AS "ShipName", "Project1"."ShipAddress" AS "ShipAddress", "Project1"."ShipCity" AS "ShipCity", "Project1"."ShipRegion" AS "ShipRegion", "Project1"."ShipPostalCode" AS "ShipPostalCode", "Project1"."ShipCountry" AS "ShipCountry"FROM ( SELECT     "GroupBy1"."A1" AS "C1",     "GroupBy1"."K1" AS "OrderID",     1 AS "C2",     "Extent2"."OrderID" AS "OrderID1",     "Extent2"."CustomerID" AS "CustomerID",     "Extent2"."EmployeeID" AS "EmployeeID",     "Extent2"."OrderDate" AS "OrderDate",     "Extent2"."RequiredDate" AS "RequiredDate",     "Extent2"."ShippedDate" AS "ShippedDate",     "Extent2"."ShipVia" AS "ShipVia",     "Extent2"."Freight" AS "Freight",     "Extent2"."ShipName" AS "ShipName",     "Extent2"."ShipAddress" AS "ShipAddress",     "Extent2"."ShipCity" AS "ShipCity",     "Extent2"."ShipRegion" AS "ShipRegion",     "Extent2"."ShipPostalCode" AS "ShipPostalCode",     "Extent2"."ShipCountry" AS "ShipCountry",     CASE WHEN ("Extent2"."OrderID" IS NULL) THEN NULL ELSE 1 END AS "C3"    FROM   (SELECT         "Extent1"."K1" AS "K1",         SUM("Extent1"."A1") AS "A1"        FROM ( SELECT             "Extent1"."OrderID" AS "K1",             "Extent1"."UnitPrice" * ( CAST( "Extent1"."Quantity" AS number(19,0))) AS "A1"            FROM "NORTHWIND_USER"."OrderDetails" "Extent1"        )  "Extent1"        GROUP BY "K1" ) "GroupBy1"    LEFT OUTER JOIN "NORTHWIND_USER"."Orders" "Extent2" ON "Extent2"."OrderID" = "GroupBy1"."K1")  "Project1"ORDER BY "Project1"."OrderID" ASC, "Project1"."C3" ASC

其實方式一與方式二的執行結果是一樣的，方式二所花費的時間稍慢一些，而方式一是直接取出Order資料，而方式二則是在query2的Selet()中以查詢式的方式取出Order資料，要注意到方式二的結果中，Order資料是回傳IOrderedQueryable<Orders>，是回傳資料集合。

為何不用FirstOrDefault()或是SingleOrDefault()來回傳單筆的Order資料呢？

方式二，使用FirstOrDefault()

出現錯誤

方式二，使用SingleOrDefault()

發生錯誤：方法 'Single' 和 'SingleOrDefault' 只能當做最終查詢作業使用。在這個執行個體中請考慮改用方法 'FirstOrDefault'。

（改用FirstOrDefault()還是一樣出現錯誤）

比較推薦的方式

如果說要取得Order的所有欄位資料，又想要取得OrderDetails的金額總計，除了上面的方式之外，其實我會比較建議使用比較笨而且比較直接的作法，不過這只是建議使用在 ODAC for Entity Framework的環境中，而在使用MS SQL Server + ADO.NET Entity Framework的環境下就不需要這麼做了。

var query1 = Orders    .OrderBy(x => x.OrderID)    .Select(x => new { order = x, details = x.OrderDetails })    .ToList(); var query2 = from r in query1             select new              {                 Amount = r.details.Sum(x => x.Quantity * x.UnitPrice),                 OrderID = r.order.OrderID,                 CustomerID = r.order.CustomerID,                 EmployeeID = r.order.EmployeeID,                                  OrderDate = r.order.OrderDate,                 RequiredDate = r.order.RequiredDate,                 ShippedDate = r.order.ShippedDate,                 Freight = r.order.Freight,                 ShipName = r.order.ShipName,                 ShipAddress = r.order.ShipAddress,                 ShipCity = r.order.ShipCity,                 ShipRegion = r.order.ShipRegion,                 ShipPostalCode = r.order.ShipPostalCode,                 ShipCountry = r.order.ShipCountry             };

執行結果：

取出830筆資料約花費 3 秒的時間。

query1 轉換的SQL Script：

SELECT "Project1"."OrderID" AS "OrderID", "Project1"."C1" AS "C1", "Project1"."CustomerID" AS "CustomerID", "Project1"."EmployeeID" AS "EmployeeID", "Project1"."OrderDate" AS "OrderDate", "Project1"."RequiredDate" AS "RequiredDate", "Project1"."ShippedDate" AS "ShippedDate", "Project1"."ShipVia" AS "ShipVia", "Project1"."Freight" AS "Freight", "Project1"."ShipName" AS "ShipName", "Project1"."ShipAddress" AS "ShipAddress", "Project1"."ShipCity" AS "ShipCity", "Project1"."ShipRegion" AS "ShipRegion", "Project1"."ShipPostalCode" AS "ShipPostalCode", "Project1"."ShipCountry" AS "ShipCountry", "Project1"."C2" AS "C2", "Project1"."OrderID1" AS "OrderID1", "Project1"."ProductID" AS "ProductID", "Project1"."UnitPrice" AS "UnitPrice", "Project1"."Quantity" AS "Quantity", "Project1"."Discount" AS "Discount"FROM ( SELECT     "Extent1"."OrderID" AS "OrderID",     "Extent1"."CustomerID" AS "CustomerID",     "Extent1"."EmployeeID" AS "EmployeeID",     "Extent1"."OrderDate" AS "OrderDate",     "Extent1"."RequiredDate" AS "RequiredDate",     "Extent1"."ShippedDate" AS "ShippedDate",     "Extent1"."ShipVia" AS "ShipVia",     "Extent1"."Freight" AS "Freight",     "Extent1"."ShipName" AS "ShipName",     "Extent1"."ShipAddress" AS "ShipAddress",     "Extent1"."ShipCity" AS "ShipCity",     "Extent1"."ShipRegion" AS "ShipRegion",     "Extent1"."ShipPostalCode" AS "ShipPostalCode",     "Extent1"."ShipCountry" AS "ShipCountry",     1 AS "C1",     "Extent2"."OrderID" AS "OrderID1",     "Extent2"."ProductID" AS "ProductID",     "Extent2"."UnitPrice" AS "UnitPrice",     "Extent2"."Quantity" AS "Quantity",     "Extent2"."Discount" AS "Discount",     CASE WHEN ("Extent2"."OrderID" IS NULL) THEN NULL ELSE 1 END AS "C2"    FROM  "NORTHWIND_USER"."Orders" "Extent1"    LEFT OUTER JOIN "NORTHWIND_USER"."OrderDetails" "Extent2" ON "Extent1"."OrderID" = "Extent2"."OrderID")  "Project1"ORDER BY "Project1"."OrderID" ASC, "Project1"."C2" ASC

之所有要這麼麻煩的在 query2 的 Select() 中去一個一個取出Order的欄位資料，是有經過測試的，如果是直接在query2 的 Select() 中去取出 Order……

var query1 = Orders    .OrderBy(x => x.OrderID)    .Select(x => new { order = x, details = x.OrderDetails })    .ToList(); var query2 = from r in query1             select new              {                 Order = r.order,                 Amount = r.details.Sum(x => x.Quantity * x.UnitPrice),             };