在LINQ to SQL中使用Translate方法以及修改查詢用SQL

目前LINQ to SQL的資料不多——老趙的意思是，目前能找到的資 料都難以擺脫“官方用法”的“陰影”。LINQ to SQL最 權威的資料自然是MSDN，但是MSDN中的文檔說明和實例總是顯得“大開大 阖”，依舊有清晰的“官方”烙印——這簡直是一 定的。不過從按照過往的經驗，在某些時候如果不按照微軟劃定的道道來走，可 能就會發現別樣的風景。老趙在最近的項目中使用了LINQ to SQL作為數據層的基礎，在LINQ to SQL開發方面積累了一定經驗，也總結出了一些官方文檔上並 未提及的有用做法，特此和大家分享。

言歸正傳，我們先看一個簡單的 例子。

Item實體對應Item表，每個Item擁有一些評論，也就是ItemComment。 Item實體中有一個Comments屬性，是ItemComment實體的集合。這個例子將會使 用這個再簡單不過的模型。

為用戶顯示他的Item列表是非常常見的需求 ，如果使用LINQ to SQL來獲取Item的話，我們可能會這麼做：

public List<Item> GetItemsForListing(int ownerId)
{
　　ItemDataContext dataContext = new ItemDataContext ();
　　var query = from item in dataContext.Items
　　　　 　　　　where item.UserID == ownerId
　　　　　　　　orderby item.CreateTime descending
　　　　　　　　select item;
　　 return query.ToList();
}

這麼做自然可以實現我們想要的功 能，這的確沒錯。但是這種做法有個很常見的問題，那就是可能會獲得太多不需 要的數據。一個Item數據量最大的是Introduction字段，而顯示列表的時候我們 是不需要顯示它的。如果我們在獲取Item列表時把Introduction一起獲得的話， 那麼應用服務器和數據庫服務器之間的數據通信量將會成百甚至上千地增長了。 因此我們在面向此類需求的話，都會忽略每個Item對象的Introduction字段。那 麼我們該怎麼做呢？對LINQ有簡單了解的朋友們可能會想到這麼做：

public List<Item> GetItemsForListing(int ownerId)
{
　　ItemDataContext dataContext = new ItemDataContext ();
　　var query = from item in dataContext.Items
　　　　 　　　　where item.UserID == ownerId
　　　　　　　　orderby item.CreateTime descending
　　　　　　　　select new Item
　　　　　　　　{
　　　　　　　　　　ItemID = item.ItemID,
　　　　　　　　　　Title = item.Title,
　　　　　　　　　　 UserID = item.UserID,
　　　　　　　　　　CreateTime = item.CreateTime
　　　　　　　　};
　　return query.ToList ();
}

這個做法很直觀，利用了C# 3.0中的Object Initializer 特性。編譯通過了，理應沒有錯，可是在運行時卻拋出了 NotSupportedException：“Explicit construction of entity type 'Demo.Item' in query is not allowed.”，意思就是不能在 LINQ to SQL中顯式構造Demo.Item對象。

事實上在RTM之前的版本中，以 上的語句是能運行通過的——我是指通過，不是正確。LINQ to SQL 在RTM之前的版本有個Bug，如果在查詢中顯式構造一個實體的話，在某些情況下 會得到一系列完全相同的對象。很可惜這個Bug我只在資料中看到過，而在RTM版 本的LINQ to SQL中這個Bug已經被修補了，確切地說是繞過了。直接拋出異常不 失為一種“解決問題”的辦法，雖然這實際上是去除了一個功能 ——沒有功能自然不會有Bug，就像沒有頭就不會頭痛了一個道理。

但是我們還得做，難道我們只能自己SQL語句了嗎？

使用 Translate方法

幸虧DataContext提供了Translate方法，Translate方法的作 用就是從一個DbDataReader對象中生成一系列的實例。其中最重要的就是一個帶 范型的重載：

public static List<Item> GetItemsForListing(int ownerId)
{
　　ItemDataContext dataContext = new ItemDataContext();
　　 dataContext.Connection.Open();
　　SqlCommand command = new SqlCommand(
　　　　"SELECT [ItemID], [Title], [UserID], [CreateTime]" +
　　　　" FROM [Item] WHERE [UserID] = " + ownerId +
　　　　" ORDER BY [CreateTime]",
　　　　(SqlConnection)dataContext.Connection);
　　using (DbDataReader reader = command.ExecuteReader
　　　　　　　　　　 　　 (CommandBehavior.CloseConnection))
　　{
　　　　return dataContext.Translate<Item>(reader).ToList();
　　}
}

在這段代碼裡，我們拼接出了一段SQL語句，實現了我們需要的邏輯 。在ExecuteReader之後即使用dataContext.Translate方法將DbDataReader裡的 數據轉換成Item對象。使用Translate方法除了方便之外，生成的對象也會自動 Attach到DataContext中，也就是說，我們可以繼續對獲得的對象進行操作，例 如訪問Item對象的Comments屬性時會自動去數據庫獲取數據，改變對象屬性之後 調用SubmitChange也能將修改提交至數據庫。Translate方法從DbDataReader中 生成對象的規則和內置的DataContext.ExecuteQuery方法一樣，大家可以查看 MSDN中的說明（中文、英文）。

此外，這裡有兩個細節值得一提：

為什麼調用ExecuteReader方法時要傳入 CommandBehavior.CloseConnection：LINQ to SQL中的DataContext對象有個特 點，如果在使用時它的Connection對象被“顯式”地打開了，即使調 用了DataContext對象的Dispose方法也不會自動關閉。因此我們在開發程序的時 候一定要注意這一點。例如，在調用ExecuteReader是傳入 CommandBehavior.CloseConnection，這樣就保證了在關閉DbDataReader時同時 關閉Connection——當然，我們也可以不這麼做。

在調用 Translate方法後為什麼要直接調用ToList方法：因為GetItemsForListing方法 的返回值是List<Item>，這是原因之一。另一個原因是Translate方法並 不會直接生成所有的對象，而是在外部代碼訪問Translate方法返回的 IEnmuerable<T>時才會生成其中每個對象。這也是一種Lasy Load，但是 也導致了所有的對象必須在Reader對象關閉之前生成，所以我一般都會在 Translate方法後直接調用ToList方法，保證所有的對象已經生成了。雖然事實 上我們也可以不使用using關鍵字而直接返回Translate方法生成的 IEnumerable<Item>，不過這麼做的話當前鏈接就得不到釋放（釋放，而 不是關閉），也就是把處理數據連接的問題交給了方法的使用者—— 很可能就是業務邏輯層。為了確保分層結構的職責分明，我一般傾向於在這裡確 保所有對象的已經生成了。

上面的例子使用拼接SQL字符串的方式來訪 問數據庫，那我們又該如何使用LINQ to SQL呢？幸虧LINQ to SQL中的 DataContext提供了GetCommand方法。我們直接來看一個完整的擴展：

public static class DataContextExtensions
{
　 　public static List<T> ExecuteQuery<T>
　　　　 (thisDataContext dataContext, IQueryable query)
　　{
　　　 　DbCommand command = dataContext.GetCommand(query);
　　　　 dataContext.OpenConnection();
　　　　using (DbDataReader reader = command.ExecuteReader())
　　　　{
　　　　　　return dataContext.Translate<T>(reader).ToList();
　　　　}
　　}
　　private static void OpenConnection
　　　　 (thisDataContext dataContext)
　　{
　　　　if (dataContext.Connection.State ==
　　　　　　 ConnectionState.Closed)
　　　　{
　　　　　　 dataContext.Connection.Open();
　　　　}
　　}
}

自從有了C# 3.0中的Extension Method，很多擴展都會顯得非常優雅 ，我非常喜歡這個特性。DataContextExtensions是我對於LINQ to SQL中 DataContext對象的擴展，如果以後有新的擴展也會寫在這個類中。 OpenConnection方法用於打開DataContext中的數據連接，今後的例子中也會經 常看到這個方法。而這次擴展的關鍵在於新的ExecuteQuery方法，它接受一個 IQueryable類型的對象作為參數，返回一個范型的List。方法中會使用 DataContext的GetCommand方法來獲得一個DbCommand。在我之前的文章，以及 MSDN中的示例都只是通過這個DbCommand對象來查看LINQ to SQL所生成的查詢語 句。也就是說以前我們用它進行Trace和Log，而我們這次將要真正地執行這個 DbCommand了。剩下的自不必說，調用ExecuteReader方法獲得一個DbDataReader 對象，再通過Translate方法生成一個對象列表。

新的ExecuteQuery方法 很容易使用：

public static List<Item> GetItemsForListing(int ownerId)
{
　　ItemDataContext dataContext = new ItemDataContext();
　　var query = from item in dataContext.Items
　　　　　　　　where item.UserID == ownerId
　　　　　　　　orderby item.CreateTime descending
　　　　　　 　　select new
　　　　　　　　{
　　　　　　　　　　ItemID = item.ItemID,
　　　　　　　　　　Title = item.Title,
　　 　　　　　　　　CreateTime = item.CreateTime,
　　　　　　　　　 　UserID = item.UserID
　　　　　　　　};
　　using (dataContext.Connection)
　　{
　　　　return dataContext.ExecuteQuery<Item>(query);
　　}
}

在通過LINQ to SQL獲得一個query之後，我們不再直接獲得查詢數據 了，而是將其交給我們的ExecuteQuery擴展來執行。現在這種做法既保證了使用 LINQ to SQL進行查詢，又構造出Item對象的部分字段，算是一種較為理想的解 決方案。不過使用這個方法來獲得僅有部分字段的對象時需要注意一點：在構造 匿名對象時使用的屬性名，可能和目標實體對象（例如之前的Item）的屬性名並 非一一對應的關系。

這種情況會在實體對象的屬性名與數據表字段名不 同的時候發生。在使用LINQ to SQL時默認生成的實體對象，其屬性名與數據庫 的字段名完全對應，這自然是最理想的情況。但是有些時候我們的實體對象屬性 名和數據庫字段名不同，這就需要在ColumnAttribute標記中設置Name參數了（ 當然，如果使用XmlMappingSource的話也可以設置），如下：

[Table(Name = "dbo.Item")]
public partial class Item : INotifyPropertyChanging, INotifyPropertyChanged
{
　　 [Column(Storage = "_OwnerID", DbType = "Int NOT NULL", Name = "UserID")]
　　public int OwnerID { get { } set { } }
}

OwnerID屬性上標記的ColumnAttribute的 Name屬性設為UserID，這表示它將與Item表中的UserID字段對應。那麼如果我們 要在這種情況下改寫之前的GetItemsForListing方法，我們該怎麼做呢？可能有 朋友會很自然的想到：

public static List<Item> GetItemsForListing(int ownerId)
{
　　ItemDataContext dataContext = new ItemDataContext();
　　var query = from item in dataContext.Items
　　　　　　　　where item.OwnerID == ownerId
　　　　　　　　orderby item.CreateTime descending
　　　　　　　　select new
　　　　　　　　{
　　　　　　　 　　　ItemID = item.ItemID,
　　　　　　　　　　Title = item.Title,
　　　　　　　　　　CreateTime = item.CreateTime,
　　　　　　　　　　OwnerID = item.OwnerID
　　　　　　　 　};
　　using (dataContext.Connection)
　　{
　　　　 return dataContext.ExecuteQuery<Item>(query);
　　}
}

按照“常理”判斷，似乎只要將所有的UserID改為 OwnerID即可——其實不然。查看方法返回的結果就能知道，所有對 象的OwnerID的值都是默認值“0”，這是怎麼回事呢？使用SQL Profiler觀察以上代碼所執行SQL語句之後我們便可明白一切：

SELECT [t0].[ItemID], [t0].[Title], [t0].[CreateTime], [t0].[UserID] AS [OwnerID]
FROM [dbo].[Item] AS [t0]
WHERE [t0].[UserID] = @p0
ORDER BY [t0].[CreateTime] DESC

由於 我們所使用的query實際上是用於生成一系列匿名對象的，而這些匿名對象所包 含的是“OwnerID”而不是“UserID”，因此LINQ to SQL 實際在生成SQL語句的時候會將UserID字段名轉換成OwnerID。由於Item的 OwnerID上標記的ColumnAttribute把Name設置成了UserID，所以Translate方法 讀取DbDataReader對象時事實上會去尋找UserID字段而不是OwnerID字段 ——這很顯然就造成了目前的問題。因此，如果您使用了 ColumnAttribute中的Name屬性改變了數據庫字段名與實體對象屬性名的映射關 系，那麼在創建匿名對象的時候還是要使用數據庫的字段名，而不是實體對象名 ，如下：

public static List<Item> GetItemsForListing(int ownerId)
{
　　ItemDataContext dataContext = new ItemDataContext();
　　var query = from item in dataContext.Items
　　　　　　　　where item.OwnerID == ownerId
　　　　　　　　orderby item.CreateTime descending
　　　　　　　　select new
　　　　　　　　{
　　　　　　　 　　　ItemID = item.ItemID,
　　　　　　　　　　Title = item.Title,
　　　　　　　　　　CreateTime = item.CreateTime,
　　　　　　　　　　UserID = item.OwnerID
　　　　　　　　};
　　using (dataContext.Connection)
　　{
　　　　return dataContext.ExecuteQuery<Item>(query);
　　}
}

這樣就能解決問題了——不過顯得不很漂亮，因此在使用 LINQ to SQL時，我建議保持實體對象屬性名與數據庫字段名之間的映射關系。

改變LINQ to SQL所執行的SQL語句

按照一般的做法我們很難改變LINQ to SQL查詢所執行的SQL語句，但是既然我們能夠將一個query轉化為DbCommand 對象，我們自然可以在執行之前改變它的CommandText。我這裡通過一個比較常 用的功能來進行演示。

數據庫事務會帶來鎖，鎖會降低數據庫並發性， 在某些“不巧”的情況下還會造成死鎖。對於一些查詢語句，我們完 全可以顯式為SELECT語句添加WITH (NOLOCK)選項來避免發出共享鎖。因此我們 現在擴展剛才的ExecuteQuery方法，使它接受一個withNoLock參數，表明是否需 要為SELECT添加WITH (NOLOCK)選項。請看示例：

public static class DataContextExtensions
{
　　public static List<T> ExecuteQuery<T>(
　　　　thisDataContext dataContext,
　　　　IQueryable query, bool withNoLock)
　　 {
　　　　DbCommand command = dataContext.GetCommand(query, withNoLock);
　　　　dataContext.OpenConnection();
　　　　 using (DbDataReader reader = command.ExecuteReader())
　　　　 {
　　　　　　return dataContext.Translate<T> (reader).ToList();
　　　　}
　　}
　　private static Regex s_withNoLockRegex =
　　　　new Regex(@"(] AS \[t\d+\])", RegexOptions.Compiled);
　　private static string AddWithNoLock(string cmdText)
　　{
　　　　 IEnumerable<Match> matches =
　　　　　　 s_withNoLockRegex.Matches(cmdText).Cast<Match>()
　　　　　 　.OrderByDescending(m => m.Index);
　　　　foreach (Match m in matches)
　　　　{
　　　　　　int splitIndex = m.Index + m.Value.Length;
　　　　　　cmdText =
　　　　　　　　 cmdText.Substring(0, splitIndex) + " WITH (NOLOCK)" +
　　　　　　　　cmdText.Substring(splitIndex);
　　　　}
　 　　　return cmdText;
　　}
　　private static SqlCommand GetCommand(
　　　　thisDataContext dataContext,
　　　　 IQueryable query, bool withNoLock)
　　{
　　　　SqlCommand command =
　　　　　　(SqlCommand)dataContext.GetCommand (query);
　　　　if (withNoLock)
　　　　{
　　　　　　 command.CommandText =
　　　　　　　　AddWithNoLock (command.CommandText);
　　　　}
　　　　return command;
　　}
}

上面這段邏輯的關鍵在於使用正則表達式查找需要添 加WITH (NOLOCK)選項的位置。在這裡我查找SQL語句中類似“] AS [t0] ”的字符串，並且在其之後添加WITH (NOLOCK)選項。其他的代碼大家應該 完全能夠看懂，我在這裡就不多作解釋了。我們直接來看一下使用示例：

public static List<Item> GetItemsForListingWithNoLock(int ownerId)
{
　　 ItemDataContext dataContext = new ItemDataContext();
　　var query = from item in dataContext.Items
　　　　　　　　where item.UserID == ownerId
　　　　　　　　orderby item.CreateTime descending
　　　　　　　　select new
　　　　　　　　{
　　　　　　　　　　ItemID = item.ItemID,
　　　　　　　　　　 Title = item.Title,
　　　　　　　　　　CreateTime = item.CreateTime,
　　　　　　　　　　UserID = item.UserID
　 　　　　　　　};
　　using (dataContext.Connection)
　　{
　　　　return dataContext.ExecuteQuery<Item>(query, true);
　　}
}

使用SQL Profiler查看上述代碼所執行的SQL語句， 就會發現：

SELECT [t0].[ItemID], [t0].[Title], [t0]. [CreateTime], [t0].[UserID]
FROM [dbo].[Item] AS [t0]WITH (NOLOCK)
WHERE [t0].[UserID] = @p0
ORDER BY [t0]. [CreateTime] DESC

很漂亮。事實上只要我們需要，就可以在 DbCommand對象生成的SQL語句上作任何修改（例如添加事務操作，容錯代碼等等 ），只要其執行出來的結果保持不變即可（事實上變又如何，如果您真有自己巧 妙設計的話，呵呵）。

以上擴展所受限制

以上的擴展並非無可挑剔。 由於Translate方法的特點，此類做法都無法充分發揮LINQ to SQL查詢的所有能 力——那就是所謂的“LoadWith”能力。

在LINQ to SQL中，默認會使用延遲加載，然後在必要的時候才會再去數據庫進行查詢。 這個做法有時候會降低系統性能，例如：

List<Item> itemList = GetItems(1);
foreach (Item item in itemList)
{
　　foreach (ItemComment comment in item.Comments)
　　{
　　　　Console.WriteLine(comment.Content);
　　}
}

這種做法的性能很低，因為默認情況下每個Item對象的ItemComment 集合不會被同時查詢出來，而是會等到內層的foreach循環執行時再次查詢數據 庫。為了避免不合適的Lazy Load降低性能，LINQ to SQL提供了 DataLoadOptions機制進行控制：

public static List<Item> GetItems(int ownerId)
{
　　ItemDataContext dataContext = new ItemDataContext();
　　DataLoadOptions loadOptions = new DataLoadOptions();
　　 loadOptions.LoadWith<Item>(item => item.Comments);
　　 dataContext.LoadOptions = loadOptions;
　　var query = from item in dataContext.Items
　　　　　　　　where item.UserID == ownerId
　　　　　　　　orderby item.CreateTime descending
　　　　　　　　select item;
　　return query.ToList();
}

當我們為DataContext對象設置了LoadOptions並且指明了 “Load With”關系，LINQ to SQL就會根據要求查詢數據庫 ——在上面的例子中，它將生成如下的SQL語句：

SELECT [t0].[ItemID], [t0].[Title], [t0]. [Introduction],
[t0].[UserID], [t0].[CreateTime], [t1]. [ItemCommentID],
[t1].[ItemID] AS [ItemID2], [t1].[Content], [t1].[UserID],
[t1].[CreateTime] AS [CreateTime2], (
　　 SELECT COUNT(*)
　　FROM [dbo].[ItemComment] AS [t2]
　　 WHERE [t2].[ItemID] = [t0].[ItemID]
　　) AS [value]
FROM [dbo].[Item] AS [t0]
LEFT OUTER JOIN [dbo].[ItemComment] AS [t1] ON
[t1].[ItemID] = [t0].[ItemID]
WHERE [t0].[UserID] = @p0
ORDER BY [t0].[CreateTime] DESC, [t0].[ItemID], [t1]. [ItemCommentID]

相信大家已經了解Translate方法為何無法充分發揮 LINQ to SQL的能力了。那麼我們又該如何解決這個問題呢？如果您希望同時使 用本文類似的擴展和Load With能力，可能就需要通過查詢兩次數據庫並加以組 合的方式來生成對象了——雖然查詢了兩次，但總比查詢100次的性 能要高。