Help us understand the problem. What is going on with this article?

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

    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.ExpireLimitSystem.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()が使用されないので、絞り込みがうまく行かない、という事象は起きなくなります。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした