Creating EF6 Database Migration In Separate .Net Core Library

Introduction

 

Hii guys! In this article, we will discuss creating a data layered architecture pattern in asp.net core 3.1. Here, we will make a separate data layer library(.Net Core) for database connection and tables. And then add this library to our front MVC core website for dealing with the database.

 

As we all know that keeping our database separate from our website is a good practice and part of the onion pattern principle, so we are just trying to follow this rule. Here we will use EF6 Database Migrations tools for DbConnection and a few packages from manage nu get package manager.

 

So let’s see how we can implement this process.

 

Step 1

 

Create a new project in Visual Studio 2019> select Asp.Net Core Web Application>Provide the WebApplication Name (DemoDBMigration) and then select >create

During the creation of the project, please make sure that you have changed the authentication from "No Authentication" to "Individual User Accounts" for enabling migration in our project. 

 

 

Step 2

 

Now right click on solution explorer> go to the add New items>select Class library (.Net Core)>Next>Give it Name (DataLayer)>create

 

 

Step 3

 

Now for working with migration, we have to install a few Entity Framework Core packages from Manage Neu get Packages in the DataLayer library project.

 

Select DataLayer Project and go to the Manage Nuget Packages and install the below packages.

Microsoft.AspNetCore.Identity(2.2.0)
Microsoft.AspNetCore.Identity.EntityFrameworkCore(3.1.5)
Microsoft. EntityFrameworkCore(3.1.5)
Microsoft. EntityFrameworkCore.Design(3.1.5)
Microsoft. EntityFrameworkCore.SqlServer(3.1.5)
Microsoft. EntityFrameworkCore.Tools(3.1.5)

 

Step 4

 

Now create a folder named Models. In this folder, we will make all our database tables and ApplicationDbContext Class

 

Now before enabling migration in the DataLayer project, we have to set up the database connection string first. For doing this, we will add the DataLayer Project reference to the “DemoDBMigration “web project.

 

Setup a connection string in the appsettings.json (DemoDBMigration web project) file like this.

"ConnectionStrings": {
    "DefaultConnection": "Data Source=MYSQLSERVERNAME;Initial Catalog=db_DBMigration;User Id=MYUSERID; Password=MYPASSWORD"
  },

And register the ApplicationDbContext Class in Startup.cs under the ConfigureServices method.

 

Startup.cs

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();
       }

Step 5

 

After setting up the proper connection string now, we are ready to enable migration in our DataLayer project.

 

Note

Don’t forget to create a blank database first to the SQL server. And delete a Data folder from the Web Project (DemoDBMigration project) if it already exists, otherwise, it throws an error of “No 2 DbContext class can create migration”.

 

Create an ApplicationDbContext.cs in Models folder. 

using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DataLayer
{
    public class ApplicationDbContext : IdentityDbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
          : base(options)
        { }
        public DbSet<Category> Category { get; set; }
        public IConfiguration Configuration { get; }
    }
}

Category.cs

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

namespace DataLayer
{
    public class Category
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Name { get; set; }
        public int DisplayOrder { get; set; }
        public bool IsActive { get; set; }
    }
}

Go to View menus> Other Windows>select Package Manager Console

 

Select DataLayer  project as Default Project over the Package Manager Console.  And for adding migration write the following command here.

PM> add-migration initial

Look that the project Migration folder is created automatically.

 

Now for moving identity tables and our table into the database let's follow the below command.

PM> update-database

 

Now check the Database, all the tables with the identity tables are auto-created.

 

 

Step 6

 

We can access it by injecting ApplicationDBContext class in the constructor of the controller.

public class AccountController : Controller
   {
       private readonly ApplicationDbContext db;
       public AccountController(ApplicationDbContext db)
       {
           this.db = db;
       }
       public IActionResult Index()
       {
           var categories = db.Category.ToList();
           return View();
       }
   }

OutPut

 

 

Summary

 

In this article, I have discussed how we can create a layered architecture. We also saw how to make a separate library for database and how to enable migration with all identity classes. Furthermore, we saw how to generate a database from migration. At last, we saw how to use access database tables over the web layer.