Entity Framework Code First Custom Database Initialization and Check if Table Already Exists

In one of our Project we had to use existing database and we wanted to use the code first entity framework as well.

There was requirement to make sure that some of the tables are already existed and all the new ones will be created using the code first approach.

Therefore, I decided to customized the MigrateDatabaseToLatestVersion.

First we will verify that the prerequisite tables are already there and then based on the auto update app setting we will migrate database to latest version.

GiftVoucherDatabaseInitialization 

public class GiftVoucherDatabaseInitialization : MigrateDatabaseToLatestVersion<GiftVoucherEntityTypesConfigurationDbContext, Migrations.Configuration>
    {
        private string IsCodeFirstAutoMigrationEnabled = "IsCodeFirstAutoMigrationEnabled";

        public GiftVoucherDatabaseInitialization(string connectionStringName) : base(connectionStringName)
        {
        }

        public override void InitializeDatabase(GiftVoucherEntityTypesConfigurationDbContext context)
        {
            var queryString = @"
                         SELECT 1 FROM sys.tables AS T
                         INNER JOIN sys.schemas AS S ON T.schema_id = S.schema_id
                         WHERE S.Name = 'dbo' AND T.Name = '{0}'";
            if (context.Database.Exists())
            {
                bool exists = context.Database
                      .SqlQuery<int?>(string.Format(queryString, "tbl_PurchaseVoucher"))
                      .SingleOrDefault() != null;

                if(!exists)
                    throw new ContentNotFoundException("Business Foundataion Entity{PurchaseVoucher} Not Found");

                exists = context.Database
                      .SqlQuery<int?>(string.Format(queryString, "tbl_PurchaseVoucherRedemption"))
                      .SingleOrDefault() != null;

                if (!exists)
                    throw new ContentNotFoundException("Business Foundation Entity{PurchaseVoucherRedemption} Not Found");
            }

            if (IsMigrationEnabled())
            {
                base.InitializeDatabase(context);
            }
        }

        private bool IsMigrationEnabled()
        {
            var migration = ConfigurationManager.AppSettings[IsCodeFirstAutoMigrationEnabled];
            return migration != null && migration.ToLower().Equals("true");
        }
    }

In Global.asax file, we will set the database initializer for above context

Setting Up Database Initializer

        protected void Application_Start()
        {
            // Add the database migration strategy to update the database on app start
            Database.SetInitializer(new GiftVoucherDatabaseInitialization(ConnectionStringConfig.CommerceConnectionStringName));

            // Run the initializer, but don't force
            new GiftVoucherEntityTypesConfigurationDbContext().Database.Initialize(true);
        }

GiftVoucherEntityTypesConfigurationDbContext

The GiftVoucherEntityTypesConfigurationDbContext looks like below

public class GiftVoucherEntityTypesConfigurationDbContext : DbContext
    {
        private readonly ICustomerContextFacade _customerContextFacade;

        public GiftVoucherEntityTypesConfigurationDbContext()
      : this("EcfSqlConnection", null)
     {
      
     }

        public GiftVoucherEntityTypesConfigurationDbContext(string nameOrConnectionString)
            : base(nameOrConnectionString)
        {
            
        }

        public DbSet<PurchaseVoucher> PurchaseVouchers { get; set; }
  public DbSet<PurchaseVoucherRedemption> PurchaseVoucherRedemptions { get; set; }

  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
   // Dynamically load all configuration
   var typesToRegister = Assembly.GetExecutingAssembly().GetTypes()
                .Where(type => !string.IsNullOrEmpty(type.Namespace))
                .Where(type => type.BaseType != null && type.BaseType.IsGenericType && type.BaseType.GetGenericTypeDefinition() == typeof(EntityTypeConfiguration<>));
            foreach (var type in typesToRegister)
            {
                dynamic configurationInstance = Activator.CreateInstance(type);
                modelBuilder.Configurations.Add(configurationInstance);
            }
            
            base.OnModelCreating(modelBuilder);
        }
     }