Dapper +Oracle 之 DateTime 注意事項

同事報案,我先前寫的 Dapper 共用程式庫有 Bug,當 WHERE 條件包含日期型別時,將 DateTime 寫入 Oracle Date 欄位,接著用同 DateTime 值做 WHERE 比對,竟找不到剛才寫入的資料。

用以下範例重現問題:

using (var cn = new OracleConnection(csOra))
{
    cn.Open();
    cn.Execute("TRUNCATE TABLE JEFFTEST");
    var idx = 1;
    var dttm = DateTime.Now;
    //將DateTime.Now寫入資料庫
    cn.Execute("INSERT INTO JEFFTEST (IDX,DTTM) VALUES (:idx, :dttm)", new { idx, dttm });
    //重新該時間當WHERE條件比對,筆數為0(登楞!)
    var cnt = cn.Query("SELECT * FROM JEFFTEST WHERE DTTM = :dttm", new { dttm }).Count();
    Console.WriteLine($"Count={cnt}");
}

第一時間懷疑是 Oracle 的 Date 欄位精準度只到秒造成,元件配合 SQL 已使用好一陣子沒遇過此狀況,應是 SQL DateTime 欄位精準度可到秒以下,故同樣做法在 SQL 不會出錯:

using (var cn = new SqlConnection(csSql))
{
    cn.Open();
    cn.Execute("TRUNCATE TABLE JEFFTEST");
    var idx = 1;
    var dttm = DateTime.Now;
    //將DateTime.Now寫入資料庫
    cn.Execute("INSERT INTO JEFFTEST (IDX,DTTM) VALUES (@idx, @dttm)", new { idx, dttm });
    //重新用DateTime.Now當WHERE條件比對
    var cnt = cn.Query("SELECT * FROM JEFFTEST WHERE DTTM = @dttm", new { dttm }).Count();
    //筆數為1,測試無誤
    Console.WriteLine($"Count={cnt}");
    Console.Read();
}

有趣的是,若 Oracle Date 搭配 .NET DateTime 有此陷阱,過去使用 ODP.NET 何以相安無事多年?於是我改用 OracleCommand、OracleParameter 做測試,也不會有日期比對不符的狀況:

using (var cn = new OracleConnection(csOra))
{
    cn.Open();
    var cmd = cn.CreateCommand();
    cmd.CommandText = "TRUNCATE TABLE JEFFTEST";
    cmd.ExecuteNonQuery();
    var dttm = DateTime.Now;
    var timeStr = dttm.ToString("yyyy/MM/dd HH:mm:ss");
    cmd.CommandText = "INSERT INTO JEFFTEST (IDX,DTTM) VALUES (:idx, :dttm)";
    cmd.Parameters.Add("idx", OracleDbType.Decimal).Value = 1;
    cmd.Parameters.Add("dttm", OracleDbType.Date).Value = dttm;
    cmd.ExecuteNonQuery();
 
    cmd.CommandText = "SELECT * FROM JEFFTEST WHERE DTTM = :dttm";
    cmd.Parameters.RemoveAt(0);
    var dr = cmd.ExecuteReader();
    if (dr.Read())
        Console.WriteLine(dr["DTTM"]);
    else
        Console.WriteLine("No Data");
 
    Console.Read();
}

嗯,很好,所以這是 Oracle + Dapper 衍生出的新問題。弄一段程式碼驗證問題及解決方法,說明寫在註解裡,直接看 Code:

using (var cn = new OracleConnection(csOra))
{
    cn.Open();
    cn.Execute("TRUNCATE TABLE JEFFTEST");
    var idx = 1;
    var dttm = DateTime.Now;
    var timeStr = dttm.ToString("yyyy/MM/dd HH:mm:ss");
    //將DateTime.Now寫入資料庫
    cn.Execute("INSERT INTO JEFFTEST (IDX,DTTM) VALUES (:idx, :dttm)", new { idx, dttm });
    //從資料庫取出剛才存入的日期備用
    var dttmInDb = cn.Query<DateTime>("SELECT DTTM FROM JEFFTEST WHERE IDX=1").Single();
    //重新用DateTime.Now當WHERE條件比對,筆數為0
    var cnt = cn.Query("SELECT * FROM JEFFTEST WHERE DTTM = :dttm", new { dttm }).Count();
    Console.WriteLine($"使用原值WHERE比對 Count={cnt}");
    //原因是Oracle DATE型別不包含毫秒
    Console.WriteLine($"DB {dttmInDb:HH:mm:ss.fff} vs C# {dttm:HH:mm:ss.fff}");
    //把毫秒刪除試試
    var dttm2 = dttm.AddMilliseconds(-dttm.Millisecond);
    cnt = cn.Query("SELECT * FROM JEFFTEST WHERE DTTM = :dttm2", new { dttm2 }).Count();
    //登楞!筆數還是0
    Console.WriteLine($"AddMilliseconds修正後比對 Count={cnt}");
    //換個方法,先轉字串再ParseExact轉回日期
    var dttm3 = DateTime.ParseExact(timeStr, "yyyy/MM/dd HH:mm:ss", null);
    cnt = cn.Query("SELECT * FROM JEFFTEST WHERE DTTM = :dttm3", new { dttm3 }).Count();
    Console.WriteLine($"ToString+ParseExact轉換後比對 Count={cnt}");
    //暗!這樣就找到得,為什麼?兩個時間看起來一樣,但CompareTo不同
    Console.WriteLine($"兩種修正結果看似相同 dttm2={dttm2:HH:mm:ss.fff} vs dttm3={dttm3:HH:mm:ss.fff}");
    Console.WriteLine($"dttm2.CompareTo(dttm3)={dttm2.CompareTo(dttm3)}");
    //問題出在Ticks,二者Ticks不同
    Console.WriteLine($"關鍵在ms以下部分:dttm2={dttm2:HH:mm:ss.fffffff} dttm3={dttm3:HH:mm:ss.fffffff}");
    //REF: http://stackoverflow.com/a/153014/4335757
    dttm2 = dttm.Trim(TimeSpan.TicksPerSecond);
    cnt = cn.Query("SELECT * FROM JEFFTEST WHERE DTTM = :dttm2", new { dttm2 }).Count();
    Console.WriteLine($"Trim()取到秒再比對 Count={cnt}");
    Console.Read();
}

執行結果如下:

使用原值WHERE比對 Count=0
DB 21:22:22.000 vs C# 21:22:22.356
AddMilliseconds修正後比對 Count=0
ToString+ParseExact轉換後比對 Count=1
兩種修正結果看似相同 dttm2=21:22:22.000 vs dttm3=21:22:22.000
dttm2.CompareTo(dttm3)=1
關鍵在ms以下部分:dttm2=21:22:22.0004700 dttm3=21:22:22.0000000
Trim()取到秒再比對 Count=1

簡單來說,關鍵在 DateTime.Now 秒之下還有 Milliseconds 跟 Ticks,存入 Oracle Date 時只存到秒,而 Dapper 預設使用 Oracle Timestamp 型別(精準到 0.000001 秒)對應 C# DateTime 型別,實際的 WHERE 條是 Date 與 Timestamp 相比。一開始修正問題,以為 AddMilliseconds(-dateVar.Millisecond) 就夠,忘記更底下還有 Tick,比對還是失敗,改用 ToString 再 ParseExact 反而成功。最後我在 stackoverflow 找到漂亮的 Trim(TimeSpan.TicksPerSecond) 函式完整截去秒以下部分,才算搞定。

關於 Dapper 將 C# DateTime 對應成 Oracle Timestamp 的行為,延伸到另一個更嚴重的議題:DateTime 被轉成 Timestamp 與 Oracle Date 欄位做比較將無法藉由索引加速,導致速度慢上五倍!參考)換句話說,即使截去小數秒結果正確,也將因 Index Scan 效能低落。網路建議解法是改寫 SQL 語法,寫成「WHERE Col = CAST (:dttm AS DATE)」,但我想到一招:其實大可不必花功夫捨去小數秒,因為在 OracleCommand 範例中,DateTime 搭配 OracleDbType.Date 就沒有小數秒差異問題。在一篇點部落文章看到 SqlMapper.AddTypeMap(typeof(DateTime), DbType.Date) 做法,要求 Dapper 將 DateTime 對應成 OracleDbType.Date,就完全不用煩惱小數秒差異囉!這應該是最簡潔有效的做法,經測試驗證,成功!

using (var cn = new OracleConnection(csOra))
{
    //修改Mapping設定
    SqlMapper.AddTypeMap(typeof(DateTime), System.Data.DbType.Date);
 
    cn.Open();
    cn.Execute("TRUNCATE TABLE JEFFTEST");
    var idx = 1;
    var dttm = DateTime.Now;
    //將DateTime.Now寫入資料庫
    cn.Execute("INSERT INTO JEFFTEST (IDX,DTTM) VALUES (:idx, :dttm)", new { idx, dttm });
    //重新用DateTime.Now當WHERE條件比對
    var cnt = cn.Query("SELECT * FROM JEFFTEST WHERE DTTM = :dttm", new { dttm }).Count();
    //筆數為1,測試無誤
    Console.WriteLine($"Count={cnt}");
    Console.Read();
}

【結論】

  • Dapper 搭配 Oracle 時,C# DateTime 預設會對應成 OracleDbType.Timestamp,當資料庫端欄位為 Date 型別,會因小數秒差造成 WHERE 比對不符,另外會因無法套用索引造成查詢效率不彰。
  • 在查詢語法中使月 CAST(:dateParam AS DATE) 轉型可以克服上述問題。
  • 若資料庫以 Date 為主未用到 Timestamp,透過 SqlMapper.AddTypeMap(typeof(DateTime), DbType.Date) 將 C# DateTime 改對應至 DbType.Date,可更巧妙避開問題。
歡迎推文分享:
Published 14 March 2017 11:21 PM 由 Jeffrey
Filed under:
Views: 3,070



意見

沒有意見

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<March 2017>
SunMonTueWedThuFriSat
2627281234
567891011
12131415161718
19202122232425
2627282930311
2345678
 
RSS
創用 CC 授權條款
【廣告】
twMVC

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication