はじめに

Entity Framework Core で INNER JOIN と LEFT JOIN を実装する方法ですが、ちょっと試行錯誤したのですがその方法をご紹介します。

アプリケーションはコンソールアプリケーションで、データベースは PostgreSQL を使用しコードファーストでデータベースを構築しました。

なお、環境は、Mac (macOS High Sierra 10.13.3) で .NET Core SDK 2.1.4、PostgreSQL 10.3 、Visual Studio Code になります。

コードファーストでデータベースを構築する

最初にコードファーストでデータベースを構築する必要があります。

JOIN するために以下のクラスを作成します。ポイントは、Pet クラスが Owner プロパティを持っていることです。

public class Person
{
    public int id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class Pet
{
    public int id { get; set; }
    public string Name { get; set; }
    public Person Owner { get; set; }
}

そしてデータベースコンテキストクラスを以下のように作成します（抜粋）。SQL ログを出力したいのでその設定も含まれています。

using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Console;

//(中略)

  public DbSet<Person> Person { get; set; }
  public DbSet<Pet> Pet { get; set; }


  public static readonly LoggerFactory MyLoggerFactory
      = new LoggerFactory(new[]
      {
          new ConsoleLoggerProvider((category, level)
              => category == DbLoggerCategory.Database.Command.Name
              && level == LogLevel.Information, true)
      });

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
      if (!optionsBuilder.IsConfigured)
      {
          optionsBuilder
              .UseLoggerFactory(MyLoggerFactory)
              .UseNpgsql("Server=localhost;Database=databasename;Username=username;Password=password;");
      }
  }

準備ができたので、以下のコマンドでデータベースを構築します。init 部分は名前が重複しなければなんでもかまいません。

$ dotnet ef migrations add init
$ dotnet ef database update

SQL ログが出力されるのですが興味深いですね。Pet クラスの作成時に Person で Owner 属性を宣言していたのに、実際のテーブルでは Ownerid のみ作成しています。この辺はさすがよくできた O/R マッパーですね。

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (33ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "Person" (
          "id" serial NOT NULL,
          "FirstName" text NULL,
          "LastName" text NULL,
          CONSTRAINT "PK_Person" PRIMARY KEY ("id")
      );

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (37ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "Pet" (
          "id" serial NOT NULL,
          "Name" text NULL,
          "Ownerid" int4 NULL,
          CONSTRAINT "PK_Pet" PRIMARY KEY ("id"),
          CONSTRAINT "FK_Pet_Person_Ownerid" FOREIGN KEY ("Ownerid") REFERENCES "Person" ("id") ON DELETE RESTRICT
      );

データの作成

データベースの構築が済んだので、実際にデータをテーブルに作成します。

Program.cs に以下のように記述します。ポイントは、斎藤さんだけペットを飼っていないところです。

static void Main(string[] args)
{
    using (var db = new consoletestContext())
    {
        var yamada = new Person {FirstName = "山田", LastName = "太郎"};
        var sato = new Person {FirstName = "佐藤", LastName = "次郎"};
        var saito = new Person {FirstName = "斎藤", LastName = "三郎"};
        db.Person.Add(yamada);
        db.Person.Add(sato);
        db.Person.Add(saito);

        db.Pet.Add(new Pet { Name = "ポチ", Owner = yamada});
        db.Pet.Add(new Pet { Name = "タマ", Owner = sato});

        db.SaveChanges();
    }

下記コマンドでデータを作成します。

$ dotnet build
$ dotnet run

INNER JOIN の実装

C# の join 句を使用することで INNER JOIN を実装することができます。

具体的には、Program.cs に以下のように記述します。

static void Main(string[] args)
{
    using (var db = new consoletestContext())
    {
        var query = from person in db.Person
                    join pet in db.Pet on person.id equals pet.Owner.id
                    select new
                    {
                        OwnerName = person.FirstName,
                        PetName = pet.Name
                    };

        foreach (var ownerAndPet in query) {
            Console.WriteLine($"{ownerAndPet.PetName} は {ownerAndPet.OwnerName} に飼われてる。");
        }
    }
}

これを実行すると、以下のようなログが出力されます。

$ dotnet build
$ dotnet run
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (20ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "person"."FirstName" AS "OwnerName", "pet"."Name" AS "PetName"
      FROM "Person" AS "person"
      INNER JOIN "Pet" AS "pet" ON "person"."id" = "pet"."Ownerid"
ポチ は 山田 に飼われてる。
タマ は 佐藤 に飼われてる。

SQL 文で INNER JOIN が使用されていることが分かります。

また、ペットを飼っていない斎藤さんは出力されていないので処理も正しく行われています。

LEFT JOIN の実装

LEFT JOIN の実装ですが、グループ結合の結果に対して DefaultEmpty メソッドを使用することで実現できます。

具体的には、Program.cs に以下のように記述します。

static void Main(string[] args)
{
    using (var db = new consoletestContext())
    {
        var query = from person in db.Person
                    join pet in db.Pet on person.id equals pet.Owner.id into gj
                    from subpet in gj.DefaultIfEmpty()
                    select new
                    {
                        OwnerName = person.FirstName,
                        PetName = subpet.Name ?? String.Empty                             };

        foreach (var v in query) {
            Console.WriteLine($"{v.OwnerName} : {v.PetName}");
        }
    }
}

この実行結果は以下のようになります。

$ dotnet build
$ dotnet run
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (35ms) [Parameters=[@__Empty_0='?'], CommandType='Text', CommandTimeout='30']
      SELECT "person"."FirstName" AS "OwnerName", COALESCE("pet"."Name", @__Empty_0) AS "PetName"
      FROM "Person" AS "person"
      LEFT JOIN "Pet" AS "pet" ON "person"."id" = "pet"."Ownerid"
山田 : ポチ
佐藤 : タマ
斎藤 :

SQL ログに LEFT JOIN が出力されていますし、ペットを飼っていない斎藤さんが出力されているので処理が正しいことがわかります。

おわりに

INNER JOIN / LEFT JOIN といったテーブルの結合処理は、データベースを使用すアプリケーションでは必須の知識なので分かってよかったです。

コードファーストでもうまくいくことが分かったこともよかったですね。

あるSEのつぶやき・改

ITやシステム開発などの技術に関する話題を、取り上げたりしています。

.NET Core, Entity Framework Coreで INNER JOIN / LEFT JOIN を実装する方法