LINQ to EntitiesのWhere文にDateTime.Nowは使わないほうがいい #C#

TL; DR

LINQ to Entities のWhereの中でDateTime.Nowを使わないほうがいいかも。
LINQのステートメント外で予めDateTime.Nowを変数に入れて、それを使うようにするのがいいかも。

それはC#のバグのように思えてしまうちょっと怖い話

EntityFrameworkCoreでLINQ to Entitiesのクエリを書いていたFilunK。

取得するテーブルは以下のような構成。

一時URIテーブル（WK_TEMP_URI)

カラム名	型
TEMP_URI	varchar(40)
EXPIRE_LIMIT	timestamp

他にもいろいろカラムはありますが、それは置いておいて。このテーブルの役割は、ユーザ作成時の2段階認証用のために生成された一時アクセス用URIのパターンと有効期限を扱うもの。EXPIRE_LIMITには期限切れになる日時が登録されている、と。

この中で期限切れになっているTEMP_URIを取得したいと考えたFilunKはこのようなLINQを組み立てました。

    using(var context = new DbContext())
    {
        var queryResult =
            from tempUri in context.WK_TEMP_URI
            where
                tempUri.ExpireLimit < DateTime.Now
            select new
            {
                TempUri = confirm.ConfirmUri,
                ExpireLimit = confirm.ExpireLimit,
            };
    }

さて、このコードを実行してみると、queryResultには1件も入ってきません。
ですが、実際にSQLをデータベースに投げてみると、複数件取れるではありませんか。
where文が怪しいというのは見当が付きます。そこで、tempUri.ExpireLimit < DateTime.Now を　 tempUri.ExpireLimit > DateTime.Now と不等号を逆さにしてみると、あら不思議、期待したデータが取得できているではありませんか？
これはもしや、DateTime.CompareToメソッドのバグなのかしら？　おいおい、怖いなあ……

ということがありました。

なしてこんなことが起こったのか

結論は簡単です。

tempUri.ExpireLimitはCLR上のDateTime（日本のロケール）
DateTime.NowはCLR上ではなく、データベース上でNOW()関数で処理される（少なくともPostgresはこうでした）（DB上のロケール）。

→ ロケールの違いで不等号が期待通りに動作しなかったのです

今回使用しているのはHerokuの Heroku Postgresで、ロケールを確認したところen-USロケールでした。

調査にあたっては、ネットの以下の記事を参考にしました。

LINQ to Entities での DateTime.Now

ここではSQLiteでの事例でしたが、LINQ to EntitiesとDBで扱っているロケールが異なるため、同じことが言えそうです。つまり、 ロケールを合わせないとおかしなことになる と。確かに。

試してみたこと

UTCにしてみる
プログラム上のロケールに合わせる

UTCにしてみる

こんなLINQを試してみました。

    using(var context = new DbContext())
    {
        var queryResult =
            from tempUri in context.WK_TEMP_URI
            where
                System.TimeZoneInfo.ConvertTimeToUtc(confirm.ExpireLimit) < DateTime.Now
            select new
            {
                TempUri = confirm.ConfirmUri,
                ExpireLimit = confirm.ExpireLimit,
            };
    }

where文の confirm.ExpireLimit を System.TimeZoneInfo.ConvertTimeToUtc(confirm.ExpireLimit)にしてみました。本来はen_USロケールにあったTimeZoneInfoでconfirm.ExpireLimitに変換したほうがいいのですが、どの標準時間でやればいいのかわからなかったのでとりあえずUTCにしてみました。

UTCと米国時間ロケールがそもそも不一致という問題はさておき、このやり方はおすすめできないです。LINQとしてまずそうです。というのも、

The LINQ expression 'where (ConvertTimeToUtc([confirm].ExpireLimit) < DateTime.Now)' could not be translated and will be　evaluated locally.

との警告がありました。これはつまり、System.TimeZoneInfo.ConvertTimeToUtc(DateTime)メソッドがSQLに変換できないと、もっと端折って言えば『EXPIRE_LIMITのWHERE文はSQLのWHERE句に変換しないで内部的に使うよ』と言っています。本来は

SELECT
    TEMP_URI,
    EXPIRE_LIMIT
FROM
    WK_TEMP_URI
WHERE
    EXPIRE_LIMIT::TIMESTAMP AT ZONE 'UTC < NOW()

みたいなSQLのつもりが、

SELECT
    TEMP_URI,
    EXPIRE_LIMIT
FROM
    WK_TEMP_URI

で全件取得の上、 CLRの上でEXPIRE_LIMITに対する処理をしてしまいます。期待したSQLにならない時点で使うべきじゃないですね。絞り込みがうまくできないので性能も出ないでしょうし。

プログラム上のロケールに合わせる

なので、安全なのはこちらです。参考にしたサイトでも使用している方法です。

    using(var context = new DbContext())
    {
        var currentTimestamp = DateTime.Now;
        var queryResult =
            from tempUri in context.WK_TEMP_URI
            where
                tempUri.ExpireLimit < currentTimestamp
            select new
            {
                TempUri = confirm.ConfirmUri,
                ExpireLimit = confirm.ExpireLimit,
            };
    }

LINQの外でDateTime.Nowを変数に代入し、LINQの中でDateTime.Nowをとしていた箇所をLINQ外で宣言した変数を使用します。
このLINQでは以下のようなSQLに変換されます。

SELECT
    TEMP_URI,
    EXPIRE_LIMIT
FROM
    WK_TEMP_URI
WHERE
    EXPIRE_LIMIT < @currentTimestamp

プログラム側からEXPIRE_LIMITの絞り込み条件として変数currentTimestampを使用します。このばあい、DB側でNOW()が使用されないので、絞り込みがうまく行かない、という事象は起きなくなります。