「LINQ to Entities」カテゴリーアーカイブ

LINQ to EntitiesのWhereとFirstに別々に条件を与えると並び順がおかしくなる

コメントを残す
こんなコードを書くほうが悪いわけだけど、
var contract = entities.Contract
    .Where(c => c.User.id == user.id)
    .OrderByDescending(c => c.applied_from).ThenByDescending(c => c.created_at).ThenByDescending(c => c.id)
    .FirstOrDefault(c => c.applied_from < dtTo);  // (1)
var contract = entities.Contract
    .Where(c => c.User.id == user.id && c.applied_from < dtTo)
    .OrderByDescending(c => c.applied_from).ThenByDescending(c => c.created_at).ThenByDescending(c => c.id)
    .FirstOrDefault();   // (2)
これらはいずれも以下のSQLの結果と同じになることを期待して書いたものです。
SELECT * FROM contracts
WHERE user_id = 10 AND applied_from < '2009-12-01 00:00:00'
ORDER BY applied_from desc, created_at desc, id desc
LIMIT 1
しかし、両者の返すレコードは異なります。
OrderByDescendingで並べ替えた先頭から一件とりたいのですが、(1)の方はまれに意図したレコードと違うものが返って来ることがあります。なぜ例外がこんなに少ないのか分かりませんが、とにかく保証はできないようです。しかし、以下の(3)は（2）と同じ結果になります。
var contract = entities.Contract
    .Where(c => c.User.id == user.id)
    .OrderByDescending(c => c.applied_from).ThenByDescending(c => c.created_at).ThenByDescending(c => c.id)
    .ToArray()
    .FirstOrDefault(c => c.applied_from < dtTo);  // (3)
こういう場合、最終的にどんなSQLが実行されているのか知りたくなるわけですが、その方法がありません。
ToTraceString()で見られる未完成のSQLでは、最終的なクエリは見えません。
このへんが、Entity Frameworkが駄目なところです。いや、System.Data.SQLiteの問題か？
今回は関係ないですが、ObjectQueryを使い回しする場合には、FirstやToArrayの直前までを変数に格納して、Firstの条件部分だけを変えるという使い方をしていることがあったんですが、これではまずいですね。。。

Default Isolation Level

コメントを残す
Entity Framework+SQLiteで、IsolationLevelを設定する方法が分かりました。

接続のプロパティ、または設定ファイルを直接編集して、
connectionStringのdata sourceに、default isolation level=ReadCommittedを追加。

やっぱり単純なことだった。

しかし、実際の挙動を見た感じでは、テーブル単位でロックがかかるみたいです。
同じテーブルで読み込みを実行している間は、database is lockedエラーが発生しました。

別のテーブルであれば、保存に成功します。

まあ、それなら今までやってきた次善策も全部が無駄になったわけではない。とはいえ、複雑な気分。
結局のところ、読み込み中の保存は別途キャッシュしないといけないということか。

IsolationLevelとしては、他も一応選べるようだったので、もしかしたらSerializable/ReadComitted以外も使えるものがあるかもしれません。

dotConnect for SQLiteのお試し版を試用中。設定のオプションがSQLite.NETより多く、ReadUncomitted=falseがデフォルトなことから、ReadComittedがデフォルトということだろうか。しかし、SaveChangesでエラーが出て使えない。値段が高いわりにはいろいろと面倒な感じ。

Entity Frameworkを使ったプロジェクトを一通り終えて

コメントを残す

しょっちゅう更新しようと考えていたブログでしたが、途中からそんな余裕もなく、
3ヶ月休みなしで出勤しつつ、極めつけは客先（宿泊施設）に3日間滞在しつつようやく終えることができました。

さて、ほとんどの問題は解決することができたものの、いまだ２つだけ心残りがあります。

ひとつは、MVC的な方法を適用したとき、バリデーションがモデル内で完結できない点です。
NOT NULLな属性の入力値がNULLであることの検証は、コントローラにしかできません。
まあ、全部Nullableにすればいいのかもしれませんが。

もうひとつは、トランザクションがフレームワークまかせになっている点です。

LINQ to Entities + SQLiteでの話なので、他のサーバクライアント型のDBを使った場合は問題ないのかもしれません。

SQLiteの場合、IsolationLevelはSerializableかReadCommittedが使えるらしいですが、デフォルトはSerializableになっています。

マルチスレッドアプリケーションで、バックグラウンドで時間のかかる帳票作成などしていると、場合によっては並行して書き込み処理が発生することがあります。
というか、今回のアプリではいろんなところからパケットを受信してはDBに保存するので、読み込みと書き込みがバッティングするのは普通に起こっていました。

その場合に読み込み処理の方が、Serializableでトランザクションを実行していると、SaveChanges()の方がタイムアウト待ちになって、しかも読み込み処理の方も影響を受けてどちらも無意味に遅くなります。

かといって、ReadCommittedを使ってみると、なぜかSaveChanges()メソッドに失敗して変更が保存されません。

結局、並行処理はあきらめて、読み込み処理中には保存処理は全て拒否して、
別途ファイルに保存しておいたイベントデータを暇なときに読み込むという方法をとるしかありませんでした。

それから、これは解決することができたんですが、メソッドチェーンでWhereの条件を追加しながら結果を絞り込む方法を使うときは、クエリの遅延実行が働くようになっているようなんですが、これを下手に使うとループや属性へのアクセスごとに接続が実行されて、非常に遅くなります。

条件が少ない最初の絞込みのときに、ToArray()かToList()などを使って、メモリに展開しておくのがよいようです。
クエリの複雑さにもよりますが、私のLet’s Note Y2 PentiumM1.5GHz+768MBのPCでは30倍以上の差が出ました。

これで、帳票作成のクエリにかかる時間が最大1分程度になったため、書き込み処理をブロックしてもそれほど業務に影響はないかな、と思ってとりあえず妥協しました。ただ、たまたま今回のプロジェクトではそれでよかったですが、読み込み中はぜんぜん書き込めないというのでは問題になることが多いのではないかなあ。

もっと直接的にコマンドを発行する方法もあるようなので、今度はそれで検討してみようか。

時期バージョンのVisual StudioではIronRubyが使えるらしいから、そのときはRailsのActiveRecordが使えるといいなあ。

LINQ to Entities + SQLiteでCAST(expression AS type)

コメントを残す

前回のエントリにて、アルファベットと数字の混在する部屋番号というのを扱ったけれど、結局、文字部分を削除しても問題ないということになったので、それはまあ、良くなった。実はいろいろ調べてAlphanumeric sortというのがあると知ったのだが、まあ、いろいろ役にたつことだろう。

さて、それならわざわざ文字列にしなくてもよいだろうということで、INTEGERに変更した。それでずいぶん無駄なロジックやキャストなんかが減って、すっきりした。

しかし、そこに新たな要求が来た。

  1. 部屋番号の範囲を指定して、複数の居住者を検索する。
  2. 部屋番号を文字列とみなして前方一致検索する。

1. の場合、INTEGERなので問題ない。

2. の場合、WHERE句で文字列にキャストしないとLIKEが使えない。クライアントサイドでの絞込みは馬鹿らしい。

正直こんなもん、SQLiteなら文字列も数値も同じだから、LIKEが使えていいんじゃないのかと思うけど、LINQ to Entitiesではそうもいかない。ObjectQuery<T>のWhereは条件に文字列とプレースホルダが使えるから、その線で探してみた。CASTを使えばよいらしい。Googleで調べるのに2時間もかかったよ。はー疲れた。

ObjectQuery<User> userQuery = entities.User.Include("Room")
  .Where("CAST(it.Room.room_no AS System.String) LIKE @keyRoom",
  new ObjectParameter("keyRoom", keyRoom + "%"));

CAST (expr AS type) というのがSQLiteにもあって、それは結構すぐ見つかったけど、typeのところがC#の型になるとは思い浮かばず、TEXTか？VARCHAR(4)とかか？と意味不明なエラーに苦しんでいました。System.Data.SQLiteのせいじゃないかと思って、dotConnect for SQLiteを自腹で買おうか真剣に悩んで見つけたサンプルをCASTでgrepしたら見つかりました。

Download dotConnect for SQLite

LINQ to Entitiesでソート

コメントを残す

部屋番号でのソートが必要になった。この部屋番号というやつが曲者で、VARCHAR(4)のカラムになっていて、頭にアルファベットのプレフィックスがつく場合とつかない場合がある。できればこうしたい。

101
102
205
1500
2020
A103
A104
A1501
A2021

普通にソートするとこうはならない。

entities.Room
.OrderBy(r => r.room_no)
.Select(r => r);

101
102
1500
2020
205
A103
A104
A1501
A2021

プレフィックスがないもので考えてみれば、

entities.Room
.OrderBy(r => int.Parse(r.room_no))
.Select(r => r);

とかやりたいところだが、このようにエラーになってしまう。

Where句にはstringで条件を記述できたが、OrderByではできないようなので、SQLiteの関数を直接使うこともできない。

LINQ to Entitiesの問題なのか、SQLite.NETの問題なのかというと、両方の問題な気がする。

LINQ Method cannot be translated into a store expression. | Arnold Matusz's Blog

解決法はToList()を使ってリストに変換した後でOrderByすればクライアントサイドでのソートになるので、上記の問題は発生しないようである。

entities.Room.ToList()
	.OrderBy(r => int.Parse(r.room_no))
	.Select(r => r.room_no);

次期バージョンではできるようになるのだろうか。

LINQ to Entities + SQLite.NET に TransactionScope が・・・・使えない？？？

コメントを残す

こちらの方の資料を参考にしてやってみた。

ADO.NET Entity Framework vNext

ソースコード：

SaveChangesをどうしても越えられない。

自分自身をロックしてしまうとは・・・

やっぱ、SQL Serverしか対応してないのかな。

ObjectQueryのStartsWith/Contains/EndsWithはLIKEと違う。

コメントを残す

LINQ to Entitiesは落とし穴の宝庫。

DataGridViewで顧客リストの前方一致による絞込みをするため、LIKEを使いたかった。調べてみると、StartsWithでできるらしい。

.Where(u => u.name_kana.StartsWith(tbxKeyNameKana.Text))

実際にやってみると、ASCII文字列の場合は使えるが、マルチバイト文字列の場合は常にどれにもマッチしないという結果になる。

ObjectQuery.ToTraceString()を使ってSQLを見ると、こんなWHERE句になっていた。

WHERE (CHARINDEX(@p__linq__4, [Extent3].[name_kana])) = 1

よくわからんが、素直にLIKEを出してくれ。

SQLに近い書き方もできるようだ。それでこうした。

.Where("it.name_kana LIKE @keyNameKana", new ObjectParameter("keyNameKana", tbxKeyNameKana.Text.Replace("%", @"%")+"%"));

ところで、SQL Serverならばこういう選択肢もあるのかもしれない（未確認）。

.Where(u => u.name_kana.Substring(0, tbxKeyNameKana.Text.Length) == tbxKeyNameKana.Text)

SQLiteではSubstringなんてないと言われてできなかった。

現状のEFには不満がつのる。.NET Framework 4.0では大幅に強化されるというが、VS2008のままで対応できるのかなー。

参考：

Anyway to get at ToTraceString form LINQ to Entities query?