Queries and Data Manipulation with Entity Framework Core

It’s true that I have created a large part of this course while skipping one of the most important sections of all, which is essentially how we perform queries or manipulate data in Entity Framework.

To understand this post, I recommend having followed most of the course, but at a minimum understand how DbContext and DbSet work, as well as the data model we are going to use.

Personally, I think it’s advisable to have an understanding or knowledge of both the repository pattern and the UnitOfWork pattern.

Before we begin, note that the examples will be purely in Entity Framework but for some of them, the code will contain part of the Web API so you have more context within a real application. 

1 - Perform queries with Entity Framework Core

The main or most common action we’re going to perform with Entity Framework is queries. For now, we’ll focus on simple queries and for that we can use LINQ. For those who don’t know, LINQ is a query language in C# and in the case of Entity Framework, it translates a LINQ query into a SQL query. 

• Here you can find a post with more information about LINQ.

In our particular case, we want to query a user by their ID:

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

Or in this other case, we’re getting all users whose work experience name is “Government”, that is, if they have worked for the government.

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

Also, as you can see, in this example we’re joining two different tables through the Include method.

• Note: You should be careful when doing these types of includes with filters and such, as they can cause major performance issues. Always check the executed query and make sure it’s as efficient as possible.

2 - Create records in the database with Entity Framework Core

What we’ll do here is an insert, and the way to do this is really simple.

We need to build the entity object in the code and then add it to the corresponding DbSet. Once added, just save with SaveChanges.

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

Remember that if you use the unit of work pattern, you don’t save in the repository, you save at the unit of work level. 

3 - Modify records in a database with Entity Framework Core

To modify a record, we need to read an existing one (as we saw in the first section), modify it, and save it.

For this example, I’m going to create a service called UpdateUserEmail that uses unit of work.

In this service, we have a single method that receives the user’s ID and the new email to then update it in the database.

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;
    }
}

One important thing to consider here is that we’re using the repository pattern with the unit of work pattern, so you need to modify the repository to include the update.

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

And to finish the implementation, you simply create a PUT endpoint in the 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);
}

• NOTE: All databases have what’s called a concurrency conflict, which happens when two update actions occur at the same time. Entity Framework Core includes a way to avoid such concurrency errors, but we’ll cover that in a separate post.

4 - Remove records from a database with Entity Framework Core

Deleting is as simple as adding or modifying because LINQ provides everything we need, which is the Remove method. First, you need to pass it the entity, just like when modifying:

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();
}

An important detail to keep in mind is that this action performs what’s called a hard delete, that is, it removes the record from the database. In your companies it’s very likely you are doing Soft-Deletes, which means marking the record as inactive instead of removing it completely. We’ll cover this in another post.