数か月前に「EntityFramework CoreでJOINするとArgumentNullExceptionが発生するので生SQLで対応する」という記事を書いたのですが、それと関連する記事です。

環境など


環境とかです。

  • .NET Framework 4.8
  • EntityFramework core 2.2.4
  • Connector/NET for Entity Framework 8.0.13

背景など


前回の記事でEntityFramework Coreで上手いことJOINできないので生SQL(FromSql)でJOINしたということを書きました。このプログラムを本番で使うことが何度かあったのですが、どうも実行中にDBの負荷があがってしまっているようで、データベースのログを調べたところ、副問い合わせが重いであろうということがわかりました。

「副問い合わせするようなSQL書いたかなぁ??」と思ってコードを確認してもそんな実装はしていなかったのですが、しかし開発環境でデバッグで実行SQLを出力すると該当コードの箇所で確かに副問い合わせが行われてしまうという困った問題にあたってしまいました。

原因と解決方法


前回の記事から流用しますが、C#側は次のようなBlogAuthorというDBのテーブルとマップされた2つのクラスと、これらをINNER JOINして取得した結果を格納するためのモデルとそれ用にDbContextを継承したクラスResultがあります。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
[Table("blog")]
public class Blog
{
[Column("id")]
public int Id { get; set; }

[Column("author_id")]
public int AuthorId { get; set; }

[Column("url")]
public string Url { get; set; }

[Column("title")]
public string Title { get; set; }

[Column("content")]
public string Content { get; set; }

[Column("created_at")]
public DateTime CreatedAt { get; set; }

[Column("updated_at")]
public DateTime? UpdatedAt { get; set; }
}

[Table("author")]
public class Author
{
[Column("id")]
public int Id { get; set; }

[Column("name")]
public string Name { get; set; }
}

public class ResultDbContext: DbContext
{
Public DbSet<Result> Result { get; set;}
}

public class Result
{
[Column("name")]
public int AuthorName { get; set; }

[Column("title")]
public string Title { get; set; }

[Column("content")]
public string Content { get; set; }
}

前回のサンプルではBlogAuthorをJOINしてResultに格納する生SQLのコードを下記のように書いていました。

1
2
3
4
5
6
7
8
9
10
11
using (var resultDbContext = new ResultDbContext())
{
var query =
resultDbContext.Result.FromSql(
"SELECT author.name, blog.title, blog.content" +
" FROM blog" +
" INNER JOIN author ON blog.author_id = author.id" +
" WHERE blog.updated_at is null"
);
return query.ToList();
}

ですが、実際のプロダクションでは全レコードを一気に取得できない(処理対象のレコードが多すぎる)のでLIMIT句に相当するものを書いていました。.Take(x)です。xは一度に取得する数値でこの辺は適当な値を想像してください。以降のサンプルは100とします。

下記のような感じです。

1
2
3
4
5
6
7
8
9
10
11
using (var resultDbContext = new ResultDbContext())
{
var query =
resultDbContext.Result.FromSql(
"SELECT author.name, blog.title, blog.content" +
" FROM blog" +
" INNER JOIN author ON blog.author_id = author.id" +
" WHERE blog.updated_at is null"
);
return query.Take(100).ToList();
}

今までの文脈からもうこの時点でわかると思うのですが、この.Take(x)を書くとなぜか副問い合わせが行われてしまいます。おそらく.NETのLINQに詳しい人なら理由がわかるんじゃないかという気もする[1]んですが、私はそこまで詳しくないのでなぜそうなるのかまではわかりません。ので、以降も試した結果だけを淡々と書いていきます。

上記をSQLのログ出力を有効にして(DBではなくてEntityFramework Core側のログ出力です)実行してみると下記のようなログが出力されます。

1
2
3
4
5
6
7
8
9
10
11
12
Microsoft.EntityFrameworkCore.Database.Comman[20101]
Executed DbCommand (23ms) [Parameters=[@__p_1='100'], CommandType='Text', CommandTimeout='30']

SELECT `s`.`name`, `s`.`title`, `s`.`content`
FROM (
SELECT author.name, blog.title, blog.content
FROM blog
INNER JOIN author
ON blog.author_id = author.id
WHERE blog.updated_at is null
) as `s`
LIMIT @__p_1

なんと信じられないことに不要な副問い合わせが行われてしまっています。原因がわからないうえに、コーヒの大量摂取も相まって頭が痛くなったのですが、サンプルコードを見たところ、メソッドチェーンですぐにToList()を書いているので、試しに下記のように書き換えてみたところ上手くいきました。

1
2
3
4
5
6
7
8
9
using (var resultDbContext = new ResultDbContext())
{
return resultDbContext.Result.FromSql(
"SELECT author.name, blog.title, blog.content" +
" FROM blog" +
" INNER JOIN author ON blog.author_id = author.id" +
" WHERE blog.updated_at is null"
).ToList(); // ここでToListしないとだめ。 .Take(100).ToList() でもダメ
}

結果は下記の通りで意図したSQLが実行されました。なお、コードにコメントしている通り.Take(100).ToList()と書いた場合でも副問い合わせが発生します。

1
2
3
4
5
6
7
8
Microsoft.EntityFrameworkCore.Database.Comman[20101]
Executed DbCommand (23ms) [CommandType='Text', CommandTimeout='30']

SELECT author.name, blog.title, blog.content
FROM blog
INNER JOIN author
ON blog.author_id = author.id
WHERE blog.updated_at is null

いちおう、これで解決したかのように思えるのですが、一つ肝心なことを忘れていて、そうです。当初やりたいLIMITができてないんですね。

どうしたものかしばらく考えたのですが、よくよく考えると既に生SQLで書いているわけでLIMIT句も生SQLで書けばよいという結論に至りました。

1
2
3
4
5
6
7
8
9
10
11
using (var resultDbContext = new ResultDbContext())
{
var take = 100;
return resultDbContext.Result.FromSql(
"SELECT author.name, blog.title, blog.content" +
" FROM blog" +
" INNER JOIN author ON blog.author_id = author.id" +
" WHERE blog.updated_at is null" +
$" LIMIT {take}"
).ToList();
}

上記はパラメータで指定できるようにしています(前回の記事に記載している通りこのプログラムは外部から入力があるわけではない[2]のでSQLインジェクション攻撃されることはない)

これで実行するとちゃんと意図したSQLが実行されるのが確認できました。

1
2
3
4
5
6
7
8
Microsoft.EntityFrameworkCore.Database.Comman[20101]
Executed DbCommand (23ms) [CommandType='Text', CommandTimeout='30']

SELECT author.name, blog.title, blog.content
FROM blog
INNER JOIN author
ON blog.author_id = author.id
WHERE blog.updated_at is null LIMIT 100

開発時も実行されるSQLは確認していたのですが「副問い合わせするSQLを書いていない」ということによる思い込みと華麗な目フィルターにより見落としてしまっていたようで大変厳しい。

おわり。


  1. たぶん遅延評価が関係してるとおもうが説明しろとまで言われたらできない、もちろんEntityFrameworkCoreの不具合の可能性もある ↩︎

  2. ので、そもそもハードコードでもいいと思う ↩︎