CODE-LINQ to SQL-讀取資料時略過部分欄位

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

        }

    }

}