Entity Data Model (EDM) 深度剖析 (下)：EntityClient 提供者、LINQ to Entities 與進階查詢技術總覽

本文介紹 Entity Framework 的兩大查詢管道：EntityClient 提供者 (低階 ADO.NET 風格) 與 LINQ to Entities (強型別、智慧提示、語法自然)，並預告進階主題。

EntityClient：標準 ADO.NET 介面操作 EDM

EntityClient 建立在 ADO.NET 3.5 提供者之上，提供 EntityConnection、EntityCommand、DbDataReader，可直接用 ESQL 查詢而無需產生實體類別，適合動態查詢或不想引入模型類別的情境。

1. EntityCommand 查詢實體型別 (SequentialAccess 讀取)

NorthwindEntities context = new NorthwindEntities();
EntityConnection conn = new EntityConnection(context.Connection.ConnectionString);
conn.Open();

var sql = "SELECT VALUE emp FROM NorthwindEntities.Employees AS emp";
EntityCommand cmd = new EntityCommand(sql, conn);
DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);

while (reader.Read())
    Console.WriteLine("{0} {1} {2} {3}", reader["EmployeeID"], reader["LastName"], reader["FirstName"], reader["Country"]);

關鍵：SequentialAccess 必須依序讀取欄位，否則拋出 InvalidOperationException。

2. EntityCommand 查詢匿名型別

var sql = "SELECT emp.LastName, emp.FirstName FROM NorthwindEntities.Employees AS emp";
EntityCommand cmd = new EntityCommand(sql, conn);
// 執行與讀取同上，reader["LastName"], reader["FirstName"]

3. EntityCommand 參數化查詢

var sql = "SELECT VALUE emp FROM NorthwindEntities.Employees AS emp WHERE emp.Country = @country";
EntityCommand cmd = new EntityCommand(sql, conn);
EntityParameter param = new EntityParameter("country", DbType.String) { Value = "USA" };
cmd.Parameters.Add(param);
// 執行與讀取同上

LINQ to Entities：強型別、自然語法、智慧提示

LINQ 在 .NET 3.5 引入，LINQ to Entities 與 LINQ to Objects/SQL 語法一致，開發體驗優於 ESQL。

1. 參數化查詢

NorthwindEntities context = new NorthwindEntities();
string country = "USA";
var query = from e in context.Employees
            where e.Country == country
            select e;

foreach (var emp in query)
    Console.WriteLine("{0} {1} {2} {3}", emp.EmployeeID, emp.FirstName, emp.LastName, emp.Country);

2. 匿名型別投影

var query = from e in context.Employees
            select new { e.LastName, e.FirstName };

foreach (var emp in query)
    Console.WriteLine("{0} {1}", emp.LastName, emp.FirstName);

系列總結與下一步

本系列涵蓋：EDM 架構、Entity SQL 基礎/參數化/匿名型別查詢、EntityClient 低階操作、LINQ to Entities 入門。下階段將深入：

• 進階 Entity SQL (複雜條件、關聯、聚合)
• 查看生成 SQL (SQL Profiler / ToTraceString)
• Eager Loading (Include) vs Lazy Loading
• 變更追蹤、並發控制、交易管理