LINQ 誕生超過十年(2007 隨 .NET 3.5 一起問市),我平日寫 .NET 程式早已「無 LINQ 不歡」,上癮程度甚至接近「無 LINQ 吾寧死」 (LINQ or Die),但我知道有不少朋友在開發時,會參考前人程式或依循習慣(或現有程式庫),繼續沿用 DataTable,for 迴圈搞定大小事,準備再戰十年。另外也有不少人對 LINQ 有誤解,以為它只能用在資料庫查詢,其實 LINQ 在一般集合物件上應用更多更廣。看在眼裡我有種「明明有打火機擺著不用,卻要猛劃火柴點火」的婉惜,總想衝上去雞婆幾句,提醒別暴殄天物。(噗)

最近有個機會在舊專案重溫古法釀造,用 DataTable 寫了一段資料比對,靈機一動,何不也寫段 LINQ 玩法對照,藉由這個案例讓尚未習慣使用 LINQ 的朋友感受其威力, 吸引更多人加入使用打火機的行列 XD

模擬情境是這樣的,有兩個 Oracle 資料庫,一個本地一個是來源,有 Schema 相同的匯率資料表,目的要寫一支程式抓取兩邊指定日期的匯率數字進行比對,找出差異。

先把時間拉回 ASP.NET 2.0 時代,最直覺做法是建個 OracleConnection、OracleCommand,加上 OracleParameter 傳入日期參數 (提醒:勿直接用參數組 SQL 字串搞出 SQL Injection 漏洞,依刑法 9487 條可處六小時以上,四十八小時以下的阿魯巴酷刑),產生兩個 DataTable。 匯率比對的鍵值為來源與目標幣別,如果不想傷腦筋,用 DataTable.Select() 下條件查詢可找出本地與來源資料表同幣別匯率資料逐一比對, 一一列舉來源資料表有缺、本地資料表有缺或兩邊數字不同三種狀況。

程式長得像這樣:

using Oracle.DataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;

namespace CodeCmp
{
    class TradCoding
    {

        static DataTable ReadRateData(DateTime date, string cnStr)
        {
            using (OracleConnection cn = new OracleConnection(cnStr))
            {
                cn.Open();
                OracleCommand cmd = cn.CreateCommand();
                cmd.BindByName = true;
                //讀取來源資料表
                cmd.CommandText = "SELECT * FROM RATE_TABLE WHERE RATE_DATE=:p_date AND RATE_TYPE='E'";
                cmd.Parameters.Add("p_date", OracleDbType.Date).Value = date;
                var dr = cmd.ExecuteReader();
                DataTable tbl = new DataTable();
                tbl.Load(dr);
                return tbl;
            }
        }

        public static void Compare(DateTime date)
        {
            DataTable src = ReadRateData(date, CommVars.CnnStrDBSrc);
            DataTable loc = ReadRateData(date, CommVars.CnnStrDBLoc);
            //比對資料是否完全一致?
            List<string> errors = new List<string>();
            //偵測來源無資料
            if (src.Rows.Count == 0) errors.Add("來源無資料");
            //以來源為準,找出本地缺資料或數值不一致者
            foreach (DataRow row in src.Rows)
            {
                var key = string.Format("{0}-{1}", row["FROM_CURR"], row["TO_CURR"]);
                DataRow[] find = loc.Select(string.Format("FROM_CURR='{0}' AND TO_CURR='{1}'",
                    row["FROM_CURR"], row["TO_CURR"]));
                
                if (find.Length == 0)
                {
                    errors.Add(string.Format("本地缺:{0}", key));
                }
                else
                {
                    DataRow mapRow = find[0];
                    if ((decimal)mapRow["RATE_VALUE"] != (decimal)row["RATE_VALUE"])
                    errors.Add(
                        string.Format("匯率不一致[{0}]:{1}(本地) vs {2}(來源)", 
                            key, mapRow["RATE_VALUE"], row["RATE_VALUE"]));


                }
            }
            //偵測本地無資料
            if (loc.Rows.Count == 0) errors.Add("本地無資料");
            //以本地為基準,找出來源缺資料者
            foreach (DataRow row in loc.Rows)
            {
                DataRow[] find = src.Select(string.Format("FROM_CURR='{0}' AND TO_CURR='{1}'",
                    row["FROM_CURR"], row["TO_CURR"]));
                if (find.Length == 0)
                    errors.Add(string.Format("來源缺:{0}", 
                        string.Format("{0}-{1}", row["FROM_CURR"], row["TO_CURR"])));

            }
            //若無任何錯誤則判為PASS,否則列出錯誤
            string res = errors.Count == 0 ? "PASS" : ("ERROR:" + string.Join("\n", errors.ToArray()));
            Console.WriteLine(res);
        }
    }
}

程式碼一堆 row["FROM_CURR"]、row["TO_CURR"] 飛來飛去好礙眼,其實可以先簡單改良,將來源幣別、目標幣別、匯率轉成 Dictionary<string, decimal>,以 "USD-TWD" 格式為 Key,改用 ContainsKey() 尋找比對,效率會比用 DataTable.Select() 好。所以我們稍做修改,結果相同,但程式簡潔不少。

using Oracle.DataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;

namespace CodeCmp
{
    class BetterCoding
    {

        static Dictionary<string, decimal> GetRateDictionary(DateTime date, string cnStr)
        {
            using (OracleConnection cn = new OracleConnection(cnStr))
            {
                cn.Open();
                OracleCommand cmd = cn.CreateCommand();
                cmd.BindByName = true;
                //讀取來源資料表
                cmd.CommandText = "SELECT * FROM RATE_TABLE WHERE RATE_DATE=:p_date AND RATE_TYPE='E'";
                cmd.Parameters.Add("p_date", OracleDbType.Date).Value = date;
                var dr = cmd.ExecuteReader();
                DataTable tbl = new DataTable();
                tbl.Load(dr);
                Dictionary<string, decimal> dict = new Dictionary<string, decimal>();
                //來源與本地資料表以轉換方向為Key建立兩個Dictionary
                foreach (DataRow row in tbl.Rows)
                {
                    string key = string.Format("{0}-{1}", row["FROM_CURR"], row["TO_CURR"]);
                    dict.Add(key, (decimal)row["RATE_VALUE"]);
                }

                return dict;
            }
        }

        public static void Compare(DateTime date)
        {
            var srcDict = GetRateDictionary(date, CommVars.CnnStrDBSrc);
            var locDict = GetRateDictionary(date, CommVars.CnnStrDBLoc);
            //比對資料是否完全一致?
            List<string> errors = new List<string>();
            //偵測來源無資料
            if (srcDict.Count == 0) errors.Add("來源無資料");
            //以來源為準,找出本地缺資料或數值不一致者
            foreach (string key in srcDict.Keys)
            {
                if (!locDict.ContainsKey(key))
                {
                    errors.Add(string.Format("本地缺:{0}", key));
                }

                else if (locDict[key] != srcDict[key])
                {
                    errors.Add(
                        string.Format("匯率不一致[{0}]:{1}(本地) vs {2}(來源)", 
                            key, locDict[key], srcDict[key]));
                }
            }
            //偵測本地無資料
            if (locDict.Count == 0) errors.Add("本地無資料");
            //以本地為基準,找出來源缺資料者
            foreach (string key in locDict.Keys)
            {
                if (!srcDict.ContainsKey(key))
                    errors.Add(string.Format("來源缺:{0}", key));
            }
            //若無任何錯誤則判為PASS,否則列出錯誤
            string res = errors.Count == 0 ? "PASS" : ("ERROR:" + string.Join("\n", errors.ToArray()));
            Console.WriteLine(res);
        }
    }
}

不過,現在我很少會再用上面的方法寫程式,只需祭出幾項法寶即可再化繁為簡。以下是這個案例中所動用的武器:

  • 短小精悍的.NET ORM神器 -- Dapper
    不用建 OracleCommand 搞 OracleParameter,SELECT 即得資料物件陣列,傳入字串或數字陣列可直接轉 WHERE Col IN (...),同一行 INSERT SQL 傳入物件陣列可一次塞入多筆資料... 包你一試成主顧
  • LINQ ToDictionary()
    物件集合直接轉 Dictionary<T1, T2> (同場加映花式應用:配合 GroupBy 快速分組)
  • 字串插值
    用 $"{o.FROM_CURR}-{o.TO_CURR}" 取代 string.Format("{0}-{1}", o.FORM_CURR, o.TO_CURR)
  • LINQ Except() 排除重複項目
  • LINQ Where() 複合條件比對數值不同者
  • LINQ Select() 直接用物件屬性轉訊息字串

結合這些新時代的武器,來看看程式能簡化到什麼程度。

using Dapper;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace CodeCmp
{
    class CoolCoding
    {
        static Dictionary<string, decimal> GetRateDictionary(DateTime date, string cnStr)
        {
            using (OracleConnection cn = new OracleConnection(cnStr))
            {
                //Dapper查詢後直接轉Dictionary<string, decimal>
                return cn.Query(
                        "SELECT * FROM RATE_TABLE WHERE RATE_DATE=:p_date AND RATE_TYPE='E'",
                        new { p_date = date })
                    .ToDictionary(o => $"{o.FROM_CURR}-{o.TO_CURR}", o => (decimal)o.RATE_VALUE);
            }
        }

        public static void Compare(DateTime date)
        {
            var srcDict = GetRateDictionary(date, CommVars.CnnStrDBSrc);
            var locDict = GetRateDictionary(date, CommVars.CnnStrDBLoc);
            List<string> errors = new List<string>();
            //偵測來源無資料
            if (srcDict.Count == 0) errors.Add("來源無資料");
            //偵測本地無資料
            if (locDict.Count == 0) errors.Add("本地無資料");
            //使用LINQ Except找出本地及來源缺資料
            errors.AddRange(srcDict.Keys.Except(locDict.Keys).Select(o => $"本地缺:{o}"));
            errors.AddRange(locDict.Keys.Except(srcDict.Keys).Select(o => $"來源缺:{o}"));
            //使用LINQ Where找出本地與來源數字不同者
            errors.AddRange(
                srcDict.Keys.Where(o => locDict.ContainsKey(o) && locDict[o] != srcDict[o])
                    .Select(o => $"匯率不一致[{o}]:{locDict[o]}(本地) vs {srcDict[o]}(來源)"));
            //若無任何錯誤則判為PASS,否則列出錯誤
            string res = errors.Count == 0 ? "PASS" : ("ERROR:" + string.Join("\n", errors.ToArray()));
            Console.WriteLine(res);
        }
    }
}

看完 Dapper、LINQ 簡化程式碼的威力,不知有沒激起大家想學習與應用它的衝動? 快一起加入用打火機的行列吧!

Use a example to demostrate how LINQ and Dapper simplify code of data tables comparison.


Comments

# by .

習慣了lamdba / linq後 轉用了golang感覺像去了原始人的部落

# by csl

Use "Datatable.Merge" and "Datatable.GetChanges" are also handy enough.

# by Stanely

我也嘗試過LINQ,但令我郤步的是效能部份,因為使用inner join處理幾十萬筆數據量時會比 SQL句的join 來得慢和出現當機狀態,會不會是LINQ語法寫得不好呢 ?

# by Jeffrey

to Stanley, 還是想再強調一下,LINQ並不只能用來查資料庫,處理任何集合物件都可以應用,更能享受它的好。 回到LINQ查DB的問題上,雖然我個人也偏好自己寫SQL(請參考舊文:https://blog.darkthread.net/blog/linq-or-direct-sql/ ) 但我相信LINQ查DB效能不佳的狀況並非無法避免,只是得累積足夠的知識經驗(意思是要知道很多眉角),我選擇直接控制SQL語法而非透過LINQ操作是一種取捨,犠牲跨資料庫優勢,付出更多學習低層SQL的心力去換取對SQL效能的直接掌控。

Post a comment


96 - 2 =