Hello everyone, in the previous article, we provided an overview of how to access data from our database through ADO.NET. Most of the time, we won't use ADO.NET in our applications; instead, we'll use an ORM (Object Relational Mapper), and in .NET Core, the most commonly used one is Entity Framework Core.

In this article, we won't be using the AdventureWorks2022 database we used previously. Instead, we will examine an example of a small TodoList. This choice will allow us to address topics like migrations and the Code First approach, which we will discuss in detail later.

Ecco il codice Markdown per creare la lista richiesta, con gli elementi principali in grassetto:

Table of Contents:

• What is an ORM
• Database First and Code First Approach
  • Database First
  • Code First
• The Context Class
• Column Mapping
• Queries with Entity Framework
  • Retrieve Entities
  • Add a New Entity
  • Update an Entity
  • Delete an Entity

What is an ORM

An ORM (Object Relational Mapper) is a data access library that enables us to map each table in our databases to a corresponding class. It allows us to map each individual column and its corresponding data type, and it seeks to provide a more fluent way of handling data access through a global configuration. In the case of Entity Framework Core (EF Core), this configuration is represented by the DbContext, which we will delve into further later on.

Database First and Code First Approach

The EF Core team has provided us with two development approaches:

• Database First: This approach starts with an existing database schema. It allows you to generate entity classes and a context based on the structure of the database. You work with the database schema as your starting point and generate code from it.

• Code First: In contrast, the Code First approach begins with defining your entity classes and their relationships in code. From there, you can generate a database schema based on your code. This approach is particularly useful when you want to work primarily with code and let EF Core create and manage the database schema for you.

Database First

The Database First approach (or DB First) is used when we have an existing database schema (or decide to create the database schema first) and then create entity classes and the database context based on it manually or by using a process called scaffolding.

Scaffolding is a reverse engineering technique that allows us to create entity classes and a DbContext based on the schema of a database.

In EF Core, you can perform this operation by installing the NuGet package Microsoft.EntityFrameworkCore.Design in addition to the EF Core package of the database provider you are using.

Once these prerequisites are satisfied, you can run the Scaffold-DbContext command from the command line, providing it with the connection string of your database like this:

Scaffold-DbContext ‘Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=TodoList’ Microsoft.EntityFrameworkCore.SqlServer

Alternatively, by including it in our appsettings.json file, we can retrieve it using this approach.

Scaffold-DbContext 'Name=ConnectionStrings:TodoContext' Microsoft.EntityFrameworkCore.SqlServer

Using these two simple commands, all the entities of our tables with their corresponding relationships will be created, and additionally, the DbContext class will be generated.

Code First

The Code First approach, on the other hand, allows us to create entity tables, their relationships, the mapping between various columns, and optionally, the generation of initial data using what are called "Migrations."

Migrations are a system for updating the database schema and keeping track of ongoing changes to it. In fact, after creating the first migration, you'll see a table named EFMigrationsHistory is created. This system allows us, in case of an error, to revert the changes.

To create a migration, you need to create the DbContext and the entities you wish to create (operations we will delve into later), install the NuGet package Microsoft.EntityFrameworkCore.Tools, and if you're using Visual Studio (recommended), you need to run the following command from the Package Manager Console:

Add-Migrations <nome della migration>

This command will create a 'Migrations' folder within the project, containing the following files:

• XXXXXXXXXXXXXX_.cs, which contains the instructions for applying the migration.
• ModelSnapshot.cs, which creates a "snapshot" of the current model. It is used to identify changes made during the implementation of the next migration.

If you wish to run the migration and update the schema of your database, you should execute the following command:

Update-Database

If you want to remove a migration, you can run the following command:

Remove-Migration

Apart from the instructions just explained, there are two tools that allow us to perform these operations much more easily, which I recommend you explore:

Entity Framework Core Command-Line Tools - dotnet ef

Ef Core Power Tools - github repository

The Context Class

The DbContext class is the primary class that allows us to query our data, manage the database connection, and ensure that the mapping is correct.

To create a DbContext class in EF Core, it is sufficient to make one of our classes inherit from the DbContext class, like this:

public class TodosContext : DbContext 
{

}

Of course, you can give your class any name you prefer. However, it's a common convention to name it after the database and add the "Context" suffix. This way, if you have multiple DbContext classes, you can easily distinguish them.

Another small step to follow is to use the default constructor of the DbContext class:

public class TodosContext : DbContext 
{
   public TodosContext(DbContextOptions<TodosContext> options) : base(options) {}
}

Now that we've created our DbContext class, we can finally create our entities. In this case, let's simulate a simple TodoList application by creating our two entities: Todo and TodoItem:

// file Todo.cs
public class Todo {
   public Todo() {
      TodoItems = new HashSet<TodoItem>();
   }
   public int Id { get;set;}
   public string Name { get;set; }
   public ICollection<TodoItem> TodoItems { get;set; }
}

// file TodoItem.cs
public class TodoItem {
   public int Id { get;set;}
   public string Description { get;set; }
   public bool IsCompleted { get;set; }
}

Once we have created our classes, we can add them to our DbContext as properties of type DbSet:

public class TodosContext : DbContext 
{
   public TodosContext(DbContextOptions<TodosContext> options) : base(options) {}

   public DbSet<Todo> Todos { get;set; }
   public DbSet<TodoItem> TodoItems { get;set; }
}

The DbSet class in EF Core represents a specific table or view in the database within the database context. It enables CRUD operations, LINQ queries, and provides a change tracking mechanism to simplify data management within the database entities.

Column Mapping

The DbContext has many methods that you can override, and one of the most important ones I'd like to mention is the OnModelCreating method. It allows you to perform fluent mapping of your entities using the modelBuilder parameter:

public class TodosContext : DbContext 
{
   public TodosContext(DbContextOptions<TodosContext> options) : base(options) {}

   ...entities classes created

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
    modelBuilder.Entity<Todo>( builder => {
        builder.HasKey(x => x.Id);

            builder.Property(t => t.Id)
                   .ValueGeneratedOnAdd();

        builder.Property(x => x.Name)
                   .HasColumnName("todo_name");

            builder.HasMany(x => x.TodoItems)
                   .WithOne(t => t.Todo)
                   .HasForeignKey(t => t.TodoId)
                   .OnDelete(DeleteBehavior.Cascade);
      });
   }
}

This is what we call Fluent Mapping of entity classes. Through the builder, we inform our DbContext about properties, the model, names (if different from the property name in the entity class), whether a property is required, and most importantly, we can map relationships between various entities.

A widely used mapping strategy in EF Core involves Data Annotations, which are attributes used to customize the mapping of entity classes to database tables. Data Annotations allow you to define column attributes and relationships in detail. An example illustrates this practice:

[Table("TodosItems")]
class TodoItems {
   [Key]
   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public int Id { get;set; }
   [MaxLength(150)]
   [Required]
   [Column("item_description")]
   public string Description { get;set; }
   [Column("item_si_completed")]
   public bool IsCompleted { get; set; }
   public int TodoId { get; set; }
   [ForeignKey(nameof(Todo))]
   public Todo? Todo { get; set; }
}

In the example above, we use Data Annotations to specify the table name ("TodoItems"), declare the primary key (Id), define the maximum length and requirement of the Description attribute, and specify the column name IsCompleted in the database. This provides detailed control over the mapping between the entity class and the database table.

Queries with Entity Framework

Now that everything is correctly configured, we can finally think about how to execute our first query with EF Core.

First of all, we need to register the DbContext class with the .NET Core dependency injection system and define the database provider we are using. In this case, we are using Sqlite. You can install the NuGet package Microsoft.EntityFrameworkCore.Sqlite for this purpose:

// file Program.cs

builder.Services.AddDbContext<TodosContext>(options => {
  options.UseSqlite(builder.Configuration.GetConnectionString("TodoContext"));
});

Let's add these instructions to the OnModelCreating method in our database context to have some sample data available immediately as an example:

modelBuilder.Entity<Todo>(builder =>
{
    // …other properties

    builder.HasData(
        new Todo
        {
            Id = 1,
            Name = "Project management",
        });
});


modelBuilder.Entity<TodoItem>(builder =>
{
    builder.HasData(
      new TodoItem { Id = 1, Description = "Create a Database Context", IsCompleted = false, TodoId = 1 },
      new TodoItem { Id = 2, Description = "Create Todo entity", IsCompleted = false, TodoId = 1 },
      new TodoItem { Id = 3, Description = "Create TodoItem entity", IsCompleted = false, TodoId = 1 }
    );
});

Retrieve Entities

Finally, let's focus on making our first query:

[Route("api/[controller]")]
[ApiController]
public class TodosController : ControllerBase
{
    private readonly TodosContext _context;

    public TodosController(TodosContext context)
    {
        _context = context;
    }

    [HttpGet]
    [Route(nameof(GetAllTodos))]
    public async Task<ActionResult<List<Todo>>> GetAllTodos()
    {
        try
        {
            List<Todo> todos = await 
               _context.Todos.AsNoTracking().ToListAsync();
            return Ok(todos);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}

As you can see, we've injected the context class into the controller's constructor and then used it in our endpoint to query the database with LINQ and retrieve the data created earlier:

Operators like ToList, ToListAsync, ToArray, ToDictionary, and similar methods are extension methods in EF Core used to actually execute LINQ queries and materialize the resulting data from the database. These methods are essential because they allow deferring the execution of queries until the actual results are requested. Additionally, they convert the results into convenient data structures such as lists, dictionaries, or arrays that can be easily used in the application. This approach provides better control over when queries are executed and yields optimized results for further processing.

Another important EF Core extension method that requires explanation is AsNoTracking(). This method is used to inform the framework not to track entities retrieved from the database. This choice significantly improves performance when fetching data from a query.

When using AsNoTracking(), the database context will not maintain an internal state of the retrieved entities. This means there will be no tracking of changes made to these entities, making the data retrieval process faster and more efficient.

However, it's important to note that when using AsNoTracking(), you won't have the ability to make direct changes to the retrieved entities and save them to the database without additional steps. Therefore, it's crucial to use this method carefully, reserving it for situations where you only need to retrieve data and not modify entities.

If, on the other hand, we want to retrieve the Todos along with their respective TodoItems, we should:

[HttpGet]
[Route(nameof(GetAllTodoWithItems))]
public async Task<ActionResult<List<Todo>>> GetAllTodoWithItems()
{
    try
    {
        List<Todo> todos = await _context.Todos.Include(x => x.TodoItems )
                                               .AsNoTracking()
                                               .ToListAsync();
        return Ok(todos);
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

That Include statement will perform a left join on the TodoItems table and retrieve, of course if they exist, all the TodoItems for each Todo item.

Add a New Entity

Inserting a new entity into our database is very simple:

[HttpPost]
[Route(nameof(CreateTodo))]
public async Task<ActionResult<Todo>> CreateTodo([FromBody] Todo todo)
{
   try
   {
      _context.Todos.Add(todo);
      await _context.SaveChangesAsync();

      return CreatedAtRoute(nameof(GetTodo), new { todoId = todo.Id }, todo);
    }
    catch (Exception ex)
    {
       throw ex;
    }
}

It's important to note that the simple Add operation won't immediately insert our new entity into the database. Instead, it adds the entity to the ChangeTracker of the database context, setting it to an Add state. The actual insertion into the database will occur only when we call the SaveChangesAsync() method. This operation is crucial for confirming and making the changes permanent in the database. So, remember that Add is just the first step, and SaveChangesAsync() is what performs the final database insertion action.

Update an Entity

To update an entity in our database, we can retrieve our TodoItem by its ID and then make changes to the object. It's important to ensure that you save the changes to the database using SaveChanges or SaveChangesAsync after making the modifications to the object:

[HttpPut("{todoItemId:int}")]
public async Task<ActionResult> UpdateTodoItem(
    int todoItemId, [FromBody] TodoItem todoItemUpdated) {
  ArgumentNullException.ThrowIfNull(todoItemId, nameof(todoItemId));
  try {
    TodoItem? todoItemFromDatabase =
        await _context.TodoItems.FirstOrDefaultAsync(x => x.Id == todoItemId);
    if (todoItemFromDatabase == null)
      return NotFound();

    todoItemFromDatabase.IsCompleted = todoItemUpdated.IsCompleted;
    todoItemFromDatabase.Description = todoItemUpdated.Description;

    _context.TodoItems.Update(todoItemFromDatabase);
    await _context.SaveChangesAsync();

    return NoContent();
  } catch (Exception ex) {
    throw ex;
  }
}

In this controller, we retrieve the existing TodoItem object from the database based on the provided ID. This is done using await _context.TodoItems.FirstOrDefaultAsync(x => x.Id == todoItemId);. Subsequently, the properties of the todoItemFromDatabase object are updated with the new data provided. Finally, the todoItemFromDatabase object is marked as modified using _context.TodoItems.Update(todoItemFromDatabase);, and the changes are applied to the database with await _context.SaveChangesAsync();.

Delete an Entity

To delete a Todo item from the database, we can use the .Remove() operator. EF Core will automatically handle the deletion of associated TodoItem properties belonging to this entity based on the relationship configuration. This simplifies the management of cascading deletions when configured correctly:

[HttpDelete("{todoId}")]
public async Task<ActionResult> Delete(int todoId) {
  try {
    Todo? todo = await _context.Todos.FirstOrDefaultAsync(y => y.Id == todoId);
    if (todo is null)
      return NotFound();

    _context.Todos.Remove(todo);
    await _context.SaveChangesAsync();

    return NoContent();
  } catch (Exception ex) {
    throw;
  }
}

If we now make a call to the endpoint GetAllTodoWithItems we will see that the entity and its respective items have been deleted from our database.

Once you have understood how to set up the basic configuration and performed the main CRUD operations, you can confidently say that you have covered the fundamental knowledge needed to start working with EF Core. You will find all the code used in this article and the previous one available in this GitHub repository:

Link to the repo

I hope you've enjoyed this introduction to data processing in .NET, and I hope it proves to be helpful in your studies. If you liked the article, please give it a thumbs up, and I hope you're willing to leave a comment to share knowledge and exchange opinions.

Happy Coding!