Entity Framework Core と SQLite で日時が検索にヒットしない問題と対策

今個人的に作っているアプリで、Entity Framwork Core と SQLite を使っています。日時を文字列型で管理してみたところ、なぜか検索にヒットしない問題にぶち当たってしまいました。その時のことを忘れないようにメモしておきます。

環境

• パッケージ：
  • Microsoft.EntityFrameworkCore.Sqlite 9.0.1

前提

SQLite にはカラムのデータ型に日時型がありません。よって、文字列型（TEXT）や数値型（INTEGER）で日時を表現することになります。

数値型（UNIX 時間）で保持すると値が明確なので、いろいろな問題は起こりにくいのでしょうが、人間（私）がデータを目視したときに、正直わかりにくいな・・・と感じます。

そこで、今個人的に作っているアプリでは、日時を文字列型で保持することにしました。精度はミリ秒までとしたいので、書式は YYYY-MM-DD HH:MM:SS.fff としました。

問題発生：ミリ秒部分が 0 のデータが検索にヒットしない

birth_date_time が “2024-12-24 13:14:15.000” のデータを取得しようと、次の通り実装したのですが、不思議なことに検索にヒットしてくれませんでした。

// 2024-12-24 13:14:15.000 生まれ（ポチ）を取得したい
var birthDateTime = new DateTime(2024, 12, 24, 13, 14, 15);
var result = animalDbContext.Dogs.Where(dog => dog.BirthDateTime == birthDateTime);

これが、次のようにミリ秒部分が 0 ではないデータであれば、想定通り検索にヒットします。

// 2024-12-24 13:14:15.001 生まれ（シロ）を取得したい
var birthDateTime = new DateTime(2024, 12, 24, 13, 14, 15, 1);
var result = animalDbContext.Dogs.Where(dog => dog.BirthDateTime == birthDateTime);

詳細は不明なのですが、ミリ秒部分の値が 0 の場合、内部的に “2024-12-24 13:14:15.000” ではなく “2024-12-24 13:14:15″（ミリ秒部分が無い）で検索しているような動きだと感じました。

実際、データを次のように変更すれば、ポチが検索にヒットするようになりました。

しかし、レコードによってミリ秒部分があったりなかったりするのは、正直かなり気持ち悪いです。これは避けたい感じです・・・。

回避策

SQLite とのデータのやり取りをする際の値変換方法を指定することで、問題を回避できました。

DbContext.OnModelCreating メソッド内で、対象のプロパティに対して、PropertyBuilder<TProperty>.HasConversion メソッドを呼び出します。

entity.Property(dog => dog.BirthDateTime)
    .HasColumnName("birth_date_time")
    .HasConversion(arg => this.ConvertToProvider(arg), arg => this.ConvertFromProvider(arg));

// ...（省略）...

private string ConvertToProvider(DateTime value)
{
    // ここがポイント。ミリ秒を常に 3 桁で扱うように指定
    return value.ToString("yyyy-MM-dd HH:mm:ss.fff");
}

private DateTime ConvertFromProvider(string value)
{
    return DateTime.ParseExact(value, "yyyy-MM-dd HH:mm:ss.fff", null);
}

そもそもミリ秒までは不要という場合は

もし「ミリ秒の精度は必要が無く、秒までで充分」という場合は、単に “YYYY-MM-DD HH:MM:SS” 形式で格納するだけで事足ります。

参考

• Time Values – SQLite
  • 日時を文字列や数値で表現する際の書式が記載されています。
• SQLite : Datetime equality comparison not working (in memory) – GitHub dotnet/efcore
  • 似たような問題に触れられていました。