In this article, we discuss how to create Microsoft SQL Server database through Code-First approach using Asp.Net MVC Core application and Entity Framework Core. Code-first means, creating database and it’s objects like tables from C# code. C# class is equivalent to database table and C# property is like a database column while working with Entity Framework.

Open Microsoft Visual Studio 2022, Create Asp.Net MVC Core application targeting .NET 6, name it as EntityFrameworkCoreExample. Add new Class library project (should target .NET 6) and name it as EntityFrameworkCoreExample.Data.

Right-click on EntityFrameworkCoreExample.Data project and select Manage NuGet Packages. Install Microsoft.EntityFrameworkCore, Microsoft.EntityFrameworkCore.Design, Microsoft.EntityFrameworkCore.SqlServer, and Microsoft.EntityFrameworkCore.Tools as shown below. Add these references for EntityFrameworkCore project also by following the same steps.

Add EntityFrameworkCoreExample.Data project reference to EntityFrameworkCoreExample. Add a new folder to EntityFrameworkCoreExample.Data project and name it as DatabaseModels. Add new classes Book and Author to this folder as shown below.

namespace EntityFrameworkCoreExample.Data.DatabaseModels
{
    publicclassBook
    {
        publicint BookId { get; set; }
 
        publicstring Title { get; set; }
    }
}
namespace EntityFrameworkCoreExample.Data.DatabaseModels
{
    publicclassAuthor
    {
        publicint AuthorId { get; set; }
 
        publicstring Name { get; set; }
    }
}

Now, add new folder Context and add new class AppDbContext to it as shown below.

using EntityFrameworkCoreExample.Data.DatabaseModels;
using Microsoft.EntityFrameworkCore;
 
namespace EntityFrameworkCoreExample.Data.Context
{
    publicclassAppDbContext : DbContext
    {
        publicAppDbContext(DbContextOptions<AppDbContext> options) : base(options)
        {
        }
 
        public DbSet<Book> Books { get; set; }
 
        public DbSet<Author> Authors { get; set; }
    }
}

As shown above AppDbContext inherits from DbContext and it is responsible for all database operations. For this class, we have added Books and Authors properties with DbSet. By adding DbSet<book>, and DbSet<Author> properties to AppDbConext, we are mentioning these are the database tables.

Let’s mention the database connection string in appsettings.json as shown below.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DBConnectionString": "Server=localhost;Database=Library;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

Here localhost is my local Microsoft SQL Server name with windows authentication.

Now we need to add AppDbConext to services as shown below in Program.cs.

using EntityFrameworkCoreExample.Data.Context;
using Microsoft.EntityFrameworkCore;
 
var builder = WebApplication.CreateBuilder(args);
 
// Add services to the container.
builder.Services.AddControllersWithViews();
 
var dbConnextionString = builder.Configuration.GetConnectionString("DBConnectionString");
builder.Services.AddDbContext<AppDbContext>(options => options.UseSqlServer(dbConnextionString));

As shown above, DBConnectionString is the database connection string mentioned in appsettings.json file.

Create the Migration script by going to Visual Studio Tools => NuGet Package Manager => Package Manager Console and run below command.

Add-Migration InitialMigration

Please select EntityFrameworkCoreExample.Data project as default project and run the command as shown below.

Now, you can find the Migrations folder in EntityFrameworkCoreExample.Data project as shown below.

These are the database migration scripts which we need to execute to create the database and corresponding tables.

To execute these migration scripts, we need to call dbContext.Database.Migrate(); in Program.cs file as shown below.

var app = builder.Build();
using (var scope = app.Services.CreateScope())
{
    var dbContext = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    dbContext.Database.Migrate();
}

As shown above, we are executing Database.Migrate() command on AppDbContext db context where we have our database information including tables.

Let’s run the application and check your database. You can see Library database get created with Books and Authors tables as shown below.

We can see Books and Authors tables created in database with Primary keys on BookId & AuthorId columns. This is because if any property name contains combination of class name and Id, Entity Core treats it as primary key column.

If you want to make any more changes to the database, just make code changes and run the Add-Migration command to create the database migration scripts. Then run the application, changes automatically applies to database.

                                                                                                                                                            Download Source Code