Now, we need to modify existing models so we can persist them within a SQL database. To allow Entity Framework Core 3.0 to create, read, update, and delete records, we need to specify a primary key for each model. We can do this by using Data Annotations, which allow us to decorate a property with the [Key] decorator.

The following is an example of how to use Data Annotations for UserModel:

    public class UserModel 
    { 
      [Key] 
      public long Id { get; set; } 
      ... 
    }

You should apply this to UserModel, GameInvitationModel, GameSessionModel, and TurnModel in the Tic-Tac-Toe application. You can reuse existing Id properties and decorate them with the [Key] decorator, or add new ones if a model doesn't contain an Id property yet.

When working with SQL Server (or any other SQL 92 DBMS), the first thing you should think about is the relationship between tables. In Entity Framework Core 3, you can specify foreign keys within models by using the [ForeignKey] decorator.

Concerning the Tic-Tac-Toe application, this means that you have to update GameInvitationModel and add a foreign key relationship to the user model ID. Perform the following steps to do so:

1. Update GameInvitationModel and add a foreign key attribute to the InvitedByUser property:

        public class GameInvitationModel 
        { 
          [Key] 
          public Guid Id { get; set; } 
          public string EmailTo { get; set; } 
 
          public string InvitedBy { get; set; } 
          public UserModel InvitedByUser {get; set;}
          [ForeignKey(nameof(InvitedByUserId))]           
          public Guid InvitedByUserId { get; set; } 
 
          public bool IsConfirmed { get; set; } 
          public DateTime ConfirmationDate { get; set; } 
        } 

This is an already existing GameInvitationalModel class, and we are just decorating the property Id with a [Key] attribute so that Entity Framework Core 3.0 will be able to identify it as a primary key. The foreign key attribute, [ForeignKey(nameof(InvitedByUserId)], decorates the GUID called InvitedUserId so that EF Core 3.0 will be able to see this property as a foreign key to another table.

2. Update GameSessionModel and add a foreign key to UserId1:

        public class GameSessionModel
        {
          [Key]
          public Guid Id { get; set; }
          ...
    
          [ForeignKey(nameof(UserId1))]
          public UserModel User1 { get; set; }
          ...
        }  

Here, we have a GameSessionModel POCO class that's decorated with a primary key attribute on its Id property and a secondary key attribute on the user model called User 1. This will allow EF Core 3.0 to create a GameSessionModel table with a primary key called Id and a foreign key called User1, respectively.

3. Update TurnModel and add a foreign key to UserId:

        public class TurnModel
        {
          [Key]
          public Guid Id { get; set; }
          
          [ForeignKey(nameof(UserId))]
          public Guid UserId { get; set; }
          public UserModel User { get; set; }
          public int X { get; set; }
          public int Y { get; set; }
          public string Email { get; set; }
          public string IconNumber { get; set; }
        }  

Entity Framework Core 3 maps all properties in a model with a schema representation by default. But some more complex property types are not compatible, which is why we should exclude them from auto-mapping. But how do we do this? Well, by using the [NotMapped] decorator. How easy and straightforward is that?

4. For the Tic-Tac-Toe application, it makes no sense to persist the active user for a turn, so you should exclude this from the auto-mapping process by using the [NotMapped] decorator in GameSessionModel:

    public class GameSessionModel 
    { 
      [Key] 
      public Guid Id { get; set; } 
      ...
 
      [NotMapped] 
      public UserModel Winner { get; set; } 
 
      [NotMapped] 
      public UserModel ActiveUser { get; set; } 
      public Guid WinnerId { get; set; } 
      public Guid ActiveUserId { get; set; } 
      public bool TurnFinished { get; set; } 
      public int TurnNumber { get; set; } 
    } 

Now that you have decorated all your models using Entity Framework Core 3 Data Annotations, you will notice that you have two properties, User1 and User2, in GameSessionModel that point to the same UserModel entity. This results in a circular relationship, and that will give us a problem (when we work with relational databases) to performing operations such as cascading updates or cascading deletions.

5. To avoid circular relationships, you need to decorate User1 with the [ForeignKey] decorator and update the OnModelCreating method in the game database context, GameDbContext, to define the foreign key for User2. These two modifications will allow you to define the two foreign keys while avoiding automatic cascading operations, which would cause problems:

    protected override void OnModelCreating(ModelBuilder 
     modelBuilder) 
    { 
      modelBuilder.RemovePluralizingTableNameConvention(); 
      modelBuilder.Entity(typeof(GameSessionModel)) 
      .HasOne(typeof(UserModel), "User2") 
      .WithMany() 
      .HasForeignKey("User2Id").OnDelete(DeleteBehavior.Restrict); 
    }

6. Now, you need to fix the unit tests. You may have already noticed that the unit test project doesn't build anymore if you try compiling the solution. Here, you need to update the unit test, since UserService now requires an instance of DbContextOptions, as follows:

    var dbContextOptionsBuilder =
      new DbContextOptionsBuilder<GameDbContext>()
      .UseSqlServer(@"Server=
        (localdb)MSSQLLocalDB;Database=TicTacToe;
       Trusted_Connection=True;MultipleActiveResultSets=true");

    var userService = new 
      UserService(dbContextOptionsBuilder.Options); 

Now, the unit tests cater for a constructor of UserService with the new overload of the options builder. Now that we have defined our primary and foreign keys in our models, we can create our initial database schema and prepare our application for migration. In the next section, we look at EF Core 3 migrations.