Querying data is one of the primary tasks performed with Entity Framework Core. We'll focus on simple queries using LINQ, which seamlessly translates into SQL queries. Let's explore some examples:

GET

To retrieve a user by their ID:

public async Task<User?> GetById(int id)
    => await _context.Users
        .FirstOrDefaultAsync(x => x.Id == id);

In this example, we fetch all users who have job experience related to the government:

public async Task<List<User>> GovernmentUsers()
    => await _context.Users
        .Include(a => a.JobExperiences
            .Where(we => we.Name == "Government"))
        .ToListAsync();

Notice how we can link multiple tables using the Include method. Be cautious with such includes, as they can impact performance. Always verify and optimize your queries.

INSERT

Inserting records into the database is straightforward with Entity Framework Core. Here's an example of how to add a user entity and save it to the database:

public async Task<User> Insert(User user)
{
    EntityEntry<User> insertedUser = await _context.Users.AddAsync(user);
    await _context.SaveChangesAsync();
    return insertedUser.Entity;
}

Remember, if you're using the Unit of Work pattern, the changes are saved at the unit of work level rather than the repository level.

UPDATE

To update a record, we first retrieve the existing entity, make the necessary modifications, and then save it back to the database. Here's an example using the Unit of Work pattern:

public class UpdateUserEmail
{
    private readonly IUnitOfWork _unitOfWork;

    public UpdateUserEmail(IUnitOfWork unitOfWork)
    {
        _unitOfWork = unitOfWork;
    }

    public async Task<bool> Execute(int userId, string newEmail)
    {
        User? user = await _unitOfWork.UserRepository.GetById(userId);
        if (user != null)
        {
            user.Email = newEmail;
            _unitOfWork.UserRepository.Update(user);
            await _unitOfWork.Save();
        }

        return true;
    }
}

It's important to note that we're using the repository pattern along with the unit of work pattern. The repository must include the update operation:

public void Update(User user)
{
    _context.Users.Update(user);
}

To complete the implementation, we can create a PUT endpoint in our API:

[Route("[controller]")]
public class UsersController : Controller
{
    private readonly UpdateUserEmail _updateUserEmail;

    public UsersController(UpdateUserEmail updateUserEmail)
    {
        _updateUserEmail = updateUserEmail;
    }

    [HttpPut("update-email/{id}")]
    public async Task<bool> UpdateEmail(int id, string newEmail)
        => await _updateUserEmail.Execute(id, newEmail);
}

DELETE

Deleting records is as simple as adding or modifying them. LINQ provides the necessary tools, such as the Remove method. Here's an example:

public async Task Delete(int id)
{
    User? user = await _context.Users
        .FirstOrDefaultAsync(x => x.Id == id);

    if (user != null)
        _context.Users.Remove(user);

    await _context.SaveChangesAsync();
}

It's important to note that this action performs a "hard delete," completely removing the record from the database. In some cases, a "soft delete" approach may be preferred, marking the record as inactive instead of deleting it entirely.

Implementing Soft Delete in Entity Framework Core

Soft delete allows us to mark records as deleted without physically removing them from the database. Let's see how to implement it in Entity Framework Core.

First, we add properties to our entities to support soft delete. It's recommended to have a base class for all entities:

public abstract class BaseEntity
{
    public bool IsDeleted { get; set; }
    public DateTime DeletedTimeUtc { get; set; }
}

Each entity should then inherit from this base class:

public class User : BaseEntity
{
    // ...
}

public class JobExperience : BaseEntity
{
    // ...
}

To perform a soft delete, we update the IsDeleted property to true and set the DeletedTimeUtc:

public async Task<bool> Delete(int id)
{
    User? user = await _context.Users
        .FirstOrDefaultAsync(x => x.Id == id);

    if (user == null)
        return false;

    user.IsDeleted = true;
    user.DeletedTimeUtc = DateTime.UtcNow;

    _context.Users.Update(user);
    return true;
}

Don't forget to run the necessary migrations to update the database schema.

To hide soft-deleted records, we can configure a global query filter in the OnModelCreating method of our DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>()
        .HasQueryFilter(a => !a.IsDeleted);   
}

The HasQueryFilter method adds a filter to all SQL queries executed on that entity. This approach helps avoid modifying all individual queries and includes.

Limitations of Global Filters

It's essential to be aware of the limitations of global filters. Directly executing SQL queries or using the IgnoreQueryFilters function can bypass these filters.