NpgsqlとEntityFramework Coreを使ってPostgreSQLをCRUDする

C#で書かれた.NET CoreアプリケーションからPostgreSQLのデータを操作する機会があり、NpgsqlとEntity Framework Core(EF Core)の使い方を調べました。

Npgsql

NpgsqlはPostgreSQL用のADO.NETデータプロバイダで、C#やVB.NETからPostgreSQLに接続してSQLを実行するためのライブラリです。
NpgsqlではEF Core用のプロバイダを提供しています。
Npgsql - .NET Access to PostgreSQL | Npgsql Documentation

DBの構築

今回はDocker HubのPostgreSQLコンテナを使ってテスト用のDB環境を作りました。
https://hub.docker.com/_/postgres/

テスト用にmembersテーブルとtodosテーブルを作成します。
membersとtodosは1:Nのリレーションを持っており、membersのidカラムが外部キーとなっています。

members

カラム名	型	制約
id	varchar(32)	primary key
name	varchar(32)	not null

create table members (
    id varchar(32) primary key,
    name varchar(32) not null
);

todos

カラム名	型	制約
id	serial	primary key
member_id	int	foreign key
content	varchar(256)	not null
due	date
done	boolean

create table todos (
    id serial primary key,
    member_id varchar(32),
    content varchar(256) not null,
    due date,
    done boolean,
    foreign key (member_id) references members (id)
);

Npgsqlのインストール

project.jsonにNpgsql.EntityFrameworkCore.PostgreSQLの1.1.0（現行最新版）を追記してdotnet restoreでインストールします。

https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL

{
  "version": "1.0.0-*",
  "buildOptions": {
    "debugType": "portable",
    "emitEntryPoint": true
  },
  "dependencies": {
    "Npgsql.EntityFrameworkCore.PostgreSQL": "1.1.0"
  },
  "frameworks": {
    "netcoreapp1.1": {
      "dependencies": {
        "Microsoft.NETCore.App": {
          "type": "platform",
          "version": "1.1.0"
        }
      },
      "imports": "dnxcore50"
    }
  }
}

エンティティクラス

アプリケーションの実装に入っていきます。

最初にテーブルレコードとマッピングされるエンティティクラスを作成します。
ここではmembersテーブルレコードに対応するMemberクラス、todosテーブルレコードに対応するTodoクラスを定義しています。

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace ConsoleApplication.Models
{
    [Table("members")]
    public class Member
    {
        [Key]
        [Column("id")]
        public string Id { get; set; }

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

        public virtual ICollection<Todo> Todos { get; set; }
    }

    [Table("todos")]
    public class Todo
    {
        [Key]
        [Column("id")]
        public int Id { get; set; }

        [Required]
        [Column("member_id")]
        public string MemberId { get; set; }

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

        [Column("due")]
        public DateTime Due { get; set; }

        [Column("done")]
        public bool Done { get; set; }

        public virtual Member Member { get; set; }
    }
}

DBコンテキストクラス

次にDbContextをオーバライドして、今回アクセスするtestデータベース用のDBコンテキストを作ります。
EF CoreではDbContextOptionsBuilderオブジェクトを介して接続文字列やロガーなどの設定をします。 今回はOnConfiguringで先程作成したローカルDBへ接続しています。

using ConsoleApplication.Models;
using Microsoft.EntityFrameworkCore;

namespace ConsoleApplication.Database
{
    public class TestDbContext : DbContext
    {
        public DbSet<Member> Members { get; set; }
        public DbSet<Todo> Todos { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseNpgsql("Host=localhost;Username=ohke;Password=ohke;Database=test");
        }
    }
}

データの挿入と参照

ここまで準備ができたら、membersとtodosにレコードをinsertして、さらにselectしてみます。

using System;
using System.Collections.Generic;
using System.Linq;
using ConsoleApplication.Database;
using ConsoleApplication.Models;

namespace ConsoleApplication
{
    public class Program
    {
        public static void Main(string[] args)
        {
            using (var db = new TestDbContext())
            {
                var members = new List<Member>
                {
                    new Member { Id = "ohke1", Name = "ohke1" },
                    new Member { Id = "ohke2", Name = "ohke2" },
                };

                var todos = new List<Todo>
                {
                    new Todo { Member = members[0], Content = "content1", Due = DateTime.Now, Done = false },
                    new Todo { Member = members[0], Content = "content2", Due = DateTime.Now, Done = false },
                    new Todo { Member = members[1], Content = "content3", Due = DateTime.Now, Done = false },
                };
                
                db.Members.AddRange(members);
                db.Todos.AddRange(todos);

                db.SaveChanges();
            }

            using (var db = new TestDbContext())
            {
                // [出力]
                //   ohke1, ohke1
                //   ohke2, ohke2
                foreach (var member in db.Members)
                {
                    Console.WriteLine($"{member.Id}, {member.Name}");
                }

                // [出力]
                //   ohke2, 3, content3
                foreach (var todo in db.Members.Where(m => m.Id == "ohke2").SelectMany(m => m.Todos))
                {
                    Console.WriteLine($"{todo.MemberId}, {todo.Id}, {todo.Content}");
                }
            }
        }
    }
}

実行しているSQLクエリをコンソールに出力する

DBへ発行されているSQLを見たい場合は、DbContextにLoggerFactoryを設定します。
ここではMicrosoft.Extensions.Loggingを使ってConsoleにDebug出力するLoggerFactoryを作成・設定しています。

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseNpgsql("Host=localhost;Username=ohke;Password=ohke;Database=test");

    var loggerFactory = new LoggerFactory().AddConsole().AddDebug();
    optionsBuilder.UseLoggerFactory(loggerFactory);
}

"dependencies": {
    "Npgsql.EntityFrameworkCore.PostgreSQL": "1.1.0",
    "Microsoft.Extensions.Logging": "1.1.0",
    "Microsoft.Extensions.Logging.Console": "1.1.0",
    "Microsoft.Extensions.Logging.Debug": "1.1.0"
},

先程のプログラムで実行されるSQLを見てみますと、membersへの挿入では2つのレコードがinsertされていますが、DBへは1回のリクエストにまとめられています。

info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (59ms) [Parameters=[@p0='?', @p1='?', @p2='?', @p3='?'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "members" ("id", "name")
      VALUES (@p0, @p1);
      INSERT INTO "members" ("id", "name")
      VALUES (@p2, @p3);
Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory:Information: Executed DbCommand (59ms) [Parameters=[@p0='?', @p1='?', @p2='?', @p3='?'], CommandType='Text', CommandTimeout='30']
INSERT INTO "members" ("id", "name")
VALUES (@p0, @p1);
INSERT INTO "members" ("id", "name")
VALUES (@p2, @p3);

また、ohke2に紐づくtodosレコードの検索ではmember_idをキーとしてinnner joinされており、memberの検索とtodoの検索が別々のSQLクエリで行われるいわゆるN+1問題の発生をEntity Framework側で防いでいることがわかります。

info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "m.Todos"."id", "m.Todos"."content", "m.Todos"."done", "m.Todos"."due", "m.Todos"."member_id"
      FROM "members" AS "m"
      INNER JOIN "todos" AS "m.Todos" ON "m"."id" = "m.Todos"."member_id"
      WHERE "m"."id" = 'ohke2'
Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory:Information: Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "m.Todos"."id", "m.Todos"."content", "m.Todos"."done", "m.Todos"."due", "m.Todos"."member_id"
FROM "members" AS "m"
INNER JOIN "todos" AS "m.Todos" ON "m"."id" = "m.Todos"."member_id"
WHERE "m"."id" = 'ohke2'