[EFCore] MMO EFCore

19 Feb 2021

Reading time ~31 minutes

Reference by [C#과 유니티로 만드는 MMORPG 게임 개발 시리즈] Part8: Entity Framework Core

linking with DB

Configuration

Table name
- Entity Class name defines Table name
- for example, the Item class makes Item table in DB and the Player class makes Player table in DB
- you can define table name by annotation, example, [Table("Item")]
Primary Key
- [ClassName]Id is specified by primary key
- you can define primary key by annotation, example, [Key]
Foreign Key
- reference another class
- Navigational Property
- when type of the object is outer class type, the object is foreign data
- for example, type of the Owner is Player, so Owner is foreign data
- to make foreign key to data, make object name to [ObjectName]Id
- or just use annotation [ForeignKey("[ObjectName]")] or [ForeignKEy("[ObjectName]Id")]
Alternate Key
- not primary key but it is unique, then it can be alternate key
- for example, if Name is unique, then it can be alternate key in Player table
DataModel.cs

  [Table("Item")]
  public class Item
  {
    public int ItemId { get; set; }
    public int TemplatedId { get; set; }
    public DateTime CreateDate { get; set; }

    public int OwnerId { get; set; }
    public Player Owner { get; set; }
  }

  public class Player
  {
    public int PlayerId { get; set; }
    public string Name { get; set; }
  }

EF Core Process

step
make DbContext
search DbSet
analize modeling class, and search column
if there is another class which is referenced in modeling class, analize it also
call OnModelCreating function(extra setting = override)
hold all modeling structure of database in inner memory
DbSet
- notice to EFCore
- ex. there is Items in DB table, and let it reference detail column/ key information on Item class
DB ConnectionString
- connect DB
- include settings, Authorization, and etc
SqlServer
- if you use Sql Server, reference OnConfiguring method below
AppDbContext.cs

  class AppDbContext : DbContext
  {
    public DbSet<Item> Items { get; set; }
    public DbSet<Player> Players { get; set; }

    public const string ConnectionString = "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=EFCoreDb;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
      options.UseSqlServer(ConnectionString);
    }
  }

Initialize DB

check exsisting DB
- (db.GetService<IDatabaseCreator>() as RelationalDatabaseCreator).Exists()
EnsuredDeleted()
- Delete Database and ensure it
EnsuredCreated()
- Create Database and ensure it
using{}
- to avoid Disposal()
DbCommands.cs

  public static void InitializeDB(bool forceReset = false)
  {
    using (AppDbContext db = new AppDbContext())
    {
      if (!forceReset && (db.GetService<IDatabaseCreator>() as RelationalDatabaseCreator).Exists())
        return;

      db.Database.EnsureDeleted();
      db.Database.EnsureCreated();

      CreateTestData(db);
      Console.WriteLine("DB Initialized");
    }
  }

MMO EFCore

CRUD

C(Create)

AddRange([ListName])
- same function with Add([ObjectName])
- but you can add a bundle of objects in List
DbCommands.cs

  public static void CreateTestData(AppDbContext db)
  {
    var hanna = new Player() { Name = "Hanna" };
    var faker = new Player() { Name = "Faker" };
    var deft = new Player() { Name = "Deft" };

    List<Item> items = new List<Item>()
    {
      new Item()
      {
        TemplatedId = 101,
        CreateDate = DateTime.Now,
        Owner = hanna
      },
      new Item()
      {
        TemplatedId = 102,
        CreateDate = DateTime.Now,
        Owner = faker
      },
      new Item()
      {
        TemplatedId = 103,
        CreateDate = DateTime.Now,
        Owner = deft
      }
    };

    db.Items.AddRange(items);
    db.SaveChanges();
  }

MMO EFCore

R(Read)

AsNoTracking
- ReadOnly
- to skip Tracking Snapshot
Tracking Snapshot
- detect data’s change
Include
- Eager Loading
- load foreign data by foreign key
DbCommands.cs

  public static void ReadAll()
  {
    using(var db = new AppDbContext())
    {
      foreach(Item item in db.Items.AsNoTracking().Include(i => i.Owner))
      {
        Console.WriteLine($"TemplatedId({item.TemplatedId}) Owner({item.Owner.Name}) Created({item.CreateDate})");
      }
    }
  }

MMO EFCore

U(Update)

DbCommands.cs

  // change items of CreateDate of speficied player
  public static void UpdateDate()
  {
    Console.WriteLine("Input Player Name");
    Console.Write("> ");
    string name = Console.ReadLine();

    using(var db = new AppDbContext())
    {
      var items = db.Items.Include(i => i.Owner)
                    .Where(i => i.Owner.Name == name);

      foreach(Item item in items)
      {
        item.CreateDate = DateTime.Now;
      }

      db.SaveChanges();
    }

    ReadAll();
  }

MMO EFCore

D(Delete)

RemoveRange([ListName])
- same function with Remove([ObjectName])
- but you can remove a bundle of objects in List
DbCommands.cs

  public static void DeleteItem()
  {
    Console.WriteLine("Input Player Name");
    Console.Write("> ");
    string name = Console.ReadLine();

    using (var db = new AppDbContext())
    {
      var items = db.Items.Include(i => i.Owner)
                    .Where(i => i.Owner.Name == name);

      db.Items.RemoveRange(items);
      db.SaveChanges();
    }

    ReadAll();
  }

MMO EFCore

Relationship

DB Relationship

DB relationship modeling
- 1:1
- 1:N
  Normally, Foreign Key is N
- N:N
DataModel.cs

  public class Item
  {
    ...
	  public Player Owner { get; set; }
  }

	public class Player
  {
    ...
    public ICollection<Item> Items { get; set; } // 1:N
  }

DbCommands.cs

  public static void ShowItems()
  {
    Console.WriteLine("Input Player Name");
    Console.Write("> ");
    string name = Console.ReadLine();

    using(var db = new AppDbContext())
    {
      foreach(Player player in db.Players.AsNoTracking().Where(p => p.Name == name).Include(p => p.Items))
      {
        foreach(Item item in player.Items)
        {
          Console.WriteLine($"{item.TemplateId}");
        }
      }
    }
  }

MMO EFCore

Loading

Model change

DataModel.cs

  [Table("Item")]
  public class Item
  {
    ...
    [ForeignKey("OwnerId")]
    public Player Owner { get; set; }
  }
  
  [Table("Player")]
  public class Player
  {
    ...
    // public ICollection<Item> Items { get; set; }
    public Item Item { get; set; }
    public Guild Guild { get; set; }
  }
  
  [Table("Guild")]
  public class Guild
  {
    public int GuildId { get; set; }
    public string GuildName { get; set; }
    public ICollection<Player> Members { get; set; }
  }

AppDbContext.cs

  public DbSet<Player> Players { get; set; }
  public DbSet<Guild> Guilds { get; set; }

DbCommands.cs

  public static void CreateTestData(AppDbContext db)
  {
    ...
    Guild guild = new Guild()
    {
      GuildName = "T1",
      Members = new List<Player>() { hanna, faker, deft }
    };

    db.Guilds.Add(guild);
  }

MMO EFCore

Eager Loading

Pros
- can access DB at once
Cons
- load all data
DbCommands.cs

  public static void EagerLoading()
  {
    Console.WriteLine("Input Guild Name");
    Console.Write("> ");
    string name = Console.ReadLine();

    using (var db = new AppDbContext())
    {
      Guild guild = db.Guilds.AsNoTracking()
                      .Where(g => g.GuildName == name)
                      .Include(g => g.Members)
                        .ThenInclude(p => p.Item)
                      .First();
      
      foreach (Player player in guild.Members)
      {
        Console.WriteLine($"TemplateId({player.Item.TemplateId}) Owner({player.Name})");
      }
    }
  }

MMO EFCore

Explicit Loading

Pros
- can define loading moment
Cons
- cost of DB access
DbCommands.cs

  public static void ExplicitLoading()
  {
    Console.WriteLine("Input Guild Name");
    Console.Write("> ");
    string name = Console.ReadLine();

    using (var db = new AppDbContext())
    {
      Guild guild = db.Guilds
                      .Where(g => g.GuildName == name)
                      .First();
                      
      db.Entry(guild).Collection(g => g.Members).Load();
      
      foreach (Player player in guild.Members)
      {
        db.Entry(player).Reference(p => p.Item).Load();
      }

      foreach (Player player in guild.Members)
      {
        Console.WriteLine($"TemplateId({player.Item.TemplateId}) Owner({player.Name})");
      }
    }
  }

MMO EFCore

Select Loading

Pros
- loading data which is really needed
Cons
- need to write in select
  ex. SELECT COUNT(*)
DbCommends.cs

  public static void SelectLoading()
  {
    Console.WriteLine("Input Guild Name");
    Console.Write("> ");
    string name = Console.ReadLine();
    
    using (var db = new AppDbContext())
    {
      var info = db.Guilds
                   .Where(g => g.GuildName == name)
                   .Select(g => new
                   {
                     Name = g.GuildName,
                     MemberCount = g.Members.Count
                   })
                   .First();
                   
      Console.WriteLine($"GuildName({info.Name}), MemberCount({info.MemberCount})");
    }
  }

MMO EFCore

Data Transfer Object(DTO)

Model change

DataModel.cs

  [Table("Item")]
  public class Item
  {
    ...
    // [ForeignKey("OwnerId")]
    public int OwnerId { get; set; }
    public Player Owner { get; set; }
  }

  public class GuildDto
  {
    public int GuildId { get; set; }
    public string Name { get; set; }
    public int MemberCount { get; set; }
  }

Select Loading

Select()
- By DTO, you can make a object instead of .Select()
- and this solution uses LINQ, so it more easier to write and manage
IEnumerable
- LINQ to Object
- LINQ to XML Query
IQueryable
- LINQ to SQL Query
Extension.cs

  public static IQueryable<GuildDto> MapGuildToDto(this IQueryable<Guild> guild)
  {
    return guild.Select(g => new GuildDto()
    {
      GuildId = g.GuildId,
      Name = g.GuildName,
      MemberCount = g.Members.Count
    });
  }

DbCommands.cs

  public static void SelectLoading()
  {
    ...
    using (var db = new AppDbContext())
    {
      var info = db.Guilds
                   .Where(g => g.GuildName == name)
                   .MapGuildToDto()
                   .First();
      ...
  }

MMO EFCore

State

State Type

Detached
- No Tracking
- cannot use SaveChanges()
Unchanged
- exist in DB, but not change
- not changed from SaveChanges()
Deleted
- exist in DB, but should be deleted
- apply deleting from SaveChanges()
Modified
- exist in DB, changed in Client
- apply changing from SaveChanges()
Added
- not exist in DB
- apply adding from SaveChanges

SaveChanges()

SaveChanges()
- state of added objects is changed to Unchanged
- manage PK by SQL Identity
- after adding data, received ID and fill Id property of object
- reference Relationship and setting FK and link Object Reference
link existed player
- first, Tracked Instance
- then, SaveChanges()

State

DbCommands.cs

  public static void CreateTestData(AppDbContext db)
  {
    var hanna = new Player() { Name = "Hanna" };
    ...
    
    // 1) Detached
    Console.WriteLine(db.Entry(hanna).State);
    ...
    db.Items.AddRange(items);
    db.Guilds.Add(guild);

    // 2) Added
    Console.WriteLine(db.Entry(hanna).State);
    Console.WriteLine(hanna.PlayerId);    

    db.SaveChanges();

    // link with existed item
    {
      var owner = db.Players.Where(p => p.Name == "Hanna").First();

      Item item = new Item()
      {
        TemplateId = 300,
        CreateDate = DateTime.Now,
        Owner = owner
      };
      db.Items.Add(item);
      db.SaveChanges();
    }

    // 3) Unchanged
    Console.WriteLine(db.Entry(hanna).State);
    Console.WriteLine(hanna.PlayerId);
  }

MMO EFCore

Update

Update 3 Step

Tracked Entity
change Property of Entity Class(set)
call SaveChanges

Update

call SaveChanges
- call DetectChanges innerly
compare first Snapshot with current Snapshot in DetectChange
in SQL

  SELECT TOP(2) GuildId, GuildName
  FROM [Guilds]
  WHERE GuildName = N'T1';

  SET NOCOUNT ON;
         
  UPDATE [Guilds]
  SET GuildName = @p0
  WHERE GuildID = @p1;

  SELECT @@ROWCOUNT;

  public static void UpdateTest()
  {
    using(AppDbContext db = new AppDbContext())
    {
      var guild = db.Guilds.Single(g => g.GuildName == "T1");

      guild.GuildName = "DWG";

      db.SaveChanges();
    }
  }

MMO EFCore

Connected vs Disconnected Update

Disconnected

Disconnected
- Update steps occur not at once
- this steps are separated
- ex. REST API
Reload method
- send essential data and process 1-2-3 Step
Full Update method
- send all data and receive
- remake Entity and update

Model Change

Extentions.cs

  public static IQueryable<GuildDto> MapGuildToDto(this IQueryable<Guild> guild)
  {
    return guild.Select(g => new GuildDto()
    {
      GuildId = g.GuildId,
      ...
      });
    }
  }

Reload

Pros
- update by minimal data
Cons
- read twice
DbCommands.cs

  public static void ShowGuilds()
  {
    using(AppDbContext db = new AppDbContext())
    {
      foreach(var guild in db.Guilds.MapGuildToDto().ToList())
      {
        Console.WriteLine($"GuildID({guild.GuildId}) GuildName({guild.Name}) MemberCount({guild.MemberCount})");
      }
    }
  }

  public static void UpdateByReload()
  {
    ShowGuilds();

    // resume to receive DataId/ Data that need to change from out
    Console.WriteLine("Input GuildId");
    Console.Write("> ");
    int id = int.Parse(Console.ReadLine());
    Console.WriteLine("Input GuildNames");
    Console.Write("> ");
    string name = Console.ReadLine();

    using(AppDbContext db = new AppDbContext())
    {
      Guild guild = db.Find<Guild>(id);
      guild.GuildName = name;
      db.SaveChanges();
    }

    Console.WriteLine("----- Update Complete -----");
    ShowGuilds();
  }

MMO EFCore

Full

Pros
- don’t neet to read DB again, update directly
- find data by Primary key, so you don’t need to read original database
Cons
- need all data
- Security problem(Confidence in the other party)
JsonConvert
- use Json String in C#
- to make object, use DeserializeObject method
DbCommands.cs by Json

  // "GuildId":1, "GuildName": "Hello", "Members":null
  public static string MakeUpdateJsonStr()
  {
    var jsonStr = "{\"GuildId\":1, \"GuildName\":Hello, \"Members\":null}";
    return jsonStr;
  }

  public static void UpdateByFull()
  {
    ShowGuilds();

    string jsonStr = MakeUpdateJsonStr();
    Guild guild = JsonConvert.DeserializeObject<Guild>(jsonStr);

    using (AppDbContext db = new AppDbContext())
    {
      db.Guilds.Update(guild);
      db.SaveChanges();
    }

    Console.WriteLine("----- Update Complete -----");
    ShowGuilds();
  }

MMO EFCore

DbCommands.cs by Object

  public static void UpdateByFull()
  {
    ShowGuilds();

    string jsonStr = MakeUpdateJsonStr();
    // Guild guild = JsonConvert.DeserializeObject<Guild>(jsonStr);

    Guild guild = new Guild()
    {
      GuildId = 1,
      GuildName = "TestGuild"
    };
    ...
  }

MMO EFCore

Foreign Key and Nullable

Relationship

Principal Entity
- ex. Player
Dependent Entity
- Foreign Key
- ex. Item
Policy about Relationship
- Dependent Data can exsist without Principal Data
  1) item cannot exsist without player
  2) item can exsist without item
Nullable
- ex. int?
- FK → int → 1)
  if player is deleted, item that reference the player is deleted by FK
- FK → int? → 2)
  if player is deleted, item that reference the player is not deleted by FK
DbCommands.cs

  public static void ShowItem()
  {
    using (AppDbContext db = new AppDbContext())
    {
      foreach (var item in db.Items.Include(i => i.Owner).IgnoreQueryFilters().ToList())
      {
        if (item.Owner == null)
          Console.WriteLine($"ItemId({item.ItemId}) TemplateId({item.TemplateId}) Owner(0)");
        else
          Console.WriteLine($"ItemId({item.ItemId}) TemplateId({item.TemplateId}) Owner({item.Owner})");
      }
    }
  }

  public static void Test()
  {
    ShowItem();

    Console.WriteLine("Input Delete PlayerId");
    Console.Write("> ");
    int id = int.Parse(Console.ReadLine());

    using (AppDbContext db = new AppDbContext())
    {
      Player player = db.Players
                        .Include(p => p.Item)
                        .Single(p => p.PlayerId == id);

      db.Players.Remove(player);
      db.SaveChanges();
    }

    Console.WriteLine("----- Test Complete -----");
    ShowItem();
  }

1) Not Nullable

DataModel.cs

  [Table("Item")]
  public class Item
  {
    ...
    public int OwnerId { get; set; }
    ...  
  }

MMO EFCore

2) Nullable

DataModel.cs

  [Table("Item")]
  public class Item
  {
    ...
    public int? OwnerId { get; set; }
    ...  
  }

MMO EFCore

Update Relationship

Update Relationship 1:1

Create new object and remove original data

  public static void Update_1v1()
  {
    ShowItem();

    Console.WriteLine("Input ItemSwitch PlayerId");
    Console.Write("> ");
    int id = int.Parse(Console.ReadLine());

    using(AppDbContext db = new AppDbContext())
    {
      Player player = db.Players
                        .Include(p => p.Item)
                        .Single(p => p.PlayerId == id);

      player.Item = new Item()
      {
        TemplateId = 777,
        CreateDate = DateTime.Now
      };
      
      db.SaveChanges();
    }
    
    Console.WriteLine("----- Test Complete -----");
    ShowItem();
  }

MMO EFCore

Not create new object, just change the data

  ...
    using(AppDbContext db = new AppDbContext())
    {
      Player player = db.Players
                        .Include(p => p.Item)
                        .Single(p => p.PlayerId == id);

      if (player.Item != null)
      {
        player.Item.TemplateId = 888;
        player.Item.CreateDate = DateTime.Now;
      }
  ...

MMO EFCore

Update Realtionship 1vN

Create new object and remove original data

  public static void ShowGuild()
  {
    using (AppDbContext db = new AppDbContext())
    {
      foreach (var guild in db.Guilds.Include(g => g.Members).ToList())
      {
        Console.WriteLine($"GuildId({guild.GuildId}) GuildName({guild.GuildName}) MemberCount({guild.Members.Count})");
      }
    }
  }
  
  public static void Update_1vN()
  {
    ShowGuild();

    Console.WriteLine("Input GuildId");
    Console.Write("> ");
    int id = int.Parse(Console.ReadLine());

    using (AppDbContext db = new AppDbContext())
    {
      Guild guild = db.Guilds
                      .Include(g => g.Members)
                      .Single(g => g.GuildId == id);

      guild.Members = new List<Player>()
      {
        new Player() {Name = "Dopa" }
      };

      db.SaveChanges();
    }

    Console.WriteLine("----- Test Complete -----");
    ShowGuild();
  }

MMO EFCore

Not create new object, just change the data

  ...
    using (AppDbContext db = new AppDbContext())
    {
      Guild guild = db.Guilds
                      .Include(g => g.Members)
                      .Single(g => g.GuildId == id);
      
      /*
      guild.Members = new List<Player>()
      {
        new Player() {Name = "Dopa" }
      };
      */

      guild.Members.Add(new Player() { Name = "Dopa" });
      guild.Members.Add(new Player() { Name = "Keria" });
      guild.Members.Add(new Player() { Name = "Pyosik" });

      db.SaveChanges();
    }
...

MMO EFCore

Delete

Model Change

DataModel.cs

  [Table("Item")]
  public class Item
  {
    ...
    public bool SoftDeleted { get; set; }
    ...
  }

AppDbContext.cs
- filtering of model level when accessing Item entity
- if want ignore filter, add IgnoreQueryfilters option

  protected override void OnModelCreating(ModelBuilder builder)
  {
    builder.Entity<Item>().HasQueryFilter(i => i.SoftDeleted == false);
  }

SoftDelete

Delete
- Tracking Entity
- call Remove
- call SaveChanges
SoftDelte
- not remove the data in DB
- just check that the data is removed
DbCommands.cs

  public static void ShowItem()
  {
    using (AppDbContext db = new AppDbContext())
    {
      foreach (var item in db.Items.Include(i => i.Owner).IgnoreQueryFilters().ToList())
      {
        if (item.SoftDeleted)
        {
          Console.WriteLine($"DELETED - ItemId({item.ItemId}) TemplateId({item.TemplateId}) Owner(0)");
        }
        else
        {                        
          if (item.Owner == null)
            Console.WriteLine($"ItemId({item.ItemId}) TemplateId({item.TemplateId}) Owner(0)");
          else
            Console.WriteLine($"ItemId({item.ItemId}) TemplateId({item.TemplateId}) Owner({item.Owner.Name})");
        }
      }
    }
  }

  public static void TestDelete()
  {
    ShowItem();
    Console.WriteLine("Select Delete ItemId");
    Console.Write("> ");
    int id = int.Parse(Console.ReadLine());

    using (AppDbContext db = new AppDbContext())
    {
      Item item = db.Items.Find(id);
      
      item.SoftDeleted = true;
      db.SaveChanges();
    }

    Console.WriteLine("----- Test Complete -----");
    ShowItem();
  }

MMO EFCore

Configuration

Convention

Convention
- make form and name with fixed rule, be processed automately in EF Core
- easy and fast, but not include all case
Entity Class
- public access modifier + non-static
- search and analize public getter during property
- property name = table column name
name, structure, size
- .NET structure ↔ SQL structure
  ex. int, bool
- follow Nullable or not of .NET structure
  ex. string is nullable
  ex. int non-nul vs int? is nullable
PK
- define property which name is Id or [ClassName]Id to PK
- Composite Key cannot be defined by Convention

Data Annotation

add Attribute at class/property

Fluent Api

set Setting directly in OnModelCreating
is the biggest in application range
DB Column type, size, nullable

	Data Annotation	Fluent Api
Nullable	[Required]	.Isrequired
String length	[MayLength(20)]	.Has MaxLength(20)
char form		.IsUnicode(true)

	Data Annotation	Fluent Api
Primary Key	[Key][Column(Order=0)] [Key][Column(Order=1)]	.HasKey(x => new {x.Prop1, x.Prop2})

Index

	Data Annotation	Fluent Api
add Index		.HasIndex(p => p.Prop1)
add complex index		.HasIndex(p => new {p.Prop1, p.Prop2})
set index name		.HasIndex(p => p.Prop1).HasName("Index_MyProp")
add unique Index		.HasIndex(p => p.Prop1).IsUnique()

table name

	Data Annotation	Fluent Api
DbSet <T >	[Property/Class]name [Table("MyTable")]	.ToTable("MyTable")

column name
- in Convention, using [Property]name

	Data Annotation	Fluent Api
Column Name<T>	[Column("MyCol")]	.HasColumnName("MyCol")

use code modeling, but not in DB modeling(Property and Class)

	Data Annotation	Fluent Api
use only in code<T>	[NotMapped]	.Ignore()

Soft Delete

	Data Annotation	Fluent Api
Soft Delete<T>		.HasQueryFilter()

Order
- Convention is the easist
- Validation and related parts → Data Annotation(directly, call SaveChanges)
- etc → Fluent Api

Test

DataModel.cs

  [Table("Player")]
  public class Player
  {
    ...
    [Required]
    [MaxLength(20)]
    public string Name { get; set; }
    ...
  }

AppDbContext.cs

  protected override void OnModelCreating(ModelBuilder builder)
  {
    ...
    builder.Entity<Player>()
           .HasIndex(p => p.Name)
           .HasName("Index_Person_Name")
           .IsUnique();
    ...
  }

MMO EFCore

Relationship Configuration

Relationship
- Principal Entity
- Dependent Entity
- Navigational Property
- Primary Key(PK)
- Foreign Key(FK)
- Principal Key = PK or Unique Alternate Key
- Required Relationship(Not-Null)
- Optional Relationship(Nullable)
Set FK by Convention
- [PrincipalKeyName]
  ex. PlayerId
- [Class][PrincipalKeyName]
  ex. PlayerPlayerId
- [NagivationalPropertyName][PrincipalKeyName]
  ex. OwnerPlayerId / OwnerId

FK and Nullable

Required Relationship(Not-Null)
- when delete, call OnDelete factor by Cascase mode → if delete Principal, delete Dependent
Optional Relationship(Nullable)
- when delete, call OnDelete factor by ClientSetNull mode
- if delete Principal and Dependent Tracking, FK is setted null
- if delete Principal and not Dependent Tracking, occur Exception

Not in Convention

Complex FK
several Navigational Property reference same class
DB or Delete Customizing

Set Relationship with Data Annotation

[ForeignKey(“Prop1”)]
[InversProperty]
- several Navigational Property reference same class

Set Relationship with Fluent Api

.HasOne()	.HasMany()
.WithOne()	.WithMany()
.HasForeignKey()	.IsRequired()	.OnDelete()

Test

DataModel.cs

  [Table("Item")]
  public class Item
  {
    ...
    public int TestOwnerId { get; set; }
    // public int OwnerId { get; set; }
    ...
    public int? TestCreatorId { get; set; }
    public Player Creator { get; set; }
    ...
  }

  [Table("Player")]
  public class Player
  {
    ...
    public Item OwnedItem { get; set; }
    public ICollection<Item> CreatedItems { get; set; }
    //public Item Item { get; set; }
    ...
  }

AppDbContext.cs

  protected override void OnModelCreating(ModelBuilder builder)
  {
    ...
    builder.Entity<Player>()
           .HasMany(p => p.CreatedItems)
           .WithOne(i => i.Creator)
           .HasForeignKey(i => i.TestCreatorId);

    builder.Entity<Player>()
           .HasOne(p => p.OwnedItem)
           .WithOne(i => i.Owner)
           .HasForeignKey<Item>(i => i.TestOwnerId);
  }

MMO EFCore

Shadow Property & Backing Field

Shadow Property

in Class OK, but Not in DB

	Data Annotation	Fluent Api
only use in code	[NotMapped]	.Ignore()

in DB OK, but Not in Class → Shadow Property

	Data Annotation	Fluent Api
create		.Property<DateTime>("RecoveredDate")
Read/Write		.Property("RecoveredDate").CurrentVale

Test

AppDbContext.cs

  protected override void OnModelCreating(ModelBuilder builder)
  {
    ...
    builder.Entity<Item>().Property<DateTime>("RecoveredDate");
  }

DbCommands.cs

  public static void CreateTestData(AppDbContext db)
  {
    ...
    db.Entry(items[0]).Property("RecoveredDate").CurrentValue = DateTime.Now;
  }

MMO EFCore

Backing Field (EF Core)

mapping private field in DB and change to public getter to use
ex. save string by Json type in DB and getter use json with processing
Fluent Api in normal
DataModel.cs

  public struct ItemOption
  {
    public int str;
    public int dex;
    public int hp;
  }

  [Table("Item")]
  public class Item
  {
    private string _jsonData;
    public string JsonData
    {
      get { return _jsonData; }
      //set { _jsonData = value; }
    }

    public void SetOption(ItemOption option)
    {
      _jsonData = JsonConvert.SerializeObject(option);
    }

    public ItemOption GetOption()
    {
      return JsonConvert.DeserializeObject<ItemOption>(_jsonData);
    }
    ...
  }

AppDbContext.cs

  protected override void OnModelCreating(ModelBuilder builder)
  {
    ...
    builder.Entity<Item>()
                .Property(i => i.JsonData)
                .HasField("_jsonData");
  }

MMO EFCore

Entity ↔ DB Table link

all Entity Class Read/ Write → Cost (Select Loading, DTO)

Owned Type

add normal Class in Entity Class

add to same table

.OwnsOne()
- Relationship .Include() vs Ownership

Test

DataModel.cs

  //public struct ItemOption
  //{
  //    public int str;
  //    public int dex;
  //    public int hp;
  //}

  public class ItemOption
  {
    public int Str { get; set; }
    public int Dex { get; set; }
    public int Hp { get; set; }
  }

  [Table("Item")]
  public class Item
  {
    //private string _jsonData;
    //public string JsonData
    //{
    //  get { return _jsonData; }
    //  set { _jsonData = value; }
    //}

    //public void SetOption(ItemOption option)
    //{
    //  _jsonData = JsonConvert.SerializeObject(option);
    //}

    //public ItemOption GetOption()
    //{
    //  return JsonConvert.DeserializeObject<ItemOption>(_jsonData);
    //}
    
    public ItemOption Option { get; set; }
  }

AppDbContext.cs

  protected override void OnModelCreating(ModelBuilder builder)
  {
    ...
    builder.Entity<Item>()
           .OwnsOne(i => i.Option);
    ...
  }

DbCommands.cs

  public static void CreateTestData(AppDbContext db)
  {
    ...
    items[1].Option = new ItemOption() { Dex = 1, Hp = 2, Str = 3 };
    ...
  }

MMO EFCore

add to diff table

.OwnsOne().ToTable()
AppDbContext.cs

  protected override void OnModelCreating(ModelBuilder builder)
  {
    //builder.Entity<Item>()
    //       .OwnsOne(i => i.Option);

    builder.Entity<Item>()
           .OwnsOne(i => i.Option)
           .ToTable("ItemOption");

DbCommands.cs

  public static void ShowItem()
  {
    using (AppDbContext db = new AppDbContext())
    {
      foreach (var item in db.Items.Include(i => i.Owner).IgnoreQueryFilters().ToList())
      {
        ...
        else
        {
          if (item.Option != null)
            Console.WriteLine("STR " + item.Option.Str);
          ...
  }

MMO EFCore

Table Per Hiearchy (TPH)

several classes of hierarchy relation ↔ mapping in one table
ex. Dog, Cat, Bird, Animal

Convention

first make class by hiearchy and add DbSet
Discriminator

Test

DataModel.cs

  public class EventItem : Item
  {
    public DateTime DestroyDate { get; set; }
  }

AppDbContext.cs

  class AppDbContext : DbContext
  {
    public DbSet<EventItem> EventItems { get; set; }
  }

MMO EFCore

Fluent Api

.HasDiscriminator().HasValue()

Test

DataModel.cs

  public enum ItemType
  {
    NormalItem,
    EventItem
  }
  
  [Table("Item")]
  public class Item
  {
    public ItemType Type { get; set; }
    ...
  }

AppDbContext.cs

  class AppDbContext : DbContext
  {
    //public DbSet<EventItem> EventItems { get; set; }
  }

  protected override void OnModelCreating(ModelBuilder builder)
  { 
    ...           
    builder.Entity<Item>()
           .HasDiscriminator(i => i.Type)
           .HasValue<Item>(ItemType.NormalItem)
           .HasValue<EventItem>(ItemType.EventItem);
  }

DbCommands.cs

  public static void CreateTestData(AppDbContext db)
  {
    ...
    new EventItem()
    {
      ...
      DestroyDate =  DateTime.Now
    },
    ...
  }

  public static void ShowItem()
  {
    using (AppDbContext db = new AppDbContext())
    {
      foreach (var item in db.Items.Include(i => i.Owner).IgnoreQueryFilters().ToList())
      {
        ...
        else
        {
          ...
          EventItem eventItem = item as EventItem;
          if (eventItem != null)
            Console.WriteLine("DestroyDate: " + eventItem.DestroyDate);
          ...
  }

MMO EFCore

Table Splitting

several entity Class ↔ mapping one table

Test

DataModel.cs

      public class ItemDetail
    {
        public int ItemDetailId { get; set; }
        public string Description { get; set; }
    }

    [Table("Item")]
    public class Item
    {
      ...
      public ItemDetail Detail { get; set; }
      ...
    }

AppDbContext.cs

  protected override void OnModelCreating(ModelBuilder builder)
  {
    ...
    //builder.Entity<Item>()
    //       .HasDiscriminator(i => i.Type)
    //       .HasValue<Item>(ItemType.NormalItem)
    //       .HasValue<EventItem>(ItemType.EventItem);

    builder.Entity<Item>()
           .HasOne(i => i.Detail)
           .WithOne()
           .HasForeignKey<ItemDetail>(i => i.ItemDetailId);

    builder.Entity<Item>().ToTable("Items");
    builder.Entity<ItemDetail>().ToTable("Items");
  }

DbCommands.cs

  public static void CreateTestData(AppDbContext db)
  {
    ...
    items[2].Detail = new ItemDetail()
    {
      Description = "This is a good item."
    };
  }

  public static void ShowItem()
  {
    using (AppDbContext db = new AppDbContext())
    {
      //foreach (var item in db.Items.Include(i => i.Owner).IgnoreQueryFilters().ToList())
      foreach (var item in db.Items.Include(i => i.Owner).Include(i => i.Detail).IgnoreQueryFilters().ToList())
      {
        ...
        else
        {
          ...
          if (item.Detail != null)
            Console.WriteLine(item.Detail.Description);
  }

MMO EFCore

Backing Field & Relationship

Backing Field → mapping private field in DB

can use in Navigation Property

Test

DataModel.cs

  //public class ItemOption
  //{
  //  public int Str { get; set; }
  //  public int Dex { get; set; }
  //  public int Hp { get; set; }
  //}

  //public class ItemDetail
  //{
  //  public int ItemDetailId { get; set; }
  //  public string Description { get; set; }
  //}

  //public enum ItemType
  //{
  //  NormalItem,
  //  EventItem
  //}

  public class ItemReview
  {
    public int ItemReviewId { get; set; }
    public int Score { get; set; } // 0~5
  }

  [Table("Item")]
  public class Item
  {
    //public ItemType Type { get; set; }
    //public ItemOption Option { get; set; }
    //public ItemDetail Detail { get; set; }
    ...
    //public int? TestCreatorId { get; set; }
    //public Player Creator { get; set; }

    public double? AverageScore { get; set; }

    private readonly List<ItemReview> _reviews = new List<ItemReview>();
    public IEnumerable<ItemReview> Reviews { get { return _reviews.ToList(); } }

    public void AddReview(ItemReview review)
    {
      _reviews.Add(review);
      AverageScore = _reviews.Any() ? _reviews.Average(r => r.Score) : (double?)null;
    }

    public void RemoveReview(ItemReview review)
    {
      _reviews.Remove(review);
    }
  }

  //public class EventItem : Item
  //{
  //  public DateTime DestroyDate { get; set; }
  //}

AppDbContext.cs

  class AppDbContext : DbContext
  {
    ...
    //public DbSet<EventItem> EventItems { get; set; }
    ...
  }

  protected override void OnModelCreating(ModelBuilder builder)
  {
    ...
    //builder.Entity<Player>()
    //       .HasMany(p => p.CreatedItems)
    //       .WithOne(i => i.Creator)
    //       .HasForeignKey(i => i.TestCreatorId);
    ...
    //builder.Entity<Item>()
    //       .OwnsOne(i => i.Option)
    //       .ToTable("ItemOption");
    ...
    //builder.Entity<Item>()
    //       .HasOne(i => i.Detail)
    //       .WithOne()
    //       .HasForeignKey<ItemDetail>(i => i.ItemDetailId);

    //builder.Entity<Item>().ToTable("Items");
    //builder.Entity<ItemDetail>().ToTable("Items");

    builder.Entity<Item>()
           .Metadata
           .FindNavigation("Reviews")
           .SetPropertyAccessMode(PropertyAccessMode.Field);
  }

DbCommands.cs

  public static void CreateTestData(AppDbContext db)
  {
    ...
    //new EventItem()
    new Item()
    {
      ...
      //DestroyDate =  DateTime.Now
    },
    //items[1].Option = new ItemOption() { Dex = 1, Hp = 2, Str = 3 };

    //items[2].Detail = new ItemDetail()
    //{
    //    Description = "This is a good item."
    //};

    items[0].AddReview(new ItemReview() { Score = 5 });
    items[0].AddReview(new ItemReview() { Score = 4 });
    items[0].AddReview(new ItemReview() { Score = 1 });
    items[0].AddReview(new ItemReview() { Score = 5 });

    ...
    //{
    //  var owner = db.Players.Where(p => p.Name == "Hanna").First();

    //  Item item = new Item()
    //  {
    //    TemplateId = 300,
    //    CreateDate = DateTime.Now,
    //    Owner = owner
    //  };
    //  db.Items.Add(item);
    //  db.SaveChanges();
    //}
  }

  public static void ShowItem()
  {
    using (AppDbContext db = new AppDbContext())
    {
      //foreach (var item in db.Items.Include(i => i.Owner).Include(i => i.Detail).IgnoreQueryFilters().ToList())
      foreach (var item in db.Items.Include(i => i.Owner).IgnoreQueryFilters().ToList())
      {
        ...
        else
        {
          //if (item.Option != null)
          //  Console.WriteLine("STR " + item.Option.Str);

          //EventItem eventItem = item as EventItem;
          //if (eventItem != null)
          //  Console.WriteLine("DestroyDate: " + eventItem.DestroyDate);

          //if (item.Detail != null)
          //  Console.WriteLine(item.Detail.Description);

          if(item.AverageScore == null)
            Console.WriteLine("Score(None)");
          else
            Console.WriteLine($"Score({item.AverageScore})");

          if (item.Owner == null)
            Console.WriteLine($"ItemId({item.ItemId}) TemplateId({item.TemplateId}) Owner(0)");
          else
            Console.WriteLine($"ItemId({item.ItemId}) TemplateId({item.TemplateId}) Owner({item.Owner.Name})");
        }
      }
    }
  }

MMO EFCore

UserDefined Function(UDF)

The ability to invoke SQL that you create
want to perform operations on the DB side.
EF Core queries are inefficient

Steps

Configuration
- make static function and assign EF Core
Database Setup
Use

Test

DataModel.cs

  [Table("Item")]
  public class Item
  {
    ...
    //public double? AverageScore { get; set; }

    //private readonly List<ItemReview> _reviews = new List<ItemReview>();
    //public IEnumerable<ItemReview> Reviews { get { return _reviews.ToList(); } }

    //public void AddReview(ItemReview review)
    //{
    //  _reviews.Add(review);
    //  AverageScore = _reviews.Any() ? _reviews.Average(r => r.Score) : (double?)null;
    //}

    //public void RemoveReview(ItemReview review)
    //{
    //  _reviews.Remove(review);
    //}

    public ICollection<ItemReview> Reviews { get; set; }
  }

Program.cs

  // assign EFCore
  // Annotation(Attribute)
  [DbFunction()]
  public static double? GetAverageReviewScore(int ItemId)
  {
    throw new NotImplementedException("Don't Use!");
  }

AppDbContext.cs

  protected override void OnModelCreating(ModelBuilder builder)
  {
    ...
    //builder.Entity<Item>()
    //       .Metadata
    //       .FindNavigation("Reviews")
    //       .SetPropertyAccessMode(PropertyAccessMode.Field);

    // DbFunction
    builder.HasDbFunction(() => Program.GetAverageReviewScore(0));
  }

DbCommands.cs

  public static void InitializeDB(bool forceReset = false)
  {
    using (AppDbContext db = new AppDbContext())
    {
      ...
      string command = 
        @" CREATE FUNCTION GetAverageReviewScore (@itemId INT) RETURNS FLOAT
           AS
           BEGIN

           DECLARE @result AS FLOAT

           SELECT @result = AVG(CAST([Score] AS FLOAT))
           FROM ItemReview AS r
           WHERE @itemId = r.ItemId

           RETURN @result

           END";

      db.Database.ExecuteSqlRaw(command);
      ...
    }
  }

  public static void CreateTestData(AppDbContext db)
  {
    ...
    //items[0].AddReview(new ItemReview() { Score = 5 });
    //items[0].AddReview(new ItemReview() { Score = 4 });
    //items[0].AddReview(new ItemReview() { Score = 1 });
    //items[0].AddReview(new ItemReview() { Score = 5 });

    items[0].Reviews = new List<ItemReview>()
    {
      new ItemReview(){Score = 5},
      new ItemReview(){Score = 3},
      new ItemReview(){Score = 2},
    };
            
    items[1].Reviews = new List<ItemReview>()
    {
      new ItemReview(){Score = 1},
      new ItemReview(){Score = 1},
      new ItemReview(){Score = 0},
    };
    ...
  }

  public static void ShowItem(AppDbContext db)
  {
    ...
    //if(item.AverageScore == null)
    //  Console.WriteLine("Score(None)");
    //else
    //  Console.WriteLine($"Score({item.AverageScore})");
  }

  public static void CalcAverage()
  {
    using(AppDbContext db = new AppDbContext())
    {
      foreach(double? average in db.Items.Select(i => Program.GetAverageReviewScore(i.ItemId)))
      {
        if(average == null)
          Console.WriteLine("No Review!");
        else
          Console.WriteLine($"Average: {average.Value}");
      }
    }
  }

MMO EFCore

Default Value

How to be defined

defined by Entity Class
defined by DB Table
- if you use DB by other way except EF ↔ DB, it makes diff
  ex) SQL Script

Auto-Property Initializer(C# 6.0)

Entity default value
- apply DB by SaveChange

Test

DataModel.cs

  [Table("Item")]
  public class Item
  {
    ...
    public DateTime CreateDate { get; set; } = new DateTime(2020, 1, 1);
    ...
    //public ICollection<ItemReview> Reviews { get; set; }
  }

  //public class ItemReview
  //{
  //    public int ItemReviewId { get; set; }
  //    public int Score { get; set; } // 0~5
  //}

DbCommands.cs

  public static CreateTestData(AppDbContext db)
  {
    ...
    //items[0].Reviews = new List<ItemReview>()
    //{
    //  new ItemReview(){Score = 5},
    //  new ItemReview(){Score = 3},
    //  new ItemReview(){Score = 2},
    //};
            
    //items[1].Reviews = new List<ItemReview>()
    //{
    //  new ItemReview(){Score = 1},
    //  new ItemReview(){Score = 1},
    //  new ItemReview(){Score = 0},
    //};
  }

MMO EFCore

Fluent Api

DB Table
- DEFAULT

Test

AppDbContext.cs

  protected override void OnModelCreating(ModelBuilder builder)
  {
    ...
    //builder.HasDbFunction(() => Program.GetAverageReviewScore(0));

    builder.Entity<Item>()
           .Property("CreateDate")
           .HasDefaultValue(DateTime.Now);
  }

MMO EFCore

SQL Fragment

.HasDefaultValueSql
- when add new value, excute in DB

Test

DataModel.cs

  [Table("Item")]
  public class Item
  {
    ...
    //public DateTime CreateDate { get; set; } = new DateTime(2020, 1, 1);
    public DateTime CreateDate { get; private set; }
    ...
  }

AppDbContext.cs

  protected override void OnModelCreating(ModelBuilder builder)
  {
    ...

    //builder.Entity<Item>()
    //       .Property("CreateDate")
    //       .HasDefaultValue(DateTime.Now);

    builder.Entity<Item>()
           .Property("CreateDate")
           .HasDefaultValueSql("GETDATE()");
  }

DbCommands.cs

  public static void CreateTestData(AppDbContext db)
  {
    ...
    List<Item> items = new List<Item>()
    {
      new Item()
      {
        TemplateId = 101,
        //CreateDate = DateTime.Now,
        Owner = hanna
      },
      new Item()
      {
        TemplateId = 102,
        //CreateDate = DateTime.Now,
        Owner = faker,
      },
      new Item()
      {
        TemplateId = 103,
        //CreateDate = DateTime.Now,
        Owner = deft
      }
    };
    ...
  }

  //public static void UpdateDate()
  //{
  //  Console.WriteLine("Input Player Name");
  //  Console.Write("> ");
  //  string name = Console.ReadLine();

  //  using(var db = new AppDbContext())
  //  {
  //    var items = db.Items.Include(i => i.Owner)
  //                  .Where(i => i.Owner.Name == name);

  //    foreach(Item item in items)
  //    {
  //      item.CreateDate = DateTime.Now;
  //    }

  //    db.SaveChanges();
  //  }

  //  ReadAll();
  //}

MMO EFCore

Value Generator

excute in EF Core
set Generator rule

Test

DataModel.cs

  public class PlayerNameGenerator : ValueGenerator<string>
  {
    public override bool GeneratesTemporaryValues => false;

    public override string Next(EntityEntry entry) 
    {
      string name = $"Player_{DateTime.Now.ToString("yyyyMMdd")}";
      return name;
    }
  }

AppDbContext.cs

  protected override void OnModelCreating(ModelBuilder builder)
  {
    ...
    builder.Entity<Player>()
           .Property(p => p.Name)
           .HasValueGenerator((p, e) => new PlayerNameGenerator());
  }

DbCommands.cs

  public static void CreateTestData(AppDbContext db)
  {
    //var hanna = new Player() { Name = "Hanna" };
    var hanna = new Player() { };
  }

MMO EFCore

Migration

EF Core DbContext ↔ need agreement about DB state

Code-First

the standard is Entity Class or Db Context
not always update recently
Migration Step
1) make Migration
2) apply Migration
Add-Migration [Name]
1) search DbContext and analize → DB Modeling(recent)
2) DB Modeling most latest Migration state by ModelSnapshot.cs
3) 1-2 Compare and print result
a) ModelSnapshot
b) Migrate.Designer.cs and Migrate.cs -> detail data related with Migration
- Add Up/Down by Hands

MMO EFCore

apply Migration
1) SQL change script
- Script-Migration [From] [To] [Options]
  2) call Database.Migrate
  3) Command Line method
- Update-Database [options]

Test

DataModel.cs

  [Table("Item")]
  public class Item
  {
    ...
    public int ItemGreade { get; set; }
    ...
  }

MMO EFCore

Sync to specific Migration
- Update-Database [Name]

MMO EFCore

Delete last Migration
- Remove-Migration

MMO EFCore

Database-First

if you have Database, you can make EF Core DbContext from Database
Download
- EFCore Poser Tools
Reverse Engineer
- [EF Core Power Tools] - [Reverse Engineer]

MMO EFCore

SQL-First

Methods
- Hands Power
- Script-Migration [From] [To] [Options]
- Compare DB and extract SQL

DbContext(Optimize)

ChangeTracker

Tracking State
- State is mostly intuitive but when it include Relationship, it becames more complexive

Database

Transaction
DB Creation / Migration
Raw SQL

Model

DB Modeling
State Check method
- Entry().State
- Entry().Property().IsModified
- Entry().Navigation().IsModified
state changing on Add/AddRange
- NotTracking State → Added
- Tracking State → seperated Modified or current state maintain by FK setting
state changing on Remove/RemoveRange
- (key that made by DB) && (not C# default value) → Updated, Modified or Deleted
- (not Key that made by DB) (C# default value) → Added
- above is Added because of behavior consistency
- DB need to the existence first (ex. Cascade Delete Process)

Test

DbCommands.cs

  public static void CreateTestData(AppDbContext db)
  {
    var hanna = new Player() { Name = "Hanna" };
    //var hanna = new Player() { };
    ...
    
    db.SaveChanges();

    // Add Test
    {
      Item item = new Item()
      {
        TemplateId = 500,
        Owner = hanna
      };
      db.Items.Add(item);
      // add item -> indirectly infect Player
      // Player is Tracking state, it doesn't need to FK setting 

      Console.WriteLine("2.)" + db.Entry(hanna).State);
    }

    // Delete Test
    {
      Player p = db.Players.First();

      // DB don't know the new guild (not exist DB key)
      p.Guild = new Guild() { GuildName = "This will be deleted Soon" };
      // Item is in DB already (exist DB Key)
      p.OwnedItem = items[0];

      db.Players.Remove(p);

      // remove Player directly
      Console.WriteLine("3.)" + db.Entry(p).State); // Deleted
      Console.WriteLine("4.)" + db.Entry(p.Guild).State); // Added
      Console.WriteLine("5.)" + db.Entry(p.OwnedItem).State); // Deleted
    }
  }

MMO EFCore

Update/UpdateRange
- the normal method to update Entity in EF is not Update
- Tracked Entity → change property → SaveChanges
- Update is to update all Untracked Entity (Disconnected State)
Update Step in EF Core
1) call Update
2) Entity State = Modified
3) all IsModified of Non-Relational Property = true
Relationship in Update
- (key that made by DB) && (not 0 → Updated, Modified or Deleted
- (not Key that made by DB) (0) → Added
Attach
- change Untracked Entity to Tracked Entity
Relationship in Attach
- (key that made by DB) && (not 0 → Unchanged
- (not Key that made by DB) (0) → Added

Test

DbCommands.cs

  public static void CreateTestData(AppDbContext db)
  {
    ...
    List<Item> items = new List<Item>()
    {
      ...
      //new Item()
      //{
      //  TemplateId = 102,
      //  Owner = faker,
      //},
      //new Item()
      //{
      //  TemplateId = 103,
      //  Owner = deft
      //}
    };

    //db.Entry(items[2]).Property("RecoveredDate").CurrentValue = DateTime.Now;
    ...
  }

  public static void TestUpdateAttach()
  {
    using (AppDbContext db = new AppDbContext())
    {
      // Update Test
      {
        // Disconnected
        Player p = new Player();
        p.PlayerId = 2;
        p.Name = "FakerLegend";

        // DB don't know the new guild(not exist DB Key)
        p.Guild = new Guild() { GuildName = "Update Guild" };

        Console.WriteLine("6) " + db.Entry(p.Guild).State); // Detached
        db.Players.Update(p);
        Console.WriteLine("7) " + db.Entry(p.Guild).State); // Added
      }

      //Attach Test
      {
        Player p = new Player();

        // TEMP
        p.PlayerId = 3;
        p.Name = "DeftHero";
        p.Guild = new Guild() { GuildName = "Update Guild" };

        Console.WriteLine("8) "+ db.Entry(p.Guild).State); // Detached
        db.Players.Attach(p);
        Console.WriteLine("9) " + db.Entry(p.Guild).State); // Added
      }
    }
  }

MMO EFCore

DbCommands.cs

  public static void TestUpdateAttach()
  {
    ...
    //Attach Test
    {
      Player p = new Player();

      // TEMP
      p.PlayerId = 3;
      //p.Name = "DeftHero";
      p.Guild = new Guild() { GuildName = "Update Guild" };

      Console.WriteLine("8) "+ db.Entry(p.Guild).State); // Detached
      db.Players.Attach(p);
      p.Name = "DeftHero";
      Console.WriteLine("9) " + db.Entry(p.Guild).State); // Added
    }
  }

MMO EFCore

State Control

can control State (ex. Optimization)
Entry().State = EntityState.Added
Entry().Property(“”).IsModified = true

TrackGraph

State Control of Untracked Entity in Relationship
ex. change speficific data

ChangeTracker

detect state data changing
ex. Print log when name of player changing
ex. Put Validation code
ex. add CreateTime data when player is created time
Steps
1) override SaveChanges
2) extract / use changing data by ChangeTracker.Entries

Test

DataModel.cs

  // detect Created Time
  public interface ILogEntity
  {
    DateTime CreateTime { get; }
    void SetCreateTime();
  }

  [Table("Player")]
  public class Player
  {
    ...
    //public ICollection<Item> CreatedItems { get; set; }
    ...
    public DateTime CreateTime { get; private set; }

    public void SetCreateTime()
    {
      CreateTime = DateTime.Now;
    }
  }

AppDbContext.cs

  public override int SaveChanges()
  {
    var entities = ChangeTracker.Entries()
                                .Where(e => e.State == EntityState.Added);

    foreach(var entity in entities)
    {
      ILogEntity tracked = entity.Entity as ILogEntity;
      if (tracked != null)
        tracked.SetCreateTime();
    }
    return base.SaveChanges();
  }

DbCommands.cs

  public static void StateControl()
  {
    using (AppDbContext db = new AppDbContext())
    {
      // State Control
      {
        Player p = new Player { Name = "StateTest" };
        db.Entry(p).State = EntityState.Added; // change Tracked
        // db.Players.Add(p);
        db.SaveChanges();
      }

      // TrackGraph
      {
        // Disconnected State
        // not all change, only change player name
        Player p = new Player()
        {
          PlayerId = 2,
          Name = "Faker_New",
        };

        p.OwnedItem = new Item() { TemplateId = 777 }; // item data
        p.Guild = new Guild() { GuildName = "TrackGraphGuild" }; // guild data

        db.ChangeTracker.TrackGraph(p, e =>
        {
          if(e.Entry.Entity is Player)
          {
            e.Entry.State = EntityState.Unchanged;
            e.Entry.Property("Name").IsModified = true;
          }
          else if(e.Entry.Entity is Guild)
          {
            e.Entry.State = EntityState.Unchanged;
          }
          else if(e.Entry.Entity is Item)
          {
            e.Entry.State = EntityState.Unchanged;
          }
        });
        db.SaveChanges();
      }
    }
  }

MMO EFCore

call SQL direct

you can call your SQL
ex. not to LINQ → call Stored Procedure, etc
ex. Performance Optimizing

FormSql → FromSql / FormSqlInterpolated

Add Raw SQL in EF Core Query

ExecuteSqlCommand → ExcuteSqlRaw / ExecuteSqlInterpolated

Non-Query SQL(not SELECT)

Reload

there is Tracked Entity already
when DB data is changed by ExecuteSqlCommand, then Reload

Test

DbCommands.cs

  public static void CreateTestData(AppDbContext db)
  {
    ...
    List<Item> items = new List<Item>()
    {
      new Item()
      {
        TemplateId = 101,
        Owner = hanna
      },
      new Item()
      {
        TemplateId = 102,
        Owner = faker,
      },
      new Item()
      {
        TemplateId = 103,
        Owner = deft
      }
    };
    
    //Console.WriteLine("1.)" + db.Entry(hanna).State);

    //db.SaveChanges();

    //{
    //  Item item = new Item()
    //  {
    //    TemplateId = 500,
    //    Owner = hanna
    //  };
    //  db.Items.Add(item);

    //  Console.WriteLine("2.)" + db.Entry(hanna).State);
    //}

    //{
    //  Player p = db.Players.First();
    //  p.Guild = new Guild() { GuildName = "This will be deleted Soon" };
    //  p.OwnedItem = items[0];

    //  db.Players.Remove(p);

    //  Console.WriteLine("3.)" + db.Entry(p).State); // Deleted
    //  Console.WriteLine("4.)" + db.Entry(p.Guild).State); // Added
    //  Console.WriteLine("5.)" + db.Entry(p.OwnedItem).State); // Deleted
    //}
  }

  public static void CallSQL()
  {
    using(AppDbContext db = new AppDbContext())
    {
      // FormSql
      {
        string name = "Hanna";

        // SQL Injection(Web Hacking)

        var list = db.Players
                     .FromSqlRaw("SELECT * FROM dbo.Player WHERE Name = {0}", name)
                     .Include(p => p.OwnedItem)
                     .ToList();

        foreach(var p in list)
        {
          Console.WriteLine($"{p.Name} {p.PlayerId}");
        }
                    
        // String Interpolation C#6.0
        var list2 = db.Players
                      .FromSqlInterpolated($"SELECT * FROM dbo.Player WHERE Name = {name}");

        foreach (var p in list2)
        {
          Console.WriteLine($"{p.Name} {p.PlayerId}");
        }
      }

      // ExecuteSqlCommand (Non-Query SQL)
      {
        Player p = db.Players.Single(p => p.Name == "Faker");
        string prevName = "Faker";
        string afterName = "Faker_New";
        db.Database.ExecuteSqlInterpolated($"UPDATE dbo.Player SET Name={afterName} WHERE Name={prevName}");

        db.Entry(p).Reload();
      }
    }
  }

MMO EFCore

Logging

print SQL log in console

Test

AppDbContext.cs

  public static readonly ILoggerFactory MyLoggerFactory = LoggerFactory.Create(builder => { builder.AddConsole(); });

  protected override void OnConfiguring(DbContextOptionsBuilder options)
  {
    options
      .UseLoggerFactory(MyLoggerFactory)
      .UseSqlServer(ConnectionString);
  }

MMO EFCore

Download