Exploring Database-First Approach with Entity Framework in .NET Core 6

Introduction: In the world of software development, the efficient management of databases is crucial for the success of any application.

Dotnet Core 6 provides powerful tools and features for working with databases. One popular approach is the database-first approach, which allows developers to design their database schema first and then generate the corresponding models and context using the Entity Framework. In this article, we will explore how to utilize the Entity Framework in a database-first manner using .NET Core 6.

Step 1: Generating the Database Script for Microsoft SQL Server

To demonstrate the database-first approach, let’s consider the example of an ecommerce application. The database for this application consists of the following tables:

• Products

• Customers

• Orders

• Order_Details

• Product_Categories

• Categories

-- Create Products table
    CREATE TABLE Products (
        ProductId INT PRIMARY KEY,
        Name NVARCHAR(100) NOT NULL,
        Price DECIMAL(10, 2) NOT NULL,
        Description NVARCHAR(MAX)
    );

    -- Create Customers table
    CREATE TABLE Customers (
        CustomerId INT PRIMARY KEY,
        FirstName NVARCHAR(50) NOT NULL,
        LastName NVARCHAR(50) NOT NULL,
        Email NVARCHAR(100) NOT NULL
    );

    -- Create Orders table
    CREATE TABLE Orders (
        OrderId INT PRIMARY KEY,
        CustomerId INT NOT NULL,
        OrderDate DATETIME NOT NULL,
        TotalAmount DECIMAL(10, 2) NOT NULL,
        FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
    );

    -- Create Order_Details table
    CREATE TABLE Order_Details (
        OrderId INT NOT NULL,
        ProductId INT NOT NULL,
        Quantity INT NOT NULL,
        Price DECIMAL(10, 2) NOT NULL,
        PRIMARY KEY (OrderId, ProductId),
        FOREIGN KEY (OrderId) REFERENCES Orders(OrderId),
        FOREIGN KEY (ProductId) REFERENCES Products(ProductId)
    );

    -- Create Categories table
    CREATE TABLE Categories (
        CategoryId INT PRIMARY KEY,
        Name NVARCHAR(50) NOT NULL
    );

    -- Create Product_Categories table
    CREATE TABLE Product_Categories (
        ProductId INT NOT NULL,
        CategoryId INT NOT NULL,
        PRIMARY KEY (ProductId, CategoryId),
        FOREIGN KEY (ProductId) REFERENCES Products(ProductId),
        FOREIGN KEY (CategoryId) REFERENCES Categories(CategoryId)
    );

Step 2: Create a new Visual Studio Project and install necessary packages

In Visual Studio Community 2022 create for example a Console Application, select .NET 6.0. Now install Entity Framework Packages, we need to install several packages using NuGet. Follow these steps to install the required packages in Visual Studio Community 2022:

1. Open your project in Visual Studio Community 2022.

2. Right-click on the project in the Solution Explorer and select “Manage NuGet Packages.”

3. In the NuGet Package Manager window, search for the following packages one by one:

• Microsoft.EntityFrameworkCore

• Microsoft.EntityFrameworkCore.Design

• Microsoft.EntityFrameworkCore.SqlServer

• Microsoft.EntityFrameworkCore.Tools

1. Select each package and click on the “Install” button to install them into your project.

Step 3: Generating Models and Context Using Scaffold-DbContext

Once the necessary packages are installed, we can generate the models and the database context using the Scaffold-DbContext command. This command helps in automatically scaffolding the entity types for the existing database schema.

1. Create the Models directory, you can choose any name of course

2. Open the Package Manager Console by navigating to “Tools” -> “NuGet Package Manager” -> “Package Manager Console.”

3. In the Package Manager Console, run the following command:

   PM> Scaffold-DbContext "Data Source=localhost\SQLEXPRESS;Initial Catalog=ecommerce;Integrated Security=True;TrustServerCertificate=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

The Scaffold-DbContext command will examine the database schema and generate the corresponding model classes and the database context. These generated classes will be placed in the specified output directory (Models in this case).

Conclusion

By following the above steps, we have explored the database-first approach using Entity Framework in .NET Core 6. We started by creating a database script for an ecommerce application and defined the necessary relationships. Then, we installed the required Entity Framework packages using NuGet in Visual Studio Community 2022. Finally, we utilized the Scaffold-DbContext command to automatically generate the model classes and the database context based on the existing database schema.

Utilizing the Entity Framework in a database-first manner allows developers to seamlessly integrate their existing databases with their .NET Core applications. This approach simplifies the development process by eliminating the need for manual creation of models and context classes, saving time and effort. With .NET Core 6 and Entity Framework, developers can build robust and scalable applications with ease.

Thank you!