読者です 読者をやめる 読者になる 読者になる

Entity FrameworkでTPT(Table Per Type)

entity-framework dapper

今ごろ感ありますがEntity FrameworkでTPT(Table Per Type)を試しました。

データを追加する方法や取得する方法、どんなクエリになるのか、最終的には派生クラスではなく基本クラスだけを取得できるのかといったことを確認することが目的でした。

クエリはちょっとしたことで変わる気もするので参考程度といった感じです。またEntity Frameworkで基本クラスだけを取得する方法はわからなくて結局はDapperを使いましたが、試したサンプルを残しておきます。

あとEntity Framework Coreではないです。Entity Framework 6.1.3で試しています。

モデルとかDBコンテキストとか

次の継承関係があるモデルをDBで管理することを考えます。RectとCircleはShapeを派生している、あるあるな感じのモデルです。とりあえずShapeは抽象クラスにしてみます。

// 図形
public abstract class Shape {
    // ID
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    public string Stroke { get; set; }  // 線の色(#000000)

    public override string ToString() {
        return $"{{ {nameof(Id)} = {Id}, {nameof(Stroke)} = #{Stroke} }}";
    }
}

// 矩形
public class Rect : Shape {
    public double X { get; set; }
    public double Y { get; set; }
    public double Width { get; set; }
    public double Height { get; set; }

    public override string ToString() {
        return $"{{ {nameof(Id)} = {Id}, {nameof(Stroke)} = #{Stroke}, "
            + $"{nameof(X)} = {X}, {nameof(Y)} = {Y}, "
            + $"{nameof(Width)} = {Width}, {nameof(Height)} = {Height} }}";
    }
}

// 円
public class Circle : Shape {
    public double X { get; set; }
    public double Y { get; set; }
    public double Radius { get; set; }

    public override string ToString() {
        return $"{{ {nameof(Id)} = {Id}, {nameof(Stroke)} = #{Stroke}, "
            + $"{nameof(X)} = {X}, {nameof(Y)} = {Y}, {nameof(Radius)} = {Radius} }}";
    }
}

それぞれのテーブルを作るクエリもぺたっと。

drop table if exists Circle;
drop table if exists Rect;
drop table if exists Shape;

create table Shape(
    Id int not null,
    Stroke varchar(6) not null,
    constraint PK_Shape primary key(Id));

create table Rect(
    Id int not null,
    X float not null,
    Y float not null,
    Width float not null,
    Height float not null,
    constraint PK_Rect primary key(Id),
    constraint FK_Rect_Shape foreign key(Id) references Shape(Id));

create table Circle(
    Id int not null,
    X float not null,
    Y float not null,
    Radius float not null,
    constraint PK_Circle primary key(Id),
    constraint FK_Circle_Shape foreign key(Id) references Shape(Id));

DBコンテキストを用意します。IDbSetのプロパティはShapeクラスだけでいいっぽいです。RectやCircleの取得や追加などもShapesプロパティを使います。

// DBコンテキスト
public class AppDbContext : DbContext {
    public AppDbContext(string nameOrConnectionString)
        : base(nameOrConnectionString) {
        Database.Log = message => Debug.WriteLine(message);
    }

    // RectもCircleもこのプロパティを使う
    public IDbSet<Shape> Shapes { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        base.OnModelCreating(modelBuilder);

        // テーブルは単数形
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

        // モデルのマッピング
        modelBuilder.Entity<Circle>().ToTable("Circle");
        modelBuilder.Entity<Rect>().ToTable("Rect");
    }
}

データの投入

まずはRectを1つ投入してみます。

// Rectを1つ投入
using(var dbContext = new AppDbContext(_connectionString)) {
    dbContext.Shapes.Add(new Rect {
        Id = 1, Stroke = "ff0000",
        X = 1.0, Y = 2.0, Width = 3.0, Height = 2.0,
    });

    dbContext.SaveChanges();
}

ログでクエリを確認すると一度にShapeとRectの2つのテーブルにinsertしていることが確認できます。

-- 実行されたクエリ
INSERT [dbo].[Shape]([Id], [Stroke])
VALUES (@0, @1)
-- @0: '1' (Type = Int32)
-- @1: 'ff0000' (Type = String, Size = -1)
-- Executing at 2016/10/06 17:49:08 +09:00
-- Completed in 8 ms with result: 1

INSERT [dbo].[Rect]([Id], [X], [Y], [Width], [Height])
VALUES (@0, @1, @2, @3, @4)
-- @0: '1' (Type = Int32)
-- @1: '1' (Type = Double)
-- @2: '2' (Type = Double)
-- @3: '3' (Type = Double)
-- @4: '2' (Type = Double)
-- Executing at 2016/10/06 17:49:08 +09:00
-- Completed in 5 ms with result: 1

この後取得するためにRectとCircleをもう少しinsertしておきます。

// Cicleを2つとRectを1つ投入
using (var dbContext = new AppDbContext(_connectionString)) {
    dbContext.Shapes.Add(new Circle {
        Id = 2, Stroke = "0000ff",
        X = 0.0, Y = 1.0, Radius = 2.0,
    });
    dbContext.Shapes.Add(new Circle {
        Id = 3, Stroke = "0000ff",
        X = 0.0, Y = 0.0, Radius = 3.0,
    });
    dbContext.Shapes.Add(new Rect {
        Id = 4, Stroke = "00ff00",
        X = 2.0, Y = 2.0, Width = 4.0, Height = 4.0,
    });

    dbContext.SaveChanges();
}

データを取得

すべて取得してみます。ShapesプロパティからCircleもRectも取得できます。

// すべての図形を取得
using(var dbContext = new AppDbContext(_connectionString)) {
    foreach (var shape in dbContext.Shapes.ToList()) {
        Console.WriteLine($"{shape.GetType()}: {shape}");
    };
}

// 結果
/*
ConsoleApp.Circle: { Id = 2, Stroke = #0000ff, X = 0, Y = 1, Radius = 2 }
ConsoleApp.Circle: { Id = 3, Stroke = #0000ff, X = 0, Y = 0, Radius = 3 }
ConsoleApp.Rect: { Id = 1, Stroke = #ff0000, X = 1, Y = 2, Width = 3, Height = 2 }
ConsoleApp.Rect: { Id = 4, Stroke = #00ff00, X = 2, Y = 2, Width = 4, Height = 4 }
*/

クエリはleft outer joinを想像しましたがunion all。

-- 実行されたクエリ
SELECT
    CASE WHEN ([UnionAll1].[C5] = 1) THEN '0X0X' ELSE '0X1X' END AS [C1],
    [UnionAll1].[Id] AS [C2],
    [Extent3].[Stroke] AS [Stroke],
    CASE WHEN ([UnionAll1].[C5] = 1) THEN [UnionAll1].[X] END AS [C3],
    CASE WHEN ([UnionAll1].[C5] = 1) THEN [UnionAll1].[Y] END AS [C4],
    CASE WHEN ([UnionAll1].[C5] = 1) THEN [UnionAll1].[Radius] END AS [C5],
    CASE WHEN ([UnionAll1].[C5] = 1) THEN CAST(NULL AS float) ELSE [UnionAll1].[C1] END AS [C6],
    CASE WHEN ([UnionAll1].[C5] = 1) THEN CAST(NULL AS float) ELSE [UnionAll1].[C2] END AS [C7],
    CASE WHEN ([UnionAll1].[C5] = 1) THEN CAST(NULL AS float) ELSE [UnionAll1].[C3] END AS [C8],
    CASE WHEN ([UnionAll1].[C5] = 1) THEN CAST(NULL AS float) ELSE [UnionAll1].[C4] END AS [C9]
    FROM   (SELECT
        [Extent1].[Id] AS [Id],
        [Extent1].[X] AS [X],
        [Extent1].[Y] AS [Y],
        [Extent1].[Radius] AS [Radius],
        CAST(NULL AS float) AS [C1],
        CAST(NULL AS float) AS [C2],
        CAST(NULL AS float) AS [C3],
        CAST(NULL AS float) AS [C4],
        cast(1 as bit) AS [C5]
        FROM [dbo].[Circle] AS [Extent1]
    UNION ALL
        SELECT
        [Extent2].[Id] AS [Id],
        CAST(NULL AS float) AS [C1],
        CAST(NULL AS float) AS [C2],
        CAST(NULL AS float) AS [C3],
        [Extent2].[X] AS [X],
        [Extent2].[Y] AS [Y],
        [Extent2].[Width] AS [Width],
        [Extent2].[Height] AS [Height],
        cast(0 as bit) AS [C4]
        FROM [dbo].[Rect] AS [Extent2]) AS [UnionAll1]
    INNER JOIN [dbo].[Shape] AS [Extent3] ON [UnionAll1].[Id] = [Extent3].[Id]

派生クラスだけを取得する

Rectクラスだけを取得してみます。OfTypeメソッドを使います。

// Rectをすべて取得
using(var dbContext = new AppDbContext(_connectionString)) {
    foreach (var shape in dbContext.Shapes.OfType<Rect>().ToList()) {
        Console.WriteLine($"{shape.GetType()}: {shape}");
    };
}

// 結果
/*
ConsoleApp.Rect: { Id = 1, Stroke = #ff0000, X = 1, Y = 2, Width = 3, Height = 2 }
ConsoleApp.Rect: { Id = 4, Stroke = #00ff00, X = 2, Y = 2, Width = 4, Height = 4 }
*/

クエリはinner joinでした。想像したとおり。

-- 実行されたクエリ
SELECT 
    '0X0X' AS [C1], 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Stroke] AS [Stroke], 
    [Extent2].[X] AS [X], 
    [Extent2].[Y] AS [Y], 
    [Extent2].[Width] AS [Width], 
    [Extent2].[Height] AS [Height]
    FROM  [dbo].[Shape] AS [Extent1]
    INNER JOIN [dbo].[Rect] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]

基本クラスだけを取得する

今回のようにテーブルを分ける理由の1つとして「基本クラスだけの情報を取得することがある」のかなと思います。なのでこれが知りたかったのですが分からずでした・・・。

試したコードはこちらです。まずOfType()でいける?と思ったのですがダメ。Shapeが抽象クラスなのがダメなのかと思ってabstractをやめてみましたがそれでもダメ。

// CircleもRectも取れる
using(var dbContext = new AppDbContext(_connectionString)) {
    foreach (var shape in dbContext.Shapes.OfType<Shape>().ToList()) {
        Console.WriteLine($"{shape.GetType()}: {shape}");
    };
}

// 結果
/*
ConsoleApp.Circle: { Id = 2, Stroke = #0000ff, X = 0, Y = 1, Radius = 2 }
ConsoleApp.Circle: { Id = 3, Stroke = #0000ff, X = 0, Y = 0, Radius = 3 }
ConsoleApp.Rect: { Id = 1, Stroke = #ff0000, X = 1, Y = 2, Width = 3, Height = 2 }
ConsoleApp.Rect: { Id = 4, Stroke = #00ff00, X = 2, Y = 2, Width = 4, Height = 4 }
*/

ということで結局はDapperでした。(Shapeのabstractをやめてからの)

// DapperでShapeとして取得
using (var dbContext = new AppDbContext(_connectionString)) {
    var sql = "select * from dbo.Shape;";
    foreach (var shape in dbContext.Database.Connection.Query<Shape>(sql)) {
        Console.WriteLine($"{shape.GetType()}: {shape}");
    }
}

// 結果
/*
ConsoleApp.Shape: { Id = 1, Stroke = #ff0000 }
ConsoleApp.Shape: { Id = 2, Stroke = #0000ff }
ConsoleApp.Shape: { Id = 3, Stroke = #0000ff }
ConsoleApp.Shape: { Id = 4, Stroke = #00ff00 }
*/