Explaining ASP.NET Core Database First Approach in Details

Introduction

The ASP.NET Core Database First Approach is a methodology in application development where the database is created and managed before building the application. This is in contrast to the Code First Approach, where the database schema is generated from the model classes in your code. The Database First Approach is particularly useful when you have an existing database or when the database schema is being managed by a separate team. In this guide, we will walk you through the process of implementing the Database First Approach in ASP.NET Core step-by-step, tailored for beginners.

Prerequisites

Before you start, make sure you have the following prerequisites set up on your development machine:

• .NET SDK: Ensure you have the .NET SDK installed. You can verify by running dotnet --version in the command prompt or terminal.
• Visual Studio or Visual Studio Code: While Visual Studio provides integrated tools, Visual Studio Code can be used with additional extensions.
• SQL Server or any relational database: This example will use SQL Server, but you can adapt it to other databases with Entity Framework Core providers.
• Entity Framework Core Tools: These tools are used to create and manage models from the database.

Step-by-Step Guide

Step 1: Create a New ASP.NET Core Project

1. Open Visual Studio or your preferred code editor.
2. Create a new project by selecting ASP.NET Core Web App (Model-View-Controller) if you are using Visual Studio. Name your project and ensure you select ASP.NET Core 5.0 or later.
3. Alternatively, create the project via command line using the command:

   dotnet new mvc -n MyDatabaseFirstApp
   cd MyDatabaseFirstApp
   

Step 2: Install Entity Framework Core Tools You need to install the Entity Framework Core Tools to enable database-first scaffolding.

1. Using NuGet Package Manager:

   • Open the NuGet Package Manager Console.
   • Run the command:

     Install-Package Microsoft.EntityFrameworkCore.Tools
     Install-Package Microsoft.EntityFrameworkCore.SqlServer
     

2. Using .NET CLI:

   • Run the following commands in your project directory:

     dotnet add package Microsoft.EntityFrameworkCore.Tools
     dotnet add package Microsoft.EntityFrameworkCore.SqlServer
     

Step 3: Create a Database For this example, we'll create a simple SQL Server database.

1. Open SQL Server Management Studio (SSMS).
2. Create a new query and execute the following SQL script to create a database and a table:

   CREATE DATABASE MySampleDatabase;
   
   USE MySampleDatabase;
   
   CREATE TABLE Students (
       StudentId INT PRIMARY KEY IDENTITY(1, 1),
       FirstName NVARCHAR(50),
       LastName NVARCHAR(50),
       Email NVARCHAR(100)
   );
   

Step 4: Scaffold the Database into Entity Framework Core Model Now, we'll use Entity Framework Core's scaffolding command to create model classes and a DbContext based on the existing database.

1. Using Package Manager Console:

   • In Visual Studio, open the Package Manager Console.
   • Run the following command:

     Scaffold-DbContext "Server=(localdb)\mssqllocaldb;Database=MySampleDatabase;Trusted_Connection=True;MultipleActiveResultSets=true" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Context MyDbContext
     

     Replace (localdb)\mssqllocaldb with the appropriate server name if you're using a different SQL Server instance.

2. Using .NET CLI:

   • Open the command line in your project directory and run:

     dotnet ef dbcontext scaffold "Server=(localdb)\mssqllocaldb;Database=MySampleDatabase;Trusted_Connection=True;MultipleActiveResultSets=true" Microsoft.EntityFrameworkCore.SqlServer -o Models -c MyDbContext
     

This command will generate the necessary model classes and a DbContext named MyDbContext in the Models folder.

Step 5: Update DbContext Configuration The DbContext class (MyDbContext in this case) needs to be configured to use your database. This is typically done in Startup.cs or Program.cs depending on your project setup.

1. Open appsettings.json and add your connection string:

   "ConnectionStrings": {
       "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=MySampleDatabase;Trusted_Connection=True;MultipleActiveResultSets=true"
   }
   

2. Modify the MyDbContext class to use the connection string from appsettings.json:

   using Microsoft.EntityFrameworkCore;
   using Microsoft.Extensions.Configuration;
   
   namespace MyDatabaseFirstApp.Models
   {
       public class MyDbContext : DbContext
       {
           public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
           {
           }
   
           public DbSet<Student> Students { get; set; }
   
           protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
           {
               if (!optionsBuilder.IsConfigured)
               {
                   IConfigurationRoot configuration = new ConfigurationBuilder()
                       .SetBasePath(AppContext.BaseDirectory)
                       .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
                       .Build();
   
                   optionsBuilder.UseSqlServer(configuration.GetConnectionString("DefaultConnection"));
               }
           }
       }
   }
   

Step 6: Register the DbContext in the Dependency Injection Container Register MyDbContext in the dependency injection container so it can be used throughout your application.

1. Open Startup.cs or Program.cs (for ASP.NET Core 6.0 and later).
2. In ConfigureServices method, add the following line:

   services.AddDbContext<MyDbContext>(options =>
       options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
   

Step 7: Create a Controller and Views Now that the DbContext and models are configured, you can create a controller to handle HTTP requests and views to render the data.

1. Right-click on the Controllers folder and select Add > Controller.
2. Choose MVC Controller with views, using Entity Framework and click Add.
3. Configure the controller:
   • Model class: Student
   • Data context class: MyDbContext
   • Views: Choose Razor Views (Razor Pages if you prefer).
4. Click Add.

Visual Studio will automatically scaffold a StudentsController and associated views (Index, Create, Edit, Details, Delete) in the Views/Students folder.

Step 8: Test the Application

1. Run your application by pressing F5 in Visual Studio or using the command line dotnet run.
2. Navigate to /Students to view, create, edit, and delete records in the Students table.

Summary

Here we've covered the essential steps to implement the Database First Approach in ASP.NET Core. Starting with setting up your project and installing necessary tools, creating a database, and scaffolding the database into Entity Framework Core models. We then configured the DbContext, registered it, created a controller, and views, and finally tested our application.

Using the Database First Approach can be very advantageous when working with pre-existing databases or when you want to maintain the database schema separately from your application code. This allows you to focus on your business logic and user interface while leveraging the robust data access capabilities provided by Entity Framework Core.

Feel free to modify and expand upon this example to suit your specific needs and continue learning more about ASP.NET Core and Entity Framework Core to build more complex and powerful applications.