###### tags: `Back-end`,`Restful API`,`ASP.NET CORE`,`Aon`
# Week 04 - Entity Framework and Async Programming
## Integrate .NET Core App with DB
This section introduces .NET Core 8 and its integration with SQLite, a lightweight, file-based relational database system. SQLite is a great choice for beginner projects or small-scale applications, especially for learning purposes.

we’ll discuss how to integrate SQLite into a .NET Core 8 project as the data tier, which will manage the interaction between the application and the database.

### Packages Installation
Entity Framework Core: A popular ORM (Object-Relational Mapper) to interact with the SQLite database in a .NET Core project.

### DbContext and Connection Setup
The DbContext is the primary class responsible for interacting with the database in Entity Framework Core. It allows you to query, update, and manage data using LINQ (Language Integrated Query).

#### Setting Up the DbContext
The DbContext class is at the core of EF Core's database interactions. It represents a session with the database and is used to query and save data.

#### Creating a Connection String
The connection string tells your application how to connect to the SQLite database. In the case of SQLite, it’s usually just the path to the database file.

using AonFreelancing.Models;
using Microsoft.EntityFrameworkCore;

namespace AonFreelancing.Contexts
{
    public class MainAppContext:DbContext
    {
        public DbSet<Freelancer> Freelancers { get; set; }
        public DbSet<Project> Projects { get; set; }
        public DbSet<Client> Clients { get; set; }
        public MainAppContext(DbContextOptions<MainAppContext> contextOptions) : base(contextOptions) {

        }

    }
}

Explanation:
`public class MainAppContext`: This defines a new class (think of a class as a blueprint for creating objects in programming) named MainAppContext.

`DbContext` means that this class is inheriting from (or based on) DbContext, a special class in Entity Framework that provides essential database functionality.

`DbSet<Freelancer> Freelancers, DbSet<Project> Projects, DbSet<Client> Clients`
Each DbSet property represents a database table. The MainAppContext class manages the following tables:

`Freelancers:` a table that will store data for freelancers.
`Projects:` a table for project data.
`Clients:` a table for client data.

`defined constructor:`which is a method that gets called when you create an instance of MainAppContext. It takes in some contextOptions, which are settings about how to connect to the database (like which database to use). It then passes these options to the DbContext class so it can set everything up correctly.

The MainAppContext class will act as the bridge between the app code and the database. By using it, the app can:

* Query the database (get information),
* Add, update, or delete records in the database, and
* Make database changes in an organized way.

With MainAppContext, the application code can now easily interact with tables in the database, represented here as Freelancers, Projects, and Clients.

### Registering DbContext in Dependency Injection (DI)
In .NET Core, we use Dependency Injection (DI) to manage services and dependencies like DbContext. To configure AppDbContext in DI, add it in the Program.cs file:

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlite("Data Source=app.db"));

var app = builder.Build();

This code snippet sets up a basic ASP.NET Core application and connects it to a SQLite database. Here’s a quick breakdown:

`WebApplication.CreateBuilder(args)`

Creates a builder object (builder) to configure services and app settings for an ASP.NET Core app.
`builder.Services.AddDbContext<AppDbContext>(...)`

Registers the AppDbContext as a service, telling the app to use a SQLite database (app.db) as the data source. AppDbContext is the class that handles interactions with the database.
`var app = builder.Build();`

Builds the application with all configured services and settings, preparing it to run.

In short, this code sets up an app that will use a SQLite database for data storage.

### Creating Models and First Code Migrations
Models in Entity Framework Core represent tables in the database. Each property in a model corresponds to a column in the table.

Let's used code of models that we implemented in the OOP lecture. we will use these models to represent tables as entities.

Below is base class for different user types:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Text.Json.Serialization;
using System.Threading.Tasks;
using AonFreelancing.Interfaces;

namespace AonFreelancing.Models
{
    public abstract class User : IUserAuthOperations,IUserPrintOperations
    {
        public string Name { get; set; }

        public string Username { get; set; }

        public string Password { get; set; }
        public int Id { get; set; }


    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AonFreelancing.Models
{
    [Table("Freelancers")]
    public class Freelancer : User
    {
        public string Skills { get; set; }
    }
}

The Freelancer class represents a database table in an application, thanks to Entity Framework (EF) conventions and annotations. Here’s how it works and why certain terms and attributes are used:

* The Freelancer class inherits from User, meaning it gets all of User’s properties (Name, Username, Password, Id) as its own.
* With Entity Framework, each class like Freelancer typically represents a database table, where properties represent table columns. So, in the database, this class could map to a table called Freelancers with columns for Id, Name, Username, Password, and Skills.

**Why It’s Called an Entity**
In database and EF terms, an entity is an object that maps to a table. It’s called an entity because it represents a meaningful item in the application, like a freelancer or a user, that can be stored and retrieved from the database.

**Adding Data Annotations for Constraints
**
For User:

 public abstract class User : IUserAuthOperations,IUserPrintOperations
    {
         [Required] // Enforces that Name cannot be null
    [StringLength(100)] // Sets a maximum length for Name
    public string Name { get; set; }

    [Required]
    [StringLength(50, MinimumLength = 5)] // Limits Username length
    public string Username { get; set; }

    [Required]
    public string Password { get; set; }

    [Key] // Specifies that Id is the primary key
    public int Id { get; set; }


    }

For Freelancers:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

[Table("Freelancers")] // Explicitly sets table name to "Freelancers"
public class Freelancer : User
{
    [Required] // Makes this property mandatory
    public string Skills { get; set; }

   
}

`[Table("Freelancers")]:` Specifies the exact name of the table in the database.
`[Required]:` Marks the field as mandatory, meaning it cannot be null in the database.
`[StringLength]:` Limits the number of characters, preventing overly long entries.
`[Key]:` Defines Id as the primary key, which uniquely identifies each row in the table.
These annotations enforce basic rules and help keep the database consistent with the application’s requirements.

now, after we understand the above, let's proceed to implement remaing models:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AonFreelancing.Models
{

    [Table("Clients")]
    public class Client : User
    {
        public string CompanyName { get; set; }

        // for relationship
        // Has many projects, 1-m
        public IEnumerable<Project>? Projects { get; set; }
  
    }
}

In this Client class, the Projects property sets up a one-to-many relationship between Client and Project in the database. Here’s how it works:

**One-to-Many Relationship**
A one-to-many relationship means that one Client can have multiple Project records associated with it, but each Project is linked to only one Client.
In Code, This relationship is represented by the Projects property, which is an `IEnumerable<Project>`. This means that a Client can have a list of associated Project entities.

**Entity Framework's Role**
When using Entity Framework, it will automatically understand that:

Each Client can have multiple Project entities (1-to-many),
And it will use the Client ID as a foreign key in the Project table to connect each project back to its respective client.

Example Usage:
Suppose you have a client record with CompanyName = "TechCorp". The Projects property can then hold multiple projects for "TechCorp," showing all projects that belong to this client.

**Adding a Foreign Key (Optional)
**

public class Project
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    public int ClientId { get; set; } // Foreign key
    [ForeignKey("ClientId")]
    public Client Client { get; set; } // Navigation property
}

This setup means:

A Client can be associated with multiple Project records,
Each Project record points back to a single Client, establishing a clear 1-to-many relationship in the database.

### Applying Migrations
Migrations in Entity Framework allow you to create or update database tables based on your model classes.

For developers who are using VSCode, install the Entity Framework tools to manage migrations:

dotnet tool install --global dotnet-ef

Next, create an initial migration:

dotnet ef migrations add InitialCreate

or if you are using Visual Studio Management IDE, use `Package Manager Console` to apply below commands:

Add-Migration InitialMig

The above commands, will create C# scripts that are responsible for convert Models, it's constraints and data types into script ready to be mapped to tables in the DB.

### Updating the Database

dotnet ef database update

or for Visual Studio:

Update-Database

This command applies the migration and creates the necessary tables in your SQLite database.

## CRUD Operation with EF
Welcome again, 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.

Let's first implement CRUD operations and endpoints for freelancer resource:

In the `FreelancersController` class, dependency injection is used to provide an instance of MainAppContext to the controller. This allows the controller to access the database context without creating it directly, making the code more modular and testable.

The `FreelancersController` constructor accepts MainAppContext as a parameter. When the controller is created, the framework automatically provides the MainAppContext instance registered in the services, allowing the controller to interact with the database.

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

Contrller Implementation:

using AonFreelancing.Contexts;
using AonFreelancing.Models;
using AonFreelancing.Models.DTOs;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace AonFreelancing.Controllers
{
    [Route("api/freelancers")]
    [ApiController]
    public class FreelancersController : ControllerBase
    {
        private readonly MainAppContext _mainAppContext;

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

        [HttpGet]
        public IActionResult GetAll()
        {
            var data = _mainAppContext.Freelancers.ToList();
            return Ok(data);
        }

        [HttpPost]
        public IActionResult Create([FromBody] Freelancer freelancer)
        {
            _mainAppContext.Freelancers.Add(freelancer);
            _mainAppContext.SaveChanges();
            return CreatedAtAction("Create", new { Id = freelancer.Id }, freelancer);
        }

        [HttpPost("Register")]
        public IActionResult Register([FromBody] FreelancerDTO freelancerDTO)
        {
            ApiResponse<object> apiResponse;

            Freelancer f = new Freelancer
            {
                Name = freelancerDTO.Name,
                Username = freelancerDTO.Username,
                Password = freelancerDTO.Password,
                Skills = freelancerDTO.Skills
            };

            _mainAppContext.Freelancers.Add(f);
            _mainAppContext.SaveChanges();
            apiResponse = new ApiResponse<object>
            {
                IsSuccess = true,
                Results = f
            };

            return Ok(apiResponse);
        }

        [HttpGet("{id}")]
        public IActionResult GetFreelancer(int id)
        {
            Freelancer? fr = _mainAppContext.Freelancers.FirstOrDefault(f => f.Id == id);
            if (fr == null)
            {
                return NotFound("The resource is not found!");
            }

            return Ok(fr);
        }

        [HttpDelete("{id}")]
        public IActionResult Delete(int id)
        {
            Freelancer f = _mainAppContext.Freelancers.FirstOrDefault(f => f.Id == id);
            if (f != null)
            {
                _mainAppContext.Remove(f);
                _mainAppContext.SaveChanges();
                return Ok("Deleted");
            }

            return NotFound();
        }

        [HttpPut("{id}")]
        public IActionResult Update(int id, [FromBody] Freelancer freelancer)
        {
            Freelancer f = _mainAppContext.Freelancers.FirstOrDefault(f => f.Id == id);
            if (f != null)
            {
                f.Name = freelancer.Name;
                _mainAppContext.SaveChanges();
                return Ok(f);
            }

            return NotFound();
        }
    }
}

In the context of Entity Framework (EF), the methods Add, Update, ToList, and Remove are used for interacting with the database. Here's a brief explanation of each method as implemented in the FreelancersController:

1. Add
Add is used to insert a new entity into the database.

_mainAppContext.Freelancers.Add(freelancer);
_mainAppContext.SaveChanges();

This code adds a new Freelancer object to the Freelancers DbSet. When SaveChanges is called, EF generates an INSERT SQL command to add this new record to the corresponding database table.

2. Update
Update is used to modify an existing entity in the database.
`_mainAppContext.SaveChanges();`
EF tracks changes to the entity and generates an UPDATE SQL command when SaveChanges is invoked.

3. ToList
ToList is used to execute a query and retrieve all entities as a list.
`var data = _mainAppContext.Freelancers.ToList();`
This code retrieves all records from the Freelancers DbSet and converts them into a list. EF constructs a SELECT SQL query to fetch the data from the database, and ToList executes that query.

4. Remove
Remove is used to delete an entity from the database.

_mainAppContext.Remove(f);
_mainAppContext.SaveChanges();

This code removes the specified Freelancer object from the Freelancers DbSet. When SaveChanges is called, EF generates a DELETE SQL command to remove the record from the database.

### DTO (Data Transfer Object)
A DTO is an object that carries data between processes, often used to transfer data in a structured format. It helps to decouple data representation from the underlying data model and can reduce the amount of data sent over the network.
Usage in Controller: In the Register method, FreelancerDTO is used to capture user input data without exposing the entire entity structure. This enhances security and allows for flexible data management.

[HttpPost("Register")]
public IActionResult Register([FromBody] FreelancerDTO freelancerDTO)

This allows the controller to only receive the necessary fields from the client, ensuring that sensitive data (like passwords) can be handled appropriately.

### Include for Relationships
The Include method is used in Entity Framework to load related data from the database. It allows you to specify which related entities should be included in the query results, enabling eager loading of related data.

_mainAppContext.Clients.Include(c => c.Projects)

This retrieves Clients along with their associated Projects, which is essential for understanding the relationships between the entities.

### Query Parameter Handling
Depending on `Mode` value, the method retrieves different data structures:
* If Mode is "basic", it retrieves a simpler list of clients without projects.
* If Mode is "r", it retrieves clients along with their associated projects.

### Select Method for Mapping
The Select method is used to project each element of a sequence into a new form. In this context, it's used to map the Client entity properties to the ClientDTO properties.

.Select(c => new ClientDTO
{
    Id = c.Id,
    CompanyName = c.CompanyName,
    Name = c.Name,
    Username = c.Username,
    Projects = c.Projects.Select(p => new ProjectOutDTO
    {
        Id = p.Id,
        Title = p.Title,
        Description = p.Description,
    }).ToList()
})

This mapping ensures that only the necessary data is returned to the client while maintaining a clear structure.

## Async Programming
1. Importance of Async Programming for Performance
Asynchronous programming is crucial for improving application performance, especially in web applications where multiple I/O-bound operations occur. Traditional synchronous methods block the execution thread until the operation completes, which can lead to:

Thread Blocking: While waiting for an I/O operation (like database access or file reading), the thread is idle and cannot handle other requests. This can lead to poor resource utilization and slower response times, particularly under heavy load.
Scalability Issues: In high-traffic applications, blocking threads can lead to exhaustion of available threads in the thread pool, making the application unable to serve new requests.

2. How Asynchronous Programming Works
Asynchronous programming allows an application to perform operations without blocking the execution thread. Here’s how it works:

Task-Based Asynchronous Pattern (TAP): In .NET, async methods return a Task or `Task<T>`, which represents an ongoing operation. The method can be awaited, allowing other operations to run concurrently while waiting for the I/O operation to complete.
Event Loop: The runtime uses an event loop to manage the execution of asynchronous tasks. When an asynchronous operation is awaited, control returns to the calling method, allowing it to continue executing other code until the awaited operation completes.
3. Enhancing Performance with I/O Operations

**Asynchronous programming is particularly beneficial for I/O-bound operations, such as:**
* Database Queries: When querying a database, the application sends a request to the database server and waits for a response. If this operation is performed asynchronously, the application can continue processing other requests while waiting for the database to respond.
* File I/O: Reading or writing files can also benefit from asynchronous operations. By not blocking the thread, the application can handle other user interactions or background tasks while waiting for the file operation to complete.

### Async Implementation
Here’s how to implement asynchronous programming in your GetAll action method of the ClientsController to handle I/O operations effectively:

[HttpGet]
public async Task<IActionResult> GetAll([FromQuery] string? Mode) {
    var ClientList = new List<ClientDTO>();
    if(Mode == null || Mode == "basic")
    {
        ClientList = await _mainAppContext.Clients
            .Include(c => c.Projects)
            .Select(c => new ClientDTO
            {
                Id = c.Id,
                CompanyName = c.CompanyName,
                Name = c.Name,
                Username = c.Username
            })
            .ToListAsync(); // Asynchronous call to fetch data
    }
    if(Mode == "r")
    {
        ClientList = await _mainAppContext.Clients
            .Include(c => c.Projects)
            .Select(c => new ClientDTO
            {
                Id = c.Id,
                CompanyName = c.CompanyName,
                Name = c.Name,
                Username = c.Username,
                Projects = c.Projects.Select(p => new ProjectOutDTO
                {
                    Id = p.Id,
                    Title = p.Title,
                    Description = p.Description,
                }).ToList()
            })
            .ToListAsync(); // Asynchronous call to fetch data
    }
   
    return Ok(ClientList);
}

* Task-Based Method: The GetAll method is defined to return `Task<IActionResult>`, indicating it is asynchronous.
* Awaiting I/O Operations: The await keyword is used before the ToListAsync() method, which asynchronously retrieves the data from the database. This allows other requests to be processed while waiting for the database operation to complete.
* Non-Blocking Behavior: By using asynchronous calls, the method can handle multiple requests concurrently, improving overall application responsiveness and scalability.

## Input Validation
Input validation is crucial for maintaining data integrity and consistency within an application. It helps ensure that the data received from clients—whether through JSON payloads or query parameters—meets certain criteria before being processed or stored. This reduces the risk of errors, corruption, or unexpected behavior in the application.

Validating input helps protect applications from common security vulnerabilities, such as:

* SQL Injection: By ensuring only valid data is processed, you can reduce the risk of SQL injection attacks where malicious input is used to manipulate database queries.
* Cross-Site Scripting (XSS): Validating and sanitizing input prevents attackers from injecting malicious scripts into web pages that can be executed in the browsers of unsuspecting users.
* Data Breaches: Validating inputs helps ensure sensitive data is not inadvertently processed or exposed due to improperly formatted or unexpected data.

In ASP.NET Core, data annotations provide a straightforward way to enforce validation rules on model properties. These annotations can be applied directly to the properties of your DTOs (Data Transfer Objects), ensuring that any incoming data is validated automatically before processing.

public class ProjectInputDTO
{
    [Required(ErrorMessage = "Title is required.")]
    public string Title { get; set; }

    [AllowNull]
    public string Description { get; set; }

    [Range(1, int.MaxValue, ErrorMessage = "ClientId must be a valid positive integer.")]
    public int ClientId { get; set; } // Foreign Key
}

Data Annotations Explanation:

[Required]: This annotation indicates that the Title property is mandatory. If the incoming JSON does not include a value for Title, the validation will fail, and an error message will be returned.

[AllowNull]: This indicates that the Description property can be null, meaning it's optional.

[Range]: This specifies that ClientId must be a positive integer greater than or equal to 1. It helps ensure that only valid foreign key values are submitted.

[Route("api/[controller]")]
[ApiController]
public class ProjectsController : ControllerBase
{
    private readonly MainAppContext _mainAppContext;
    
    public ProjectsController(MainAppContext mainAppContext)
    {
        _mainAppContext = mainAppContext;
    }

    [HttpPost]
    public IActionResult CreateProject([FromBody] ProjectInputDTO project)
    {
        // Validate the model state
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState); // Return validation errors
        }

        Project p = new Project
        {
            Title = project.Title,
            Description = project.Description,
            ClientId = project.ClientId
        };

        _mainAppContext.Projects.Add(p);
        _mainAppContext.SaveChanges();
        return Ok(p);
    }

    [HttpGet("{id}")]
    public IActionResult GetProject(int id)
    {
        var project = _mainAppContext.Projects
            .Include(p => p.Client)
            .FirstOrDefault(p => p.Id == id);

        return Ok(project);
    }
}

* Model Validation: The CreateProject method first checks if the ModelState is valid. If not, it returns a BadRequest response with the validation errors. This step is essential for ensuring that only valid data is processed.

* Error Messages: When using data annotations, you can customize the error messages returned to the client by setting the ErrorMessage property within the annotation.

## Task 06 - Project Wrap-Up: Implement Missing Functionality
- Complete Endpoitns and CRUD operations for `Clients,Freelancers, Projects`
- Implement `async` programming in all the controllers/actions methods that include I/O operations.
- Implement relationships in the entities:
- User has one freelancer record
- User has one client record
- User has one System User record
- Freelancer have many projects
- Client have many projects

- Implement DTOs for representing request input and response output
- Implement endpoint `api/v1/freelancers/{id}?loadProjects=x`, where x = 1 or 0
- Implementation input validation on `loadProjects` query parameter as below:
- Mandatory
- 0 or 1 values only

**Getting Started**
- Sync fork wth origin repo
- Clone forked repo into your machine if you didn't clone it yet, or `git pull` to update your local repo.
- Create new branch:
- First make sure that no changes unstaged or need commitments on your current status of the repo.
- run the command to create new branch `git checkout -b task06/YOUR_NAME`
- Run `git branch` to make sure that you are in the correct branch.
- Implement requirements and do your changes.
- Once you complete, commit your changes and push them on the branch as below

`git add .`
`git commit -m 'your message'`
`git push origin task06/YOUR_NAME`

- Now after pushing success, go to your repo on GitLab, Create `Pull Request` to the original repo.

- Submit the form.

Deadline is Thu 30/10/2024.