• Home
  • About
    • Hanna's Blog photo

      Hanna's Blog

      I wanna be a global developer.

    • Learn More
    • Email
    • LinkedIn
    • Github
  • Posts
    • All Posts
    • All Tags
  • Projects

[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)
  • PK
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
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



DatabaseEntity Framework CoreORM Share Tweet +1