はじめに
以下の記事で、.NET Core の EntityFramework Core 2.1で GROUP BY 句がインメモリではなく、SQLでデータベース上で集計されるようになったということで検証してみました。
EF Core 2.1 に対する最初の大きな追加は、GroupBy LINQオペレーターがGROUP BY句を使ったSQLに変換されることである。このサポートが欠けていることは、EF Core 2.0の大きな欠落だと考えられている。
検証環境は、Mac (macOS High Sierra 10.13.5)、PostgreSQL 10.3 になります。
なお、検証結果が予想に反したため、MySQL 5.7.12 での追加検証を行っています。
検証方針
検証方針は以下の通り。
- コンソールアプリケーションで、EntiryFramework Core 2.0 と 2.1 で GROUP BY句を使用したLINQを実行する
- 実行されたSQL比較し検証する
- 検証を適切に行うために、コードファーストではなくデータベースファーストで共通のデータベースを使用する
- 検証結果の考察をする
PostgreSQL で検証用データベースの作成
PostgreSQL で以下の SQL を実行して、検証用データベースを作成します。
CREATE DATABASE consolegroupby; CREATE TABLE Result ( Id SERIAL PRIMARY KEY, Name text, Subj text, Point int ); INSERT INTO Result (Name, Subj, Point) values ('山田', '国語', 80); INSERT INTO Result (Name, Subj, Point) values ('山田', '数学', 60); INSERT INTO Result (Name, Subj, Point) values ('山田', '英語', 70); INSERT INTO Result (Name, Subj, Point) values ('佐藤', '国語', 55); INSERT INTO Result (Name, Subj, Point) values ('佐藤', '数学', 70); INSERT INTO Result (Name, Subj, Point) values ('佐藤', '英語', 60); INSERT INTO Result (Name, Subj, Point) values ('田中', '国語', 55); INSERT INTO Result (Name, Subj, Point) values ('田中', '数学', 70); INSERT INTO Result (Name, Subj, Point) values ('田中', '英語', 60);
GROUP BY句の検証は以下の SQL を実行して行います。
select Name, Sum(Point) from Result group by Name; name | sum ------+----- 佐藤 | 185 田中 | 185 山田 | 210
EntityFramework 2.0 での検証
.NET Core 2.0 / EntiryFramework 2.0 の環境でコンソールアプリケーションを作成して、SQL を検証します。
なお、実行環境の情報は以下のようになります。検証のためにあえて古い環境を使用しています。
$ dotnet --info .NET コマンド ライン ツール (2.0.3) Product Information: Version: 2.0.3 Commit SHA-1 hash: eb1d5ee318 Runtime Environment: OS Name: Mac OS X OS Version: 10.13 OS Platform: Darwin RID: osx.10.13-x64 Base Path: /usr/local/share/dotnet/sdk/2.0.3/ Microsoft .NET Core Shared Framework Host Version : 2.0.7 Build : 2d61d0b043915bc948ebf98836fefe9ba942be11
検証用コンソールアプリケーションの作成
以下のコマンドで検証用コンソールアプリケーションを作成します。
$ dotnet new console -o TestGroupBy
必要なパッケージをインストールインストールします。
$ cd TestGroupBy
$ dotnet add package Microsoft.EntityFrameworkCore.Tools.DotNet
$ dotnet add package Microsoft.EntityFrameworkCore.Design
$ dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
$ dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL.Design
$ dotnet add package Microsoft.Extensions.Logging.Console
$ dotnet restore
検証時点（2018/06/16）だと、ビルド時に以下のエラーが発生したので対処します。
[fail]: OmniSharp.MSBuild.ProjectLoader Detected package downgrade: Microsoft.NETCore.App from 2.0.7 to 2.0.0. Reference the package directly from the project to select a different version. TestGroupBy -> Microsoft.EntityFrameworkCore.Tools.DotNet 2.0.3 -> Microsoft.NETCore.App (>= 2.0.7) TestGroupBy -> Microsoft.NETCore.App (>= 2.0.0)
TestGroupBy.csproj に記述されている Microsoft.EntityFrameworkCore.Tools.DotNet のバージョンを、2.0.3から2.0.0に修正して保存し、dotnet restore を実行します。
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />
また、このままだとデータベースからクラスを生成する際に以下のエラーが発生してしまうので対処しておきます。
コマンド "dotnet-ef" に一致する実行可能ファイルが見つかりません
TestGroupBy.csproj に以下の記述をのすぐ上に追加して、dotnet restore を実行します。
<ItemGroup> <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" /> </ItemGroup>
検証用コンソールアプリケーションにデータベース定義を取り込む
下記コマンドを実行して、検証用コンソールアプリケーションにデータベース定義を取り込みます。ユーザー名、パスワードは適宜変更してください。
$ dotnet ef dbcontext scaffold "Server=localhost;Database=consolegroupby;Username=username;Password=password;" Npgsql.EntityFrameworkCore.PostgreSQL -o Models -f
さらに、SQL 実行ログを出力するために自動生成された Models/consolegroupbyContext.cs を以下のように書き換えます。ここでも、ユーザー名とパスワードは適宜環境に合わせてください。
using System; using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Metadata; using Microsoft.Extensions.Logging; using Microsoft.Extensions.Logging.Console; namespace TestGroupBy.Models { public partial class consolegroupbyContext : DbContext { public consolegroupbyContext() { } public consolegroupbyContext(DbContextOptions<consolegroupbyContext> options) : base(options) { } public virtual DbSet<Result> Result { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (!optionsBuilder.IsConfigured) { optionsBuilder .UseLoggerFactory(MyLoggerFactory) .UseNpgsql("Server=localhost;Database=consolegroupby;Username=username;Password=password;"); } } public static readonly LoggerFactory MyLoggerFactory = new LoggerFactory(new[] { new ConsoleLoggerProvider((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information, true) }); protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Result>(entity => { entity.ToTable("result"); entity.Property(e => e.Id).HasColumnName("id"); entity.Property(e => e.Name).HasColumnName("name"); entity.Property(e => e.Point).HasColumnName("point"); entity.Property(e => e.Subj).HasColumnName("subj"); }); } } }
これでようやく検証するための準備ができました。検証するために Program.cs を以下のように書き換えます。この際、using System.Linq; を追加し忘れると GroupByが LINQ で使用できないのでご注意ください。私は何時間もそれにハマりました。💦
using System; using System.Linq; using TestGroupBy.Models; namespace TestGroupBy { class Program { static void Main(string[] args) { using(var db = new consolegroupbyContext()) { var query = db.Result .GroupBy(x => x.Name) .Select(x => new { Name = x.Key, Sum = x.Sum(y => y.Point)}); foreach (var result in query) { Console.WriteLine($"{result.Name}:{result.Sum}"); } } } } }
では、dotnet run コマンドで検証用アプリケーションを実行します。
実行結果は、以下のようになりました。
info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT x.name AS "Name", SUM(x.point) AS "Sum" FROM result AS x GROUP BY x.name 佐藤:185 田中:185 山田:210
おや？処理結果は正しいのですが、GROUP BY句が SQL として実行されていますね。
InfoQの情報が間違っていたのでしょうか？
推測ですが、PostgreSQL 側のモジュールが EntityFramework Core より早く GROUP BY句に対応していたのではないかと思います。
実際、ネット上にも GROUP BY句が LINQ では使用できるのに SQL では GROUP BY句にならないという情報がありました(参考１、参考２)。
EntityFramework 2.1 での検証
.NET Core 2.1/Entity Framework 2.1 の環境にするためには、最新の SDK とランタイムをこちらのサイトよりダウンロードしてインストールするだけです。
最新の実行環境は以下のようになります。
$ dotnet --info .NET Core SDK (global.json を反映): Version: 2.1.300 Commit: adab45bf0c ランタイム環境: OS Name: Mac OS X OS Version: 10.13 OS Platform: Darwin RID: osx.10.13-x64 Base Path: /usr/local/share/dotnet/sdk/2.1.300/ Host (useful for support): Version: 2.1.0 Commit: caa7b7e2ba .NET Core SDKs installed: 2.0.3 [/usr/local/share/dotnet/sdk] 2.1.300 [/usr/local/share/dotnet/sdk] .NET Core runtimes installed: Microsoft.AspNetCore.All 2.1.0 [/usr/local/share/dotnet/shared/Microsoft.AspNetCore.All] Microsoft.AspNetCore.App 2.1.0 [/usr/local/share/dotnet/shared/Microsoft.AspNetCore.App] Microsoft.NETCore.App 2.0.3 [/usr/local/share/dotnet/shared/Microsoft.NETCore.App] Microsoft.NETCore.App 2.0.7 [/usr/local/share/dotnet/shared/Microsoft.NETCore.App] Microsoft.NETCore.App 2.1.0 [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]
検証用コンソールアプリケーションの作成
検証用コンソールアプリケーションは、基本的に EntityFramework 2.0 と同様に行います。但し、エラーなどは発生しないため、エラー対応は不要です。
その上で、dotnet run を実行した結果は以下のようになりました。
info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (12ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT x.name AS "Name", SUM(x.point) AS "Sum" FROM result AS x GROUP BY x.name 佐藤:185 田中:185 山田:210
はい。EntityFramework Core 2.0のときと同様ですね。まあ、これでも検証はできたといえなくもないですが、折角なので MySQL で追加検証をしたいと思います。
但し、MySQL はコードファーストでしか検証できないためご了承ください。
MySQLでの追加検証
MySQLの追加検証アプリケーションの作成方法やコードは割愛します。
その代りに、GitHubにソースコードをアップしましたので必要な方は参照してください。
EntityFramework 2.0 の検証アプリ
EntityFramework 2.1 の検証アプリ
検証結果は以下のようになりました。
・EntityFramework Core 2.0 の実行結果
LINQ で GROUP BY句を使用しているのに、実際の SQL は ORDER BY 句でソートを行い全件メモリに読み込んで集計をしていることが分かります。
info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (399ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT `x0`.`Id`, `x0`.`Name`, `x0`.`Point`, `x0`.`Subj` FROM `Result` AS `x0` ORDER BY `x0`.`Name` :0 佐藤:185 山田:210 田中:185
・EntityFramework Core 2.1 の実行結果
MySQL のライブラリが EntityFramework 2.1に全く追随できておらず、メソッドが実装されていないというありさまです。2ヶ月も更新がないので当然といえば当然ですが。個人的には、他の件も含めて Oracle は .NET Core にやる気がないと判断しています。
Method not found: 'Void Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory..ctor(Microsoft.EntityFrameworkCore.Diagnostics.IDiagnosticsLogger`1<Command>, Microsoft.EntityFrameworkCore.Storage.IRelationalTypeMapper)'.
検証結果の考察
EntityFramework 2.0 が LINQ の GROUP BY句で実行されない仕様であったということは MySQL の実装やネットの情報で確認ができました。PostgreSQL では EntityFramework 2.0 から GROUP BY句に対応していたようですがこれは例外でしょう。
EntityFramework 2.1 では MySQL は実装が追いついていないというありさまで検証すらできませんでした。PostgreSQLは EntityFramework 2.0 と同様に GROUP BY句に対応しています。
これにより、InfoQ の情報のように、EntityFramework 2.0 では LINQ は基本的に GROUP BY句には対応しておらず、EntityFramework 2.1 で GROUP BY句に対応したと考えて問題ないでしょう。
しかし、Microsoft が開発を急いでいるとはいえ、GROUP BY 句という基本的な機能が実装されてなかったことは少々驚きました。軽量なO/Rマッパー的な存在にしたいために機能を厳選しているのかもしれませんが、実装の優先順位をユーザーの使用状況に合わせて欲しいところです。
GROUP BY 句が SQL で実装できていないことを知らずに、何十万件というデータの集計を実行した日にはサーバーがダウンしてしまいますからね。
Windows / Linux / Mac で .NET が動作するという .NET Core の思想は素晴らしく、大変期待しているので Microsoft には頑張ってほしいですね。
それにしても以下の記事でも言及しましたが、MySQL は .NET Core に対してやる気がなさすぎますね。やはり、.NET Core を選択するのであれば、MySQL の選択肢はなく PostgreSQL 一択になるでしょう。SQL Server という選択肢も残されていますが、もう少し様子を見たいところです。
おわりに
.NET Core の技術調査は毎度のことながら想定時間を大幅にオーバーしますね。日本語情報が少ないことももちろんですが、情報そのものがまずないことが大きいかと。
Microsoft は ASP.NET MVC の頃あたりから技術開発はするけれども、一般的な普及に力をあまり注いでいない印象があります。一般技術者の頑張りにまかせているというかほったらかしというか。その辺の改善は欲しいと思います。
.NET Core だけでなく、Xamarin や Azure など素晴らしい技術が多いので変なところでがっかりさせることなく頑張ってほしいですね。
