依照資料庫的效能原則 -- 進行SELECT查詢時,應只選取必要欄位,選取欄位數愈少愈好。

以SELECT * FROM Attachment為例,假設資料有1萬筆,其中還有個Content欄位存放檔案的二進位內容,若平均檔案大小為1M,這個查詢動作將會引發10GB的磁碟機存取及網路傳輸量。如果我們的目的只不過想出一份檔案名稱及寫入日期的清單,那就千萬別這麼做,請以蒼生為念: SELECT FileName, CreatedDate FROM Attachment就成了!

在LINQ to SQL裡,大家先學到的是from o in DataContext where ... select o的寫法,實際對應到SQL語法,就等同SELECT *。因此當我們只需要其中某些欄位時,可以用select new { o.Col1, o.Col2 }的技巧請Compiler偷偷產生一個匿名類別來,而這個匿名類別可用var宣告變數承接,自始至終察覺不到它的存在,對我們來說是完全不沾手!

不過用var的缺點是我們無法把匿名類別的物件當成方法傳回值抛回呼叫端,解決之道是另外定義一個自訂類件以吻合要選取的欄位。再進一步,若要選取哪些欄位是動態決定的,則可改用DataContext.ExecuteQuery,再設法尋求屬性可涵蓋選取欄位的類別或另外自訂類別承接查詢結果。[可參考ScottGu這篇文章關於Custom SQL Expressions with Custom Classes的示範]
(我還為此搞過一台潛盾機自動產生承接查詢結果的類別:
)

我常遇到的另一種情境是資料表中有幾個重量級欄位(例如: 超大XML、超大檔案內容),要產生清單時用不到(且應避免),其餘欄位倒跟原LINQ to SQL自動產生的類別吻合,頂多這幾個被略過的欄位沒有資料。如此,我們可以不用另外產生一個客製化類別,決定好要SELECT的欄位,用ExecuteQuery<OrigLinqDataClass>()就可搞定。

不過,若30個欄位剔出兩個不要的長欄位,得正向表列28個欄位名稱也是挺累人的,所以,我又發懶了,試寫了一個"只要負向表列哪些欄位不要"的輔助Method,有興趣的人可以參考之。

排版顯示純文字
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Data.Linq.Mapping;
using System.Data.Linq;
 
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            using (AfaDataContext db = new AfaDataContext())
            {
                db.Log = Console.Out;
                //只要選取少數欄位時,自訂匿名物件最簡便
                var q = (from o in db.WorkItems
                         where o.Subject.StartsWith("BLAH")
                         select new { o.WorkItemId, o.Subject })
                         .ToList();
                foreach (var w in q)
                    Console.WriteLine(w.Subject);
                //但若要將結果傳遞到其他DLL,var就弱掉了
                //另一個做法是利用DataContext.ExecuteQuery,只選取部分欄位
                //(注意: Primary Key欄位不能一定要被選取,不可省略)
                //結果可借用原資料物件,未選取的欄位就是null或value type的初始值
                var r = db.ExecuteQuery<WorkItem>(
                    "SELECT WorkItemId, Subject FROM WorkItem " +
                    "WHERE Subject LIKE {0} + '%'", "BLAH");
                foreach (var w in r)
                    Console.WriteLine(w.Subject);
                //不過,我常遇到的情境是只想避開一兩個NVarChar(MAX), Image超大欄位
                //為此要列出剩下的數十個欄位有點笨拙,或許負向表列要略過欄位會比較簡潔...
                //所以,以下是我的嘗試:
                List<WorkItem> res =
                    PartialRead<WorkItem>(db, 
                    new string[] { "LongVarCharMax", "LargeImage", "BigText" }, 
                    "Subject LIKE {0} + '%'", "COS-POC");
                foreach (var w in res)
                    Console.WriteLine(w.Subject);
            }
            Console.Read();
        }
 
        /// <summary>
        /// 查詢資料,但略過部分欄位(限非PK欄位)
        /// </summary>
        /// <typeparam name="T">傳回的物件型別</typeparam>
        /// <param name="db">DataContext物件</param>
        /// <param name="ignoreCols">要略過的欄位(限非PK)</param>
        /// <param name="where">查詢時的WHERE條件,語法如DataContext.ExecuteQuery。
        /// 注意: 有SQL Injection風險,切忌放任使用者自由輸入。</param>
        /// <param name="args">DataContext.ExecuteQuery的WHERE條件參數</param>
        /// <returns>查詢結果,略過欄位的內容為null</returns>
        public static List<T> PartialRead<T>(DataContext db, string[] ignoreCols,
            string where, params object[] args)
        {
            Type t = typeof(T);
            Dictionary<string, PropertyInfo> props = 
                new Dictionary<string, PropertyInfo>();
            foreach (PropertyInfo pi in t.GetProperties())
            {
                //檢查: Primary Key欄位不可略過
                if (ignoreCols.Contains(pi.Name) &&
                    (pi.GetCustomAttributes(typeof(ColumnAttribute), true)[0] as 
                    ColumnAttribute).IsPrimaryKey
                    )
                    throw new ApplicationException(
                        pi.Name + " is PK and not ignorable!");
                props.Add(pi.Name, pi);
            }
            //列舉欄位,略過的不納入
            List<string> selectCols = new List<string>();
            foreach (string c in props.Keys)
                if (!ignoreCols.Contains(c))
                    selectCols.Add(c);
            //找出資料表名稱
            string tableName = 
                (t.GetCustomAttributes(typeof(TableAttribute), true)[0]
                as TableAttribute).Name;
            //組合SQL語法
            string commandText = string.Format("SELECT {0} FROM {1} WHERE {2}",
                string.Join(",", selectCols.ToArray()), tableName, where);
            //查詢後傳回結果
            return db.ExecuteQuery<T>(commandText, args).ToList();
        }
    }
}

警告 : 這種借用原類別但某幾個欄位不取值的做法,會形成被略過欄位的對應屬性為null或是value type初始值的情況,呼叫時應保持正確認知,切忌將其視為有效值使用。如果在可能發生混淆的場合(例如: 結果要傳給對方,但無法掌握對方會如何使用),請避免以上做法!


Comments

# by Will 保哥

有一段句子感覺有點繞口:「Primary Key欄位不能一定要被選取,不可省略」 不能 * 一定要被選取 * 不可省略 = -1 * 1 * -1 = 1 ( 所以是一定要選取的意思嗎? ) ^__^

# by Jeffrey

to 保哥,謝謝指正。原本打"PK欄位不能被省略",後來想改成"PK欄位一定要被選取,不可省略",結果一時眼花手殘就成了你看到的德行了... orz

# by hason

可以用DataTable 欄位設定好以後select table.Rows.AddRow(field1, field2....)

# by Labrus

使用 PartialRead<T>會有一個現象: 如果在之前的 ExecuteQuery<T>裡沒有包含使用到的欄位,則在 PartialRead裡也不會出現,即使不在負向表列名單內,但是執行不會報錯

Post a comment