Entity Framework: Code first

By using Code-First apporch, we can target a database that doesn’t exist and Code First will create, or an empty database that Code First will add new tables too. Code First allows you to define your model using C# or VB.Net classes. Additional configuration can optionally be performed using attributes on your classes and properties or by using a fluent API or Data Annotations.

Here is a walk through …

  • Open Visual Studio and create new Class Library by File -> New -> Project…
  • Enter SoftSchool as the name and click OK
  • Add EntityFramework reference from NuGet packages
  • It will add all required EF references to your project
  • Now add two folders Model & Configuration and Config file ..

First of all we will create a base class, that will have common fields and will be used in almost every model class, it will be abstract so can be used by inheriting only …

  1. [Key]: will define primary key in DB
  2. [DatabaseGenerated(DatabaseGeneratedOption.Identity)]: will set auto-generate in DB
  3. public bool Status { get; set; } = true; its will set default status as true is not provided
  4. public DateTime? CreatedOn { get; set; } = DateTime.Now;: Here ‘?’ will allow DB to store null value and ‘= DateTime.Now’ can set default date
  5. To set Foreign Key we need to set it in below way
    1. [ForeignKey(“CreatedBy”)] will point to public Person CreatedBy { get; set; }
    2. public int? CreatedById { get; set; } will create ForeignKey and relate it to ‘Person’ table and we mentioned in 5.1
    3. ‘?’ will allow to keep FK as null value
    4. [Display(Name = “Created By”)] is a data annotation, will hide actual column name and overwrite it by provided name in MVC code ..
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace SoftSchool.Model
{
    public abstract class baseFields
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        public bool Status { get; set; } = true;

        [Display(Name = "Created On")]
        public DateTime? CreatedOn { get; set; } = DateTime.Now;

        [Display(Name = "Modified On")]
        public DateTime? ModifiedOn { get; set; }

        [ForeignKey("CreatedBy")]
        [Display(Name = "Created By")]
        public int? CreatedById { get; set; }
        public Person CreatedBy { get; set; }

        [ForeignKey("ModifiedBy")]
        [Display(Name = "Created By")]
        public int? ModifiedById { get; set; }
        public Person ModifiedBy { get; set; }

    }
}

Right click on the Model folder and add a class named ‘Nation’

  1. We need below name spaces for this ..
    1. using System.ComponentModel.DataAnnotations;
    2. using System.ComponentModel.DataAnnotations.Schema;
  2. [Table(“Nation”)]: will be used to name Table in DB while create it in SQL
  3. [Required] and [Display(Name = “Nation Code”)] and data annotation
  4. public virtual ICollection…. {get; set;} properties are navigation properties, while DB creation these properties will be ignored ..
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace SoftSchool.Model
{
    [Table("Nation")]
    public partial class Nation : baseFields
    {
        [Required]
        [Display(Name = "Nation Name")]
        public string NationName { get; set; }

        [Required]
        [Display(Name = "Nation Code")]
        public string NationCode { get; set; }

        public virtual ICollection<Address> Addresses { get; set; }
        public virtual ICollection<State> States { get; set; }
    }
}

Now, right click on configuration folder and and add a class named ‘NationConfig’

  1. .HasColumnOrder(1); will decide order in DB
  2. .IsUnicode(true) will keep a unique key
  3. .IsRequired() will set required level
  4. .HasMaxLength(50); will decide max length for the column string
using System.Data.Entity.ModelConfiguration;

namespace SoftSchool.Model.Configuration
{
    public class NationConfig: EntityTypeConfiguration<Nation>
    {
        public NationConfig()
        {
            ToTable("Nation");

            Property(n => n.Id)
                .HasColumnOrder(1);
            
            Property(n => n.NationName)
                .IsUnicode(true)
                .IsRequired()
                .HasMaxLength(50);

            Property(n => n.NationCode)
                .IsUnicode(true)
                .IsRequired()
                .HasMaxLength(3);
        }
    }
}

Create one more class in Model ‘Optionset’

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

namespace SoftSchool.Model
{
    [Table("Optionset")]
    public class Optionset : baseFields
    {
        [Required]
        [Display(Name = "Display Text")]
        public string Text { get; set; }

        public int? Value { get; set; }

        [Required]
        [Display(Name = "Option Set Name")]
        public string OptionSetFor { get; set; }

        [Display(Name = "Status")]
        public bool? IsActive { get; set; } = true;
    }

}

create one more class ‘Person’

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace SoftSchool.Model
{
    [Table("Person")]
    public partial class Person: baseFields
    {

        [Display(Name = "Full Name")]
        public string FullName { get; set; }

        [Display(Name ="Title")]
        [ForeignKey("OptionsetTitle")]
        public int? TitleOptionsId { get; set; }
        public Optionset OptionsetTitle { get; set; }

        [Display(Name ="Person Type")]
        [ForeignKey("OptionsetPersonOption")]
        public int PersonOptionId { get; set; }
        public Optionset OptionsetPersonOption { get; set; }

        [Display(Name ="First Name")]
        public string FirstName { get; set; }

        [Display(Name ="Middle Name")]
        public string MiddleName { get; set; }

        public string Surname { get; set; }

        [Display(Name ="Gender")]
        [ForeignKey("OptionsetGender")]
        public int? GenderOptionId { get; set; }
        public Optionset OptionsetGender { get; set; }

        [DataType(DataType.Date, ErrorMessage = "Not a valid date")]
        public DateTime Birthdate { get; set; }

        [Display (Name = "E-mail")]
        [DataType(DataType.EmailAddress, ErrorMessage = "Not a valid email address.")]
        public string Email { get; set; }

        [Display(Name= "Primary Contact No.")]
        [DataType(DataType.PhoneNumber, ErrorMessage = "Not a valid phone number")]
        public string PrimaryPhoneNo { get; set; }

        [Display(Name = "Secondary Contact No.")]
        [DataType(DataType.PhoneNumber, ErrorMessage = "Not a valid phone number")]
        public string SecondaryPhoneNo { get; set; }

        public virtual ICollection<Address> Addresses { get; set; }

        public virtual ICollection<Class> Classes { get; set; }


    }
}

Now create Db context class

  1. public SoftSchoolDbContext(): base(“name=SchoolDbContext”) {} is constructor and will use ‘SchoolDbContext ‘ as connection string from App.Config file
  2. protected override void OnModelCreating(DbModelBuilder modelBuilder) function will use all configuration class while creating DB
using SoftSchool.Model;
using System.Data.Entity;

namespace SoftSchool
{
    public class SoftSchoolDbContext: DbContext
    {   
        public virtual DbSet<Person> People { get; set; }
        public virtual DbSet<Nation> Nations { get; set; }
        
        public virtual DbSet<Optionset> Optionsets { get; set; }

        
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
                        modelBuilder.Configurations.AddFromAssembly(typeof(SoftSchoolDbContext).Assembly);
            
        }

        public SoftSchoolDbContext()
            : base("name=SchoolDbContext")
        {

        }
    }
}

Now, write connection string in app.config file, you can explore more on connection string here … https://www.connectionstrings.com/

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
  </configSections>
  <connectionStrings>
    <add name="SchoolDbContext" connectionString="data source=.;initial catalog=SoftSchoolDB;integrated security=True;multipleactiveresultsets=True;application name=EntityFramework" providerName="System.Data.SqlClient" />
  </connectionStrings>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
</configuration>

Now, go to your ‘ Package Manager Console ‘ and enable migrations … by ‘ Enable-Migrations ‘; when run this command, it will create one folder named ‘Migrations’ with some .CS files, it will store all you changes and history of DB creation and it updates

Now execute another command to crate migration file by ‘ Add-Migration MIGRATION_NAME‘, it will create you migration file with all details, you can see it in ‘Migrations’ folder

then execute DB creation / Update by ‘Update-Database’ command in Package Manager Console

… and that’s it, it will create you DB in SQL.

When you want to change / update your DB execute ‘ Add-Migration MIGRATION_NAME‘ commands and each time it will create Migration file and to Update DB execute ‘Update-Database’ command.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s