The Next Generation of ADO.NET (3)

讀寫 ADO.NET Entity Framework

在前一篇文章中，我們說明了要如何存取 Entity Framework 的方法，接著我們來進一步深入到較複雜型式的讀寫方法。

Entity SQL 簡介

對於操作資料庫非常熟悉的開發人員來說，由資料庫取出資料這件事是再平常不過的了，Transact-SQL 的 SELECT 陳述式可以做到的事情太多了，包含關聯性連結，子查詢與遞迴查詢（Recursive Query）等等，當然在 ADO.NET Entity Framework 中的查詢能力也不能太弱，但是 Entity SQL 只是 Transact-SQL 的一個子集，無法做到 Transact-SQL 的所有功能。也就因為如此，在設計資料庫時，應盡可能的將複雜查詢使用檢視表、預存程序或使用者函數（UDFs）來包裝，讓開發人員在寫程式時可以使用簡單的查詢方式，而這就是 DBA 的工作。

Entity SQL 可以做到 Transact-SQL 多數的 SELECT 能力，不過會有一些限制，列示部份常見的限制如下：

查詢表格與欄位時，必須要帶有別名（Alias)：

以往在 Transact-SQL 中，不需要別名即可存取資料庫，但是在 Entity SQL 中，必須要使用別名來參考，才可以存取 Entity，例如：
```
SELECT a FROM T (Transact-SQL)
SELECT n.a FROM T AS a (Entity SQL)
SELECT T.a FROM T (Entity SQL with implicit alias)
```
若不想指定別名時，就要指定表格的名稱，Entity SQL會做內隱式的別名轉換。
不支援 (*)：

Transact-SQL 中若要提取所有欄位，我們會用 * 來指示，但是這在 Entity SQL 中是不支援的，必須要明確的指出要提取的欄位名稱，例如：
```
SELECT * FROM T (Transact-SQL)
SELECT n.* FROM T AS n (Syntax error in Entity SQL)
SELECT n.a, n.b, n.c FROM T AS a (Correct Entity SQL)
```
不支援 DDL 與修改類型的 DML 指令：

所有的 DDL 指令，以及 INSERT/UPDATE/DELETE 等主要修改的 DML 指令，在 Entity SQL 中皆不支援，所有的修改和刪除都是透過程式碼來做。
GROUP BY 指令必須要帶有別名：

在 Entity SQL 中支援 GROUP BY 能力，不過做 GROUP BY 運算時必須要帶有別名，否則會有語法錯誤，例如：
```
Transact-SQL:
select b + c, count(*), sum(a) 
from T 
group by b + c
Entity SQL:
select k, count(t.a), sum(t.a) 
from T AS t
group by b + c AS k
```
其他還有一些限制，這些都可以在ADO.NET Entity Framework所附的文件中查到。

讀取 Entity - Entity SQL 方法

了解了 Entity SQL 之後，我們就可以開始動手寫 SQL 指令來查詢，首先我們用 Entity Client 來作讀寫工作，程式碼和前幾篇都大致相同，其中 Entity 是用 Northwind 資料庫所產生的 Entity Model：


using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.EntityClient;
using System.Linq;
using System.Text;

namespace EntitySqlClient
{
    class Program
    {
        static void Main(string[] args)
        {
            string sql = “";
            string entityClientConnectionString =
                @"Provider=System.Data.SqlClient; Metadata=.; 
                  Provider Connection String='Initial Catalog=Northwind; 
Integrated Security=SSPI'";

            using (EntityConnection conn = new 
EntityConnection(entityClientConnectionString))
            {
                EntityCommand cmd = new EntityCommand(sql, conn);
                conn.Open();

                EntityDataReader reader = 
cmd.ExecuteReader(CommandBehavior.SequentialAccess);

                while (reader.Read())
                    Console.WriteLine("Result: {0}, {1}", 
reader.GetValue(0), reader.GetValue(1));

                reader.Close();
                conn.Close();
            }
        }
    }
}

若我們想要存取 Customers 資料表的 CustomerID, ContactName 二個欄位，在 Transact-SQL 中我們會下：

SELECT CustomerID, ContactName FROM Customers

但若在 Entity SQL，則我們要這樣下：

SELECT c.CustomerID, c.ContactName FROM NorthwindEntities.Customers AS c

另外，Entity SQL 使用 VALUE 來將查詢出來的結果轉換成物件（即單一數值），這會在 Object Service 中使用到，若想要把前例的 Customers 的所有資料轉換為單一數值，則可以用下面的陳述式：

SELECT VALUE c FROM NorthwindEntities.Customers AS c

若想要用條件式查詢，則可以利用 WHERE，例如：

SELECT c.CustomerID, c.ContactName FROM NorthwindEntities.Customers AS c WHERE c.CustomerID = @customerID

然後利用 EntityParameter 來設定參數的值，例如：


// 請注意，參數名稱要把 "@" 拿掉，否則會出現錯誤。
EntityParameter param = new EntityParameter("CustomerID", DbType.String);
param.Value = "QUICK";
cmd.Parameters.Add(param);

在程式中使用參數化查詢（Parameterized-Query）是個良好的習慣，可以避免像 SQL-Injection 這樣的攻擊手法。

若想要執行彙總，可以使用 GROUP BY 加上彙總函數，例如：


SELECT o.OrderID, SUM(o.UnitPrice * o.Quantity * o.Discount) FROM 
NorthwindEntities.[Order_Details] AS o 
GROUP BY o.OrderID

讀取 Entity－Entity SQL 關聯式查詢

在程式中使用關聯式查詢（Relationship-Query），在企業應用程式中可說是稀鬆平常，有時候還可能會 JOIN 很多的資料表，來做出報表或是查詢數值等等工作，Entity 也不例外，Entity SQL 支援 JOIN 方法，不過在 Entity 中，不叫 Relationship，而叫做 Association。

例如以下圖之 Customers 與 Orders 資料表為例：

在 Orders 和 Customers 之間有一條關聯資訊（FK_Orders_Customers），是一對多的關聯，這條關聯在 CSDL 是這樣表示的：


<AssociationSet Name="FK_Orders_Customers"
 Association="NorthwindModel.FK_Orders_Customers">
    <End Role="Customers" EntitySet="Customers" />
    <End Role="Orders" EntitySet="Orders" />
  </AssociationSet>

並且，在 Entity 的 CSDL 宣告中，也會有描述這個的表示：


<EntityType Name="Customers">
          … (略)
          <NavigationProperty Name="Orders" 
Relationship="NorthwindModel.FK_Orders_Customers" 
FromRole="Customers" 
ToRole="Orders" />
          <NavigationProperty Name="CustomerDemographics"
Relationship="NorthwindModel.CustomerCustomerDemo"
FromRole="Customers" 
ToRole="CustomerDemographics" />
</EntityType>

每一條關聯性都由 Association 和 NavigationProperty 組成，而 NavigationProperty 就是要在 Entity SQL中使用的關聯查詢法。

例如我們都想要取得特定 Customer 所有的 Orders，則我們可以這樣查：


SELECT o.Customers.CustomerID, o.OrderID 
FROM NorthwindEntities.Orders AS o 
WHERE o.Customers.CustomerID = @customerID

注意到了嗎？在 NavigationProperty 所設定的，可以在 Entity SQL 中取用，以前例來說，Orders 中有一個 Customers 的 NavigationProperty，它會去比對 Customers 中的 CustomerID，而回傳的數值則是 Customers 和 Orders 有關聯的資料。

若想要利用 JOIN 來查詢，則可以利用下列語法：


SELECT c.CustomerID, o.OrderID 
FROM NorthwindEntities.Orders AS o INNER JOIN NorthwindEntities.Customers AS c 
ON o.Customers.CustomerID = c.CustomerID 
WHERE c.CustomerID = @customerID

兩者查詢出來的結果是相同的。

若想要做更多層次的查詢，也可以用相同作法，例如我想要查詢出訂單是由誰處理的，可以這樣下查詢：


SELECT 
o.Customers.CustomerID, o.OrderID, o.Employees.FirstName + ' ' + o.Employees.LastName 
FROM NorthwindEntities.Orders AS o  
WHERE o.Customers.CustomerID = @customerID

以上我們介紹了常用的 Entity SQL 作法，讀者可以發現，在 Entity SQL 中部份工作反而比 Transact-SQL 簡單（例如關聯性查詢），的確，因為它是用 Collection 的角度來做設計的，而不是以ERD的角度，所以若用物件集合的方式來想，那 Entity SQL 就會變得簡單。

不過，它卻有一個最致命的問題－無法更新資料，因為不支援 INSERT/UPDATE 與 DELETE 指令，所以我們需要往上來找尋解決方案。

存取 Entity - Object Service

在 Object Service 存取 Entity，一樣是使用 Entity SQL，但讀取的工具由 EntityDataReader 換成物件瀏覽，例如下列的程式碼：


using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Objects;
using System.Text;
using NorthwindModel;

namespace ObjectServiceClient
{
    class Program
    {
        static void Main(string[] args)
        {
            using (NorthwindEntities db = new NorthwindEntities())
            {
                ObjectQuery<Customers> query = new ObjectQuery<Customers>(
                    "SELECT VALUE c FROM Customers AS c WHERE c.CustomerID = @customerID",
　　　　　　　　　　 db);

                query.Parameters.Add(new ObjectParameter("customerID", "QUICK"));

                foreach (Customers c in query)
                {
                    Console.WriteLine("Result: {0}, {1}", c.CustomerID, c.ContactName);
                }
            }
        }
    }
}

而若是想要查詢特定數值，則要改用 IExtendedDataReader：


using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Objects;
using System.Data;
using System.Text;
using NorthwindModel;

namespace ObjectServiceClient
{
    class Program
    {
        static void Main(string[] args)
        {
            using (NorthwindEntities db = new NorthwindEntities())
            {
                ObjectQuery<IExtendedDataRecord> query = new 
　　　　　　　　　　ObjectQuery<IExtendedDataRecord >(
                      @"SELECT c.CustomerID, c.ContactName FROM Customers AS c WHERE
 c.CustomerID = @customerID", 
db);

                query.Parameters.Add(new ObjectParameter("customerID", "QUICK"));

                foreach (IExtendedDataRecord reader in query)
                {
                    Console.WriteLine("Result: {0}, {1}", 
reader.GetValue(0), reader.GetValue(1));
                }
            }
        }
    }
}

由於讀取部份和 Entity Client 作法類似，故不多加說明，不過 Object Service 支援一個很重要的功能：寫入，Object Service 的寫入功能是由 ObjectContext.SaveChanges() 來達成，例如下列的程式碼：


uObjectQuery<Customers> query = new ObjectQuery<Customers>(
    "SELECT VALUE c FROM Customers AS c WHERE c.CustomerID = @customerID", 
    db);

query.Parameters.Add(new ObjectParameter("customerID", "QUICK"));

Customers c = query.First();
Console.WriteLine("CustomerID: " + c.CustomerID + " Contact Name: " + c.ContactName);

// update information.
c.ContactName = c.ContactName + " SAVED";
// save to database.
db.SaveChanges(true);

query = new ObjectQuery<Customers>(
    "SELECT VALUE c FROM Customers AS c WHERE c.CustomerID = @customerID", 
    db);

query.Parameters.Add(new ObjectParameter("customerID", "QUICK"));
Console.WriteLine("CustomerID: " + c.CustomerID + " Contact Name: " + c.ContactName);

在第一次存取時更新了 ContactName 的值，在呼叫 SaveChanges() 後寫入資料庫，第二次存取時就可以看到更新後的結果。同樣的道理，若要插入新的資料，則要先加入新物件，也就是 AddObject()，例如下列程式碼：


Customers customer = new Customers();

customer.CustomerID = "VVVVV";
customer.ContactName = "Jeff Chu";
customer.CompanyName = "JCST Studio";
customer.Address = "...";
customer.City = "Pingtung";
customer.ContactTitle = "Software Architect";
customer.Country = "TW";
customer.Fax = "0";
customer.PostalCode = "900";
customer.Region = "PINGTUNG";
customer.Phone = "1";

db.AddObject("Customers", customer); // insert new object
db.SaveChanges(true); // save to database

ObjectQuery<Customers> query = new ObjectQuery<Customers>(
    "SELECT VALUE c FROM Customers AS c WHERE c.CustomerID = @customerID", 
    db);

query.Parameters.Add(new ObjectParameter("customerID", "VVVVV"));

Customers c = query.First();
Console.WriteLine("CustomerID: " + c.CustomerID + " Contact Name: " + c.ContactName);

利用 ObjectContext.AddObject() 將要插入資料庫的資料先加入 ObjectContext，然後再透過 SaveChanges() 寫入資料庫中，刪除則是使用 DeleteObject()，例如下列程式碼：


ObjectQuery<Customers> query = new ObjectQuery<Customers>(
    "SELECT VALUE c FROM Customers AS c WHERE c.CustomerID = @customerID", 
    db);

query.Parameters.Add(new ObjectParameter("customerID", "VVVVV"));

Customers c = query.First();

db.DeleteObject(c); // delete object.
db.SaveChanges(); // save to database.

query = new ObjectQuery<Customers>(
    "SELECT VALUE c FROM Customers AS c WHERE c.CustomerID = @customerID",
    db);
query.Parameters.Add(new ObjectParameter("customerID", "VVVVV"));

foreach (Customers r in query)
    Console.WriteLine("Object Still exist");

Console.WriteLine("Deleted");

存取 Entity - LINQ to Entities

由於 LINQ to Entities 是奠基在 Object Service 之上，所以 Object Service 可以做到的事，在 LINQ to Entities 也可以做到，不過是使用 LINQ 的語法，例如下列語法是查詢超過 20 筆訂單的客戶：


IQueryable<Customers> query = 
　　　from c in db.Customers
      where c.Orders.Count > 20
      select c;

foreach (var c in query)
    Console.WriteLine("Result: {0}, {1}", c.CustomerID, c.ContactName);

相較於 Entity Client 與 Object Service，LINQ to Entities 讓查詢的語法變得更簡單了，除了可以直接取得物件外，亦可以透過 C# 2.0 開始提供的匿名型別（Anonymous type）來組合出想要回傳的數值，例如：


var query = from c in db.Customers
            where c.Orders.Count > 20
            select new { c.CustomerID, c.ContactName };

foreach (var c in query)
    Console.WriteLine("Result: {0}, {1}", c.CustomerID, c.ContactName);

這個方法也可以應用在關聯性查詢中，例如：


var query = from c in db.Customers
            join o in db.Orders on c.CustomerID equals o.Customers.CustomerID
            select new { c.CustomerID, c.ContactName, o.OrderID };

foreach (var c in query)
    Console.WriteLine("Result: {0}, {1}, {2}", c.CustomerID, c.ContactName, c.OrderID);

同樣的，對於資料的修改和 Object Service 的方式相同，一樣是呼叫 AddObject()、DeleteObject()與SaveChanges() 等方法，在此不再贅述，讀者可自行撰寫測試看看。

交易處理（Transaction）

在資料庫應用程式中，經常會使用到交易模式來確保資料已經正確的寫入資料庫，發生錯誤時則做 Rollback 回溯動作，在 Entity Framework 中，是透過 System.Transactions 命名空間中的 TransactionScope 來執行交易程序，例如：


using (TransactionScope tran = new TransactionScope())
{
    try
    {
        db.DeleteObject((from c in db.Customers
                         where c.CustomerID == "QUICK"
                         select c).AsEnumerable().First());
        db.SaveChanges(true);

        throw new InvalidOperationException(); // 故意放的，要做Rollback。
        tran.Complete();
    }
    catch (Exception)
    {
    }
}

當 TransactionScope.Complete() 被呼叫到時，SaveChanges() 的寫入資料庫動作才會生效，否則會被 Rollback 回溯到原本的狀況。

下一步

我們已經瀏覽了在 Entity Framework 中要如何利用 Entity SQL，Object Service 與 LINQ 的資料存取方法，接下來我們會進入如何使用 LINQ 對現有的 ADO.NET 做查詢與延伸的設計。