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效能的直接掌控。

# by kyle

新手請教大大,目前專案存取資料方式是將SQL資料存放在DataSet(內有多個TABLE),然後Writeml成xml檔,等下次要用的時候再ReadXmlnew 用 new DataSet()去接,想請問有甚麼好方法轉成強型別以利查詢?還是有其他處理資料的建議? 註:依資料變動性,有分成兩個xml檔,一個是不會變動的(定義標準格式),一個是會變動的(user選取的資料)

# by kyle

再附註: 目前是在開發VSTO,所以操作只會在公司內部運作,利用EXCEL對資料庫做CRUD,並非網頁開發

# by Jeffrey

to kyle, 我會選擇用 Json.NET SerializeObject() 把 DataTable 轉成 JSON 陣列,之後可以用 https://blog.darkthread.net/blog/json-linq-wo-declare-class/ 這篇介紹的做法查詢。如果想轉成強型別,Visual Studio 有個 Paste Special 功能可以分析 JSON 轉成 .NET Class https://dotblogs.com.tw/stanley14/2016/06/02/223055 ,很好用,大推。

# by kyle

to Jeffery 大大,我試做了您說的第一種方法,並與table.Select做比較如下: jsonstr = JsonConvert.SerializeObject(dataTable, Formatting.Indented); dynamic[] data = JsonConvert.DeserializeObject<JObject[]>(jsonstr); var dict = data .Where(.... DataRow[] _dr = dataTable.Select(" date> ' 2019/05/30' "); 問題一:結果上table.Select()似乎快了些(可能我比較的方式不對),是否因為LINQ查詢最後需要轉型成list<T>等形式才能便於後續操作的關係? 還是說 問題二:若是這樣,有方式是不需要DataTable(好像多繞一圈的感覺),而是Xml -> JSON (JSON ->Xml )嗎?

# by Jeffrey

to kyle, 關於第1點,如果你懷疑是強型別轉換造成效能不佳,我建議你用上次回答提到 Paste Special的方式將DataTable轉成List<PasteSpecial轉成的類別>再試看看是否有改善。 至於DataSet/XML的問題,如果你在C#端打算走強型別,建議將SQL查詢結果直接存成JSON更省事。

# by aska

linq 不好直覺式的debug,由其遇到複雜的sql語句,無法知道linq為何會有這樣的結果 反觀傳統的sql,可以直接貼出來執行,馬上驗證

Post a comment