EF CoreのIncludeとThenInclude

monster.sql

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;

Program.cs

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);
			}
		}
	}
}