Code First vs. Database First...Choose Wisely

Introduction

Object-relational mapping (ORM)is a programming technique that allows database interaction using objects, mapping database models to application domain models. One ORM we have previously discussed is Entity Framework (EF). When starting, developers have two choices:

• Starting with the code

• Starting with the database

These dictate the application's architecture, influencing operations, modification and synchronisation between application model and database schema. The database schema is how data is organised and configured.

Code-First Approach

The code-first approach models the database using classes from your code, and EF will generate the database schema. This approach is suitable because :

• It avoids editing the database.

• It provides complete control over the code.

• The code automates database management.

• Version control of the database is integrated with the code.

However, it does come with its limitations, such as:

• It reduces database control, and complex queries cannot be performed.

• The generated database schema may not be optimised for specific database engines.

• The database changes are more challenging to keep track of.

• It becomes problematic to work with an existing database.

Setups for Demos

These demos will simply show the code-first and data-first approaches. The goal is to achieve a model and database schema that reflects the data attributes and relational data of the products and shopping carts.

1. Create an empty database in SSMS called EStore.

   

2. Create an empty ASP.NET project in VS Studio called EStore and install the following packages:

   • Microsoft.EntityFrameworkCore

   • Microsoft.EntityFrameworkCore.SqlServer

   • Microsoft.EntityFrameworkCore.Tools

3. Configure the app settings file by adding the connection string to your database.

4. Configure the Program class to add the DbContext and to search for the database.

Code-First Demo

1. Create a folder named Model and create three classes inside the Model:

   • ShoppingCart

   • Product

   • EStoreContext

2. The Product and ShoppingCart classes represent the tables in the database. Feel free to add as many attributes as you want. Context is used to interpret the structure and relationships of data models and maintain the database schema.

Product

ShoppingCart

Context

3. In the Package Manager Console, enter the commands in the following order:

   • Add-Migration Initial

   • Update-Database

Note: The initial is the name of the migration used to identify and keep track of changes. We will come back to this when modifying our existing database.

4. You will see a Migration folder has been created, and your database schema corresponds to the class models.

Product

ShoppingCart

Database-First Approach

The database-first approach creates the database schema, and EF will generate corresponding classes. This is appropriate when:

• It supports working with existing databases.

• Provides complete control over the database, allowing for complex queries.

• The database acts as a blueprint to structure the code.

• The changes are clearly distinguished between the database and the code, as version controls are separate.

However, there are drawbacks to this approach as well:

• The code is heavily dependent on the database.

• Agile approaches must divert time to the database, reducing rapid development.

• The database must be manually managed.

• A poorly designed database will lead to inherited flaws in the generated code.

Database-First Demo

1. Right-click on the empty on the empty database and select New Query. This is where we will create the database schema.

2. Execute the query, and you should receive a success message. Refresh the database, and the database schema has been created.

   

   Product

   

   ShoppingCart

3. Moving to VS Studio, begin by creating a new folder called database. Create a New Item and name it 01 - Initial Database Tables.sql. Copy the query from SSMS to the database file, which acts as version control for the database, independent of the code.

4. Enter the commands in the Package Manager Console to generate context and models:

   • Scaffold-DbContext "Server=.;Database=EStore;User Id=sa;Password=YourPassword;TrustServerCertificate=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

Context

Product

ShoppingCart

Modifying an Existing Database

Modifying an existing database will depend on the selected approach mentioned above. If the code-first approach was used, then class models must be updated, and the database will be updated using EF migrations. The database-first approach requires the database to be manually altered, and then EF is used to update the application models.

Code-First Modification Demo

Since we already have our database, we want to modify it by adding a description attribute to the product database table.

1. Begin by modifying the Product model in VS Studio. I have added the ProductDescription attribute to the Product class.

2. The context will also need to be modified to match the changes in the code so it can be reflected in the database schema.

   

3. In the Package Manager Console, enter the commands in the following order:

   • Add-Migration AddProductDescription

   • Update-Database

4. The Migration folder will be updated with the changes made, and as mentioned previously, it integrates version control of the code and database.

5. Right-click on the database to refresh and watch it update like magic.

Database-First Modification Demo

Using a database-first approach, let's add a description attribute to products to our existing database.

1. Right-click on the database and create a New Query. Execute the query, and if it is successful, refresh it to see the updated database.

2. The query must be taken to the code and documented, as shown below.

3. Enter the commands in the Package Manager Console to generate context and models:

   • Scaffold-DbContext "Server=.;Database=EStore;User Id=sa;Password=YourPassword;TrustServerCertificate=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Force

4. This will force override the existing data and update the context and models.

Context

Product

Summary

Before making a final verdict, let's revise the essential concepts presented. ORM is a powerful tool to bridge the gap between the relational database world and the object-oriented domain of an application. EF presents us with a choice between a database-first approach and a code-first approach. The database-first approach is where the database schema is designed first, and the corresponding data models are generated to fit this schema. The code-first approach defines the data models in code, and EF generates the database schema based on these models. Both have pros and cons, but the database stands out for its simplicity, efficiency, and ability to write complex queries. The database-first approach is my preferred choice because it acts as a blueprint, allowing me to envision the code structure clearly and is optimal when working with existing databases.