Database First Approach in Entity Framework Core: A Step-by-Step Guide

Introduction:

In this blog, I'll walk you through the process of setting up an Entity Framework Core project with a focus on Microsoft SQL Server as the database provider.

Setting Up the Database:

To begin, we created two tables, namely "Students" and "Teachers," in the "SampleDB" database. This forms the foundation for our data model.

Creating the MVC Project:

Next, we initiated a new MVC project using Visual Studio.

Installing Necessary Packages:

In the Package Manager Console, execute the following commands to install the essential packages for Entity Framework Core:

Install-Package Microsoft.EntityFrameworkCore.SqlServer

Install-Package Microsoft.EntityFrameworkCore.SqlServer.Design

Install-Package Microsoft.EntityFrameworkCore.Tools

Microsoft.EntityFrameworkCore.SqlServer: Installs the required package to enable Entity Framework Core with Microsoft SQL Server as the database provider. Microsoft.EntityFrameworkCore.SqlServer.Design: Includes design-time components important for development. Microsoft.EntityFrameworkCore.Tools: Provides a set of Entity Framework Core tools for tasks like database migrations and code generation.

Scaffolding the DbContext:

Now, run the following command in the Package Manager Console to generate C# code for the data model based on the existing database schema:

Scaffold-DbContext "Server=(local)\SQLEXPRESS;Database=SampleDB;Trusted_Connection=True;TrustServerCertificate=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

Understanding the Command:

Connection String: Specifies the server's name, database name, and authentication method. In our case, it uses Windows authentication and trusts the server certificate. Provider (Microsoft.EntityFrameworkCore.SqlServer): Indicates the database provider, specifying that the database is Microsoft SQL Server. Output Directory (-OutputDir Models): Sets the directory where the generated code will be placed. In this example, it's the "Models" directory.

Reviewing the Results:

After executing the command, you'll find the following files under the "Models" folder:

SampleDbContext.cs

Student.cs

Teacher.cs

Displaying Student Information in an MVC View: A Simple Guide

Now that we've successfully set up the Database First Approach in Entity Framework, it's time to visualize the student information in our MVC view. In this section, I'll guide you through displaying student details on a webpage.

Updating HomeController.cs:

To begin, let's add the following code to your HomeController.cs file:

            private SampleDbContext dbContext = new SampleDbContext(); 
        
public IActionResult Index() 
            { 

            List<Student> students = dbContext.Students.ToList(); 
        
            return View(students); 
        
            }

This code initializes a SampleDbContext and retrieves a list of students from the database, passing it to the Index view.

Creating the Index View (index.cshtml):

Next, open your Index view (index.cshtml) and replace its content with the following code:

@model List<Student> 
<!DOCTYPE html> 
<html> 
<head> 
    <title>Students List</title> 
</head> 
<body> 
    <h2>Students List</h2>   
    <table border="1"> 
        <tr> 
            <th>Student ID</th> 
            <th>First Name</th> 
            <th>Last Name</th> 
            <th>Date of Birth</th> 
            <th>Address</th> 
            <th>Contact Number</th> 
            <th>Email</th> 
        </tr> 
        @foreach (var student in Model) 
        { 
            <tr> 
                <td>@student.StudentId</td> 
                <td>@student.FirstName</td> 
                <td>@student.LastName</td> 
                <td>@student.DateOfBirth?.ToShortDateString()</td> 
                <td>@student.Address</td> 
                <td>@student.ContactNumber</td> 
                <td>@student.Email</td> 
            </tr> 
        } 
    </table> 
</body> 
</html> 

This HTML code utilizes the Razor syntax to iterate through the list of students and display their information in a table.

Reviewing the Output:

The output of the above code will be a clean and organized table showcasing the student details, as demonstrated in the accompanying image.

With these simple additions to your HomeController.cs and Index view, you've successfully implemented the display of student information in your MVC application.

Enhancing Database Connectivity: Adding Connection Strings and Implementing Dependency Injection

Now lets explore how to streamline your connection string management and implement dependency injection for improved database access. These enhancements aim to make your application more modular, scalable, and maintainable.

Updating appsettings.json:

Open your appsettings.json file and remove the OnConfiguring method from the SampleDbContext.cs file. Instead, add the following code to include the connection string

        {
        "Logging": {
        "LogLevel": {
        "Default": "Information",
        "Microsoft.AspNetCore": "Warning"
        },
        "AllowedHosts": "*",
        "ConnectionStrings": {
        "SampleDBConnectionString": "Server=(local)\\SQLEXPRESS;Database=SampleDB;Trusted_Connection=True;TrustServerCertificate=True;"
        }
        }
        }

This structure allows for centralized management of connection strings within your application configuration.

updating program.cs:

In your program.cs file, add the following code snippets

builder.Services.AddDbContext<SampleDbContext>(options => 
{   options.UseSqlServer(builder.Configuration.GetConnectionString("SampleDBConnectionString")); 
}); 

This code registers and configures the SampleDbContext with the ASP.NET Core dependency injection system, specifying Microsoft SQL Server as the database provider. The connection string is retrieved from the application's configuration.

Implementing Dependency Injection:

In your HomeController.cs file, modify the constructor and Index method as follows:

private readonly SampleDbContext _context; 

 public HomeController(SampleDbContext context) 
{ 
    _context = context; 
} 

 public IActionResult Index() 
{ 
    List<Student> students = _context.Students.ToList(); 
    return View(students); 
} 

By utilizing dependency injection, we inject the SampleDbContext into the HomeController, making it cleaner and more testable. Now, the student information is fetched seamlessly, enhancing the overall efficiency and maintainability of your application.

These enhancements not only optimize the organization of your application but also contribute to a more modular and maintainable codebase.

Adding a New Column to Your Entity Framework Model

In the continuous evolution of your Entity Framework model, the need to incorporate new data fields often arises. In this segment, we'll explore the process of seamlessly adding a new column to an existing database table and updating your Entity Framework model accordingly.

Altering the Database Table:

To initiate this process, let's assume we want to add a new column named "GradeLevel" to the "Students" table. Execute the following SQL command to alter the table structure:

 ALTER TABLE Students ADD GradeLevel INT; 

This simple SQL statement appends the new column to the "Students" table.

Running the Scaffold-DbContext Command:

Next, navigate to the NuGet Package Manager Console and run the following command. Note the addition of the -Force attribute, an important distinction from previous commands, as it ensures the overwriting of the existing DbContext:

 

Scaffold-DbContext "Server=(local)\SQLEXPRESS;Database=SampleDB;Trusted_Connection=True;TrustServerCertificate=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models –Force 

This command dynamically updates the Student.cs file, adding the new "GradeLevel" property to reflect the changes in the database.

Reviewing the Modified Student.cs File:

After executing the command, examine the modified Student.cs file. The new structure includes the added GradeLevel property:

public partial class Student 
{ 
    public int StudentId { get; set; } 
    public string? FirstName { get; set; } 
    public string? LastName { get; set; } 
    public DateOnly? DateOfBirth { get; set; } 
    public string? Address { get; set; } 
    public string? ContactNumber { get; set; } 
    public string? Email { get; set; } 
    public int? GradeLevel { get; set; } 
}

Removing OnConfiguring Method (Note):

It's important to note that whenever the Scaffold-DbContext command is executed with the -Force attribute, it automatically adds the OnConfiguring method with the connection string. It's advisable to remove this method manually to maintain a clean and organized codebase.

By seamlessly integrating new columns into your Entity Framework model, you ensure that your application evolves with the changing requirements of your data.

Exploring Entity Framework Core Commands: A Quick Reference Guide

In this segment, we'll explore how to access detailed information about Entity Framework Core commands using the NuGet Package Manager Console.

Accessing General Information:

To begin, if you seek comprehensive details about the Entity Framework Core Package Manager Console Tools, execute the following command in the NuGet Package Manager Console:

 
  Get-Help about_EntityFrameworkCore 
          

This command provides a wealth of information regarding the Entity Framework Core Package Manager Console Tools, offering insights into its functionalities and use cases.

Exploring Specific Commands:

If you want to learn more about a particular command, like scaffold-DbContext, follow these steps in the NuGet Package Manager Console:

 
 Get-Help scaffold-DbContext 
            

This command drills down into the specifics of the scaffold-DbContext command, offering detailed explanations of its parameters, use cases, and options.

Conclusion:

I hope that this blog has provided valuable insights into navigating the Entity Framework Core commands. Should you have any queries or require further clarification on specific commands, feel free to leave your questions in the comments below. Happy coding!

Search This Blog

Code Murals