###### tags: `Back-end`,`Restful API`,`ASP.NET CORE`,`Titanium`
# Day 06/07 - CRUD with Database
## Introduction
Welcome to today’s tutorial on performing CRUD (Create, Read, Update, Delete) operations with a database in .NET Core 8. This session will build upon what we learned in the previous lecture about setting up the DbContext, and managing database migrations.

CRUD stands for Create, Read, Update, and Delete. These are the four basic operations that you will perform on the database. In this session, we will implement these operations using Entity Framework Core.
## Session Objectives
By the end of this session, trainees will:

* Implement CRUD Operations in the Service Layer using MainAppContext.
* Understand Entity Relationships (e.g., one-to-many) and how to apply them through migrations.
* Differentiate Between Find and FirstOrDefault with practical examples.
* Use DTOs to Simplify API Input and Output, preventing over-posting and exposing unnecessary data.
* Apply Asynchronous Programming (async/await) to improve performance in database operations.
* Add and Apply Migrations to update the database based on model changes.
## DbContext and DbSet

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

    public DbSet<Program> Programs { get; set; }
    public DbSet<Category> Categories { get; set; }
    public DbSet<Course> Courses { get; set; }
}

## Applying Migrations and Updating the Database in .NET Core
Once you've defined the DbSet properties in your MainAppContext and set up your models with relationships, the next step is to apply these changes to the database using Entity Framework Core (EF Core) migrations.

Once the DbSets are defined, the next step is to create a migration that will generate the necessary SQL scripts to create or update the database schema.

Migration Process Overview:
* Add Migration: This command generates a migration based on the current state of your models and DbContext.
* Update Database: This command applies the generated migration to the actual database, modifying it to match your models.

Run Add-Migration:
VSCode(you need to install dotnet ef package)

dotnet ef migrations add InitialCreate

or for Visual Studio Management IDE

Add-Migration InitialCreate

Explanation:
InitialCreate is the name of your migration. You can name it anything that represents the change, like AddProgramCategoryRelationship.
This command will generate a migration file in your project’s Migrations folder, containing the SQL code needed to create tables and relationships based on your models.

Generated Migration:

public partial class InitialCreate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Categories",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Name = table.Column<string>(nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Categories", x => x.Id);
            });

        migrationBuilder.CreateTable(
            name: "Programs",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Title = table.Column<string>(nullable: true),
                Description = table.Column<string>(nullable: true),
                CategoryId = table.Column<int>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Programs", x => x.Id);
                table.ForeignKey(
                    name: "FK_Programs_Categories_CategoryId",
                    column: x => x.CategoryId,
                    principalTable: "Categories",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });

        // Additional code for Course table and relationships...
    }
}

This migration creates the Categories and Programs tables and establishes a foreign key relationship between them (i.e., a one-to-many relationship).

**Update-Database Command**
After the migration is created, the next step is to apply it to your actual database using the Update-Database command.

dotnet ef database update

Or in Visual Studio Package Manager Console:

Update-Database

This command will execute the SQL code generated in the migration and update your database schema to match the state of your models.
The migration will create the necessary tables and relationships.

## Understanding Relationships Based on Models
In your models, you defined relationships using navigation properties and the ForeignKey attribute.

Here’s how EF Core interprets and implements these relationships based on your model definitions:

### One-to-Many Relationship: Program and Category
In your Program class, you have the following relationship:

public class Program
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }

    public int CategoryId { get; set; } // Foreign Key for Category

    // Belongs to
    [ForeignKey("CategoryId")]
    public Category Category { get; set; } // Navigation Property
}

Explanation: A Program belongs to a single Category (via CategoryId foreign key), and a Category can have multiple Programs (via the ICollection<Program> navigation property in the Category class).

In your Category model, the relationship is:

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }

    // Has Many (One Category has many Programs)
    public ICollection<Program> Programs { get; set; }
}

EF Core uses this configuration to create a one-to-many relationship in the database between Program and Category. The CategoryId is the foreign key, and the Programs collection represents the related entities.

### Many-to-One Relationship: Course and Trainer

public class Course
{
    public int Id { get; set; }
    public int ProgramId { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
    public bool IsActive { get; set; }
    public int TrainerId { get; set; } // Foreign Key for Trainer

    [ForeignKey("TrainerId")]
    public Trainer Trainer { get; set; } // Navigation Property
}

Here, the TrainerId serves as the foreign key to the Trainer table. EF Core will create a many-to-one relationship, meaning many Courses can belong to one Trainer.
## CRUD implementation for Programs

public class ProgramsService : IProgramCRUD
{
    private MainAppContext _mainAppContext;

    public ProgramsService(MainAppContext mainAppContext)
    {
        _mainAppContext = mainAppContext;
    }
    public Models.Program Update(int id, Models.Program newProgram)
    {
        //#1 go to the programs list and get the resource
        var oldProgam = _mainAppContext.Programs.FirstOrDefault(p => p.Id == id);
        if (oldProgam != null)
        {
            oldProgam.Title = newProgram.Title;
        }

        return oldProgam;
    }

    public bool Delete(int id)
    {
        // #1 go to the programs list and get the resource
        var program = _mainAppContext.Programs.FirstOrDefault(p => p.Id == id);
        if (program != null)
        {
            _mainAppContext.Programs.Remove(program);
            return true;
        }
        return false;
    }

    IEnumerable<Models.Program> IProgramCRUD.GetAll()
    {
        return _mainAppContext.Programs.ToList() ;
    }

    Models.Program IProgramCRUD.Create(Models.Program p)
    {
        _mainAppContext.Programs.Add(p);
        return p;
    }

  

}

In ASP.NET Core, dependency injection (DI) is a built-in feature that allows for the management of dependencies within an application. In your case, the MainAppContext (which is your DbContext responsible for interacting with the database) is being injected into the ProgramsService class.

Explanation:
The constructor of ProgramsService receives the MainAppContext object as a parameter (mainAppContext). ASP.NET Core’s DI system automatically provides an instance of MainAppContext when it creates an instance of ProgramsService.

### Difference Between IEnumerable, ICollection, and IList
* IEnumerable: It represents a forward-only, read-only cursor over a collection of items. You can iterate over the collection but cannot modify it (unless explicitly cast or modified at the source), Best for read-only access or when you need to perform foreach iterations.

* ICollection: Inherits from IEnumerable but adds methods for adding, removing, and checking the number of items in a collection, Suitable when you need basic collection manipulation like adding/removing elements.

* IList:Inherits from ICollection and provides indexed access to the collection, Suitable when you need access by index or more control over the order of elements.

Note:In your service class, you are using IEnumerable<Program> for the GetAll() method because it is a simple read-only operation that retrieves all programs, which is perfect for iteration.

## async and await
Asynchronous programming is essential in modern software development, especially in scenarios involving I/O-bound operations such as reading from a database, making API calls, or accessing external resources.

In C#, asynchronous programming is handled using the async and await keywords. These keywords enable methods to run without blocking the main thread, allowing other operations to execute simultaneously, improving application responsiveness and scalability.

Benefits of Asynchronous Programming:
* Improved Application Performance: Async methods allow applications to perform other tasks while waiting for I/O operations, reducing idle time.
* Non-Blocking Code Execution: Long-running tasks (like querying a database) do not block the execution of other parts of the application.
* Better Resource Management: Optimizes resource usage, allowing more tasks to run concurrently

### Async and Await in Service Layer - Example
Let's consider the following service class that handles Category operations. It uses async and await for database operations to prevent blocking the main thread while querying or modifying data in the database.

public class CategoryService : ICategoryCRUD
{
    private MainAppContext _mainAppContext;

    public CategoryService(MainAppContext mainAppContext)
    {
        _mainAppContext = mainAppContext;
    }

    // Create a new category asynchronously
    public async Task Create(CategoryDTO c)
    {
        Category category = new Category();
        category.Name = c.Name;
        await _mainAppContext.Categories.AddAsync(category);
        await _mainAppContext.SaveChangesAsync();
    }

    // Retrieve all categories asynchronously
    public async Task<IEnumerable<Category>> GetAll()
    {
        return await _mainAppContext.Categories.ToListAsync();
    }

    // Delete a category asynchronously
    public async Task<bool> Delete(Category c)
    {
        _mainAppContext.Categories.Remove(c);
        await _mainAppContext.SaveChangesAsync();
        return Task.FromResult(true);
    }

    // Edit a category asynchronously
    public async Task<bool> Edit(Category c)
    {
        try
        {
            _mainAppContext.Categories.Update(c);
            await _mainAppContext.SaveChangesAsync();
            return Task.FromResult(true);
        }
        catch (Exception)
        {
           return Task.FromResult(false);
        }
    }

    // Get a category by Id asynchronously
    public async Task<Category> GetById(int Id)
    {
        return await _mainAppContext.Categories.FirstOrDefaultAsync(c => c.Id == Id);
    }
}

In the above service class, each method interacts with the database asynchronously using await. To convert a synchronous method into an asynchronous one, we:

* Add the async keyword: This allows the method to be asynchronous and enables the use of the await keyword.
* Use the await keyword: The await keyword pauses the method execution until the awaited task completes, without blocking the calling thread.
* Return Task or Task<T>: If the method doesn't return a result, it should return Task. If it returns a result (like Category), it should return Task<T>.

Let's focus on how we convert the synchronous Create method into an asynchronous one:

**Synchronous Code:**

public void Create(CategoryDTO c)
{
    Category category = new Category { Name = c.Name };
    _mainAppContext.Categories.Add(category);
    _mainAppContext.SaveChanges();
}

**Asynchronous Code:**

public async Task Create(CategoryDTO c)
{
    Category category = new Category { Name = c.Name };
    await _mainAppContext.Categories.AddAsync(category);  // Add asynchronously
    await _mainAppContext.SaveChangesAsync();             // Save changes asynchronously
}

Here’s what changed:

We added the async keyword to the method.
We used await with AddAsync and SaveChangesAsync to handle asynchronous database operations.

**Why AddAsync but No UpdateAsync or RemoveAsync?**
* AddAsync: Adding a new entity might involve generating keys, performing database-related operations asynchronously, and therefore AddAsync is available.
* Update and Remove: Updating or removing an entity doesn’t require generating new data or performing complex operations. These operations only track changes and mark entities for update or deletion, so they don't have async counterparts (UpdateAsync or RemoveAsync) because the actual database interaction (saving) happens in SaveChangesAsync.

## Data Transfer Objects (DTO)
In the development of APIs, it’s common to work with entities that directly map to database tables, such as the Category class in our example. However, when sending or receiving data from the front-end, we may not always want to expose all the fields of the entity. This is where Data Transfer Objects (DTOs) come in.

### What is a DTO?
A Data Transfer Object (DTO) is a simple class that is used to transfer data between layers of an application, such as between the front-end and back-end, without exposing the full structure of the entity models. DTOs allow us to shape the data that is sent or received, ensuring only the required fields are included.

### Example Scenario Without DTO
Let’s assume we didn’t use DTOs, and we directly used the Category entity for both database interaction and input/output in the API:

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime CreatedAt { get; set; }
    public bool IsActive { get; set; }
}

When sending data from the front-end, it could look something like this:

Problems with this approach:
* Security Concerns: The front-end might submit fields like CreatedAt or Id, which should not be controlled by the user.
* Data Integrity: A malicious or unintentional user could change fields such as Id or IsActive when creating or updating categories, which could lead to data integrity issues.
* Unnecessary Complexity: Fields like CreatedAt and IsActive might not be relevant to the user input during category creation. Submitting them makes the API call more complex.

### Introducing DTO to Fix These Problems
To address these issues, we use a DTO to define exactly which fields are necessary when interacting with the API.

For example, the CategoryDTO would look like this:

public class CategoryDTO
{
    public string Name { get; set; }
}

With this DTO, the front-end only needs to submit the Name field. Other fields, like Id, CreatedAt, or IsActive, are handled internally by the server.

### How to Use the DTO in Our Service Layer
Now that we’ve defined the CategoryDTO, we can use it in our service layer to handle the creation of a new Category:

public async Task Create(CategoryDTO c)
{
    // Map the DTO to the entity
    Category category = new Category();
    category.Name = c.Name;

    // Add to database and save changes asynchronously
    await _mainAppContext.Categories.AddAsync(category);
    await _mainAppContext.SaveChangesAsync();
}