2012年1月2日 星期一

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

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

image

轉換的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的部份:

image

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

轉換的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

image

可以看到改使用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方式查詢的是一樣的。

 

接著看看完整的查詢:

image

轉換的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的資料給取出,

執行結果:

image

轉換的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資料就取出,

執行結果:

SNAGHTML114c160

轉換的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()

出現錯誤

image


方式二,使用SingleOrDefault()

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

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

image

 


比較推薦的方式

如果說要取得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
             };

執行結果:

SNAGHTMLd4fbfe

取出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),
             };

執行結果:

image

這一次的查詢就要花費約 20 秒的時間,會什麼要花這麼久的時間呢?

就查詢的結果來仔細的看看……

image

應該可以一眼就看出來吧,因為在 query2 的 Select() 中是直接取出 Orders 物件,所以就會把Orders以及關連的資料再從資料庫取出來,而全部830筆的資料,就表示要做 830 次的資料庫存取,所以就慢了。

 


以上提供幾種不同的關聯資料的查詢操作方式讓大家參考。

P.S

使用LINQ的查詢語法,無論是否使用LINQ to SQL, ADO.NET Entity Framework, Oracle ODAC for Entity Framework,真的建議各位一定要搭配 LINQPad 來做為開發的輔助工具,事先測試各種的查詢方式就可以尾我們節省相當多的時間。

 

延伸閱讀:

博客園 - 李彥 - 关于LINQ to SQL中SelectMany()方法的讨论

Javen-Studio 咖啡小屋 - Select 与 SelectMany 的比较(Select vs. SelectMany)

點部落 - 我的Coding之路 - 用 Linq to SQL 完成Left join-DefaultIfEmpty

 

Oracle Technology Network > Oracle Magazine Issue Archive  2011 > September 2011

TECHNOLOGY: Oracle Data Provider for .NET Published Distinctive Database Development

Use Entity Framework with Oracle Data Provider for .NET

http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51odt-453447.html

 

以上

沒有留言:

張貼留言

提醒

千萬不要使用 Google Talk (Hangouts) 或 Facebook 及時通訊與我聯繫、提問,因為會掉訊息甚至我是過了好幾天之後才發現到你曾經傳給我訊息過,請多多使用「詢問與建議」(在左邊,就在左邊),另外比較深入的問題討論,或是有牽涉到你實作程式碼的內容,不適合在留言板裡留言討論,請務必使用「詢問與建議」功能(可以夾帶檔案),謝謝。

最近的留言