Skip to content

Instantly share code, notes, and snippets.

@ichiroku11 ichiroku11/Program.cs
Last active Dec 18, 2018

Embed
What would you like to do?
EF CoreのIncludeとThenInclude
use Test;
begin tran;
drop table if exists dbo.MonsterItem;
drop table if exists dbo.MonsterArea;
drop table if exists dbo.Monster;
drop table if exists dbo.MonsterCategory;
drop table if exists dbo.Item;
drop table if exists dbo.Area;
create table dbo.Area(
Id int,
Name nvarchar(20) not null,
constraint PK_Area primary key(Id)
);
create table dbo.Item(
Id int,
Name nvarchar(20) not null,
constraint PK_Item primary key(Id)
);
create table dbo.MonsterCategory(
Id int,
Name nvarchar(20) not null,
constraint PK_MonsterCategory primary key(Id)
);
create table dbo.Monster(
Id int,
Name nvarchar(20) not null,
CategoryId int not null,
constraint PK_Monster primary key(Id),
constraint FK_Monster_MonsterCategory foreign key(CategoryId) references dbo.MonsterCategory(Id)
);
create table dbo.MonsterArea(
MonsterId int,
No int not null,
AreaId int not null,
constraint PK_MonsterArea primary key(MonsterId, No),
constraint FK_MonsterArea_Monster foreign key(MonsterId) references dbo.Monster(Id),
constraint FK_MonsterArea_Area foreign key(AreaId) references dbo.Area(Id)
);
create table dbo.MonsterItem(
MonsterId int,
No int not null,
ItemId int not null,
constraint PK_MonsterItem primary key(MonsterId, No),
constraint FK_MonsterItem_Monster foreign key(MonsterId) references dbo.Monster(Id),
constraint FK_MonsterItem_Item foreign key(ItemId) references dbo.Item(Id)
);
commit;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Abstractions;
namespace ConsoleApp {
// 地域
public class Area {
public int Id { get; set; }
public string Name { get; set; }
}
// アイテム
public class Item {
public int Id { get; set; }
public string Name { get; set; }
}
// モンスターカテゴリ
public class MonsterCategory {
public int Id { get; set; }
public string Name { get; set; }
public List<Monster> Monsters { get; set; }
}
// モンスター
public class Monster {
public int Id { get; set; }
public string Name { get; set; }
public int CategoryId { get; set; }
public MonsterCategory Category { get; set; }
public List<MonsterArea> Areas { get; set; }
public List<MonsterItem> Items { get; set; }
}
// モンスター生息地
public class MonsterArea {
public int MonsterId { get; set; }
public int No { get; set; }
public int AreaId { get; set; }
public Monster Monster { get; set; }
public Area Area { get; set; }
}
// モンスターアイテム
public class MonsterItem {
public int MonsterId { get; set; }
public int No { get; set; }
public int ItemId { get; set; }
public Monster Monster { get; set; }
public Item Item { get; set; }
}
// ロガープロバイダー
public class AppLoggerProvider : ILoggerProvider {
public ILogger CreateLogger(string categoryName) {
if (string.Equals(categoryName, DbLoggerCategory.Database.Command.Name)) {
return new ConsoleLogger();
}
return NullLogger.Instance;
}
public void Dispose() {
}
// ロガー
private class ConsoleLogger : ILogger {
public IDisposable BeginScope<TState>(TState state) => null;
// 情報レベル以上のログを有効にする
public bool IsEnabled(LogLevel logLevel) => logLevel >= LogLevel.Information;
public void Log<TState>(
LogLevel logLevel, EventId eventId,
TState state, Exception exception,
Func<TState, Exception, string> formatter) {
Console.WriteLine(formatter(state, exception));
Console.WriteLine("---");
}
}
}
// DBコンテキスト
public class AppDbContext : DbContext {
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
var connectionString = new SqlConnectionStringBuilder {
DataSource = ".",
InitialCatalog = "Test",
IntegratedSecurity = true,
}.ToString();
optionsBuilder.UseSqlServer(connectionString);
}
protected override void OnModelCreating(ModelBuilder modelBuilder) {
modelBuilder.Entity<Area>().ToTable(nameof(Area));
modelBuilder.Entity<Item>().ToTable(nameof(Item));
modelBuilder.Entity<MonsterCategory>().ToTable(nameof(MonsterCategory));
modelBuilder.Entity<Monster>().ToTable(nameof(Monster));
modelBuilder.Entity<MonsterArea>().ToTable(nameof(MonsterArea))
// 複合主キーのマッピング
.HasKey(area => new { area.MonsterId, area.No });
modelBuilder.Entity<MonsterItem>().ToTable(nameof(MonsterItem))
.HasKey(item => new { item.MonsterId, item.No });
}
}
class Program {
private static void Init(DbContext dbContext) {
dbContext.Set<Area>().AddRange(
new Area { Id = 1, Name = "サンタローズ" },
new Area { Id = 2, Name = "レヌール" },
new Area { Id = 3, Name = "ラインハット" });
dbContext.Set<Item>().AddRange(
new Item { Id = 1, Name = "やくそう" },
new Item { Id = 2, Name = "どくけしそう" });
dbContext.Set<MonsterCategory>().AddRange(
new MonsterCategory { Id = 1, Name = "スライム" },
new MonsterCategory { Id = 2, Name = "悪魔" });
dbContext.Set<Monster>().AddRange(
new Monster {
Id = 1,
Name = "スライム",
CategoryId = 1, // スライム
Areas = new List<MonsterArea> {
new MonsterArea { No = 1, AreaId = 1 }, // サンタローズ
},
Items = new List<MonsterItem> {
new MonsterItem { No = 1, ItemId = 1 }, // やくそう
},
},
new Monster {
Id = 2,
Name = "ドラキー",
CategoryId = 2, // 悪魔
Areas = new List<MonsterArea> {
new MonsterArea { No = 1, AreaId = 1 }, // サンタローズ
new MonsterArea { No = 2, AreaId = 2 }, // レヌール
},
},
new Monster {
Id = 3,
Name = "バブルスライム",
CategoryId = 1, // スライム
Areas = new List<MonsterArea> {
new MonsterArea { No = 1, AreaId = 3 }, // ラインハット
},
Items = new List<MonsterItem> {
new MonsterItem { No = 1, ItemId = 1 }, // やくそう
new MonsterItem { No = 2, ItemId = 2 }, // どくけしそう
},
});
dbContext.SaveChanges();
}
private static void Test(DbContext dbContext) {
var monsters = dbContext.Set<Monster>()
.ToList();
foreach (var monster in monsters) {
Console.WriteLine($"#{monster.Id} {monster.Name}");
}
/*
Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [m].[Id], [m].[CategoryId], [m].[Name]
FROM [Monster] AS [m]
---
#1 スライム
#2 ドラキー
#3 バブルスライム
*/
}
// 1対多の1をIncludeするとINNER JOINになるっぽい
private static void Test_IncludeOne(DbContext dbContext) {
var monsters = dbContext.Set<Monster>()
.Include(monster => monster.Category)
.ToList();
foreach (var monster in monsters) {
Console.WriteLine($"#{monster.Id} {monster.Name} [{monster.Category.Name}]");
}
/*
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [monster].[Id], [monster].[CategoryId], [monster].[Name], [monster.Category].[Id], [monster.Category].[Name]
FROM [Monster] AS [monster]
INNER JOIN [MonsterCategory] AS [monster.Category] ON [monster].[CategoryId] = [monster.Category].[Id]
---
#1 スライム [スライム]
#2 ドラキー [悪魔]
#3 バブルスライム [スライム]
*/
}
// 1対多の多をIncludeするたびにSELECT文が実行されるっぽい
private static void Test_IncludeMany(DbContext dbContext) {
var monsters = dbContext.Set<Monster>()
.Include(monster => monster.Areas)
.Include(monster => monster.Items)
.ToList();
foreach (var monster in monsters) {
var areas = string.Join(", ", monster.Areas.Select(area => area.AreaId));
var items = string.Join(", ", monster.Items.Select(item => item.ItemId));
Console.WriteLine($"#{monster.Id} {monster.Name} in {areas} has {items}");
}
/*
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [monster].[Id], [monster].[CategoryId], [monster].[Name]
FROM [Monster] AS [monster]
ORDER BY [monster].[Id]
---
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [monster.Areas].[MonsterId], [monster.Areas].[No], [monster.Areas].[AreaId]
FROM [MonsterArea] AS [monster.Areas]
INNER JOIN (
SELECT [monster0].[Id]
FROM [Monster] AS [monster0]
) AS [t] ON [monster.Areas].[MonsterId] = [t].[Id]
ORDER BY [t].[Id]
---
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [monster.Items].[MonsterId], [monster.Items].[No], [monster.Items].[ItemId]
FROM [MonsterItem] AS [monster.Items]
INNER JOIN (
SELECT [monster1].[Id]
FROM [Monster] AS [monster1]
) AS [t0] ON [monster.Items].[MonsterId] = [t0].[Id]
ORDER BY [t0].[Id]
---
#1 スライム in 1 has 1
#2 ドラキー in 1, 2 has
#3 バブルスライム in 3 has 1, 2
*/
}
// ThenInclude
// 1対多-1対1
private static void Test_IncludeMany_ThenIncludeOne(DbContext dbContext) {
var monsters = dbContext.Set<Monster>()
.Include(monster => monster.Areas)
.ThenInclude(monsterArea => monsterArea.Area)
.Include(monster => monster.Items)
.ThenInclude(monsterItem => monsterItem.Item)
.ToList();
foreach (var monster in monsters) {
var areas = string.Join(", ", monster.Areas.Select(area => area.Area.Name));
var items = string.Join(", ", monster.Items.Select(item => item.Item.Name));
Console.WriteLine($"#{monster.Id} {monster.Name} in {areas} has {items}");
}
/*
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [monster].[Id], [monster].[CategoryId], [monster].[Name]
FROM [Monster] AS [monster]
ORDER BY [monster].[Id]
---
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [monster.Areas].[MonsterId], [monster.Areas].[No], [monster.Areas].[AreaId], [m.Area].[Id], [m.Area].[Name]
FROM [MonsterArea] AS [monster.Areas]
INNER JOIN [Area] AS [m.Area] ON [monster.Areas].[AreaId] = [m.Area].[Id]
INNER JOIN (
SELECT [monster0].[Id]
FROM [Monster] AS [monster0]
) AS [t] ON [monster.Areas].[MonsterId] = [t].[Id]
ORDER BY [t].[Id]
---
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [monster.Items].[MonsterId], [monster.Items].[No], [monster.Items].[ItemId], [m.Item].[Id], [m.Item].[Name]
FROM [MonsterItem] AS [monster.Items]
INNER JOIN [Item] AS [m.Item] ON [monster.Items].[ItemId] = [m.Item].[Id]
INNER JOIN (
SELECT [monster1].[Id]
FROM [Monster] AS [monster1]
) AS [t0] ON [monster.Items].[MonsterId] = [t0].[Id]
ORDER BY [t0].[Id]
---
#1 スライム in サンタローズ has やくそう
#2 ドラキー in サンタローズ, レヌール has
#3 バブルスライム in ラインハット has やくそう, どくけしそう
*/
}
// ThenInclude
// 1対多-1対多
private static void Test_IncludeMany_ThenIncludeMany(AppDbContext dbContext) {
var categories = dbContext.Set<MonsterCategory>()
.Include(category => category.Monsters)
.ThenInclude(monster => monster.Areas)
.ToList();
foreach (var monster in categories.SelectMany(category => category.Monsters)) {
var areas = string.Join(", ", monster.Areas.Select(area => area.AreaId));
Console.WriteLine($"#{monster.Id} {monster.Name} [{monster.Category.Name}] in {areas}");
}
/*
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [category].[Id], [category].[Name]
FROM [MonsterCategory] AS [category]
ORDER BY [category].[Id]
---
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [category.Monsters].[Id], [category.Monsters].[CategoryId], [category.Monsters].[Name]
FROM [Monster] AS [category.Monsters]
INNER JOIN (
SELECT [category0].[Id]
FROM [MonsterCategory] AS [category0]
) AS [t] ON [category.Monsters].[CategoryId] = [t].[Id]
ORDER BY [t].[Id], [category.Monsters].[Id]
---
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [category.Monsters.Areas].[MonsterId], [category.Monsters.Areas].[No], [category.Monsters.Areas].[AreaId]
FROM [MonsterArea] AS [category.Monsters.Areas]
INNER JOIN (
SELECT DISTINCT [category.Monsters0].[Id], [t0].[Id] AS [Id0]
FROM [Monster] AS [category.Monsters0]
INNER JOIN (
SELECT [category1].[Id]
FROM [MonsterCategory] AS [category1]
) AS [t0] ON [category.Monsters0].[CategoryId] = [t0].[Id]
) AS [t1] ON [category.Monsters.Areas].[MonsterId] = [t1].[Id]
ORDER BY [t1].[Id0], [t1].[Id]
---
#1 スライム [スライム] in 1
#3 バブルスライム [スライム] in 3
#2 ドラキー [悪魔] in 1, 2
*/
}
static void Main(string[] args) {
// 初期化
/*
using (var dbContext = new AppDbContext()) {
Init(dbContext);
}
*/
using (var dbContext = new AppDbContext()) {
// 基本的にトラッキングしないほうがいいのでは?と
dbContext.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
// ロガープロバイダーを設定
var serviceProvider = dbContext.GetInfrastructure();
var loggerFactory = serviceProvider.GetService<ILoggerFactory>();
loggerFactory.AddProvider(new AppLoggerProvider());
Console.WriteLine("===");
Console.WriteLine($"{nameof(Test)}");
Console.WriteLine("===");
Test(dbContext);
Console.WriteLine("===");
Console.WriteLine($"{nameof(Test_IncludeOne)}");
Console.WriteLine("===");
Test_IncludeOne(dbContext);
Console.WriteLine("===");
Console.WriteLine($"{nameof(Test_IncludeMany)}");
Console.WriteLine("===");
Test_IncludeMany(dbContext);
Console.WriteLine("===");
Console.WriteLine($"{nameof(Test_IncludeMany_ThenIncludeOne)}");
Console.WriteLine("===");
Test_IncludeMany_ThenIncludeOne(dbContext);
Console.WriteLine("===");
Console.WriteLine($"{nameof(Test_IncludeMany_ThenIncludeMany)}");
Console.WriteLine("===");
Test_IncludeMany_ThenIncludeMany(dbContext);
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.