Entity Framework Core < - > PostgreSQL 間の MAX/MIN 値のやりとり (数値データ編) 1
はじめに
Entity Framework Core (以下 EF Core)を使って PostgreSQL に数値データを追加したり取得したりしているときに、エラーが発生することがありました。
値を丸めたりして対処したりはしたものの、どういうデータでエラーが出るのか、ということが気になったので試してみることにしました。
数値データ編としましたが、他のデータ型については気が向いたらやります。
どちらかというと、 PostgreSQL に比較的最近？追加された JSON 型や Address 型などが扱えるのか？というのが気になるところではあります。
準備
とりあえず PostgreSQL のドキュメントを参考に、数値データを持つテーブルを作ります。
CREATE Table "NumberSample" (
"Id" serial PRIMARY KEY,
"BigIntValue" bigint,
"BigSerialValue" bigserial,
"DoubleValue" double precision,
"IntegerValue" integer,
"NumericValue" numeric,
"SerialValue" serial,
"RealValue" real,
"SmallIntValue" smallint,
"SmallSerialValue" smallserial,
"MoneyValue" money
)
検証は、
- DB に各データ型の MAX/MIN 値を持つレコードを追加し、 EF Core でそれを検索
- C# の MAX/MIN 値の幅が DB 側より広い場合はその値を含むレコードを追加
- C# の MAX/MIN 値が DB 側より狭い場合は、 C# での MAX/MIN 値を含むレコードを追加
これでエラーになるかを調べ、エラーを抑える方法(数値を丸めるなど)も調べてみたいと思います。
なお C# 側の MAX/MIN 値は下記を参考にしています。
bigint
PostgreSQL における bigint の取り得る範囲は -9,223,372,036,854,775,808 ～ +9,223,372,036,854,775,807 。
C# でこれに近い範囲を持つ型は、 long 型です (-9,223,372,036,854,775,808 ～ 9,223,372,036,854,775,807)。
INSERT INTO "NumberSample" ("BigIntValue") VALUES (-9223372036854775808);
INSERT INTO "NumberSample" ("BigIntValue") VALUES (9223372036854775807);
BigSerialValue などのカラムは、 MIN 値( Id = 2 )、 MAX 値( Id = 3 )のレコードを更新する形で値を入れていくことにします。
NumberSample.cs
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace EfCoreDataTypeSample.Models
{
[Table("NumberSample")]
public class NumberSample
{
[Key]
public int Id { get; set; }
public long BigIntValue { get; set; }
}
}
HomeController.cs
using System; using System.Collections.Generic; using System.Threading.Tasks; using EfCoreDataTypeSample.Models; using Microsoft.AspNetCore.Mvc; using Microsoft.EntityFrameworkCore; using Microsoft.EntityFrameworkCore.Storage; namespace EfCoreDataTypeSample.Controllers { public class HomeController : Controller { private readonly EfCoreDataTypeSampleContext _context; public HomeController(EfCoreDataTypeSampleContext context) { _context = context; } [Route("/")] [Route("/Home")] [Produces("application/json")] public async Task< List< NumberSample>> Index() { return await _context.NumberSamples .ToListAsync(); } [Route("/Create")] public async Task Create() { using (IDbContextTransaction transaction = _context.Database.BeginTransaction()) { NumberSample newMinNumber = new NumberSample { BigIntValue = -9223372036854775808, }; _context.NumberSamples.Add(newMinNumber); NumberSample newMaxNumber = new NumberSample { BigIntValue = 9223372036854775807, }; _context.NumberSamples.Add(newMaxNumber); await _context.SaveChangesAsync(); transaction.Commit(); } } [Route("/Update")] public async Task< bool> Update() { using (IDbContextTransaction transaction = _context.Database.BeginTransaction()) { // Min NumberSample minNumber = await _context.NumberSamples .FirstOrDefaultAsync(n => n.Id == 4); minNumber.BigIntValue = GetOppositeBigIntValue(minNumber.BigIntValue); NumberSample maxNumber = await _context.NumberSamples .FirstOrDefaultAsync(n => n.Id == 5); maxNumber.BigIntValue = GetOppositeBigIntValue(maxNumber.BigIntValue); int result = await _context.SaveChangesAsync(); transaction.Commit(); return result > 0; } } private long GetOppositeBigIntValue(long originalValue) { if (originalValue <= 0) { return 9223372036854775807; } return -9223372036854775808; } } }
REST はいずこという感じですが、今回は気にしない方向で。。。
結果としては、検索、追加、更新ともに値が変化することなくやり取りできました。
ただ一点、 Index() で返される JSON を Firefox で見たところ、下記のように表示されていました。
生データをみると正しい値になっていたため、 JSON を整形して表示する上で発生しているだけのようですが、注意が必要かもしれません。
bigserial
PostgreSQL における bigserial の取り得る範囲は 1 ～ +9,223,372,036,854,775,807 。
C# でこれに近い範囲を持つ型は、 ulong 型です (0 ～ 9,223,372,036,854,775,807)。
UPDATE "NumberSample" SET "BigSerialValue" = 1 WHERE "Id" = 2; UPDATE "NumberSample" SET "BigSerialValue" = 9223372036854775807 WHERE "Id" = 3
HomeController.cs
～省略～
public async Task Create()
{
using (IDbContextTransaction transaction = _context.Database.BeginTransaction())
{
NumberSample newMinNumber = new NumberSample
{
～省略～
BigSerialValue = 0,
};
_context.NumberSamples.Add(newMinNumber);
NumberSample newMaxNumber = new NumberSample
{
～省略～
BigSerialValue = 9223372036854775807,
};
_context.NumberSamples.Add(newMaxNumber);
await _context.SaveChangesAsync();
transaction.Commit();
}
}
[Route("/Update")]
public async Task Update()
{
using (IDbContextTransaction transaction = _context.Database.BeginTransaction())
{
// Min
NumberSample minNumber = await _context.NumberSamples
.FirstOrDefaultAsync(n => n.Id == 4);
～省略～
minNumber.BigSerialValue = GetOppositeBigSerialValue(minNumber.BigSerialValue);
// Max
NumberSample maxNumber = await _context.NumberSamples
.FirstOrDefaultAsync(n => n.Id == 5);
～省略～
maxNumber.BigSerialValue = GetOppositeBigSerialValue(maxNumber.BigSerialValue);
int result = await _context.SaveChangesAsync();
transaction.Commit();
return result > 0;
}
}
～省略～
private ulong GetOppositeBigSerialValue(ulong originalValue)
{
if (originalValue <= 0)
{
return 9223372036854775807;
}
return 0;
}
}
}
結果は、値が変化することなく検索、追加、更新されました。
あれ？
ulong は最小値が 0 のため、範囲から外れているのに。。。
と思ったら、カラムのデータ型としては bigint で設定されるため、明示的に指定して入れる分には先ほどと同じ範囲の値が扱える、ということのようです。
bigserial の範囲というのは、値が空であった場合に自動でインクリメントした値をセットする、シーケンスの取り得る範囲のようです。
double precision
double precision ですが、範囲は 15桁精度 となっています。
実際どの程度の桁数まで登録できるのかを確認したところ、整数部分はドキュメント通り 1E-307 ～ 1E+308 程度であり、私の環境で確認したところ、308 桁を超えるとエラーとなっていました。 小数点以下は 1000 桁ぐらいでもエラーにはならないのですが、インサート後は小数点第 15 位で四捨五入され(これが 15桁精度 ということなのかと)、それ以下は反映されません。
また 1E+308 のように指数表記が使われている場合、小数点以下が丸められてしまうようです。
C# で doule が持つ MAX/MIN 値として、 double.MaxValue/double.MinValue があります。
それぞれ +1.79769313486232e+308 / -1.79769313486232e+308 であり、これも合わせて試すことにします。
あと、小数点第 15 位以下を持つ値を使った場合も見てみたいと思います。
HomeController.cs
～省略～
[Route("/Create")]
public async Task Create()
{
using (IDbContextTransaction transaction = _context.Database.BeginTransaction())
{
NumberSample newMinNumber = new NumberSample
{
～省略～
DoubleValue = double.MinValue
};
_context.NumberSamples.Add(newMinNumber);
NumberSample newMaxNumber = new NumberSample
{
～省略～
DoubleValue = double.MaxValue
};
_context.NumberSamples.Add(newMaxNumber);
NumberSample newMinNumber2 = new NumberSample
{
～省略～
DoubleValue = -99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999.9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999,
};
_context.NumberSamples.Add(newMinNumber2);
NumberSample newMaxNumber2 = new NumberSample
{
～省略～
DoubleValue = 99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999.9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999,
};
_context.NumberSamples.Add(newMaxNumber2);
NumberSample newMinNumber3 = new NumberSample
{
～省略～
DoubleValue = -3.33333333333333333333333333333333333333,
};
_context.NumberSamples.Add(newMinNumber3);
NumberSample newMaxNumber3 = new NumberSample
{
～省略～
DoubleValue = 3.33333333333333333333333333333333333333,
};
_context.NumberSamples.Add(newMaxNumber3);
await _context.SaveChangesAsync();
transaction.Commit();
}
}
[Route("/Update")]
public async Task Update()
{
using (IDbContextTransaction transaction = _context.Database.BeginTransaction())
{
// Min
NumberSample minNumber = await _context.NumberSamples
.FirstOrDefaultAsync(n => n.Id == 4);
～省略～
minNumber.DoubleValue = GetOppositeDoubleValue(minNumber.DoubleValue);
// Max
NumberSample maxNumber = await _context.NumberSamples
.FirstOrDefaultAsync(n => n.Id == 5);
～省略～
maxNumber.DoubleValue = GetOppositeDoubleValue(maxNumber.DoubleValue);
// Min
NumberSample minNumber2 = await _context.NumberSamples
.FirstOrDefaultAsync(n => n.Id == 6);
～省略～
minNumber2.DoubleValue = GetOppositeDoubleValue2(minNumber2.DoubleValue);
// Max
NumberSample maxNumber2 = await _context.NumberSamples
.FirstOrDefaultAsync(n => n.Id == 7);
～省略～
maxNumber2.DoubleValue = GetOppositeDoubleValue2(maxNumber2.DoubleValue);
// Min
NumberSample minNumber3 = await _context.NumberSamples
.FirstOrDefaultAsync(n => n.Id == 8);
～省略～
minNumber3.DoubleValue = GetOppositeDoubleValue3(minNumber3.DoubleValue);
// Max
NumberSample maxNumber3 = await _context.NumberSamples
.FirstOrDefaultAsync(n => n.Id == 9);
～省略～
maxNumber3.DoubleValue = GetOppositeDoubleValue3(maxNumber3.DoubleValue);
int result = await _context.SaveChangesAsync();
transaction.Commit();
return result > 0;
}
}
～省略～
private double GetOppositeDoubleValue(double originalValue)
{
if (originalValue <= 0)
{
return double.MaxValue;
}
return double.MinValue;
}
private double GetOppositeDoubleValue2(double originalValue)
{
if (originalValue <= 0)
{
return 99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999.9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
}
return -99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999.9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
}
private double GetOppositeDoubleValue3(double originalValue)
{
if (originalValue <= 0)
{
return 3.33333333333333333333333333333333333333;
}
return -3.33333333333333333333333333333333333333;
}
}
}
結果は下記の通りです( maxNumber, minNumber などの名前は Create() のものです)。
- maxNumber: C#, DB ともに 1.79769313486232e+308
- minNumber: C#, DB ともに -1.79769313486232e+308
- maxNumber2: C#, DB ともに 1e+308
- minNumber2: C#, DB ともに -1e+308
- maxNumber3: C# では 3.3333333333333335 、 DB では 3.33333333333333
- minNumber3: C# では -3.3333333333333335 、 DB では -3.33333333333333
C# と PostgreSQL では、小数点の丸め方が異なるようです。
maxNumber3 、 minNumber3 の違いは、 C# から値を入れた場合も DB から取得した場合も両方同じ値になります。
Double なので正確性はないとはいえ、場合によっては注意が必要かもしれません。
NaN 、 Infinity
思い付きでこれらも確認してみることにしました。
HomeController.cs
～省略～
public async Task Create()
{
using (IDbContextTransaction transaction = _context.Database.BeginTransaction())
{
～省略～
NumberSample newMinNumber4 = new NumberSample
{
～省略～
DoubleValue = double.NaN,
};
_context.NumberSamples.Add(newMinNumber4);
NumberSample newMinNumber5 = new NumberSample
{
～省略～
DoubleValue = double.NegativeInfinity,
};
_context.NumberSamples.Add(newMinNumber5);
NumberSample newMaxNumber5 = new NumberSample
{
～省略～
DoubleValue = double.PositiveInfinity,
};
_context.NumberSamples.Add(newMaxNumber5);
await _context.SaveChangesAsync();
transaction.Commit();
}
}
[Route("/Update")]
public async Task Update()
{
using (IDbContextTransaction transaction = _context.Database.BeginTransaction())
{
～省略～
// NaN
NumberSample nanNumber = await _context.NumberSamples
.FirstOrDefaultAsync(n => n.Id == 10);
～省略～
nanNumber.DoubleValue = double.NaN;
// Min
NumberSample minNumber5 = await _context.NumberSamples
.FirstOrDefaultAsync(n => n.Id == 11);
～省略～
minNumber5.DoubleValue = GetOppositeDoubleValue5(minNumber5.DoubleValue);
// Max
NumberSample maxNumber5 = await _context.NumberSamples
.FirstOrDefaultAsync(n => n.Id == 12);
～省略～
maxNumber5.DoubleValue = GetOppositeDoubleValue5(maxNumber5.DoubleValue);
int result = await _context.SaveChangesAsync();
transaction.Commit();
return result > 0;
}
}
～省略～
private double GetOppositeDoubleValue5(double originalValue)
{
if (originalValue <= 0)
{
return double.PositiveInfinity;
}
return double.NegativeInfinity;
}
}
}
結果は、検索、追加、更新ともに値が変化することなく値が渡されていました。
ただし JSON では文字列として渡されていました。
おそらく変換自体はできるものと思いますが。
integer
PostgreSQL における integer の範囲は -2147483648 ～ +2147483647 。
C# でこれに近い範囲を持つ型は、int です。 ( -2147483648 ～ +2147483647 )
UPDATE "NumberSample" SET "IntegerValue" = -2147483648 WHERE "Id" = 2; UPDATE "NumberSample" SET "IntegerValue" = 2147483647 WHERE "Id" = 3
HomeController.cs
～省略～
public async Task Create()
{
using (IDbContextTransaction transaction = _context.Database.BeginTransaction())
{
NumberSample newMinNumber = new NumberSample
{
～省略～
IntegerValue = int.MinValue,
};
_context.NumberSamples.Add(newMinNumber);
NumberSample newMaxNumber = new NumberSample
{
～省略～
IntegerValue = int.MaxValue,
};
_context.NumberSamples.Add(newMaxNumber);
await _context.SaveChangesAsync();
transaction.Commit();
}
}
[Route("/Update")]
public async Task Update()
{
using (IDbContextTransaction transaction = _context.Database.BeginTransaction())
{
// Min
NumberSample minNumber = await _context.NumberSamples
.FirstOrDefaultAsync(n => n.Id == 4);
～省略～
minNumber.IntegerValue = GetOppositeIntValue(minNumber.IntegerValue);
// Max
NumberSample maxNumber = await _context.NumberSamples
.FirstOrDefaultAsync(n => n.Id == 5);
～省略～
maxNumber.IntegerValue = GetOppositeIntValue(maxNumber.IntegerValue);
～省略～
int result = await _context.SaveChangesAsync();
transaction.Commit();
return result > 0;
}
}
～省略～
private int GetOppositeIntValue(int originalValue)
{
if (originalValue <= 0)
{
return int.MaxValue;
}
return int.MinValue;
}
}
}
結果は、検索、追加、更新ともに値が変化することなく値が渡されていました。
範囲外の値
ここまで C# 、 PostgreSQL のデータ型の取り得る値の範囲が同じであったこともあり、範囲内の値だけを用いて確認していました。
では、範囲外の値が入るとどうなるのでしょうか。
C# 側の値が大きすぎ・小さすぎた場合
IntegerValue の値を long に変更し、long.Max / long.Min を入れてみたところ、下記の例外が発生しました。
PostgresException: 22003: integer out of range
PostgreSQL 側の値が大きすぎ・小さすぎた場合
IntegerValue の値を short に変更し、検索を行ったところ、下記の例外が発生しました。
OverflowException: Arithmetic operation resulted in an overflow.
当然といえば当然ですが、エラーが起きる場所が異なりますね。
いったん切ります。
参照
PgAdmin4, Entity Framework Core 周り小ネタ
はじめに
PgAdmin4 や Entity Framework Core (以下 EF Core )を使ってあれこれ試しているうちに、取り立てて大きなトピックにするほどでもないけど、という程度の小ネタがたまってきた気がするので、ここにまとめてみます。
[PgAdmin4] クエリ実行
PgAdmin4 で PostgreSQL に接続して、さあ CREATE TABLE でテーブル作るぞ！と思ったらクエリ実行するウインドウらしきものがない？？？
と思ったら、 Database のツリーの上にありました。
なおこのウインドウはタブで開くのですが、ボタンをポチポチ押しているとその分タブが開いていきます。
過去に実行した SQL は Query History から見たり Copy All でコピーしたりできますが、タブを閉じてしまうと消えてしまうため、状況に応じて保存が必要です。
その他ツリーの Schemas から、作ったテーブルやトリガー、主キーや制約などをみることができます。
簡単な変更( Not Null にするとか)なら Property などから実行できてしまうのも便利ですね。
[PgAdmin4] バックアップとリストア
バックアップ
作った Database のバックアップ(論理)を作成するには、ツリー上にある対象の Database 上で右クリック > Backup を選択します。
FileName では保存先をファイル名まで指定します。
Format として sql 、 backup 、または all files が選択できますが、例えば sql を選んでいても拡張子として .sql が付くわけではないので注意が必要です。
注意が必要ですと書きましたが、主に影響を受けるのは後述のリストア時に Format を all files にしないと表示されない、というくらいで、バックアップやリストアの結果には関係ありません。多分。
Format ですが、バックアップ時のデフォルトは sql になっていますが、リストア時のデフォルトは backup になっているため、リストア時の手間を省くという意味で(他に理由が無ければ) backup にするのが良さそうです。
あと Encoding は UTF8 にしました。
少し引っかかったのがバックアップ結果の表示。
Stop Process の赤いボタンが気になりすぎて、「あれ？なんか失敗してる？」としばらくあれこれ確認してしまいました(；ﾟДﾟ)
いや、 Successfully とか書いているしわかるのですけれども。
リストア
リストアのためには復元対象となる Database が必要です。
ということで空の Database を作り、ツリー上で右クリック > Restore を選択します。
こちらは FileName で先ほどのバックアップファイルを選択すれば OK です。
[EFCore] 複合主キー
例えば下記のように複合主キーを持つテーブルを参照する場合。
CREATE TABLE "Book2"(
"Book2Id" Integer not null,
"BookId" Bigint references "Book"("Id"),
"Name" TEXT,
PRIMARY KEY("Book2Id", "BookId")
)
こんな感じでデータを入れます。
INSERT INTO "Book2"(
"Book2Id",
"BookId",
"Name"
)
VALUES(
0,
1,
'Bookだよ'
);
INSERT INTO "Book2"(
"Book2Id",
"BookId",
"Name"
)
VALUES(
1,
1,
'Book1でした'
);
INSERT INTO "Book2"(
"Book2Id",
"BookId",
"Name"
)
VALUES(
2,
2,
'Bookですか？'
);
INSERT INTO "Book2"(
"Book2Id",
"BookId",
"Name"
)
VALUES(
2,
3,
'Bookだってば'
);
これをこうして
Book2.cs
using System.ComponentModel.DataAnnotations.Schema;
namespace EfCoreNpgsqlSample.Models
{
[Table("Book2")]
public class Book2
{
public int Book2Id { get; set; }
public int BookId { get; set; }
public string Name { get; set; }
}
}
EfCoreNpgsqlSampleContext.cs
using Microsoft.EntityFrameworkCore; namespace EfCoreNpgsqlSample.Models { public class EfCoreNpgsqlSampleContext: DbContext { public EfCoreNpgsqlSampleContext(DbContextOptions< EfCoreNpgsqlSampleContext> options) : base(options) { } public DbSet< Store> Stores { get; set; } public DbSet< Author> Authors { get; set; } public DbSet< Book> Books { get; set; } public DbSet< Book2> Books2 { get; set; } } }
こうするとどうなるか。
HomeController.cs
～省略～
foreach (Book2 book in _context.Books2)
{
Console.WriteLine($"ID: {book.Book2Id} BookID: {book.BookId} Name: {book.Name}");
}
～省略～
出力結果はこちらです。
ID: 0 BookID: 1 Name: Bookだよ ID: 1 BookID: 1 Name: Book1でした ID: 2 BookID: 2 Name: Bookですか？ ID: 2 BookID: 2 Name: Bookですか？
。。。あれ？
Entity Framework Core では、主キーが指定されていない場合、 ～Id という名前のプロパティを主キーとして扱うようです。
今回の場合、 Book2Id がユニークのものは問題がないのですが、 4 つ目のレコードは 3 つ目と重複しているため、上記のような結果となりました。
ではどうするか。 DbContext クラスで主キーを指定してやります。
EfCoreNpgsqlSampleContext.cs
～省略～
public class EfCoreNpgsqlSampleContext: DbContext
{
public EfCoreNpgsqlSampleContext(DbContextOptions< EfCoreNpgsqlSampleContext> options)
: base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity< Book2>().HasKey(b => new {b.Book2Id, b.BookId});
}
public DbSet< Store> Stores { get; set; }
public DbSet< Author> Authors { get; set; }
public DbSet< Book> Books { get; set; }
public DbSet< Book2> Books2 { get; set; }
}
～省略～
これで正しい結果が得られます。
ID: 0 BookID: 1 Name: Bookだよ ID: 1 BookID: 1 Name: Book1でした ID: 2 BookID: 2 Name: Bookですか？ ID: 2 BookID: 3 Name: Bookだってば
なお、 INNER JOIN で複数カラムを指定して ON としたい場合も HasKey による指定が必要となります。
※下記を見ると [Key] を複数付ける方法でも解決できそうです。
参照
【Entity Framework Core】 N + 1 とかなんとか 2
はじめに
続きです。
今回は処理速度を雑に計測してみるところから。
あんまり速くない
前回の Include を使った処理、INNER JOIN で取ってきた値を C# でまとめた処理、そして SELECT 文がたくさん発行される処理を計測してみることにしました。
HomeController.cs
～省略～
[Route("/")]
[Route("/Home")]
[Produces("application/json")]
public async Task> Index()
{
var watch = new System.Diagnostics.Stopwatch();
watch.Start();
// ------ ここの部分を差し替え ------
List< Store> results = await _context.Stores
.Include(s => s.Authors)
.ThenInclude(a => a.Books)
.ToListAsync();
foreach (var result in results)
{
foreach (var author in result.Authors)
{
author.Books = author.Books.Where(b => b.Available).ToList();
}
}
// ------ ここまで ------
watch.Stop();
Console.WriteLine("time " + watch.ElapsedMilliseconds);
return results;
}
}
}
計測方法はもう少しちゃんと考えた方が良いかもしれませんが、今回はこれで試すことにしました。
また、SELECT 文がたくさん投げられちゃうバージョンは、前回別のクラスに格納していたため他と合わせることにしました。
結果
Include
- 3615 ms
- 4659 ms
- 2876 ms
N+1
- 3156 ms
- 2680 ms
- 3975 ms
index
- 3931 ms
- 2805 ms
- 2604 ms
… 3 回ずつしか計測していないこともあり、ばらつきがあることを考慮すると、あまりどれも変わらないような。。。
INNER JOIN を使ったコードも結局のところ、 SELECT 文を投げなくなった分 C# のコードで繰り返し処理をしているわけで、そもそも Include を使った時に速くなりうるのは DB アクセスとの速度差によるものなのでしょう。
※今回のように DB 、 アプリの両方がローカルサーバー上で動いている状態より、別々のサーバーで動いているような状態だとわかりやすいのかもしれません。
もう一回 Include を使ってみる
結局速度に大差がないのであれば、せめてシンプルに書きたいところ。
ということで、 Include をもう少し見てみることにしました。
いつ値はセットされるか
前回少しだけ触れましたが、 Include というのは DB に頻繁にアクセスされることが予想される場合に、あらかじめデータを読み込んでおき、発行される SELECT 文の数を減らすための機能と理解しています。
ということは、大量にレコードがあるテーブルを不用意に Include しちゃうとかえって遅くなったり。。。？
というところも気になるのですが、まずはどのタイミングで Store クラスの Authors や Author クラスの Store に値がセットされるのかを調べてみます。
やり方は単純で、それぞれの Setter でコンソールに出力するよう手を加えるだけです。
Store.css
～省略～
private List< Author> _authers;
[NotMapped]
public List< Author> Authors
{
get { return _authers;}
set
{
Console.WriteLine("Set Authors");
_authers = value;
}
}
～省略～
Author.cs
～省略～
private Store _store;
[NotMapped]
[IgnoreDataMember]
public Store Store {
get { return _store;}
set
{
Console.WriteLine("Set Store");
_store = value;
}
}
～省略～
先ほどの Include と、ついでに Include せず個別に実行するとどうなるのか見てみることにしました。
HomeController.cs
～省略～
Console.WriteLine("Prepare");
List< Store> stores = await _context.Stores.ToList();
List< Author> authors = await _context.Authors.ToList();
List< Book> books = await _context.Books.ToList();
Console.WriteLine("Start Including");
List< Store> results = _context.Stores
.Include(s => s.Authors)
.ThenInclude(a => a.Books)
.ToList();
Console.WriteLine("end");
～省略～
順番がわかりすいよう同期的に実行しています。
出力された内容はこんな感じ。
Prepare [Store に対する SELECT] [Author に対する SELECT] Set Store Set Authors Set Store Set Store Set Store Set Authors Set Store Set Store [Book に対する SELECT] Start Including [Include の SQL] Set Store Set Store Set Store Set Authors Set Store Set Store Set Store end
う～ん、よくわからない順番。。。
Store.Authors が Include では一回呼ばれていないのも気になりますね。
まぁそれはそれとして、 ToList で実体化するときに値がセットされることはわかりました。
また、最初に全レコードを読み込んだ後も、 _context.Stores などでアクセスするたびに値がセットされているのもわかります。
…これあんまり呼ばない方が良いのでは。。。？
コンストラクターで全レコード読み込んでみる(失敗)
_context.Stores などによる DB アクセスはあまり頻繁に行わない方が良さそう、ということであれば、最初に全部読み込んでしまえば...？
と思ったのでやってみました。
HomeController.cs
～省略～
private readonly EfCoreNpgsqlSampleContext _context;
private List< Store> _stores;
public HomeController(EfCoreNpgsqlSampleContext context)
{
_context = context;
_stores = _context.Stores
.Include(s => s.Authors)
.ThenInclude(a => a.Books)
.ToList();
}
[Route("/")]
[Route("/Home")]
[Produces("application/json")]
public async Task> Index()
{
List< Store> results = _stores
/* Where とか Select とか */
.ToList();
～省略～
ぱっと見は良さげな感じです。
見た感じは。
問題点 1
大きく 2 つ問題があるのですが、まず 1 つ目。
_stores に対して Where でフィルタリングを掛けてしまうと、 Include していたはずの Authors が空になってしまいます。
おそらく値がセットされるタイミングのためと考えられますが、手動で Author のリストをセットしても正しくセットされませんでした。
そのため、今回のような使い方をしようとすると、常に同じ条件で(つまり全レコード)返す必要がでてきます。
問題点 2
値が追加されるなど、 DB が更新されても反映されません。
まぁそれはそうですよね。というところでもありますが。
更新時にも必ず _stores の内容を変更した上で DB を変更する、ということにすればある程度までは対応できるかもしれませんが、直接、または別アプリで DB を更新された場合はどうすることもできません。
ということで、前もって読み込んでおく方法はとらない方が良さそうです。
怒りの手動 Include
最初に前もって読み込んでおく方法は使えず、 Include した値をフィルタリングするためにはいったん全部読み込む必要があって。。。
だんだん悲しくなってきた気がしますが気のせいですよね。
さて、 Include の役割というのは、最初に必要なデータを一括で読み込んでしまい、個別に SELECT 文を発行しなくて良いようにする、ということ。
であれば、Include を使わず直接値を入れてしまっても良さそうです。
EfCoreNpgsqlSampleContext.cs
～省略～
public class EfCoreNpgsqlSampleContext: DbContext
{
public EfCoreNpgsqlSampleContext(DbContextOptions options)
: base(options)
{
}
public DbSet< Store> Stores { get; set; }
public DbSet< Author> Authors { get; set; }
public DbSet< Book> Books { get; set; }
}
}
- HasMany などを取り除いて、デフォルトとほぼ同じ状態に戻しました。
HomeController.cs
～省略～
List< Store> stores = await _context.Stores.ToListAsync();
List< Author> authors = await (from author in _context.Authors
join store in stores on author.StoreId equals store.Id
select author)
.ToListAsync();
List< Book> books = await (from book in _context.Books
join author in authors on book.AuthorId equals author.Id
where book.Available
select book)
.ToListAsync();
foreach (Author author in authors)
{
author.Books = books.Where(b => b.AuthorId == author.Id).ToList();
}
foreach (Store store in stores)
{
store.Authors = authors.Where(a => a.StoreId == store.Id).ToList();
}
～省略～
- 今回の条件では Stores や Authors はフィルタリングしていないため Join は不要ですね。
これでフィルタリングした値を持たせることができる！…は良いのですが、やっぱり目から汗が。
おわりに
結論としては、
- 階層構造を持ったデータを取得する際、特に検索結果が多い場合、大量の SELECT 文が発行されてしまい、パフォーマンス問題の原因となる場合がある
- Include などを使ってあらかじめ DB から値を取得しておくことでそれを抑えることができる
- とはいえ最初に読み込んでそのまま保持、とするのは難しそう
といったところでしょうか。
次回に続く。。。かも。
参照
【Entity Framework Core】 N + 1 とかなんとか 1
はじめに
今日も Entity Framework Core と戯れているワタクシですが、いわゆる N + 1 問題が気になってしまったのであれこれ試してみたメモです。
準備
環境
- ASP.NET Core 2.2
- Entity Framework Core 2.2.2
- Npgsql.EntityFrameworkCore.PostgreSQL 2.2.0
前提条件など
画像のように、 Store が複数の Author を持ち、その Author が複数の Book を持つ、というテーブル間の関係性を持つ場合。
( Store に Author が紐づくのはおかしい、という話はあるかもしれませんが、ここでは見逃してください(..)_)
Class
各モデルクラスは下記とします。
Store.cs
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace EfCoreNpgsqlSample.Models
{
[Table("Store")]
public class Store
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
}
}
Author.cs
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace EfCoreNpgsqlSample.Models
{
[Table("Author")]
public class Author
{
[Key]
public int Id { get; set; }
[ForeignKey("Store")]
public int StoreId { get; set; }
public string Name { get; set; }
}
}
Book.cs
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace EfCoreNpgsqlSample.Models
{
[Table("Book")]
public class Book
{
[Key]
public int Id { get; set; }
[ForeignKey("Author")]
public int AuthorId { get; set; }
public string Name { get; set; }
public bool Available { get; set; }
}
}
SQL
SQL はこんな感じ。
Store
CREATE TABLE "Store" (
"Id" SERIAL PRIMARY KEY,
"Name" TEXT NOT NULL)
Author
CREATE TABLE "Author" (
"Id" SERIAL PRIMARY KEY,
"StoreId" BIGINT REFERENCES "Store"("Id"),
"Name" TEXT NOT NULL)
Book
CREATE TABLE "Book" (
"Id" SERIAL PRIMARY KEY,
"AuthorId" BIGINT REFERENCES "Author"("Id"),
"Name" TEXT NOT NULL,
"Available" BOOLEAN NOT NULL)
データは Store: 3 件、 Author: 6 件、 Book: 18 件程追加してみました。
取得したいデータ
で、下記の StoreItem のようなクラスのリストを取得したいとします。
StoreItem.cs
using System.Collections.Generic;
namespace EfCoreNpgsqlSample.Models
{
public class StoreItem
{
public Store Store { get; set; }
public List< AuthorItem> Authors { get; set; }
}
}
AuthorItem
using System.Collections.Generic;
namespace EfCoreNpgsqlSample.Models
{
public class AuthorItem
{
public Author Author { get; set; }
public List< Book> Books { get; set; }
}
}
N + 1 問題について
ようやく課題の話に入れます。
これを実現しようと、まずあまり何も考えずに、下記のように書いてみました。
using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using Microsoft.AspNetCore.Mvc; using Microsoft.EntityFrameworkCore; using EfCoreNpgsqlSample.Models; namespace EfCoreNpgsqlSample.Controllers { public class BooksController : Controller { private readonly EfCoreNpgsqlSampleContext _context; public BooksController(EfCoreNpgsqlSampleContext context) { _context = context; } [Route("/")] [Route("/Home")] [Produces("application/json")] public async Task< List< StoreItem>> Index() { List< StoreItem> results = await _context.Stores.Select(s => new StoreItem { Store = s, Authors = _context.Authors.Where(a => a.StoreId == s.Id) .Select(a => new AuthorItem { Author = a, Books = _context.Books.Where(b => b.AuthorId == a.Id).ToList(), }).ToList(), }) .ToListAsync(); return results; } } }
これで目的の結果自体は得ることができます。
で、何が問題なのかというと、 SQL が大量に発行されてしまい、結果の数が増えれば増えるほどパフォーマンスが低下してしまうことです。
今回はコンソールに出力されていた分で 10 回発行されているのが確認できました。
info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT s."Id", s."Name" FROM "Store" AS s info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (19ms) [Parameters=[@_outer_Id='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT a."Id", a."Name", a."StoreId" FROM "Author" AS a WHERE a."StoreId" = @_outer_Id info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (311ms) [Parameters=[@_outer_Id1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT b."Id", b."AuthorId", b."Available", b."Name" FROM "Book" AS b WHERE b."AuthorId" = @_outer_Id1 info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (6ms) [Parameters=[@_outer_Id1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT b."Id", b."AuthorId", b."Available", b."Name" FROM "Book" AS b WHERE b."AuthorId" = @_outer_Id1 info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (4ms) [Parameters=[@_outer_Id1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT b."Id", b."AuthorId", b."Available", b."Name" FROM "Book" AS b WHERE b."AuthorId" = @_outer_Id1 info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (3ms) [Parameters=[@_outer_Id='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT a."Id", a."Name", a."StoreId" FROM "Author" AS a WHERE a."StoreId" = @_outer_Id info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (2ms) [Parameters=[@_outer_Id='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT a."Id", a."Name", a."StoreId" FROM "Author" AS a WHERE a."StoreId" = @_outer_Id info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (3ms) [Parameters=[@_outer_Id1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT b."Id", b."AuthorId", b."Available", b."Name" FROM "Book" AS b WHERE b."AuthorId" = @_outer_Id1 info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (3ms) [Parameters=[@_outer_Id1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT b."Id", b."AuthorId", b."Available", b."Name" FROM "Book" AS b WHERE b."AuthorId" = @_outer_Id1 info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (5ms) [Parameters=[@_outer_Id1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT b."Id", b."AuthorId", b."Available", b."Name" FROM "Book" AS b WHERE b."AuthorId" = @_outer_Id1
内訳は下記の通りです。
- Store への SELECT: 1 回
- Author への SELECT: 3 回
- Book への SELECT: 6 回
Store が 3 件あるのでそこに紐づく Author を 3 回検索し、 Author が 6 件あるので Book を 6 回検索している、ということのようです。
当然レコード数が増えればさらに増えていくことになります。
というのが N + 1 問題で、このような問題は言語を問わず、 Entity Framework Core のような ORM を使う場合に起こりがちであるようです。
(リンクは Ruby ばかりですが)
これを防ぐ方法として、上記サイトでは includes を使って先にテーブルの情報を読み込んでおき、発行される SQL の数を抑える、という方法をとっています。
また、発行される SQL の数を抑える、ということであれば JOIN を使って Author や Book がリストに入っていない状態(各レコードが別の行に分かれた状態)で一旦結果を取得し、 C# 側で加工する、という方法も良いかもしれません。
ということで試してみます。
Include
Ruby(ActiveRecord?) にはあるらしい includes 、 Entity Framework Core ではどうかしらと思ったらありました。
準備
Include を使うには、例えば Store と Author が、 Store:1 < - > Author:多 の関係にあることを教えておく必要があります。
Store.cs
～省略～
public class Store
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
[NotMapped]
public List< Author> Authors { get; set; }
}
}
- NotMapped としているのは DB のテーブルに該当カラムが無くエラーになるのを防ぐためです。
Author.cs
～省略～
public class Author
{
[Key]
public int Id { get; set; }
[ForeignKey("Store")]
public int StoreId { get; set; }
public string Name { get; set; }
[NotMapped]
[IgnoreDataMember]
public Store Store { get; set; }
[NotMapped]
public List< Book> Books { get; set; }
}
}
Book.cs
～省略～
public class Book
{
[Key]
public int Id { get; set; }
[ForeignKey("Author")]
public int AuthorId { get; set; }
public string Name { get; set; }
public bool Available { get; set; }
[NotMapped]
[IgnoreDataMember]
public Author Author { get; set; }
}
}
EfCoreNpgsqlSampleContext.cs
using Microsoft.EntityFrameworkCore; namespace EfCoreNpgsqlSample.Models { public class EfCoreNpgsqlSampleContext: DbContext { public EfCoreNpgsqlSampleContext(DbContextOptions< EfCoreNpgsqlSampleContext> options) : base(options) { } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity< Store>().HasMany(store => store.Authors) .WithOne(author => author.Store); modelBuilder.Entity< Author>().HasMany(author => author.Books) .WithOne(book => book.Author); } public DbSet< Store> Stores { get; set; } public DbSet< Author> Authors { get; set; } public DbSet< Book> Books { get; set; } } }
- この指定を行わない場合、実行時に Authors が Store の navigation property と InvalidOperationException が発生します。
これらのデータを Include を使ってひとまとめにします。
HomeController.cs
～省略～
[Route("/")]
[Route("/Home")]
[Produces("application/json")]
public async Task< List< Store>> Index()
{
List< Store> results = await _context.Stores
.Include(store => store.Authors)
.ThenInclude(author => author.Books)
.ToListAsync();
return results;
}
}
}
- 「.Include(store => store.Authors)」 で Store の Authors に Store.Id で関連付けられた Author がセットされます。
- 「.ThenInclude(author => author.Books)」 で、Store.Authors にセットされた Author の Books に Author.Id で関連付けられた Book がセットされます。
出力された SQL は 3 つになりました。
info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT s."Id", s."Name" FROM "Store" AS s ORDER BY s."Id" info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT "s.Authors"."Id", "s.Authors"."Name", "s.Authors"."StoreId" FROM "Author" AS "s.Authors" INNER JOIN ( SELECT s0."Id" FROM "Store" AS s0 ) AS t ON "s.Authors"."StoreId" = t."Id" ORDER BY t."Id", "s.Authors"."Id" info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT "s.Authors.Books"."Id", "s.Authors.Books"."AuthorId", "s.Authors.Books"."Available", "s.Authors.Books"."Name" FROM "Book" AS "s.Authors.Books" INNER JOIN ( SELECT DISTINCT "s.Authors0"."Id", t0."Id" AS "Id0" FROM "Author" AS "s.Authors0" INNER JOIN ( SELECT s1."Id" FROM "Store" AS s1 ) AS t0 ON "s.Authors0"."StoreId" = t0."Id" ) AS t1 ON "s.Authors.Books"."AuthorId" = t1."Id" ORDER BY t1."Id0", t1."Id"
Include/ThenInclude を使うと INNER JOIN が使用されることがわかります。
注意
上記のように結果をそのまま JSON で出力しようとする場合、 1:多 の 多 のクラスで持つ 1 のインスタンス(例: Store < - > Author の場合の Author.Store )は IgnoreDataMember で JSON に出力されないようにする必要があります。
理由は、 Store > Author > Store ... と無限ループが発生するためですorz
Include を使った場合のフィルタリング
ここまでは全レコードを取得していました。
では、フィルタリングしたい場合はどうすれば良いでしょうか。
例えば Book.Available が true のレコードのみ取得したいとします。
こんなことをしようとすると。。。？
HomeController.cs
～省略～
public async Task< List< Store>> Index()
{
List< Store> results = await _context.Stores
.Include(store => store.Authors)
.ThenInclude(author => author.Books.Where(b => b.Available))
.ToListAsync();
return results;
}
}
}
当然のごとくエラーが発生します。
Include を使ったフィルタリングには対応していないようです。
となると。。。
HomeController.cs
～省略～
public async Task< List< Store>> Index()
{
List< Store> results = await _context.Stores
.Include(s => s.Authors)
.ThenInclude(a => a.Books)
.ToListAsync();
foreach (Store result in results)
{
foreach (Author author in result.Authors)
{
author.Books = author.Books.Where(b => b.Available).ToList();
}
}
return results;
}
}
}
この敗北感よ。。。
C# で JOIN した結果をまとめる
もう一つ、 INNER JOIN して取得した結果を、 C# でまとめる、というのも試したいと思います。
JOIN すると結果は画像のようにバラバラに出力されるので、リストにセットするようにします。
HomeController.cs
～省略～
public async Task< List< Store>> Index()
{
var results = await (from store in _context.Stores
join author in _context.Authors on store.Id equals author.StoreId
join book in _context.Books on author.Id equals book.AuthorId
where book.Available
select new
{
StoreId = store.Id,
StoreName = store.Name,
Author = author,
Book = book,
})
.ToListAsync();
List< Store> mergedResults = new List< Store>();
foreach (var result in results)
{
int index = mergedResults.FindIndex(m => m.Id == result.StoreId);
if(index < 0)
{
index = mergedResults.Count;
mergedResults.Add(new Store
{
Id = result.StoreId,
Name = result.StoreName,
Authors = new List()
});
};
int authorIndex = mergedResults[index].Authors.FindIndex(a => a.Id == result.Author.Id);
if (authorIndex < 0)
{
authorIndex = mergedResults[index].Authors.Count;
result.Author.Books = new List();
mergedResults[index].Authors.Add(result.Author);
}
mergedResults[index].Authors[authorIndex].Books.Add(result.Book);
}
}
}
}
長くなってきたのでいったん切ります。
参照
ASP.NET Core Identity でログイン・ログアウトしてみたい - AddAuthentication 1
はじめに
前回生成したプロジェクトをベースに、もう少し ASP.NET Core Identity (以下 Identity )について追いかけてみたいと思います。
構造
まず Programming ASP.NET Core によると、下記のような層に分かれて構成されているようです。
各層の役割を雑にまとめます。
- WebApplication ・・・ アプリケーション。 Identity を呼び出す。
- UserManager ・・・ 高レベル側のやり取りをする。
- UserStore ・・・ 低レベル側のやり取りをする。前回は Entity Framework Core を使った DB アクセスを行っていた。
- PhysicalStore ・・・ 実際にデータを保存する。今回は PostgreSQL。
前回 1., 3. のクラスは自作し(ほぼコピペですが)、 4.も用意しました。
ということで全く触れていないのは 2.のみですが、それぞれどのように動いているのかもう少し見ていきます。
IdentityBuilder
どこからたどっていくか。。。というところなのですが、とりあえず User や UserStore などを登録していた、 Startup > ConfigureServices から手を付けてみることにします。
なお NuGet などで追加インストールをしなくても利用できる ASP.NET Core Identity ですが、プロジェクトは分割されているようです。
こちらのコードも参照しながらだいたいどのようなことをしているのか、を追いかけてみたいと思います。
Startup.cs
～省略～
public void ConfigureServices(IServiceCollection services)
{
～省略～
services.AddIdentity< ApplicationUser, IdentityRole>()
.AddUserStore< ApplicationUserStore>()
.AddEntityFrameworkStores< LoginLogoutSampleContext>()
.AddDefaultTokenProviders();
～省略～
}
～省略～
User 、 Role をどこかに追加している AddIdentity ですが、このメソッドは Identity > src > Identity > IdentityServiceCollectionExtensions.cs に定義されています。
IdentityServiceCollectionExtensions.cs
public static IdentityBuilder AddIdentity< TUser, TRole>(
this IServiceCollection services)
where TUser : class
where TRole : class
=> services.AddIdentity< TUser, TRole>(setupAction: null);
ここでは IServiceCollection (実装クラスは Microsoft.Extensions.DependencyInjection.ServiceCollection )の拡張メソッドとして、 AddIdentity を追加しています。
参照
IdentityServiceCollectionExtensions.cs
～省略～
public static IdentityBuilder AddIdentity< TUser, TRole>(
this IServiceCollection services,
Action< IdentityOptions> setupAction)
where TUser : class
where TRole : class
{
// Services used by identity
services.AddAuthentication(options =>
{
options.DefaultAuthenticateScheme = IdentityConstants.ApplicationScheme;
options.DefaultChallengeScheme = IdentityConstants.ApplicationScheme;
options.DefaultSignInScheme = IdentityConstants.ExternalScheme;
})
.AddCookie(IdentityConstants.ApplicationScheme, o =>
{
o.LoginPath = new PathString("/Account/Login");
o.Events = new CookieAuthenticationEvents
{
OnValidatePrincipal = SecurityStampValidator.ValidatePrincipalAsync
};
})
.AddCookie(IdentityConstants.ExternalScheme, o =>
{
o.Cookie.Name = IdentityConstants.ExternalScheme;
o.ExpireTimeSpan = TimeSpan.FromMinutes(5);
})
.AddCookie(IdentityConstants.TwoFactorRememberMeScheme, o =>
{
o.Cookie.Name = IdentityConstants.TwoFactorRememberMeScheme;
o.Events = new CookieAuthenticationEvents
{
OnValidatePrincipal = SecurityStampValidator.ValidateAsync< ITwoFactorSecurityStampValidator>
};
})
.AddCookie(IdentityConstants.TwoFactorUserIdScheme, o =>
{
o.Cookie.Name = IdentityConstants.TwoFactorUserIdScheme;
o.ExpireTimeSpan = TimeSpan.FromMinutes(5);
});
// Hosting doesn't add IHttpContextAccessor by default
services.AddHttpContextAccessor();
// Identity services
services.TryAddScoped< IUserValidator< TUser>, UserValidator< TUser>>();
services.TryAddScoped< IPasswordValidator< TUser>, PasswordValidator< TUser>>();
services.TryAddScoped< IPasswordHasher< TUser>, PasswordHasher< TUser>>();
services.TryAddScoped< ILookupNormalizer, UpperInvariantLookupNormalizer>();
services.TryAddScoped< IRoleValidator< TRole>, RoleValidator< TRole>>();
// No interface for the error describer so we can add errors without rev'ing the interface
services.TryAddScoped< IdentityErrorDescriber>();
services.TryAddScoped< ISecurityStampValidator, SecurityStampValidator< TUser>>();
services.TryAddScoped< ITwoFactorSecurityStampValidator, TwoFactorSecurityStampValidator< TUser>>();
services.TryAddScoped< IUserClaimsPrincipalFactory< TUser>, UserClaimsPrincipalFactory< TUser, TRole>>();
services.TryAddScoped< UserManager< TUser>>();
services.TryAddScoped< SignInManager< TUser>>();
services.TryAddScoped< RoleManager< TRole>>();
if (setupAction != null)
{
services.Configure(setupAction);
}
return new IdentityBuilder(typeof(TUser), typeof(TRole), services);
}
～省略～
Oh...めっさ追加されてる。。。
大きく AddAuthentication による認証 + Cookie 関連の設定と、 TryAddScoped として 前回登場した PasswordHasher や SignInManager などの追加が行われているようです。
また、下記のように Identity を使わず AddAuthentication を直接使うこともできるようです。
見ただけでお腹いっぱいになりそうな気が早くもしていますが、力尽きるまで追いかけてみますよ。
services.AddAuthentication
まずは認証の部分から。
IdentityServiceCollectionExtensions.cs
～省略～
services.AddAuthentication(options =>
{
options.DefaultAuthenticateScheme = IdentityConstants.ApplicationScheme;
options.DefaultChallengeScheme = IdentityConstants.ApplicationScheme;
options.DefaultSignInScheme = IdentityConstants.ExternalScheme;
})
～省略～
ここでは AuthenticationOptions として、 HTTP 認証方法( AuthenticateScheme )を指定しているようです。
引数の Action< AuthenticationOptions> で渡している DefaultXXXXScheme ですが、各値は Identity > src > Identity > IdentityConstants.cs に定義されており、下記のような内容でした。
- Identity.Application
- Identity.External
DefaultAuthenticateScheme というのは Basic 認証(ユーザー名とパスワードによる認証)のことでしょうか。
次の DefaultChallengeScheme というのは、チャレンジレスポンス認証を指すようです。
サーバー側から nonce と呼ばれるランダムな値を送信し、クライアントでユーザー名、パスワードなどをハッシュ化して返し、サーバー側で nonce の値を元に受け取った値を復元 -> 認証する方式。
Challenge-response authentication (チャレンジレスポンス認証) - MDN Web Docs 用語集: ウェブ関連用語の定義 - MDN
- Understanding HTTP Authentication - Microsoft Docs
DefaultSignInScheme として External (外部の) スキーマを指定しています。何となく Twitter など外部サービスのアカウントで認証する、ということを想像してしまいますが、これとは違うのでしょうか。
AuthenticationBuilder
さてこの AddAuthentication ですが、 AspNetCore > src > Security > Authentication > Core > src > AuthenticationServiceCollectionExtensions.cs が呼ばれており、 AuthenticationBuilder が返されます。
AddAuthentication では先ほどの DefaultXXXXScheme の値をオプションとして services.Configure< AuthenticationOptions> に設定するほか、オーバーロードされた下記を呼び出します。
AuthenticationServiceCollectionExtensions.cs
～省略～
public static AuthenticationBuilder AddAuthentication(this IServiceCollection services)
{
if (services == null)
{
throw new ArgumentNullException(nameof(services));
}
services.AddAuthenticationCore();
services.AddDataProtection();
services.AddWebEncoders();
services.TryAddSingleton< ISystemClock, SystemClock>();
return new AuthenticationBuilder(services);
}
～省略～
ここでは 4 つのものが追加されているようです。
まずは services.AddAuthenticationCore() を見てみます。
services.AddAuthenticationCore()
services.AddAuthenticationCore() は、 AspNetCore > src > Http > Authentication.Core > src > AuthenticationCoreServiceCollectionExtensions.cs で定義されています。
AuthenticationCoreServiceCollectionExtensions.cs
～省略～
public static IServiceCollection AddAuthenticationCore(this IServiceCollection services)
{
if (services == null)
{
throw new ArgumentNullException(nameof(services));
}
services.TryAddScoped< IAuthenticationService, AuthenticationService>();
services.TryAddSingleton< IClaimsTransformation, NoopClaimsTransformation>(); // Can be replaced with scoped ones that use DbContext
services.TryAddScoped< IAuthenticationHandlerProvider, AuthenticationHandlerProvider>();
services.TryAddSingleton< IAuthenticationSchemeProvider, AuthenticationSchemeProvider>();
return services;
}
～省略～
また 4 つ追加されています。
まず初めの AuthenticationService ですが、これも AspNetCore > src > Http > Authentication.Core > src にあります。
コードを見ていくと、 AuthenticateAsync や SignInAsync など、前回 SignInManager を使ってログイン・ログアウトしたときのベースらしきメソッドが見つかります。
試しに SignInAsync を見てみることにします。
AuthenticationService.cs
～省略～ public virtual async Task SignInAsync(HttpContext context, string scheme, ClaimsPrincipal principal, AuthenticationProperties properties) { if (principal == null) { throw new ArgumentNullException(nameof(principal)); } if (scheme == null) { var defaultScheme = await Schemes.GetDefaultSignInSchemeAsync(); scheme = defaultScheme?.Name; if (scheme == null) { throw new InvalidOperationException($"No authenticationScheme was specified, and there was no DefaultSignInScheme found."); } } var handler = await Handlers.GetHandlerAsync(context, scheme); if (handler == null) { throw await CreateMissingSignInHandlerException(scheme); } var signInHandler = handler as IAuthenticationSignInHandler; if (signInHandler == null) { throw await CreateMismatchedSignInHandlerException(scheme, handler); } await signInHandler.SignInAsync(principal, properties); } ～省略～
引数として渡された scheme ( Identity.Application ) をもとに、 Handlers から IAuthenticationSignInHandler を取得し、 SignInAsync() を呼んでいる、と。
で、この Handlers というのが何か？というと AddAuthenticationCore() で 3 つ目に渡していた AuthenticationHandlerProvider です。
( AspNetCore > src > Http > Authentication.Core > src > AuthenticationHandlerProvider.cs )
AuthenticationService へは DI で渡されます。
今回 signInHandler として渡されているのは、 CookieAuthenticationHandler でした( BreakPoint を置いて確認)。
CookieAuthenticationHandler
この CookieAuthenticationHandler.cs は、 AspNetCore > src > Security > Authentication > Cookies > src にあります。
が、 SignInAsync は見つからない。。。(´・ω・｀)
AspNetCore > src > Security > Authentication > Core > src > SignInAuthenticationHandler.cs
と
AspNetCore > src > Http > Authentication.Abstractions > src > AuthenticationHttpContextExtensions.cs
を辿っていくと、下記のクラスにたどり着きました。
AuthenticationService.cs
／(^o^)＼
長くなってきたのでいったん切ります。
参照
ASP.NET Core Identity でログイン・ログアウトしてみたい 1
はじめに
前回の通り、 ASP.NET Core でのログイン・ログアウト機能を試してみたいと思います。
ASP.NET Core では ASP.NET Core Identity なるものが用意されている、ということで、これを利用することにします。
とりあえずの目標は、 ASP.NET Core Identity + Entity Framework Core + PostgreSQL を組み合わせてログイン・ログアウト機能を作ってみる、ということにします。
実際にはパスワードの暗号化など不足する部分がでてくるかもですが、一旦は標準で提供されているレベルの機能を使う、くらいに抑えたいと思います。
ASP.NET Core Identity のプロジェクトを作ってみる
先ほどの Microsoft Docs によると、下記コマンドを実行するとログイン・ログアウト機能を持ったプロジェクトが生成できます。
dotnet new webapp --auth Individual -o AspNetCoreIdentifySample
- 「AspNetCoreIdentifySample」はプロジェクト名です念のため。
生成されたプロジェクトを動かすと、ユーザー登録・ログイン・ログアウトを試すことができます。
コードがない
早速どんな処理が行われているのだろう、と思って生成されたコードを見てみると、ユーザー登録やログインに関連しそうなコードが見当たりません。
なん.........だと.........!?
関連しそうなのは下記ぐらいです( DB を除くと)。
Pages/Shared/_LoginPartial.cshtml
@using Microsoft.AspNetCore.Identity @inject SignInManager< IdentityUser> SignInManager @inject UserManager< IdentityUser> UserManager < ul class="navbar-nav"> @if (SignInManager.IsSignedIn(User)) { < li class="nav-item"> < a class="nav-link text-dark" asp-area="Identity" asp-page="/Account/Manage/Index" title="Manage">Hello @User.Identity.Name!< /a> < /li> < li class="nav-item"> < form class="form-inline" asp-area="Identity" asp-page="/Account/Logout" asp-route-returnUrl="@Url.Page("/", new { area = "" })" method="post" > < button type="submit" class="nav-link btn btn-link text-dark">Logout< /button> < /form> < /li> } else { < li class="nav-item"> < a class="nav-link text-dark" asp-area="Identity" asp-page="/Account/Register">Register< /a> < /li> < li class="nav-item"> < a class="nav-link text-dark" asp-area="Identity" asp-page="/Account/Login">Login< /a> < /li> } < /ul>
Startup.cs
using Microsoft.AspNetCore.Builder; using Microsoft.AspNetCore.Identity; using Microsoft.AspNetCore.Identity.UI; using Microsoft.AspNetCore.Hosting; using Microsoft.AspNetCore.Http; using Microsoft.AspNetCore.Mvc; using Microsoft.EntityFrameworkCore; using AspNetCoreIdentifySample.Data; using Microsoft.Extensions.Configuration; using Microsoft.Extensions.DependencyInjection; namespace AspNetCoreIdentifySample { public class Startup { public Startup(IConfiguration configuration) { Configuration = configuration; } public IConfiguration Configuration { get; } // This method gets called by the runtime. Use this method to add services to the container. public void ConfigureServices(IServiceCollection services) { ～省略～ services.AddDbContext< ApplicationDbContext>(options => options.UseSqlite( Configuration.GetConnectionString("DefaultConnection"))); services.AddDefaultIdentity< IdentityUser>() .AddDefaultUI(UIFramework.Bootstrap4) .AddEntityFrameworkStores< ApplicationDbContext>(); ～省略～ } // This method gets called by the runtime. Use this method to configure the HTTP request pipeline. public void Configure(IApplicationBuilder app, IHostingEnvironment env) { ～省略～ app.UseAuthentication(); app.UseMvc(); } } }
_LoginPartial.cshtml はログインページなどへのリンクしか持っていないため、カギを握っているのは Startup での設定となります。
またログインページの UI は、 _Layout.cshtml を下記のような非常にシンプルなものにしても変わりませんでした。
Pages/Shared/_Layout.cshtml
< !DOCTYPE html>
< html>
< head>
< meta charset="utf-8" />
< /head>
< body>
@RenderBody()
< /body>
< /html>
以上から、完全に別のところからログインページは来ていることがわかります。
Startup.cs の ConfigureServices の引数として渡されている IServiceCollection には、「 services.AddDefaultIdentity< IdentityUser>() 」 の他に 「 services.AddIdentity< T>() 」 なるメソッドも用意されています。
ログイン・ログアウトなどの設定をデフォルトから変更するには、どうやらこの辺りを設定してやれば良さそうです。
と、ドキュメントもロクに読まずに当たりをつけたところで、あれこれ試してみたいと思います。
シンプルなユーザー情報でログインしたい
テンプレートを利用して生成したユーザー情報は、 Data > Migrations のファイルなどを見るとわかりますが、かなり項目が多いです。
- Id
- UserName
- NormalizedUserName
- NormalizedEmail
- EmailConfirmed
- PasswordHash
- SecurityStamp
- ConcurrencyStamp
- PhoneNumber
- PhoneNumberConfirmed
- TwoFactorEnabled
- LockoutEnd
- LockoutEnabled
- AccessFailedCount
今どきのセキュリティなどを考えれば妥当なのだろうとは思うのですが、とにかく多い。
今回は、必要に応じて項目を追加していくことにして、まず下記の項目だけを使ってログインできるようにしてみたいと思います。
- Id
- UserName
- Password
また手抜きでいきたいので、パスワードもハッシュ化せずに DB に登録しちゃいます(本番環境では決してマネしないでねはーと)。
項目も少ないし、さらっと試してもう少し詳しく調べてみたい。
この時の僕はそんな風に軽く考えていたのです。。。
準備( DB )
とりあえず DB 側の準備です。
ユーザー情報を入れるテーブルだけを用意して、ユーザーを雑に追加します。
CREATE TABLE "User" (
"UserId" Integer PRIMARY KEY,
"Name" TEXT NOT NULL,
"Password" TEXT NOT NULL
)
INSERT INTO "User" (
"UserId",
"Name",
"Password"
)
VALUES(
0,
'Example1',
'Test0123'
)
準備 ( ASO.NET Core )
こちらもシンプルに作りたいため、 Empty でプロジェクトを生成し、スキャフォールドもせずに作っていきます。
NuGet で Microsoft.EntityFrameworkCore と Npgsql.EntityFrameworkCore.PostgreSQL をインストールしておきます。
Startup.cs
using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using LoginLogoutSample.Models; using Microsoft.AspNetCore.Builder; using Microsoft.AspNetCore.Hosting; using Microsoft.AspNetCore.Http; using Microsoft.AspNetCore.Identity; using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.Configuration; using Microsoft.Extensions.DependencyInjection; namespace LoginLogoutSample { public class Startup { public IConfiguration Configuration { get; } public Startup(IConfiguration configuration) { Configuration = configuration; } public void ConfigureServices(IServiceCollection services) { services.AddDbContext< LoginLogoutSampleContext>(options => options.UseNpgsql("Host=localhost;Database=LoginLogoutSample;Username=postgres;Password=XXXX")); services.AddDefaultIdentity< ApplicationUser>() .AddEntityFrameworkStores< LoginLogoutSampleContext>() .AddDefaultTokenProviders(); services.AddMvc(); } public void Configure(IApplicationBuilder app, IHostingEnvironment env) { if (env.IsDevelopment()) { app.UseDeveloperExceptionPage(); } app.UseAuthentication(); app.UseMvc(); } } }
- DB 設定の他、先ほどのテンプレートを参考に、 AddDefaultIdentity や UseAuthentication を追加しています。
HomeController.cs
using System; using System.Threading.Tasks; using LoginLogoutSample.Models; using Microsoft.AspNetCore.Identity; using Microsoft.AspNetCore.Mvc; using SignInResult = Microsoft.AspNetCore.Identity.SignInResult; namespace LoginLogoutSample.Controllers { public class HomeController: Controller { private readonly SignInManager< ApplicationUser> _signInManager; public HomeController(SignInManager< ApplicationUser> signInManager) { _signInManager = signInManager; } [Route("")] [Route("/Home")] public async TaskIndex() { await Login(); return "hello"; } [Route("/Account/Login")] public async Task Login() { ApplicationUser debugUser = new ApplicationUser { UserName = "Example1", Password = "Test0123", }; SignInResult result = await _signInManager.PasswordSignInAsync( debugUser.UserName, debugUser.Password, false, false); if (result.Succeeded) { Console.Write("Success!"); } else { Console.WriteLine("Failed"); } } } }
- ログインができるかを確認するため、固定でデータを作成し、ログインするようにしています。
ApplicationUser.cs
using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using Microsoft.AspNetCore.Identity; namespace LoginLogoutSample.Models { [Table("User")] public class ApplicationUser: IdentityUser { [Key] [Column("UserId")] public int ApplicationUserId { get; set; } [Column("Name")] public override string UserName { get; set; } public string Password { get; set; } } }
ユーザー情報を格納するクラスです。
ASP.NET Framework 時代は IUser インターフェースを継承して、独自のユーザーを作ることができていたようなのですが、 ASP.NET Core では IdentityUser のみに限定されているようです(調べた限りでは)。
LoginLogoutSampleContext.cs
using Microsoft.EntityFrameworkCore; namespace LoginLogoutSample.Models { public class LoginLogoutSampleContext: DbContext { public LoginLogoutSampleContext(DbContextOptions< LoginLogoutSampleContext> options) : base(options) { } public DbSet< ApplicationUser> Users { get; set; } } }
- Entity Framework Core を使って DB に接続するときと同じですね。
そしてエラー
これを実行すると、なんとエラーになります。
ええもう当然かのように。
理由は AccessFailedCount などのカラムがテーブルにないからです。
上で触れたように、 ASP.NET Core では ASP.NET Core Identity を使う場合、ユーザー情報は IdentityUser 、またはそれを継承したクラスで持つ必要があるようです。
なんでデフォルトをこんなモリモリにしたんや。。。
ということで、考えられる解決方法は 2 つあります。
- テーブルに残りの項目を追加する
- 不要な項目はテーブルにマッピングしないようにする
1.は使いもしないカラムを増やしまくるのがつらいため、 2.でいきたいところ。
Model クラスで、 DB にマッピングしないようにするには、 NotMapped を使います。
ApplicationUser.cs
using System; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using Microsoft.AspNetCore.Identity; namespace LoginLogoutSample.Models { [Table("User")] public class ApplicationUser: IdentityUser { [Key] [Column("UserId")] public int ApplicationUserId { get; set; } [Column("Name")] public override string UserName { get; set; } public string Password { get; set; } [NotMapped] public override string Id { get; set; } [NotMapped] public override string NormalizedUserName { get; set; } [NotMapped] public override string Email { get; set; } [NotMapped] public override string NormalizedEmail { get; set; } [NotMapped] public override bool EmailConfirmed { get; set; } [NotMapped] public override string PasswordHash { get; set; } [NotMapped] public override string SecurityStamp { get; set; } [NotMapped] public override string ConcurrencyStamp { get; set; } [NotMapped] public override string PhoneNumber { get; set; } [NotMapped] public override bool PhoneNumberConfirmed { get; set; } [NotMapped] public override bool TwoFactorEnabled { get; set; } [NotMapped] public override DateTimeOffset? LockoutEnd { get; set; } [NotMapped] public override bool LockoutEnabled { get; set; } [NotMapped] public override int AccessFailedCount { get; set; } } }
うーむ。。。思うことがないではないのですが、とにかくエラーは出なくなりました。
ログインできない
確かにこれでエラーは出なくなったのですが、相変わらずログインには失敗します。
というのも、 SignInManager.PasswordSignInAsync で引数として渡したユーザー名、パスワードをそのまま使うのではなく、 NotMapped で無視している NormalizedUserName や PasswordHash を使って認証するためです。
ということで、この参照部分を変更しなければなりません。
結局どうしたかというと、下記などを参考に、 IUserPasswordStore を継承したクラスを用意し、認証時に呼ばれるよう設定しました。
Startup.cs
～省略～
public void ConfigureServices(IServiceCollection services)
{
～省略～
services.AddIdentity< ApplicationUser, IdentityRole>()
.AddUserStore< ApplicationUserStore>()
.AddEntityFrameworkStores< LoginLogoutSampleContext>()
.AddDefaultTokenProviders();
services.AddMvc();
}
～省略～
- 今回 UserRole については特に何も変更していないため、デフォルトで用意されているクラスをそのまま使っています。
LoginLogoutSampleContext
using System.Threading; using System.Threading.Tasks; using Microsoft.AspNetCore.Identity; using Microsoft.EntityFrameworkCore; namespace LoginLogoutSample.Models { public class ApplicationUserStore: IUserPasswordStore< ApplicationUser> { private LoginLogoutSampleContext _context; public ApplicationUserStore(LoginLogoutSampleContext context) { _context = context; } public void Dispose() { /* とりあえず何もしない */ } public Task< string> GetUserIdAsync(ApplicationUser user, CancellationToken cancellationToken) { return Task.Run(() => user.ApplicationUserId.ToString(), cancellationToken); } public Task< string> GetUserNameAsync(ApplicationUser user, CancellationToken cancellationToken) { return Task.Run(() => user.UserName,cancellationToken); } public Task SetUserNameAsync(ApplicationUser user, string userName, CancellationToken cancellationToken) { throw new System.NotImplementedException(); } public Task< string> GetNormalizedUserNameAsync(ApplicationUser user, CancellationToken cancellationToken) { return Task.Run(() => user.UserName.ToUpper(),cancellationToken); } public Task SetNormalizedUserNameAsync(ApplicationUser user, string normalizedName, CancellationToken cancellationToken) { throw new System.NotImplementedException(); } public Task< IdentityResult> CreateAsync(ApplicationUser user, CancellationToken cancellationToken) { throw new System.NotImplementedException(); } public Task< IdentityResult> UpdateAsync(ApplicationUser user, CancellationToken cancellationToken) { throw new System.NotImplementedException(); } public Task< IdentityResult> DeleteAsync(ApplicationUser user, CancellationToken cancellationToken) { throw new System.NotImplementedException(); } public Task< ApplicationUser> FindByIdAsync(string userId, CancellationToken cancellationToken) { return _context.Users.FirstOrDefaultAsync(u => u.ApplicationUserId.ToString() == userId, cancellationToken); } public Task< ApplicationUser> FindByNameAsync(string normalizedUserName, CancellationToken cancellationToken) { return _context.Users.FirstOrDefaultAsync(u => u.UserName.ToUpper() == normalizedUserName, cancellationToken); } public Task SetPasswordHashAsync(ApplicationUser user, string passwordHash, CancellationToken cancellationToken) { throw new System.NotImplementedException(); } public Task< string> GetPasswordHashAsync(ApplicationUser user, CancellationToken cancellationToken) { return Task.Run(() => new PasswordHasher< ApplicationUser>().HashPassword(user, user.Password), cancellationToken); } public Task< bool> HasPasswordAsync(ApplicationUser user, CancellationToken cancellationToken) { return Task.Run(() => string.IsNullOrEmpty(user?.Password), cancellationToken); } } }
- 今回ユーザー情報の追加や編集を行っていないため、実装していないメソッドが結構あります(某勉強会参加の方に見られたら怒られそうです(;´Д｀))。
- IUserStore も継承が必要そうな気がしますが、 IUserPasswordStore が継承しているため不要だそうです。
- NormalizedUserName は登録されたユーザー名を大文字に変換したものです。
- GetPasswordHashAsync は、その名の通りハッシュ化されたパスワードを返す必要があり、そのまま返すとエラーになります。 PasswordHasher.HashPassword を使って変換したものを返すようにします。
不足メソッドの生成
話がそれますが、 ApplicationUserStore を実装する際一つ一つのメソッドを手動で追加していくのはなかなか大変ですね。
Rider だと、 Interface (IUserPasswordStore) を追加し、エラーが発生した状態で 右クリック > Generate > Missing members からひとまとめで追加することができます。
中身は全部 throw new System.NotImplementedException() のため、手動で実装が必要であるとはいえ便利ですね。
おわりに
曲がりなりにもカスタムのユーザー情報を使ったログインもできるようになりました。
次回は IUserPasswordStore や SignInManager など今回登場したクラス・インターフェースを中心に、もう少し追いかけてみたいと思います。
参照
Entity Framework Core で色々な SQL を投げてみる 3
はじめに
ここまで SQL ゼロからはじめるデータベース操作 を参考に、色々な SELECT 文を投げてみました。
本当はこの後 CROSS JOIN やウインドウ関数があるのですが、どうやら標準では用意されていない様子。
データを分析するウインドウ関数については Raw SQL で実行するにせよ C# の処理として実行するにせよ、追いかけてみたいとは思います。
が、まずは CRUD の残りの Create(Insert)、 Update 、 Delete を試してみたいと思います。
EntityState
Insert 、 Update 、 Delete はどれも DB の値を変更する処理なわけですが、これが実行されるには次のステップを踏むことになります。
- EntityState の値を変更する
- SaveChangesAsync (SaveChanges) で 1. を反映する
1.については直接そのようなコードを書くのではなく、 Add (AddRange) 、 Update (UpdateRange) 、 Remove (RemoveRange) を実行したときに自動で変更されます。
(現在の EntityState の内容を直接見たり変更したりすることはできないようです)
EntityState の種類はこの辺りを参照。
この後見ていきますが、レコードの追加や削除は、 Add 、 Remove と List に対して行うのとほぼ同じ処理でできるのですが、 SaveChangesAsync を実行しないと DB には反映されないため、注意が必要です。
実行する
ともあれ、まずはあれこれ実行してみますよ。
[INSERT] レコードの追加
レコードの追加から。
コード
var newBook = new Book
{
BookId = 996,
AuthorId = 0,
Name = "hello"
};
_context.Add(newBook);
await _context.SaveChangesAsync();
発行される SQL
INSERT INTO "Book" ("BookId", "AuthorId", "Genre", "Name", "Price", "ReleaseDate", "Thumbnail")
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6);
[UPDATE] レコードの更新
コード
Book addedBook = await _context.Book.FirstAsync(b => b.BookId == 996); addedBook.Genre = "Comic"; _context.Update(addedBook); await _context.SaveChangesAsync();
発行される SQL
SELECT b."BookId", b."AuthorId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
FROM "Book" AS b
WHERE b."BookId" = 996
LIMIT 1
UPDATE "Book" SET "AuthorId" = @p0, "Genre" = @p1, "Name" = @p2, "Price" = @p3, "ReleaseDate" = @p4, "Thumbnail" = @p5
WHERE "BookId" = @p6;
[DELETE] レコードの削除
コード
Book addedBook = await _context.Book.FirstAsync(b => b.BookId == 996); _context.Remove(addedBook); await _context.SaveChangesAsync();
発行される SQL
SELECT b."BookId", b."AuthorId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
FROM "Book" AS b
WHERE b."BookId" = 996
LIMIT 1
DELETE FROM "Book"
WHERE "BookId" = @p0;
Key が無い場合
UPDATE 、 DELETE はそれぞれ主キーである BookId で対象のレコードを検索していました。
では主キーが無い場合はどうなるのか。
というと、 DbContext (EfCoreNpgsqlSampleContext) の初期化時に InvalidOperationException が発生します。
理由は主キーが見つからないから。
怖い。。。
テーブルで主キーが設定されていることがわかっている場合は問題ありませんが、そうでない場合は Model クラスの主キーとなる要素に [Key] 属性を持たせる必要があります。
Author2.cs ( テーブルでは主キーを未設定)
using System.ComponentModel.DataAnnotations;
namespace EfCoreNpgsqlSample.Models
{
public class Author2
{
// 属性を外すとエラー
[Key]
public int AuthorId { get; set; }
public string Name { get; set; }
}
}
もちろんテーブルに主キーが設定されている場合も [Key] 属性を付けても問題ないため、主キーの設定有無によらず必ず付けてあげるのが良い気がします。
組み合わせ
先ほど触れた通り、 CREATE や UPDATE は SaveChangesAsync が実行されて初めて実行されます。
ということは、 EntityState を変更する処理を複数実行し、まとめて反映することも可能なのでは？
ということでやってみました。
AddRange でまとめて登録
組み合わせではありませんが、 Add には、 AddRange のような複数データをまとめて追加するメソッドが用意されています ( Update 、 Remove も同様)。
これを使った場合、要素数分 SQL が発行されるのでしょうか。
それとも、別の方法でデータが追加されるのでしょうか。
コード
List< Book> books = new List< Book>
{
new Book
{
BookId = 996,
AuthorId = 0,
Name = "hello"
},
new Book
{
BookId = 995,
AuthorId = 0,
Name = "hello1"
},
};
await _context.AddRangeAsync(books);
await _context.SaveChangesAsync();
発行される SQL
INSERT INTO "Book" ("BookId", "AuthorId", "Genre", "Name", "Price", "ReleaseDate", "Thumbnail")
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6);
INSERT INTO "Book" ("BookId", "AuthorId", "Genre", "Name", "Price", "ReleaseDate", "Thumbnail")
VALUES (@p7, @p8, @p9, @p10, @p11, @p12, @p13);
シンプルに 2 回 INSERT が実行されました。
Add を複数回実行してから反映する
コード
_context.Add(newBook); _context.Add(newBook2); await _context.SaveChangesAsync();
発行される SQL
INSERT INTO "Book" ("BookId", "AuthorId", "Genre", "Name", "Price", "ReleaseDate", "Thumbnail")
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6);
INSERT INTO "Book" ("BookId", "AuthorId", "Genre", "Name", "Price", "ReleaseDate", "Thumbnail")
VALUES (@p7, @p8, @p9, @p10, @p11, @p12, @p13);
結果としては AddRange と同じ内容となりました。
Add したデータを反映前に Remove する
コード
_context.Add(newBook); _context.Remove(newBook); await _context.SaveChangesAsync();
INSERT も DELETE も実行はされませんでした。
Add したデータを反映前に Update する
コード
_context.Add(newBook); _context.Update(newBook); await _context.SaveChangesAsync();
似たような発想で DB 反映前に Update を実行してみたところ、 UPDATE 対象のレコードが見つからないとエラーになりました。
SQL 実行時に(実行順ではない)優先順位が付けられており、 CREATE より UPDATE が先に実行されたようです。
これは主キーの重複を避けるためだと思われます。
トランザクション
DB の値を変更する処理では、処理の途中でエラーが発生した場合などに、変更が中途半端に反映されたりしないようトランザクションを使ってDBへの反映をコントロールします。
Entity Framework Core では、 DbContext が持つ Database でトランザクションがコントロールされています。
using (IDbContextTransaction transaction = _context.Database.BeginTransaction())
{
try
{
await _context.AddRangeAsync(books);
await _context.SaveChangesAsync();
// DB に変更が反映される.
transaction.Commit();
}
catch (Exception e)
{
// BeginTransaction より前の状態に巻き戻される.
transaction.Rollback();
}
}
この例だとほぼトランザクションの意味がないわけですが、複数のテーブルの値を更新する場合などに効力を発揮します。
Commit しない場合
Commit しない場合、 SQL 自体は発行されるのでしょうか。
コード
using (IDbContextTransaction transaction = _context.Database.BeginTransaction())
{
try
{
await _context.AddRangeAsync(books);
await _context.SaveChangesAsync();
// DB に変更が反映される.
//transaction.Commit();
}
catch (Exception e)
{
// BeginTransaction より前の状態に巻き戻される.
transaction.Rollback();
}
}
発行される SQL
INSERT INTO "Book" ("BookId", "AuthorId", "Genre", "Name", "Price", "ReleaseDate", "Thumbnail")
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6);
INSERT INTO "Book" ("BookId", "AuthorId", "Genre", "Name", "Price", "ReleaseDate", "Thumbnail")
VALUES (@p7, @p8, @p9, @p10, @p11, @p12, @p13);
SQL は発行されました。が、 DB には値が追加されませんでした。
おわりに
だいぶ端折りはしたものの、 Entity Framework Core を使った簡単な DB 操作は何となく触れられたんじゃないかな～、と思います。
Linq 的に書けたり、 BeginTransaction が using でスコープ設定できるところなど、 C# 的に DB 操作が書ける、というのはやはり便利ですね。
次はいよいよ DbContext …と言いたいところなのですが、諸事情により ASP.NET Core のログイン・ログアウトを色々試してみることにする予定です。
