This content originally appeared on Telerik Blogs and was authored by Assis Zang
Manipulating databases is a common activity in a web developer’s routine, so it is important to know the main approaches used to carry out this task and what features are available in ASP.NET Core. Learn about Code First and Database First with practical examples.
Creating and maintaining databases is an essential part of web application development. The most common approaches include Code First and Database First, each of which has its advantages and particularities, in addition to being more effective in certain scenarios.
In this blog post, we will learn in detail about these approaches and when to use each of them. We will also create practical examples by taking a detailed look at the features available in ASP.NET Core using the Entity Framework Core.
To create a practical example of each of the approaches, we will build an application for managing a forum, where we will have two applications (Users and Forum), each using one of the approaches. You need to have the latest version of .NET installed, in addition to the Visual Studio IDE. (Other IDEs can be used, but are not covered in this post).
You can access the application source code here: DevDiscussion.
Entity Framework Core (EF Core)
Before we check out the approaches to maintaining databases, it is important to take a look at EF Core, as it makes it easier to implement either of the two approaches.
Entity Framework Core is an object-relational mapping (ORM) framework developed by Microsoft. With EF Core, developers can work efficiently with relational databases using objects and classes in ASP.NET Core, simplifying integrations and eliminating the need to write SQL queries and other database access code that otherwise would be necessary.
EF Core allows you to define data models (entity classes) and is responsible for mapping these classes to database tables and vice versa, depending on just a few configurations.
Furthermore, EF Core offers other features such as change tracking and database and table migration schemes, as well as allowing integration with different database providers, such as SQL Server, MySQL, PostgreSQL and SQLite, among others.
Database First
Database First is a database development approach where you start with an existing database and then generate entity models (classes) from that database.
The use of this approach is appropriate in scenarios that have legacy databases or that already have a readymade production database and there is a need to use it in an ASP.NET Core application.
EF Core is a great tool for performing the Database First process as it provides native capabilities for generating entity models from an existing database.
To do this, EF Core first analyzes the database structure and then generates entity classes that correspond to the tables, columns and relationships found during the analysis. This allows integration with the database in ASP.NET Core applications to be done automatically and reduces the chances of typing errors, which could occur if the process were done manually.
To demonstrate the use of the Database First approach, we will create the user management application, which will be a simple web API to store forum user data.
In Visual Studio, click on “Create a new project,” then choose the “Blank solution” option, name it “DevDiscussion” and click on Create.
Then, right-click on the Solution name.
- Choose the options “Add” -> “New Project”
- In the window that opens select “ASP.NET Core Web API”
- Click “Next”
- In the next window, enter the name “UserManagementService”.
- In the next window just uncheck the “Use controllers” option and then click Create.
Now let’s add the EF Core NuGet packages to the project. Click on the “Tools” menu, select “NuGet Package Manager,” then “Manage NuGet packages for solution” in the window that opens, and install the following packages:
- Microsoft.EntityFrameworkCore.SqlServer
- Microsoft.EntityFrameworkCore.Tools
- Microsoft.EntityFrameworkCore.SqlServer.Design
To create classes from the existing database and tables, you must previously configure a local connection to the SQL SERVER. To add a new connection through Visual Studio, do the following:
- In Visual Studio click on “View” => “Server Explorer.”
- Right-click on Data Connections => Add Connection.
- Select Microsoft SQL Server as the data source.
- Connect to LocalDB or SQL Express, depending on which you have configured, then enter DevDiscussion as the database name.
- Select OK and you will be asked if you want to create a new database. Select Yes.
- The newly created database will appear in Server Explorer, so right-click on it and select New Query.
- Paste the SQL code below into the new query, right-click on the query and select Execute.
CREATE TABLE Users (
UserId INT IDENTITY (1, 1) NOT NULL,
Name NVARCHAR(200) NULL,
Email NVARCHAR(200) NULL,
CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (UserId ASC)
);
The table will be created and will be available for use.
Generating Classes with Reverse Engineering
In this context, reverse engineering is the process of scaffolding entity classes and a DbContext class based on an existing database schema, which is executed through a few commands.
Select the “Tools” menu => “NuGet Package Manager” => “Package Manager Console.” In the window that opens, execute the command below modifying the text “YOUR_SERVER_CONNECTION” using your SQL SERVER settings:
Scaffold-DbContext "Data Source=YOUR_SERVER_CONNECTION;Initial Catalog=DevDiscussion;" Microsoft.EntityFrameworkCore.SqlServer
Note that after the command was executed, two new classes were generated—the Model class representing the User entity and the context class that contains the EF Core settings and which inherits from the DbContext class.
Now let’s create the rest of the configurations to make the API functional, and then check if everything is working.
So, in the file appsettings.json add the string connection, and change the text YOUR_SERVER_CONNECTION to your local settings.
"ConnectionStrings": {
"DefaultConnection": "Data Source=YOUR_SERVER_CONNECTION;Initial Catalog=DevDiscussion;"
},
And in the file Program.cs add the code below:
using Microsoft.EntityFrameworkCore;
using UserManagementService;
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddDbContext<DevDiscussionContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
var app = builder.Build();
app.UseHttpsRedirection();
app.MapPost("/user", async (DevDiscussionContext db, User user) =>
{
db.Users.Add(user);
await db.SaveChangesAsync();
return Results.NoContent();
}).WithName("CreateUser").WithOpenApi();
app.MapGet("/user", (DevDiscussionContext db) =>
{
return Results.Ok(db.Users);
}).WithName("GetAllUsers").WithOpenApi();
app.Run();
Here we add the dependency injection of the DevDiscussionContext class and also the configuration of the connection string with the database.
We also added two endpoints, one for creating new records and another for searching them.
The images below demonstrate the writing and reading functions through Progress Fiddler Everywhere. As you can see, the generated code is functional.
When to Use Database First?
By using the Database First approach, we can save time and effort when implementing C# code because, by scaffolding classes with EF Core, it is possible to automate the entire process.
In the example above, we only created one model class because our database was simple, but imagine you are working in a scenario with hundreds of databases and tables. How much time could be saved using scaffolding? Another positive point is that we avoid data type and naming errors by automating the class creation process.
Furthermore, there are scenarios where development teams are separated into database developers and application developers. In these cases, database developers can design the database schema, and application developers can use the Database First approach to generate the entity model from that schema, enabling good collaboration between teams.
However, it is important to note that the “Database First” approach also has some limitations, such as the dependency on the existing database schema and the need to manually update the data model in case of direct database changes.
Code First
Code First is an approach for creating databases and tables from C# classes, allowing the creation and maintenance of the database and its dependencies in an automated way through migration commands.
To implement Code First, we will create a new project.
- Right-click on the Solution name.
- Choose the options “Add” -> “New Project.”
- In the window that opens, select “ASP.NET Core Web API.”
- Click “Next.”
- In the next window, enter the name “ForumTopicManagementService.”
- In the next window just uncheck the “Use controllers” option and then click Create.
Then, add the same EF Core packages to the project as in the previous project. They are:
- Microsoft.EntityFrameworkCore.SqlServer
- Microsoft.EntityFrameworkCore.Tools
- Microsoft.EntityFrameworkCore.SqlServer.Design
Now, create a new class called “ForumTopic” and place the code below in it:
namespace ForumManagementService;
public class ForumTopic
{
public int Id { get; set; }
public string Title { get; set; }
public string Content { get; set; }
public DateTime CreatedAt { get; set; }
public DateTime? UpdatedAt { get; set; }
public string Author { get; set; }
}
Then, create a new class called “ForumTopicDbContext” and place the following code in it:
using Microsoft.EntityFrameworkCore;
namespace ForumManagementService;
public class ForumTopicDbContext : DbContext
{
public ForumTopicDbContext(DbContextOptions<ForumTopicDbContext> options) : base(options) { }
public DbSet<ForumTopic> ForumTopics { get; set; }
}
The class above inherits from the DbContext class, which is responsible for communicating with the database. The ForumTopics object is also declared, which will load the records from the ForumTopic table and make them available for use.
In the same way as before, add the following configuration in the appsettings.json file:
"ConnectionStrings": {
"DefaultConnection": "Data Source=YOUR_SERVER_CONNECTION;Initial Catalog=DevDiscussion;"
},
And in the Program.cs file place the following:
using ForumManagementService;
using Microsoft.EntityFrameworkCore;
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddDbContext<ForumTopicDbContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
var app = builder.Build();
app.UseHttpsRedirection();
app.MapPost("/forumTopic", async (ForumTopicDbContext db, ForumTopic forumTopic) =>
{
db.ForumTopics.Add(forumTopic);
await db.SaveChangesAsync();
return Results.NoContent();
}).WithName("CreateForumTopic").WithOpenApi();
app.MapGet("/forumTopic", (ForumTopicDbContext db) =>
{
return Results.Ok(db.ForumTopics);
}).WithName("GetAllForumTopicS").WithOpenApi();
app.Run();
Generating the Database with Migrations
EF Core’s migrations feature allows you to maintain synchronization between the data model and the database schema, preserving existing data without any losses during the process.
To perform the sync, developers use EF Core tools to generate and persist changes to the database according to the new model. EF Core compares the models and generates migration source files, which are organized in the project’s source code. Furthermore, it is possible to monitor the cycle of changes through a history table created and maintained by EF Core itself. In this sense, the developer only needs to execute the migration commands, and all modifications are created and persisted by EF Core.
For complete coverage of migrations, you can check out Microsoft’s documentation: Migrations Overview.
To generate the database tables using migrations, in Visual Studio select the “Tools” menu => “NuGet Package Manager” => “Package Manager Console” in the window that opens. And execute the command below:
dotnet ef migrations add InitialCreate
Note that after executing the commands, some files were generated. The first file with the name “_InitialCreate” contains the methods with instructions for creating tables based on the application model, which in this case is the ForumTopics class. The DbContextModelSnapshot file stores a representation of the current database model in code form, containing information about entities, primary key and foreign property settings, and other mapping settings that have been defined in the database context (DbContext). This information is used by EF Core during the migration process to update the database based on the model.
Now that we have the migration files ready, let’s run the command to persist the changes to the database. Then, still in the NuGet console, run the command below:
Update-Database
When executing the above command, the files were executed and the __EFMigrationsHistory and ForumTopics tables were created.
Note that the ForumTopics table was created exactly like the ForumTopic model class, while __EFMigrationsHistory was automatically generated by EF Core to store the history of table changes related to migrations. If you query this table, you can see that the first record recorded in it refers to the first migration command, so with each new migration, a new history record will be recorded in this table.
These commands created the ForumTopic table and the history table, but assuming you need to modify the data model (ForumTopic class), how would the database be updated?
To do this, add the following property to the ForumTopic class:
public bool Active { get; set; }
Then run the following command in the console:
Add-Migration AddActiveBoolean
Now run the command again to persist the change:
Update-Database
Note that the table was updated with the new column (Active) and, if you query the history table, you will see the new record indicating the new update. This way, with each new change to the model, it is possible to easily perform synchronization with the model in the database.
When to Use Code First?
As seen previously, through the Code First approach it is possible to quickly create a complete database schema based on the data model defined in the application.
When using the Code First approach, it is important to consider some aspects where this approach has some advantages.
Creating new applications: If, when creating a new application, you do not yet have a database ready, it is advisable to use Code First, as you can focus on defining your entities and then use EF Core to create the database schema.
Change control and integration with DevOps and CI/CD: Code First may be more suitable for scenarios where agile development and continuous integration are priorities, as EF Core’s capabilities allow model changes to be easily versioned and deployed alongside the source code.
Despite the advantages described above, it is important to consider that fine control over the database schema may not be possible through EF Core. In addition, the complexity when using Code First may also increase, and, depending on the team’s level of experience, this can become a problem.
Final Considerations
Learning to use the main database manipulation approaches is essential for backend developers. This is because these approaches can significantly reduce the effort required in creating and maintaining databases, especially in large applications where there are a large number of tables and databases involved.
In this blog post, we covered the two main approaches that, in addition to other advantages, allow two or more teams to work flexibly, being able to partition development between teams specialized in databases and teams focused on creating code.
With the Code First approach, developers can focus their attention on creating models and then use EF Core to generate database migrations, simplifying the implementation of details, such as tables and relationships between them, in addition to having the possibility of keeping track of change history.
The Database First approach is most appropriate when you have ready-made databases and want to generate C# code from them.
The choice to use one or the other approach, even both, depends on each scenario. As discussed throughout the post, each approach has advantages and disadvantages and it is important to consider them when creating new applications.
This content originally appeared on Telerik Blogs and was authored by Assis Zang
Assis Zang | Sciencx (2024-07-23T15:12:07+00:00) ASP.NET Core Basics: Manipulating Databases with Different Approaches. Retrieved from https://www.scien.cx/2024/07/23/asp-net-core-basics-manipulating-databases-with-different-approaches/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.