Entity Framework Core Database First Approach

Back to: ASP.NET Core Tutorials For Beginners and Professionals

Entity Framework Core Database First Approach

In this article, I will discuss the Entity Framework Core (EF Core) Database First Approach with Examples. Please read our previous article discussing Global Query Filters in Entity Framework Core with Examples.

Entity Framework Core Database First Approach

Entity Framework Core (EF Core) is a popular Object-Relational Mapping (ORM) framework for .NET applications. It enables developers to work with a database using .NET objects, eliminating the need for most of the data-access code developers usually need to write. EF Core supports two primary ways of developing a data access layer: Database-First and Code-First. Here, we’ll focus on the Database-First approach.

The Entity Framework Core (EF Core) Database First approach is a technique where we start with an existing database and use EF Core to create the data access layer of our application. This approach is useful when working with a pre-existing database or when a database administrator or a separate team leads the database design.

Steps to Implement EF Core Database-First Approach:

• Existing Database: You need an existing database to begin with. The database can be designed and managed using any compatible database management system like SQL Server, MySQL, Oracle, etc.
• Install EF Core Tools: Ensure you have the EF Core CLI tools or the EF Core Power Tools (for Visual Studio) installed. These tools are essential for generating the code from the database.
• Create a .NET Core Project: Start by creating a .NET Core project in which you want to use EF Core. This could be any .NET Core project, like an ASP.NET Core web application or a Console Application.
• Install EF Core NuGet Packages: Install the necessary EF Core NuGet packages required for EF Core. You would typically need Microsoft.EntityFrameworkCore and provider-specific packages (like Microsoft.EntityFrameworkCore.SqlServer for SQL Server).
• Generate the Models: Use the EF Core CLI or Power Tools to scaffold the DbContext and Entity Classes from your existing database. This is done using the Scaffold-DbContext command. This command generates C# code that represents the tables in your database as classes and properties.
• Use the DbContext: Once the DbContext and entities are scaffolded, you can use them in your application to perform CRUD operations on the database.
• Handling Database Changes: If the database schema changes, you need to re-run the scaffolding command to update your models. Be aware that this may overwrite any customizations you have made to the models.
• Migrations: Unlike the Code First approach, in Database First, migrations are usually handled directly in the database using SQL scripts or other database management tools rather than through EF Core.
• Testing and Deployment: Finally, test your application to ensure it works correctly with the database, then deploy it.

Example to Understand EF Core Database First Approach:

Let us see an Example to Understand the EF Core Database First Approach with Multiple Tables, Views, Stored Procedures, and Functions. Let us create a database in SQL Server with the following:

• Database: EFCoreDB:
• Table: Employees: This table should contain employee-related information.
• Table: Departments: This table should store department details.
• Views: EmployeeDetails: A view to aggregate and present detailed employee information.
• Stored Procedure: GetEmployeeById: Fetching employee data by their ID.
• Function: CalculateBonus: A function to calculate bonuses for employees.

So, please execute the following SQL Script to Create the data and required database tables, views, stored procedures, and stored functions:

CREATE DATABASE EFCoreDB;
USE EFCoreDB;

-- Create Departments Database Table
-- This table will store department details. 
-- It has an ID as the primary key and a name for the department.
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY IDENTITY,
    DepartmentName NVARCHAR(100) NOT NULL
);
GO

-- Create Employees Database Table
-- This table will store employee details. 
-- It includes an ID as the primary key, personal details like name and email, 
-- and a foreign key to the Departments table.
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100),
    Salary INT,
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
GO

-- Create EmployeeDetails View
-- Joins data from the Employees and Departments tables to EmployeeDetails View
CREATE VIEW EmployeeDetails AS
SELECT 
    e.EmployeeID,
    e.FirstName,
    e.LastName,
    e.Email,
    e.Salary,
    d.DepartmentName
FROM Employees e
INNER JOIN Departments d 
ON e.DepartmentID = d.DepartmentID;
GO

-- Create GetEmployeeById Stored Procedure
-- This will return the Employee Details based on the EmployeeID
CREATE PROCEDURE GetEmployeeById
    @EmployeeID INT
AS
BEGIN
    SELECT 
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.Email,
        e.Salary,
        e.DepartmentID,
        d.DepartmentName
    FROM Employees e
    INNER JOIN Departments d 
 ON e.DepartmentID = d.DepartmentID
    WHERE e.EmployeeID = @EmployeeID;
END;
GO

-- Create CalculateBonus Stored Function
-- This will calculate and return the Bonus of the Employee Based on the EmployeeID
CREATE FUNCTION CalculateBonus 
(
    @EmployeeID INT
)
RETURNS DECIMAL(12, 2)
AS
BEGIN
    DECLARE @Salary DECIMAL(12, 2);
    DECLARE @Bonus DECIMAL(12, 2);

    -- Assuming there is a Salary column in Employees table
    SELECT @Salary = Salary FROM Employees WHERE EmployeeID = @EmployeeID;

    -- Calculate the bonus which is 25% of the Salary
    SET @Bonus = @Salary * 25 / 100;

    RETURN @Bonus;
END;

At this point, your database structure should be as shown in the image below:

Creating a New Console Application:

You can use EF Core Database First Approach with any Dot Net Core Applications, including ASP.NET Core MVC, Web API, Console Application, etc. So, let us create a new Console Application. Once you create the Console Application, please add the following two packages from NuGet, which are required for EF Core.

• Microsoft.EntityFrameworkCore.SqlServer
• Microsoft.EntityFrameworkCore.Tools

Creating and implementing DB Context

Now, we will see how to create the Context and Entity classes from our existing EFCoreDB database in Entity Framework Core. Creating Context and Entity classes for an existing database is called Database-First Approach. Entity Framework Core does not support Visual Designer for DB Model and Wizard to create the entity and context classes similar to Entity Framework 6. Here, we need to use the Scaffold-DbContext command.

This Scaffold-DbContext command creates entity and context classes based on the schema of the existing database, and we need to do this using Package Manager Console (PMC) tools.

Understanding Scaffold-DbContext Command in EF Core:

The Scaffold-DbContext command in Entity Framework Core (EF Core) is a powerful feature used for reverse engineering. It essentially generates entity and context classes based on an existing database, allowing developers to work with a database using EF Core’s object-relational mapping capabilities.

Purpose: The primary purpose of Scaffold-DbContext is to create a set of classes that represent the tables and relationships in an existing database. This allows developers to interact with the database using strongly typed objects rather than writing raw SQL queries.

Usage: This command is used within the Package Manager Console in Visual Studio or via the .NET Core CLI (Command Line Interface). It requires a few parameters to be specified:

• Connection String: The database connection string tells EF Core how to connect to the database.
• Provider: Specifies the EF Core database provider to use. This is typically the NuGet package corresponding to the database used (e.g., Microsoft.EntityFrameworkCore.SqlServer for SQL Server).
• Options (Optional): Additional options include specifying specific tables to scaffold, the directory where the classes should be created, the context’s namespace, and whether to use data annotations or Fluent API for configuration.

Result: After running the command, EF Core generates several classes in your project:

• DbContext Class: This class derives from DbContext and includes properties (DbSets) for each table.
• Entity Classes: Each table in the database corresponds to an entity class with properties that map to the columns in the table.

Customization: The scaffolded classes can be modified to suit the needs of your application. However, it’s important to note that changes to these classes may be overwritten if you re-run the command.

Updating Models: If the database schema changes, the EF Core models need to be updated. This can be done by re-running Scaffold-DbContext with the -Force option to overwrite the existing models. However, as mentioned, this will overwrite any customizations.

Syntax: Scaffold-DbContext “Your_Connection_String” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables Employees, Departments -DataAnnotations

Example: Scaffold-Dbcontext “Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV; Database=EFCoreDB; Trusted_Connection=True; TrustServerCertificate=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

Now, let us proceed and execute the above Scaffold-DbContext Command using the Package Manager Console. Open the Package Manager Console, type Scaffold-Dbcontext “Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV; Database=EFCoreDB; Trusted_Connection=True; TrustServerCertificate=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models command, select the project where you want to execute this command, then press the enter button as shown in the below image.

Once you press the Enter button, if everything is provided correctly, you will get the following message.

The entity classes and DBContext class will be created in the Models folder, as shown in the image below.

Now, let us Proceed and see how we can perform the database CRUD Operations using the Entity Framework Core Database First Approach. Later, I will show how to use the Views, Stored Procedures, and Stored Functions.

CRUD Operations Using Database First Approach:

Modify the Main method of the Program class as follows. The following example performs the database CRUD Operations using the EF Core Database First Approach:

using EFCoreCodeFirstDemo.Models;
namespace EFCoreCodeFirstDemo
{
    public class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                //Create an Instance of DbContext
                EfcoreDbContext context = new EfcoreDbContext();

                //Adding Departments to the Departments database table
                //We are adding two Departments IT and HR
                Department ITDepartment = new()
                {
                    DepartmentName = "IT",
                };
                context.Add(ITDepartment);

                Department HRDepartment = new()
                {
                    DepartmentName = "HR",
                };
                context.Add(HRDepartment);
                context.SaveChanges();
                Console.WriteLine("\nIT and HR Departments Added...");

                //Create Operations
                //Adding Few Employees to the Employees Database Table
                var employee1 = new Employee { FirstName = "Pranaya", LastName = "Rout", Salary = 550000, Email = "A@Example.com", DepartmentId = ITDepartment.DepartmentId };
                context.Employees.Add(employee1);

                var employee2 = new Employee { FirstName = "Tarun", LastName = "Kumar", Salary = 650000, Email = "B@Example.com", DepartmentId = ITDepartment.DepartmentId };
                context.Employees.Add(employee2);

                var employee3 = new Employee { FirstName = "Hina", LastName = "Sharma", Salary = 750000, Email = "C@Example.com", DepartmentId = HRDepartment.DepartmentId };
                context.Employees.Add(employee3);
                context.SaveChanges();
                Console.WriteLine("\nEmployees Pranaya, Tarun and Hina Added...");
               
                //Read Operations
                //Display All the Employees Details
                Console.WriteLine("\nDisplaying All the Employees Details");
                var employees = context.Employees.ToList();
                foreach (var emp in employees)
                {
                    Console.WriteLine($"\tName: {emp.FirstName} {emp.LastName}, Salary: {emp.Salary}, Department: {emp.Department?.DepartmentName}");
                }

                //Update Operation
                //Update the Salary, Last name and Department of First Employee
                Console.WriteLine("\nUpdating Salary, Last name and Department of First Employee");
                employee1.LastName = "Parida";
                employee1.Salary = 900000;
                employee1.DepartmentId = HRDepartment.DepartmentId;
                context.SaveChanges();
                Console.WriteLine("After Updation");
                Console.WriteLine($"Name: {employee1.FirstName} {employee1.LastName}, Salary: {employee1.Salary}, Department: {employee1.Department?.DepartmentName}");

                //Delete Operation
                //Delete the Third Employee
                Console.WriteLine("\nRemoving Third Employee");
                context.Remove(employee3);
                context.SaveChanges();
                Console.WriteLine("After Remove, All Employees");
                employees = context.Employees.ToList();
                foreach (var emp in employees)
                {
                    Console.WriteLine($"\tName: {emp.FirstName} {emp.LastName}, Salary: {emp.Salary}, Department: {emp.Department?.DepartmentName}");
                }

                Console.Read();
            }

            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}

Output:

Now, let us proceed and see how we can use the Views, Stored Procedures, and Functions in Entity Framework Core Database First Approach:

Using Views and Stored Procedures in EF Core DB First Approach:

Please modify the Main Method of the Program class as follows: I am showing how to use the Stored procedure and views with Entity Framework Core Database First Approach using the FromSqlRaw method.

using EFCoreCodeFirstDemo.Models;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

namespace EFCoreCodeFirstDemo
{
    public class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                //Create an Instance of DbContext
                EfcoreDbContext context = new EfcoreDbContext();

                Console.WriteLine("Using View");
                var emp = context.EmployeeDetails.FirstOrDefault(emp => emp.EmployeeId == 1);
                Console.WriteLine($"\tName: {emp?.FirstName} {emp?.LastName}, Salary: {emp?.Salary}, Department: {emp?.DepartmentName}");

                //Executing Stored Procedure (GetEmployeeById):
                //As the Stored Procedure Returning an Emloyee Details, we can use FromSqlRaw Method
                Console.WriteLine("Using Stored Procedure");
                var employeeIdParameter = new SqlParameter("@EmployeeID", 1);
                var employee = context.Employees
                                       .FromSqlRaw("EXEC GetEmployeeById @EmployeeID", employeeIdParameter)
                                       .AsEnumerable()
                                       .FirstOrDefault();

                Console.WriteLine($"\tName: {employee?.FirstName} {employee?.LastName}, Salary: {employee?.Salary}, Department: {employee?.Department?.DepartmentName}");

                Console.Read();
            }

            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}

Note: The most important point you need to remember is while using the FromSqlRaw method, it will map the result to the specified Entity type, and it is mandatory to return the required columns in the Stored Procedures or from the Views. Otherwise, the mapping will not be done, and it will throw a Runtime Exception.

Calling Stored Function using EF Core:

Even though we are using a Database-First approach, we need to manually define the function in our DbContext class, as EF Core does not automatically import functions through the scaffolding process. So, add the following method in your DbContext that represents the scalar function. Use the DbFunction attribute to link it to the SQL function.

//Specify the database Function name and database schema
[DbFunction("CalculateBonus", Schema = "dbo")]
public static decimal CalculateBonus(int EmployeeID)
{
    // The actual implementation is in the SQL Server function.
    // This method is for EF Core to know how to call the function.
    throw new NotImplementedException();
}

Once you have defined the function in your DbContext class, you can call it like any other static method in your code. So, modify the Program class as follows:

using EFCoreCodeFirstDemo.Models;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

namespace EFCoreCodeFirstDemo
{
    public class Program
    {
        static async Task Main(string[] args)
        {
            try
            {
                //Don't call the CalculateBonus Method as follows
                //decimal bonus = EfcoreDbContext.CalculateBonus(EmployeeID);

                //Create an Instance of DbContext
                EfcoreDbContext context = new EfcoreDbContext();

                //Using CalculateBonus method inside a LINQ Query
                var employee = context.Employees
                .Where(x => x.EmployeeId == 1)
                .Select(d => new
                {
                    Name = d.FirstName + " "+ d.LastName,
                    Salary = d.Salary,
                    DepartmentName = d.Department.DepartmentName,
                    Bonus = EfcoreDbContext.CalculateBonus(d.EmployeeId)
                }).FirstOrDefault();

                Console.WriteLine($"Name: {employee?.Name}, Salary: {employee?.Salary}, Bonus: {employee?.Bonus}, Department: {employee?.DepartmentName}");

                
                Console.Read();
            }

            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}

In this article, I try to explain the Entity Framework Core (EF Core) Database First Approach with Examples. I hope you enjoy this Entity Framework Core Database First Approach article.