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'
