ASP.NET Core Identity でログイン・ログアウトしてみたい - AddAuthentication 1

• はじめに
• 構造
• IdentityBuilder
• services.AddAuthentication
  • AuthenticationBuilder
  • services.AddAuthenticationCore()
  • CookieAuthenticationHandler
• 参照

はじめに

• ASP.NET Core Identity でログイン・ログアウトしてみたい 1

前回生成したプロジェクトをベースに、もう少し ASP.NET Core Identity (以下 Identity )について追いかけてみたいと思います。

構造

まず Programming ASP.NET Core によると、下記のような層に分かれて構成されているようです。

各層の役割を雑にまとめます。

1. WebApplication ・・・ アプリケーション。 Identity を呼び出す。
2. UserManager ・・・ 高レベル側のやり取りをする。
3. UserStore ・・・ 低レベル側のやり取りをする。前回は Entity Framework Core を使った DB アクセスを行っていた。
4. PhysicalStore ・・・ 実際にデータを保存する。今回は PostgreSQL。

前回 1., 3. のクラスは自作し(ほぼコピペですが)、 4.も用意しました。

ということで全く触れていないのは 2.のみですが、それぞれどのように動いているのかもう少し見ていきます。

IdentityBuilder

どこからたどっていくか。。。というところなのですが、とりあえず User や UserStore などを登録していた、 Startup > ConfigureServices から手を付けてみることにします。

なお NuGet などで追加インストールをしなくても利用できる ASP.NET Core Identity ですが、プロジェクトは分割されているようです。

• Identity - aspnet - GitHub

こちらのコードも参照しながらだいたいどのようなことをしているのか、を追いかけてみたいと思います。

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 を追加しています。

参照

• 拡張メソッド - C# によるプログラミング入門 - ++C++; // 未確認飛行 C

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 を直接使うこともできるようです。

• ASP.NET Core 2.0 Authentication - Qiita

見ただけでお腹いっぱいになりそうな気が早くもしていますが、力尽きるまで追いかけてみますよ。

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 にあります。

https://github.com/aspnet/AspNetCore/blob/master/src/Http/Authentication.Core/src/AuthenticationService.cs

コードを見ていくと、 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^)＼

長くなってきたのでいったん切ります。

参照

• Programming ASP.NET Core
• Identity - aspnet - GitHub
• AspNetCore - aspnet - GitHub
• ASP.NET Core 2.0 Authentication - Qiita
• Introduction to Authentication with ASP.NET Core

ASP.NET Core Identity でログイン・ログアウトしてみたい 1

• はじめに
• ASP.NET Core Identity のプロジェクトを作ってみる
• シンプルなユーザー情報でログインしたい
• そしてエラー
• 不足メソッドの生成
• おわりに
• 参照

はじめに

前回の通り、 ASP.NET Core でのログイン・ログアウト機能を試してみたいと思います。

ASP.NET Core では ASP.NET Core Identity なるものが用意されている、ということで、これを利用することにします。

• Introduction to Identity on ASP.NET Core - Microsoft Docs

とりあえずの目標は、 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
• Email
• 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 Task Index()
        {
            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. 不要な項目はテーブルにマッピングしないようにする

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 を継承したクラスを用意し、認証時に呼ばれるよう設定しました。

• ASP.NET Identityカスタマイズに挑戦 - かずきのBlog@hatena

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 など今回登場したクラス・インターフェースを中心に、もう少し追いかけてみたいと思います。

参照

• Programming ASP.NET Core
• Introduction to Identity on ASP.NET Core - Microsoft Docs
• ASP.NET Identityカスタマイズに挑戦 - かずきのBlog@hatena

Entity Framework Core で色々な SQL を投げてみる 3

• はじめに
• EntityState
• 実行する
  • [INSERT] レコードの追加
  • [UPDATE] レコードの更新
  • [DELETE] レコードの削除
  • Key が無い場合
• 組み合わせ
  • AddRange でまとめて登録
  • Add を複数回実行してから反映する
  • Add したデータを反映前に Remove する
  • Add したデータを反映前に Update する
• トランザクション
• おわりに

はじめに

• Entity Framework Core で色々な SQL を投げてみる 1
• Entity Framework Core で色々な SQL を投げてみる 2

ここまで SQL ゼロからはじめるデータベース操作 を参考に、色々な SELECT 文を投げてみました。

本当はこの後 CROSS JOIN やウインドウ関数があるのですが、どうやら標準では用意されていない様子。

データを分析するウインドウ関数については Raw SQL で実行するにせよ C# の処理として実行するにせよ、追いかけてみたいとは思います。

が、まずは CRUD の残りの Create(Insert)、 Update 、 Delete を試してみたいと思います。

EntityState

Insert 、 Update 、 Delete はどれも DB の値を変更する処理なわけですが、これが実行されるには次のステップを踏むことになります。

1. EntityState の値を変更する
2. SaveChangesAsync (SaveChanges) で 1. を反映する

1.については直接そのようなコードを書くのではなく、 Add (AddRange) 、 Update (UpdateRange) 、 Remove (RemoveRange) を実行したときに自動で変更されます。
(現在の EntityState の内容を直接見たり変更したりすることはできないようです)

EntityState の種類はこの辺りを参照。

• EntityState Enum (System.Data) - Microsoft Docs

この後見ていきますが、レコードの追加や削除は、 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 には値が追加されませんでした。

• 【参照】Transactions - EF Core - Microsoft Docs

おわりに

だいぶ端折りはしたものの、 Entity Framework Core を使った簡単な DB 操作は何となく触れられたんじゃないかな～、と思います。

Linq 的に書けたり、 BeginTransaction が using でスコープ設定できるところなど、 C# 的に DB 操作が書ける、というのはやはり便利ですね。

次はいよいよ DbContext …と言いたいところなのですが、諸事情により ASP.NET Core のログイン・ログアウトを色々試してみることにする予定です。

Entity Framework Core で色々な SQL を投げてみる 2

• はじめに
• SELECT 2
  • [LIKE]前方一致で検索する
  • [LIKE]中間一致で検索する
  • [LIKE]後方一致で検索する
  • 正規表現で検索する
  • [BETWEEN] 価格帯で検索する
  • [WHERE IN] 価格リストに合致するレコードを検索する
  • [EXISTS] 価格が 5000 円以上のレコードが存在するか
  • [CASE] CASE 式を使う
  • [UNION ALL] 2 つのテーブルの検索結果を足し合わせる(重複あり)
  • [UNION] 2 つのテーブルの検索結果を足し合わせる(重複なし)
  • [INTERSECT] 2 つのテーブルの共通部分を取り出す
  • [EXCEPT] 2 つのテーブルで共通しないレコードのみを取り出す
  • [INNER JOIN] 複数のテーブルを参照する(内部結合)
  • [LEFT OUTER JOIN] 複数のテーブルを参照する(外部結合)

はじめに

• Entity Framework Core で色々な SQL を投げてみる 1

SELECT 文の続きです。

今回も SQL ゼロからはじめるデータベース操作 を参考に、色々試してみますよ。

SELECT 2

[LIKE]前方一致で検索する

コード

await _context.Book.Where(b => b.Name.StartsWith("Entity")).ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      WHERE b."Name" LIKE 'Entity%'

[LIKE]中間一致で検索する

コード

await _context.Book.Where(b => b.Name.Contains("Framework")).ToListAsync();

発行される SQL

SELECT b."BookId", b."Name", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      WHERE STRPOS(b."Name", 'Framework') > 0

むむむ。発行したかった SQL は「 WHERE b."Genre" LIKE '%Entity%' 」だったのですが。。。

あくまで %QUERY% にこだわるなら、もう少し別の方法もあるかもしれませんね。

[参照]

• LINQ：文字列コレクションで「LIKE検索」（部分一致検索）をするには？［C#、VB］：.NET TIPS - ＠IT

[LIKE]後方一致で検索する

コード

await _context.Book.Where(b => b.Name.EndsWith("Action")).ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      WHERE RIGHT(b."Name", LENGTH('Action')) = 'Action'

むぅ。。。これも違う。

結果が正しいので良い。。。のかな？

正規表現で検索する

コード

System.Text.RegularExpressions.Regex regex1 = new System.Text.RegularExpressions.Regex("Action$");
await _context.Book.Where(b => regex1.IsMatch(b.Name)).ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b

Where ごと消えた／(^o^)＼

この辺は発行された SQL の内容で確認するのが難しそうですねぇ。。。

warn: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'where __regex1_0.IsMatch([b].Name)' could not be translated and will be evaluated locally.

[BETWEEN] 価格帯で検索する

コード

await _context.Book.Where(b => b.Price >= 500m && b.Price <= 5000m).ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      WHERE (b."Price" >= 500.0) AND (b."Price" <= 5000.0)

ですよね～、という結果ですが、どうやら Linq に BETWEEN 的なものはなさそうです。

[WHERE IN] 価格リストに合致するレコードを検索する

コード

await _context.Book.Where(b => new []{10m, 500m, 7000m}.Contains(b.Price)).ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      WHERE b."Price" IN (10.0, 500.0, 7000.0)

なお、下記のように配列部分を変更しても同じ結果となりました。

var prices = new[] {10m, 500m, 7000m};
await _context.Book.Where(b => prices.Contains(b.Price)).ToListAsync();

[EXISTS] 価格が 5000 円以上のレコードが存在するか

コード

_context.Book.Any(b => b.Price >= 5000m);

発行される SQL

 SELECT CASE
          WHEN EXISTS (
              SELECT 1
              FROM "Book" AS b
              WHERE b."Price" >= 5000.0)
          THEN TRUE::bool ELSE FALSE::bool
      END

おっと。ここで CASE が登場しましたね。

THEN 以降は、 C# と SQL で型を合わせるためなのでしょうか。

[CASE] CASE 式を使う

コード

_context.Book.Select(b => (b.Price >= 5000m)? 1:0 ).ToList();

発行される SQL

SELECT CASE
          WHEN b."Price" >= 5000.0
          THEN 1 ELSE 0
      END
      FROM "Book" AS b

Linq には Case が無いようです。

そのため Entity Framework Core でも使えない。。。と思いきや、参考演算子にすると CASE 式に変換されれるようです。

なお上記サンプルの雑さは見ないことにしてくださいorz

[UNION ALL] 2 つのテーブルの検索結果を足し合わせる(重複あり)

準備

Book テーブルと同じレイアウトで Book2 というテーブルを作ります。

Book2

CREATE TABLE "Book2" (
"BookId" integer PRIMARY KEY,
"Genre" text,
"Name" text,
"Price" numeric,
"ReleaseDate" timestamp without time zone,
"Thumbnail" bytea)

ここにいくつかのレコードは Book と重複する内容で、雑にレコードを追加します。

C# 側でも、 Model クラスと Context クラスへの追加を行います。

Book2.cs

using System;

namespace EfCoreNpgsqlSample.Models
{
    public class Book2
    {
        [Key]
        public int BookId { get; set; }
        public string Name { get; set; }
        public DateTime ReleaseDate { get; set; }
        public string Genre { get; set; }
        public decimal Price { get; set; }
        public byte[] Thumbnail { get; set; }
    }
}

• BookId に [Key] がついているのは、 CREATE TABLE 時に BookId を PRIMARY KEY として登録したためです(外すとエラーになります)。

EfCoreNpgsqlSampleContext.cs

using Microsoft.EntityFrameworkCore;

namespace EfCoreNpgsqlSample.Models
{
    public class EfCoreNpgsqlSampleContext: DbContext
    {
        public EfCoreNpgsqlSampleContext(DbContextOptions< EfCoreNpgsqlSampleContext> options)
            : base(options)
        {            
        }  
        public DbSet< Book> Book { get; set; }

        public DbSet< Book2> Book2 { get; set; }

    }
}

で、 Book 、 Book2 への検索結果を足し合わせたい、というのが目標です。

SQL はこちら。

SELECT "BookId", "Genre", "Name", "Price", "ReleaseDate"
FROM "Book"
UNION ALL
SELECT "BookId", "Genre", "Name", "Price", "ReleaseDate"
FROM "Book2"

SQL はこれで良いのですが、 C# では Book と Book2 が別のクラスとして扱われるので、これをそのまま実行することができません。

Book2 の結果を受け取った後、 Book に変換したあと Concat で結合します。

コード

IQueryable< Book> books1 = _context.Book
    .Where(b => b.Name != null);
IQueryable< Book> books2 = _context.Book2
    .Where(b => b.Name != null)
    .Select(b => new Book
    {
        BookId = b.BookId,
        Name = b.Name,
        Genre = b.Genre,
        Price = b.Price,
        ReleaseDate = b.ReleaseDate
    });
await books1.Concat(books2).ToListAsync();

Book テーブルと Book2 テーブルに対してそれぞれ SELECT を実行しているため、発行される SQL も 2 つに分かれています。

発行される SQL

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      WHERE b."Name" IS NOT NULL
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT b0."BookId", b0."Name", b0."Genre", b0."Price", b0."ReleaseDate"
      FROM "Book2" AS b0
      WHERE b0."Name" IS NOT NULL

[UNION] 2 つのテーブルの検索結果を足し合わせる(重複なし)

Concat で結合するだけだと、重複するデータがある場合もそのまま出力されます。

重複するデータを取り除く方法として Distinct があります。

が、そのままだと Book テーブルと Book2 テーブルに重複があっても別物として扱われます。

ということで、 Book クラスの Equals をオーバーライドします。

Book.cs

using System;
using System.ComponentModel.DataAnnotations;

namespace EfCoreNpgsqlSample.Models
{
    public class Book
    {
        [Key]
        public int BookId { get; set; }
        public string Name { get; set; }
        [DataType(DataType.Date)]
        public DateTime ReleaseDate { get; set; }
        public string Genre { get; set; }
        public decimal Price { get; set; }
        public byte[] Thumbnail { get; set; }

        public override bool Equals(object obj)
        {
            var compareBook = obj as Book;
            if (compareBook == null)
            {
                return false;
            }
            return compareBook.BookId == BookId;
        }
        public override int GetHashCode()
        {
            return BookId;
        }
    }
}

GetHashCode で、戻り値が static じゃないよ！と警告が出たりしていますが、今回は見なかったことに。。。(良い子はマネしない)

また、今回は BookId のみで比較していますが、実際の用途的には Name や Genre で比較したほうが良さそうです。

なお SQL の UNION では「 SELECT "BookId", "Genre", "Name", "Price", "ReleaseDate" ～」なら BookId 、 Genre 、 Name 、 Price 、 ReleaseDate の全部が一致しているかどうかを見ているようです。

• 【参照】自作クラスのEqualsメソッドをオーバーライドして、等価の定義を変更する - .NET Tips (VB.NET,C#...)

あとは　Distinct で重複データが取り除けます。

await books1.Concat(books2).Distinct().ToListAsync();

[INTERSECT] 2 つのテーブルの共通部分を取り出す

Linq にもそのままズバリ Intersect が存在するため、これを使えば実現できます。

コード

await books1.Intersect(books2).ToListAsync();

先ほどと同じく SQL には反映されないため、発行された SQL は省略します。

[EXCEPT] 2 つのテーブルで共通しないレコードのみを取り出す

こちらも Except が存在するため楽に実現できますね。

コード

await books1.Except(books2).ToListAsync();

注意点？としては、上記の例では Book テーブルの持つレコードがベースとなるため、 Book1 にのみ共通しないレコードが存在する場合、 0 件で返ってきます。

[INNER JOIN] 複数のテーブルを参照する(内部結合)

準備

Author テーブルを作り、 Book テーブルに AuthorId 列を追加します。

Author

CREATE TABLE "Author" (
"AuthorId" integer PRIMARY KEY,
"Name" text)

Book

ALTER TABLE "Book" 
ADD COLUMN "AuthorId" integer

C# 側でも、 Model クラスと Context クラスへの追加を行います。

Author.cs

using System.ComponentModel.DataAnnotations;

namespace EfCoreNpgsqlSample.Models
{
    public class Author
    {
        [Key]
        public int AuthorId { get; set; }
        public string Name { get; set; }
    }
}

Book.cs

using System;
using System.ComponentModel.DataAnnotations;

namespace EfCoreNpgsqlSample.Models
{
    public class Book
    {
        [Key]
        public int BookId { get; set; }

        public int AuthorId { get; set; }
        ～省略～
    }
}

EfCoreNpgsqlSampleContext.cs

using Microsoft.EntityFrameworkCore;

namespace EfCoreNpgsqlSample.Models
{
    public class EfCoreNpgsqlSampleContext: DbContext
    {
        public EfCoreNpgsqlSampleContext(DbContextOptions options)
            : base(options)
        {
            
        }
        
        public DbSet< Book> Book { get; set; }
        public DbSet< Book2> Book2 { get; set; }
        public DbSet< Author> Author { get; set; }
    }
}

Linq には Join が用意されているため、これを使います。

コード

// 戻り値の型は IQueryable< anonymous type>
from book in _context.Book
        join author in _context.Author
            on book.AuthorId equals author.AuthorId
        select new
        {
            BookId = book.BookId,
            AuthorId = author.AuthorId,
            BookName = book.Name,
            AuthorName = author.Name,
        };

発行される SQL

SELECT book."BookId", author."AuthorId", book."Name" AS "BookName", author."Name" AS "AuthorName"
      FROM "Book" AS book
      INNER JOIN "Author" AS author ON book."AuthorId" = author."AuthorId"

無名クラスとして結合した値を受け取ることができるため、一時的に使うだけならこの形が便利ですね。

なお唐突にクエリ式になったのは、 Join についてはこっちの方が理解しやすそうだったためです。

• 【参照】 LINQでの内部結合・外部結合 - Qiita

[LEFT OUTER JOIN] 複数のテーブルを参照する(外部結合)

※ 2019/02/26 23:30 更新

Book テーブルに Author テーブルに存在しない AuthorId を設定した場合もエラーが起きないよう修正しました。

コード

var results = from book in _context.Book
                join author in _context.Author
                    on book.AuthorId equals author.AuthorId into gj
                from ba in gj.DefaultIfEmpty()
                select new
                {
                    BookId = book.BookId,
                    AuthorId = book.AuthorId,
                    BookName = book.Name,
                    AuthorName = (ba == null)? "": ba.Name,
                };

発行される SQL

SELECT book."BookId", book."AuthorId", book."Name" AS "BookName", CASE
          WHEN author."AuthorId" IS NULL
          THEN '' ELSE author."Name"
      END AS "AuthorName"
      FROM "Book" AS book
      LEFT JOIN "Author" AS author ON book."AuthorId" = author."AuthorId"

SQL に反映されていない。。。

が、これで Book テーブルにあって、 Author テーブルに登録されていない AuthorId を持つレコードが結果に出力されるようになりました。

• 【参照】Perform left outer joins (LINQ in C#) - Microsoft Docs

またまた長くなってきたので切ります。

Entity Framework Core で色々な SQL を投げてみる 1

• はじめに
• メモ
• SELECT
  • 全件取得
  • カラムを指定して検索
  • [LIMIT] 条件に合致するレコードを一件だけ取得する
  • [LIMIT] 条件に合致するレコードを三件(二件以上)取得する
  • [DISTINCT] ジャンルが重複するデータを除く
  • [演算子] SELECT で演算子を使う
  • [演算子] WHERE で演算子を使う
  • [COUNT] レコード数をカウントする
  • [COUNT] ジャンルが NULL でないレコード数をカウントする
  • [AVG] 価格の平均を求める
  • [MAX] 価格の最大値を求める
  • [MIN] 価格の最小値を求める
  • [GROUP BY???] ジャンルでグループ分けする
  • [HAVING] グループ化したレコードをフィルタリングする
  • [ORDER BY] レコードをリリース日でソートする(昇順)
  • [ORDER BY] レコードをリリース日でソートする(降順)

はじめに

• ASP.NET Core のプロジェクトに EntityFrameworkCore で Model を追加してみた話
• Entity Framework Core のスキャフォールド・マイグレーションで生成されたものを見たい
• 【ASP.NET Core】【Entity Framework Core】PostgreSQL に接続してみる

さて、 PostgreSQL に接続できたことだし、 DbContext を追うぞ～と思ったのですが、あまりに知識が雑すぎたためどこから手を付けていいかもわからない状態に。。。orz

引き続き追いかけてはいくのですが、まずは下記の書籍を参考に、 Entity Framework Core で色々な SQL 文を発行してみたいと思います。

• SQL ゼロからはじめるデータベース操作

まずは SELECT から。

メモ

とその前に小ネタをメモっておきます。

Model クラスとテーブルに差異がある場合

• Model クラスに DB のテーブルに無い値(プロパティ)がある場合 → そのプロパティを使っている、使っていないにかかわらず Exception が発生します。
• DB のテーブルには存在するが、 Model クラスにプロパティが無い値(カラム)がある場合 → 問題なし。ただ値が取れないだけ。

また、 テーブルのカラム名と Model クラスのプロパティの名前が異なる場合、 [ Column("")] で指定することができます。

• Column Mapping - EF Core - Microsoft Docs

SELECT

全件取得

Book テーブルのデータを全件取得します。

コード

await _context.Book.ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b

コンソールに出力されるのが便利ですね。

カラムを指定して検索

カラム BookId のみを指定して取得してみます。

コード

await _context.Book.Select(b => b.BookId).ToListAsync();

発行される SQL

SELECT b."BookId"
      FROM "Book" AS b

[WHERE] 価格が 3000 円以上のレコードを取得する

コード

await _context.Book.Where(b => b.Price >= 3000).ToListAsync();

発行される SQL

 SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      WHERE b."Price" >= 3000.0

[LIMIT] 条件に合致するレコードを一件だけ取得する

コード

_context.Book.FirstOrDefault(b => b.Price >= 3000);

発行される SQL

 SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      WHERE b."Price" >= 3000.0
      LIMIT 1

[LIMIT] 条件に合致するレコードを三件(二件以上)取得する

コード

await _context.Book.Where(b => b.Price >= 3000).Take(3).ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      WHERE b."Price" >= 3000.0
      LIMIT @__p_0

結果としては FirstOrDefault と同じように LIMIT が使われるのですが、パラメーターが不思議な値になっています。
(なお Take の引数を変えてもコンソール出力の内容は変わりませんでした)

コンソール出力後に置き換えられるのでしょうか。

※なお TakeLast はサポート外らしく、使用すると Exception が発生します。

[DISTINCT] ジャンルが重複するデータを除く

コード

await _context.Book.Select(b => b.Genre).Distinct().ToListAsync();

発行される SQL

SELECT DISTINCT b."Genre"
      FROM "Book" AS b

[演算子] SELECT で演算子を使う

コード

await _context.Book.Select(b => b.Price * 1.08m).ToListAsync();

発行される SQL

SELECT b."Price" * 1.08
      FROM "Book" AS b

[演算子] WHERE で演算子を使う

コード

await _context.Book.Where(b => (b.Price * 1.08m) >= 3000.0m).ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      WHERE (b."Price" * 1.08) >= 3000.0

[COUNT] レコード数をカウントする

コード

await _context.Book.CountAsync();

発行される SQL

SELECT COUNT(*)::INT
      FROM "Book" AS b

[COUNT] ジャンルが NULL でないレコード数をカウントする

コード

await _context.Book.CountAsync(b => b.Genre != null);

発行される SQL

SELECT COUNT(*)::INT
      FROM "Book" AS b
      WHERE b."Genre" IS NOT NULL

「 SELECT COUNT("Genre") FROM "Book" 」とできると、同様のことが WHERE 無しでできるのですが、少なくとも CountAsync() を使う場合、「 COUNT(*)::INT 」となってしまうようです。

[SUM] 価格の合計を求める

コード

await _context.Book.SumAsync(b => b.Price);

発行される SQL

SELECT SUM(b."Price")
      FROM "Book" AS b

[AVG] 価格の平均を求める

コード

await _context.Book.AverageAsync(b => b.Price);

発行される SQL

SELECT AVG(b."Price")
      FROM "Book" AS b

[MAX] 価格の最大値を求める

コード

await _context.Book.MaxAsync(b => b.Price);

発行される SQL

SELECT MAX(b."Price")
      FROM "Book" AS b

[MIN] 価格の最小値を求める

コード

await _context.Book.MinAsync(b => b.Price);

発行される SQL

SELECT MIN(b."Price")
      FROM "Book" AS b

[GROUP BY???] ジャンルでグループ分けする

コード

await _context.Book.GroupBy(b => b.Genre).ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      ORDER BY b."Genre"

ORDER BY ？？？

発行したかったのは

SELECT "Genre" FROM "Book" GROUP BY "Genre"

です。

結果としてはどちらもジャンルでグループ分けできているようなのですが。。。

なお、当然ながら発行された SQL の内容を PgAdmin で実行すると、ジャンルでソートされた結果が表示されるだけです。

と思ったら、コンソールに下記の警告が出力されていました。

warn: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'GroupBy([b].Genre, [b])' could not be translated and will be evaluated locally.

どうやら、上記の SQL が発行される前に C# 側で何か処理を行っているようです。

同じようなことを Issue で挙げている方もいたようですが。。。

https://github.com/aspnet/EntityFrameworkCore/issues/9964

これについては、次回以降でもう少し見てみたいと思います(挫折しなければ)。

[HAVING] グループ化したレコードをフィルタリングする

コード

await _context.Book.GroupBy(b => b.Genre)
                .Where(b => string.IsNullOrEmpty(b.Key) == false)
               .ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      ORDER BY b."Genre"

あれ？発行される SQL がさっきと変わってない(´・ω・｀)

結局 GroupBy がそのまま変換されないため、そこに対する Where も変換はされないようです(結果には反映されますが)。

ちなみに Where についても警告が出力されています。

warn: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'GroupBy([b].Genre, [b])' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'where (IsNullOrEmpty([b].Key) == False)' could not be translated and will be evaluated locally.

[参照]

• LINQ：データをグループ化する - group句［C#］ - Build Insider

[ORDER BY] レコードをリリース日でソートする(昇順)

コード

await _context.Book.OrderBy(b => b.ReleaseDate).ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      ORDER BY b."ReleaseDate"

GroupBy でも(なぜか) ORDER BY が発行されていたので違いがよくわからない感じになっていますが(苦笑)、割とそのまま書けますね。

[ORDER BY] レコードをリリース日でソートする(降順)

コード

await _context.Book.OrderByDescending(b => b.ReleaseDate).ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      ORDER BY b."ReleaseDate" DESC

長くなってきたのでいったん切ります。

次回は SQL ゼロからはじめるデータベース操作 の六章ぐらいから。

【ASP.NET Core】【Entity Framework Core】PostgreSQL に接続してみる

• はじめに
• 準備
• 接続する

はじめに

Entity Framework Core の謎を追うシリーズの途中ですが、ここまで使ってきた SQLite ではなく、 PostgreSQL を使うことにしました。

PostgreSQL に興味があるとか、 PgAdmin4 を使ってみたいとかはもちろんあるのですが、唐突に話が変わる場合は。。。お察しくださいw

準備

今回 PostgreSQL は Docker のものを使用しました。

https://hub.docker.com/_/postgres

Description の内容を元に設定したのですが、この辺りはもう少しちゃんと内容を理解してから取り上げたいと思います。
(今はとりあえず動いてる状態なので)

PgAdmin はローカルにインストールしたものを使用しています。

https://www.pgadmin.org/

接続する

基本的な内容は SQLite を使った時と同じです。

NuGet パッケージとして、 SQLite の代わりに Npgsql.EntityFrameworkCore.PostgreSQL をインストールしました。

https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/blob/dev/doc/index.md

Connection string などの設定は、 SQLite と同じく Startup でできる他、上記を見ると DbContext クラスに書いているようです。

Startup.cs に書く場合

EfCoreNpgsqlSampleContext.cs

using Microsoft.EntityFrameworkCore;

namespace EfCoreNpgsqlSample.Models
{
    public class EfCoreNpgsqlSampleContext: DbContext
    {
        public EfCoreNpgsqlSampleContext(DbContextOptions< EfCoreNpgsqlSampleContext> options)
            : base(options)
        {   
        }        
        public DbSet< Book> Book { get; set; }
    }
}

Startup.cs

        ～省略～
        public void ConfigureServices(IServiceCollection services)
        {
            services.Configure< CookiePolicyOptions>(options =>
            {
                options.CheckConsentNeeded = context => true;
                options.MinimumSameSitePolicy = SameSiteMode.None;
            });

            services.AddDbContext< EfCoreNpgsqlSampleContext>(options =>
                options.UseNpgsql("Host=localhost;Database=DbName;Username=postgres;Password=XXXX"));

            services.AddMvc();
        }
        ～省略～

DbContext に書く場合

EfCoreNpgsqlSampleContext.cs

using Microsoft.EntityFrameworkCore;

namespace EfCoreNpgsqlSample.Models
{
    public class EfCoreNpgsqlSampleContext: DbContext
    {
        public EfCoreNpgsqlSampleContext(DbContextOptions< EfCoreNpgsqlSampleContext> options)
            : base(options)
        {
            
        }
        
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder.UseNpgsql("Host=localhost;Database=DbName;Username=postgres;Password=XXXX");
            
        public DbSet< Book> Book { get; set; }
    }
}

Startup.cs

        ～省略～
        public void ConfigureServices(IServiceCollection services)
        {
            services.Configure< CookiePolicyOptions>(options =>
            {
                options.CheckConsentNeeded = context => true;
                options.MinimumSameSitePolicy = SameSiteMode.None;
            });

            services.AddEntityFrameworkNpgsql()
                .AddDbContext< EfCoreNpgsqlSampleContext>()
                .BuildServiceProvider();

            services.AddMvc();
        }
        ～省略～      

後者は Connection string が実際にそれを必要とする DbContext に書ける、という利点はあるものの、二か所に分割して書くことになるため面倒にも感じましたが、他にも利点はあるのでしょうか。

ともあれ、これで PostgreSQL に接続し、データの入出力ができるようになりました。

NuGet パッケージの差し替えと数か所のコードの変更だけで DB が切り替えられる、というのはやはり便利ですね。

Entity Framework Core のスキャフォールド・マイグレーションで生成されたものを見たい

• はじめに
• 生成した・されたファイルを見てみる
• Movie.cs を見る
• EfCoreSampleContext.cs を見る
• 参照

はじめに

• ASP.NET Core のプロジェクトに EntityFrameworkCore で Model を追加してみた話

前回、半年間溜め込んでいた内容をようやくブログとして書くことができて、スッキリしている今日この頃ですw

今回は生成されたファイルを覗いてみることにします。

マイグレーションはスキャフォールドを必要とするか

前回はスキャフォールドで View 、 Controller を生成したあとマイグレーションを実行していました。

ただ、 Controller はともかく View は不要( Web API とか)な場合、わざわざスキャフォールドしなくても Controller だけ作れば良いような気もします。

ということで確認したところ、特にスキャフォールドを実行していなくてもマイグレーションできました。

ということで、状況に合わせて使い分けるのが良さそうですね。

生成した・されたファイルを見てみる

さて、前回 Empty テンプレートの ASP.NET Core プロジェクトに追加した、またはスキャフォールド・マイグレーションで生成されたファイルを見てみることにします。

Controllers
  Ｌ MoviesController.cs
Migrations
  Ｌ 20190214222750_AddMovie.cs
  Ｌ 20190214222750_AddMovie.Designer.cs
  Ｌ EfCoreSampleContextModelSnapshot.cs
Models
  Ｌ EfCoreSampleContext.cs
  Ｌ Movie.cs
Views
  Ｌ Movies
    Ｌ Create.cshtml
    Ｌ Delete.cshtml
    Ｌ Details.cshtml
    Ｌ Edit.cshtml
    Ｌ Index.cshtml
  Ｌ Shared
    Ｌ _ValidationScriptsPartial.cshtml

View の部分は基本的に Razor で Form を出しているだけなのでスキップすることにして、それ以外の部分を追いかけてみたいと思います。

Movie.cs を見る

C# 側でデータを保持し、マイグレーション時に DB のテーブル生成するのにも使われるクラスです。

Movie.cs

using System;
using System.ComponentModel.DataAnnotations;

namespace EfCoreSample.Models
{
    public class Movie
    {
        public int MovieId { get; set; }
        public string Title { get; set; }

        [DataType(DataType.Date)]
        public DateTime ReleaseDate { get; set; }
        public string Genre { get; set; }
        public decimal Price { get; set; }
    }
}

DataType

ただデータを保持するクラス、ということで、そんなに変わったところも見られません。

唯一気になるのが「[DataType(DataType.Date)]」の属性。

• System.ComponentModel.DataAnnotations Namespace - Microsoft Docs

↑などを見ると、どうやら Razor で下記のようなフォームを表示するのに使われているようです。

試しにコメントアウトしてみると、他と同じようにただ入力エリアが表示されていました。

DB 側に関係があるかはよく分かっていませんが。

EfCoreSampleContext.cs を見る

次は EfCoreSampleContext.cs です。

Entity Framework Core in Action によると DB へのアクセスや設定を担う、ということで、今回の中で一番重要なクラスと言えそうです。

EfCoreSampleContext.cs

using Microsoft.EntityFrameworkCore;

namespace EfCoreSample2.Models
{
    public class MovieContext : DbContext
    {
        public MovieContext (DbContextOptions< MovieContext> options) : base(options)
        {
        }

        public DbSet< Movie> Movie { get; set; }
    }
}

この中に含まれるキーワードとしては下記の 3 つがあります。

1. DbContext
2. DbContextOptions
3. DbSet

DbContext Class (Microsoft.EntityFrameworkCore) などを見ると、 DB との直接的なやり取りをしているのは DbContext クラスとのこと。

2.の DbContextOptions は、接続する DB の情報などを渡すのに使われるようです。

https://github.com/JonPSmith/EfCoreInAction/blob/Chapter01/MyFirstEfCoreApp/AppDbContext.cs

(関係ないですが、ブランチを本の章を切り替えるのに使うのが新鮮というか、ちょっとびっくりしました)

これをどこから渡しているかというと、 Startup > ConfigureServices() です。

Startup.cs

～省略～
public void ConfigureServices(IServiceCollection services)
{
    services.Configure< CookiePolicyOptions>(options =>
    {
        options.CheckConsentNeeded = context => true;
        options.MinimumSameSitePolicy = SameSiteMode.None;
    });

    services.AddDbContext< EfCoreSampleContext>(options =>
        options.UseSqlite(Configuration.GetConnectionString("MovieContext")));

    services.AddMvc();
}
～省略～

SQLite の使用と Data Source として MvcMovie.db が指定されています。

appsettings.json

～省略～
  "ConnectionStrings": {
    "MovieContext": "Data Source=MvcMovie.db"
  }
}

3.の DbSet は、 DbContext の Set() で Entity クラスとして登録するのに使われるようです。

また、スキャフォールドで生成された MoviesController.cs を見てみると、 DB アクセスの部分は MovieContext(DbContext) 、または DbSet を検索したり、値を取得しているだけのようです。

MoviesController.cs

～省略～
[Route("/Movies/Details/{*id}")]
public async Task< IActionResult> Details(int? id)
{
    if (id == null)
    {
        return NotFound();
    }

    var movie = await _context.Movie
        .FirstOrDefaultAsync(m => m.MovieId == id);
    if (movie == null)
    {
        return NotFound();
    }

    return View(movie);
}
～省略～
[HttpPost]
[Route("/Movies/Create")]
[ValidateAntiForgeryToken]
public async Task< IActionResult> Create([Bind("MovieId,Title,ReleaseDate,Genre,Price")] Movie movie)
{
    if (ModelState.IsValid)
    {

        _context.Add(movie);
        await _context.SaveChangesAsync();

        return RedirectToAction(nameof(Index));
    }
    return View(movie);
}
～省略～

ということを考えると、 Entity Framework Core がどんなことをしてくれるのかを知るためには DbContext を中心に調べていくのが良さそうです。

処理を調べるにはドキュメントとコードを見るのが一番、だと思いますが、 Entity Framework Core のコードは下記にあります。

• aspnet/EntityFrameworkCore - GitHub

その中の DbContext はこちら。

https://github.com/aspnet/EntityFrameworkCore/blob/master/src/EFCore/DbContext.cs

次回はこれをたどってみることにします。

参照

• Entity Framework Core in Action - Manning
• DbContext Class (Microsoft.EntityFrameworkCore) - Microsoft Docs
• JonPSmith/EfCoreInAction - GitHub
• aspnet/EntityFrameworkCore - GitHub