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

## 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を組み立てました。

``` C#
    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](https://jp.heroku.com/postgres)で、ロケールを確認したところen-USロケールでした。

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

[LINQ to Entities での DateTime.Now](https://mikazuki.hatenablog.jp/entry/2016/08/01/221927)

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

### 試してみたこと

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

#### UTCにしてみる

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

``` C#
    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句に変換しないで内部的に使うよ』と言っています。本来は

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

みたいなSQLのつもりが、

``` SQL
SELECT
    TEMP_URI,
    EXPIRE_LIMIT
FROM
    WK_TEMP_URI
```

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

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

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

``` C#
    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に変換されます。

``` SQL
SELECT
    TEMP_URI,
    EXPIRE_LIMIT
FROM
    WK_TEMP_URI
WHERE
    EXPIRE_LIMIT < @currentTimestamp
```

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