Posted at

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