Database Interaction

Besides the application logic, there must be data persistence on which the actual logic is performed. In this regard, most applications use databases. To simplify the interaction between programs and the database, ORMs (Object-Relational Mapping) have been implemented. These are frameworks that map database tables and data types to objects, called entities, and types declared in the application code.

ORMs generally expose a generic interface that can be used for multiple databases (such as PostgreSQL, MariaDB, or SQL Server) using the same code, even though there may be customizations for each. These specific implementations are found in various libraries available on NuGet; for .NET, the ORM used is EntityFramework. The generic interface is exposed through a database context. The context is nothing but a client for the database, which serializes/deserializes requests and objects in communication with the database and also serves as a cache for entities. In EntityFramework, the context will be a class derived from DbContext.

danger

We recommend using SQL databases; most applications will not need NoSQL databases, and traditional databases will most likely meet your needs. The choice of the database should be an informed one and adapted to the project's needs. Do not adopt technologies just because they are trendy or simply because you are familiar with them.

Defining the Database Schema

Most of the application logic will be dictated by the data schema. To start developing an application, the first step is to define the database schema and the objectives you want to achieve with it. If these aspects are well defined, implementing logic over data will be much easier and will require fewer changes to the application.

Below is an example of mapping some entities to database tables. Note that entities represented by regular classes can inherit abstract classes to avoid duplicate code. Each entity will represent a table in the database, and the relationships between entities, represented by properties containing other entity types, are called navigation properties. Through these properties, foreign key relationships will be established.

public abstract class BaseEntity
{
    public Guid Id { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public DateTime UpdatedAt { get; set; } = DateTime.UtcNow;
    public void UpdateTime() => UpdatedAt = DateTime.UtcNow;
}

public class User : BaseEntity
{
    public string Name { get; set; } = default!;
    public string Email { get; set; } = default!;
    public string Password { get; set; } = default!;
    public UserRoleEnum Role { get; set; } = default!;
    public ICollection<UserFile> UserFiles { get; set; } = default!;
}

public class UserFile : BaseEntity
{
    public string Path { get; set; } = default!;
    public string Name { get; set; } = default!;
    public string? Description { get; set; }

    public Guid UserId { get; set; }
    public User User { get; set; } = default!;
}

For each entity, a configuration class is created so that the ORM knows various details regarding table creation, such as which properties correspond to primary, unique, or reference keys. Note how the relationships between tables are defined through navigation properties. You can access more information about EntityFramework and how to use it here.

public class UserConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.Property(e => e.Id) // Aici se specifică care proprietate este configurată.
            .IsRequired(); // Aici se specifică dacă proprietatea este obligatorie, ceea ce înseamnă că nu poate fi nulă în baza de date.
        builder.HasKey(x => x.Id); // Aici se specifică că proprietatea Id este cheia primară.
        builder.Property(e => e.Name)
            .HasMaxLength(255) // Aici se specifică lungimea maximă pentru tipul varchar în baza de date.
            .IsRequired();
        builder.Property(e => e.Email)
            .HasMaxLength(255)
            .IsRequired();
        builder.HasAlternateKey(e => e.Email); // Aici se specifică că proprietatea Email este o cheie unică.
        builder.Property(e => e.Password)
            .HasMaxLength(255)
            .IsRequired();
        builder.Property(e => e.Role)
            .HasMaxLength(255)
            .IsRequired();
        builder.Property(e => e.CreatedAt)
            .IsRequired();
        builder.Property(e => e.UpdatedAt)
            .IsRequired();
    }
}

public class UserFileConfiguration : IEntityTypeConfiguration<UserFile>
{
    public void Configure(EntityTypeBuilder<UserFile> builder)
    {
        builder.Property(e => e.Id)
            .IsRequired();
        builder.HasKey(x => x.Id);
        builder.Property(e => e.Path)
            .HasMaxLength(255)
            .IsRequired();
        builder.Property(e => e.Name)
            .HasMaxLength(255)
            .IsRequired();
        builder.Property(e => e.Description)
            .HasMaxLength(4095)
            .IsRequired(false); // Aici se specifică că această coloană poate fi nulă în baza de date.
        builder.Property(e => e.CreatedAt)
            .IsRequired();
        builder.Property(e => e.UpdatedAt)
            .IsRequired();

        builder.HasOne(e => e.User) // Aici se specifică o relație de unu-la-mulți.
            .WithMany(e => e.UserFiles) // Aici se furnizează maparea inversă pentru relația de unu-la-mulți.
            .HasForeignKey(e => e.UserId) // Aici este specificată coloana cheii străine.
            .HasPrincipalKey(e => e.Id) // Aici se specifică cheia referențiată în tabela referențiată.
            .IsRequired()
            .OnDelete(DeleteBehavior.Cascade); // Aici se specifică comportamentul de ștergere atunci când entitatea referențiată este eliminată.
    }
}

Migrations

When implementing a database schema, it can undergo various changes during the development and maturation of the application. Therefore, changes to the database schema should be made incrementally, meaning any change applies over previous changes. Hence, the concept of migration exists. A migration is a transformation, often reversible, of the database schema to reflect changes in the code. In Entity Framework, you can use migrations by installing dotnet-ef:

dotnet tool install --global dotnet-ef --version 8.*

Once the entities have been created and configured appropriately (consult the configurations for entities in the lab project), you can run the command to generate migrations with the database open:

dotnet ef migrations add <nume_migrare> --context <nume_clasa_context> --project <proiect_cu_migrarile> --startup-project <proiect_cu_startup>

Example:

dotnet ef migrations add InitialCreate --context WebAppDatabaseContext --project .\MobyLabWebProgramming.Infrastructure --startup-project .\MobyLabWebProgramming.Backend

In the lab code, created migrations will be automatically applied on the first request made to the database. Alternatively, you can run the command:

dotnet ef database update

For more information about migrations and command line tools, you can consult the documentation for the dotnet-ef utility.

tip

Always create migrations in project development and do not neglect their importance. This helps in two ways: you automate the process of changing the database and can track changes throughout the application's history to detect potential errors that may arise due to a change.

caution

Applying a migration can fail if column constraints are violated. For example, if a not null constraint is placed on an existing column and there are records with NULL in that column, the migration will fail.

danger

Be aware that certain changes to the database are irreversible, such as deleting tables or columns. Before applying a migration, make a backup of the database.

Citirea de date

A particularity of EntityFramework is that it does not use SQL queries written by the programmer but can be functionally specified through LINQ (Language Integrated Query) for data access. The framework abstracts the requests through a functional interface, and these are translated into specific requests for each type of database. Below is an example of how LINQ code translates to SQL for Postgres:

var search = "Dan Geros";
await DbContext.Set<UserFile>()
    .Where(e => EF.Functions.Like(e.Name, $"%{search}%"))
    .OrderByDescending(e => e.CreatedAt)
    .Select(e => new UserFileDTO
    {
        Id = e.Id,
        Name = e.Name,
        Description = e.Description,
        CreatedAt = e.CreatedAt,
        UpdatedAt = e.UpdatedAt,
        User = new()
        {
            Id = e.User.Id,
            Email = e.User.Email,
            Name = e.User.Name,
            Role = e.User.Role
        }
    }).ToListAsync(); 

This code translates to the following SQL, the lambda functions described in the previous operations will be translated verbatim into this SQL through C# reflection mechanisms:

select uf."Id", uf."Name", uf."Description", uf."CreatedAt", uf."UpdatedAt", u."Id", u."Email", u."Name", u."Role" from "UserFile" uf 
left join "User" u on u."Id" = uf."UserId" 
where uf."Name" like '%Dan Geros%'
order by uf."CreatedAt" desc

In principle, this is possible because database tables are nothing but collections of entries, and the same functional operations as in functional programming can be applied. Functional operations in LINQ were inspired by analogous operations in databases. This correspondence with SQL is one-to-one, for example, projection/select corresponds to .Select, filtering/where corresponds to .Where, and sorting/order to .OrderBy. An introduction to LINQ can be found here.

Although these operations can be used directly with the database context, repository components can be implemented to interact with the ORM. A repository can be implemented for specific entities, such as the user entity, or be generic, and requests can be grouped in the specifications design pattern. A specification in the design pattern context is an object that contains the database request to be reused in multiple parts of the code. You can see in the lab code how specifications and the generic repository are implemented. If you choose to work with specifications, use the same package as in the lab project.

A very important thing to know here is that once entities are extracted from the database, they are implicitly linked to the database context and are tracked by the framework, these entities are called tracked, and they will not be consumed by the garbage collector until the database context is consumed first.

danger

It is not recommended to expose database entities directly to the outside of the application. Therefore, it is best to transform/map the entities into DTOs (Data Transfer Objects), which are simple objects that only transfer information from entities and can be consumed by the garbage collector independently of the database context. Also, not all information in the entity may be necessary or desired to be exposed outside the services, and it is better to use DTOs for the security of the application.

Modifying Data

In addition to data reading operations, there are, of course, data modification operations in the database. Adding, modifying, and deleting data is done through the set exposed by the database context as follows:

var user = new User
{
    Email = "admin@default.com",
    Name = "Admin",
    Role = UserRoleEnum.Admin,
    Password = PasswordUtils.HashPassword("default")
}

dbContext.Set<T>().Add(user); // Add the entity to the context, but do not immediately send it to the database; it is just marked for insertion.

dbContext.SaveChanges(); // Only now, at the call of this method, is the insert request sent to the database, and the context tracks changes made to the entity.

user.Name = "NewAdmin";
dbContext.SaveChanges(); // After making changes to a tracked entity, at saving the context, update requests are sent to the database.

dbContext.Remove(user); // Once linked to the context, an entity can be removed from a set. Similarly, the delete request is not sent immediately.
dbContext.SaveChanges(); // When saving the context, entities removed from the context set are deleted from the database through delete requests.

It should be noted that changes to the data set are always made at .SaveChanges() or .SaveChangesAsync(); this helps to group multiple requests and send them in a single request to optimize database writes. To obtain entities linked to the context without inserting them first, extract them through LINQ operations.

tip

Although you can perform any possible operation on the database through the context, you can use the code from the lab for the repository and specifications to have some abstractions that reduce duplicate code.

caution

Keep in mind, you cannot track multiple entities by the context with the same primary or unique key. If you attempt to attach an entity to the context when another entity with the same key exists, the context will return an error.

danger

Be careful with modifications to entities. If you do not intend to modify the entities in the database, they should not be modified in the code either, because when calling the .SaveChanges() or .SaveChangesAsync() methods later, even if it is not done within the same function, the changes will be transmitted to the database and unwanted modifications to the data will occur. If you want to modify data retrieved from the database, it is best to project them onto DTOs and work with those.

Lab Tasks

Download the lab code from Gitlab and follow these types of classes:

• Entities
• Entity configurations
• Specifications
• Repository

Create the first migration named "InitialCreate" with the dotnet-ef command and run the project with the database started. Connect to the database and observe the database schema.

Try adding your own entities and create new migrations. You can now create your database schema for the project.