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

言歸正傳，我們先看一個(gè)簡(jiǎn)單的例子。

Item實(shí)體對(duì)應(yīng)Item表，每個(gè)Item擁有一些評(píng)論，也就是ItemComment。Item實(shí)體中有一個(gè)Comments屬性，是ItemComment實(shí)體的集合。這個(gè)例子將會(huì)使用這個(gè)再簡(jiǎn)單不過的模型。

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

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();}

這么做自然可以實(shí)現(xiàn)我們想要的功能，這的確沒錯(cuò)。但是這種做法有個(gè)很常見的問題，那就是可能會(huì)獲得太多不需要的數(shù)據(jù)。一個(gè)Item數(shù)據(jù)量最大的是Introduction字段，而顯示列表的時(shí)候我們是不需要顯示它的。如果我們?cè)讷@取Item列表時(shí)把Introduction一起獲得的話，那么應(yīng)用服務(wù)器和數(shù)據(jù)庫(kù)服務(wù)器之間的數(shù)據(jù)通信量將會(huì)成百甚至上千地增長(zhǎng)了。因此我們?cè)诿嫦虼祟愋枨蟮脑挘紩?huì)忽略每個(gè)Item對(duì)象的Introduction字段。那么我們?cè)撛趺醋瞿兀繉?duì)LINQ有簡(jiǎn)單了解的朋友們可能會(huì)想到這么做：

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();}

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

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

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

使用Translate方法

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

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語句，實(shí)現(xiàn)了我們需要的邏輯。在ExecuteReader之后即使用dataContext.Translate方法將DbDataReader里的數(shù)據(jù)轉(zhuǎn)換成Item對(duì)象。使用Translate方法除了方便之外，生成的對(duì)象也會(huì)自動(dòng)Attach到DataContext中，也就是說，我們可以繼續(xù)對(duì)獲得的對(duì)象進(jìn)行操作，例如訪問Item對(duì)象的Comments屬性時(shí)會(huì)自動(dòng)去數(shù)據(jù)庫(kù)獲取數(shù)據(jù)，改變對(duì)象屬性之后調(diào)用SubmitChange也能將修改提交至數(shù)據(jù)庫(kù)。Translate方法從DbDataReader中生成對(duì)象的規(guī)則和內(nèi)置的DataContext.ExecuteQuery方法一樣，大家可以查看MSDN中的說明（中文、英文）。

此外，這里有兩個(gè)細(xì)節(jié)值得一提：

• 為什么調(diào)用ExecuteReader方法時(shí)要傳入CommandBehavior.CloseConnection：LINQ to SQL中的DataContext對(duì)象有個(gè)特點(diǎn)，如果在使用時(shí)它的Connection對(duì)象被“顯式”地打開了，即使調(diào)用了DataContext對(duì)象的Dispose方法也不會(huì)自動(dòng)關(guān)閉。因此我們?cè)陂_發(fā)程序的時(shí)候一定要注意這一點(diǎn)。例如，在調(diào)用ExecuteReader是傳入CommandBehavior.CloseConnection，這樣就保證了在關(guān)閉DbDataReader時(shí)同時(shí)關(guān)閉Connection——當(dāng)然，我們也可以不這么做。
• 在調(diào)用Translate方法后為什么要直接調(diào)用ToList方法：因?yàn)镚etItemsForListing方法的返回值是List<Item>，這是原因之一。另一個(gè)原因是Translate方法并不會(huì)直接生成所有的對(duì)象，而是在外部代碼訪問Translate方法返回的IEnmuerable<T>時(shí)才會(huì)生成其中每個(gè)對(duì)象。這也是一種Lasy Load，但是也導(dǎo)致了所有的對(duì)象必須在Reader對(duì)象關(guān)閉之前生成，所以我一般都會(huì)在Translate方法后直接調(diào)用ToList方法，保證所有的對(duì)象已經(jīng)生成了。雖然事實(shí)上我們也可以不使用using關(guān)鍵字而直接返回Translate方法生成的IEnumerable<Item>，不過這么做的話當(dāng)前鏈接就得不到釋放（釋放，而不是關(guān)閉），也就是把處理數(shù)據(jù)連接的問題交給了方法的使用者——很可能就是業(yè)務(wù)邏輯層。為了確保分層結(jié)構(gòu)的職責(zé)分明，我一般傾向于在這里確保所有對(duì)象的已經(jīng)生成了。

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

public static class DataContextExtensions{    public static List<T> ExecuteQuery<T>        (this DataContext 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        (this DataContext dataContext)    {        if (dataContext.Connection.State ==             ConnectionState.Closed)        {            dataContext.Connection.Open();        }    }}

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

新的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獲得一個(gè)query之后，我們不再直接獲得查詢數(shù)據(jù)了，而是將其交給我們的ExecuteQuery擴(kuò)展來執(zhí)行。現(xiàn)在這種做法既保證了使用LINQ to SQL進(jìn)行查詢，又構(gòu)造出Item對(duì)象的部分字段，算是一種較為理想的解決方案。不過使用這個(gè)方法來獲得僅有部分字段的對(duì)象時(shí)需要注意一點(diǎn)：在構(gòu)造匿名對(duì)象時(shí)使用的屬性名，可能和目標(biāo)實(shí)體對(duì)象（例如之前的Item）的屬性名并非一一對(duì)應(yīng)的關(guān)系。

這種情況會(huì)在實(shí)體對(duì)象的屬性名與數(shù)據(jù)表字段名不同的時(shí)候發(fā)生。在使用LINQ to SQL時(shí)默認(rèn)生成的實(shí)體對(duì)象，其屬性名與數(shù)據(jù)庫(kù)的字段名完全對(duì)應(yīng)，這自然是最理想的情況。但是有些時(shí)候我們的實(shí)體對(duì)象屬性名和數(shù)據(jù)庫(kù)字段名不同，這就需要在ColumnAttribute標(biāo)記中設(shè)置Name參數(shù)了（當(dāng)然，如果使用XmlMappingSource的話也可以設(shè)置），如下：

[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屬性上標(biāo)記的ColumnAttribute的Name屬性設(shè)為UserID，這表示它將與Item表中的UserID字段對(duì)應(yīng)。那么如果我們要在這種情況下改寫之前的GetItemsForListing方法，我們?cè)撛趺醋瞿兀靠赡苡信笥褧?huì)很自然的想到：

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即可——其實(shí)不然。查看方法返回的結(jié)果就能知道，所有對(duì)象的OwnerID的值都是默認(rèn)值“0”，這是怎么回事呢？使用SQL Profiler觀察以上代碼所執(zhí)行SQL語句之后我們便可明白一切：

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

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

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時(shí)，我建議保持實(shí)體對(duì)象屬性名與數(shù)據(jù)庫(kù)字段名之間的映射關(guān)系。

改變LINQ to SQL所執(zhí)行的SQL語句

按照一般的做法我們很難改變LINQ to SQL查詢所執(zhí)行的SQL語句，但是既然我們能夠?qū)⒁粋€(gè)query轉(zhuǎn)化為DbCommand對(duì)象，我們自然可以在執(zhí)行之前改變它的CommandText。我這里通過一個(gè)比較常用的功能來進(jìn)行演示。

數(shù)據(jù)庫(kù)事務(wù)會(huì)帶來鎖，鎖會(huì)降低數(shù)據(jù)庫(kù)并發(fā)性，在某些“不巧”的情況下還會(huì)造成死鎖。對(duì)于一些查詢語句，我們完全可以顯式為SELECT語句添加WITH (NOLOCK)選項(xiàng)來避免發(fā)出共享鎖。因此我們現(xiàn)在擴(kuò)展剛才的ExecuteQuery方法，使它接受一個(gè)withNoLock參數(shù)，表明是否需要為SELECT添加WITH (NOLOCK)選項(xiàng)。請(qǐng)看示例：

public static class DataContextExtensions{    public static List<T> ExecuteQuery<T>(        this DataContext 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(        this DataContext dataContext,         IQueryable query, bool withNoLock)    {        SqlCommand command =            (SqlCommand)dataContext.GetCommand(query);        if (withNoLock)        {            command.CommandText =                AddWithNoLock(command.CommandText);        }        return command;    }}

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

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查看上述代碼所執(zhí)行的SQL語句，就會(huì)發(fā)現(xiàn)：

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

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

以上擴(kuò)展所受限制

以上的擴(kuò)展并非無可挑剔。由于Translate方法的特點(diǎn)，此類做法都無法充分發(fā)揮LINQ to SQL查詢的所有能力——那就是所謂的“LoadWith”能力。

在LINQ to SQL中，默認(rèn)會(huì)使用延遲加載，然后在必要的時(shí)候才會(huì)再去數(shù)據(jù)庫(kù)進(jìn)行查詢。這個(gè)做法有時(shí)候會(huì)降低系統(tǒng)性能，例如：

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

這種做法的性能很低，因?yàn)槟J(rèn)情況下每個(gè)Item對(duì)象的ItemComment集合不會(huì)被同時(shí)查詢出來，而是會(huì)等到內(nèi)層的foreach循環(huán)執(zhí)行時(shí)再次查詢數(shù)據(jù)庫(kù)。為了避免不合適的Lazy Load降低性能，LINQ to SQL提供了DataLoadOptions機(jī)制進(jìn)行控制：

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();}

當(dāng)我們?yōu)镈ataContext對(duì)象設(shè)置了LoadOptions并且指明了“Load With”關(guān)系，LINQ to SQL就會(huì)根據(jù)要求查詢數(shù)據(jù)庫(kù)——在上面的例子中，它將生成如下的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] = @p0ORDER BY [t0].[CreateTime] DESC, [t0].[ItemID], [t1].[ItemCommentID]

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

it知識(shí)庫(kù)：在LINQ to SQL中使用Translate方法以及修改查詢用SQL，轉(zhuǎn)載需保留來源！

鄭重聲明：本文版權(quán)歸原作者所有，轉(zhuǎn)載文章僅為傳播更多信息之目的，如作者信息標(biāo)記有誤，請(qǐng)第一時(shí)間聯(lián)系我們修改或刪除，多謝。