Manage Primary Key And Foreign Key Relationship During Migration

Introduction

 

In this article, we will learn how to create models for database table migration. When we build a simple database for our project, then it is easy to build class models. But when we develop a complex database for our project, then it's hard to maintain PK & FK relationship over the model. It is not easy to maintain, the string length and datatypes over the model.

 

So, let's see a few points while creating a model class.

 

Okay, here are the three tables, names - UserDetails, Customer, and Order. Here, we want to create the ID column as the primary key and a foreign key to the UserId column with the AspNetUser table. Like in the database here, also we want to restrict columns with string length. And also want to create two primary keys in a single model.

like below,

 

   

 

 

 

Here, Firstly I am going to create the UserDetails table. In which few properties and a foreign key relationship exit with the AspnetUser table.

public class UserDetails {
    [Key]
    public int Id {
        get;
        set;
    }
    [StringLength(100)]
    [Column(TypeName = "varchar(100)")]
    public string Name {
        get;
        set;
    }
    [StringLength(50)]
    [Column(TypeName = "varchar(50)")]
    public string Email {
        get;
        set;
    }
    [StringLength(1)]
    [Column(TypeName = "char(1)")]
    public string Gender {
        get;
        set;
    }
    public string ProfilePic {
        get;
        set;
    }
    [ForeignKey("IdentityUser")]
    public string UserId {
        get;
        set;
    }
    public virtual IdentityUser IdentityUser {
        get;
        set;
    }
    public byte Role {
        get;
        set;
    }
    public DateTime ? CreatedOn {
        get;
        set;
    }
    public bool ? IsActive {
        get;
        set;
    }
}

Here are a few packages for model attribute properties, which we will use to decorate class properties.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

In this model, we are making a Foreign Key relationship with IdentityUser class, which is available in the database with the name AspnetUsers.

using Microsoft.AspNetCore.Identity;

If we want to set the string length of 100/50 or something else varchar characters in the database of the Name property, then we define attribute like this,

[StringLength(100)]
[Column(TypeName = "varchar(100)")]
public string Name { get; set; }

If we desire to set the string length of 1 character only in the database of the Gender property, then we define attribute like this,

[StringLength(1)]
[Column(TypeName = "char(1)")]
public string Gender { get; set; }

If we desire to set the DateTime datatype with nullable constraint in the database of the CreatedOn property, then we define attribute like this,

public DateTime? CreatedOn { get; set; }

If we desire to make a foreign Key relationship with IdentityUser and store the IdentityUser table Id in the UserId column, then we define attribute like this,

[ForeignKey("IdentityUser")]
public string UserId { get; set; }
public virtual IdentityUser IdentityUser { get; set; }

Now, we will create the customer table. Which contains FullName, Description & Address properties, and the one foreign Key relationship with UserDetails table in the UserDetailsId column.

public class Customer {
    [Key]
    public int Id {
        get;
        set;
    }
    [StringLength(100)]
    [Column(TypeName = "varchar(100)")]
    public string FullName {
        get;
        set;
    }
    [StringLength(500)]
    [Column(TypeName = "varchar(500)")]
    public string Description {
        get;
        set;
    }
    [StringLength(500)]
    [Column(TypeName = "varchar(500)")]
    public string Address {
        get;
        set;
    }
    [ForeignKey("UserDetails")]
    public virtual int UserDetailsId {
        get;
        set;
    }
    public virtual UserDetails UserDetails {
        get;
        set;
    }
}

If we wish to create two foreign Keys in a single table like UserDetailsId, CustomerId in the Order table, then we write two properties for a single foreign key like this,

public class Order
   {
       [Key]
       public int Id { get; set; }

       [StringLength(100)]
       [Column(TypeName = "varchar(100)")]
       public string OrderNumber { get; set; }

       [ForeignKey("Customer")]
       public virtual int CustomerId { get; set; }
       public virtual Customer Customer { get; set; }

       [ForeignKey("UserDetails")]
       public virtual int UserDetailsId { get; set; }
       public virtual UserDetails UserDetails { get; set; }
   }

Register all tables in ApplicationDbContext class set the proper connection string in appsettings.json, register properly this class to Startup.cs class, and then run migration by migration command.

 

ApplicationDbContex.cs class

public class ApplicationDbContext: IdentityDbContext {
    public ApplicationDbContext(DbContextOptions < ApplicationDbContext > options): base(options) {}
    public DbSet < UserDetails > UserDetails {
        get;
        set;
    }
    public DbSet < Customer > Customer {
        get;
        set;
    }
    public DbSet < Order > Order {
        get;
        set;
    }
}

appsettings.json class

"ConnectionStrings": {
   "DefaultConnection": "Server=MyServer;Database=db_A;user=sa;password=admin@1234;Trusted_Connection=False;"
 },

Startup.cs class 

public void ConfigureServices(IServiceCollection services) {
    services.AddDbContext < ApplicationDbContext > (options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
    services.AddDefaultIdentity < IdentityUser > (options => options.SignIn.RequireConfirmedAccount = true).AddEntityFrameworkStores < ApplicationDbContext > ();
    services.AddControllersWithViews();
    services.AddRazorPages();
}

Summary

 

In this article, we learned the concepts of creating model classes with the Pk & Fk relationship. Apart from this, we saw to maintain data types and data length with a class decorative attribute.