Photo by Luca Bravo on Unsplash
Using CRUD Operations in Entity Framework with In-Memory Database in Web API
If you are short on time, you can directly view the code repo here.
Introduction
In this article, we will go over how to implement CRUD operations in Web API with the help of Entity Framework. Furthermore, we will use the In Memory database provided by Entity Framework. In a later article, we will use a real database such as SQL Server.
Our Web API will provide endpoints to perform CRUD operations on the employee database. We will have two underlying tables or entities - Employee and Address. There will be a 1-to-many (1:N) relationship between Employee and Address i.e. one employee can have multiple addresses such as work, mailing, and permanent address.
Entity Framework (EF)
Entity Framework is a powerful and widely used Object-Relational Mapping (ORM) framework in the .NET ecosystem. It simplifies database operations by providing a high-level, object-oriented API for interacting with relational databases. In this article, we will explore how to use CRUD (Create, Read, Update, Delete) operations in Entity Framework within a Web API project, with a focus on an in-memory database.
In-memory
In Entity Framework (EF), an in-memory database is a type of database provider that allows you to work with a database that exists solely in memory, rather than being stored persistently on disk. It's often used for testing, development, and prototyping scenarios where you need a lightweight and transient database that can be created, used, and discarded quickly. It is useful when you want to quickly test out your Entity Framework flow, without needing to connect to a real database.
Step 1. Setting up the project
You need to create a basic Web API project using Visual Studio.
Add the Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.InMemory nuget packages to your project
Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.InMemory
These packages are crucial for enabling Entity Framework functionality and utilizing an in-memory database for testing.
The .csproj file should look like this afterwards:
<Project Sdk="Microsoft.NET.Sdk.Web">
<PropertyGroup>
<TargetFramework>net6.0</TargetFramework>
<Nullable>enable</Nullable>
<ImplicitUsings>enable</ImplicitUsings>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.10" />
<PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="7.0.10" />
<PackageReference Include="Swashbuckle.AspNetCore" Version="6.2.3" />
</ItemGroup>
</Project>
Step 2. Define the EF data models, relationships and DbContext
Let us create data model classes (entities) for use with Entity Framework.
Create Employee model
We will create an Employee class as our data model.
using System.ComponentModel.DataAnnotations;
namespace WebApiEntityFramework.Models
{
public class Employee
{
[Key]
public string EmployeeId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int Age { get; set; }
public string EmailAddress { get; set; }
}
}
We have added EmployeeId as the primary key by adding the [Key] attribute above it.
Create Address model
Let us now create an Address class which will store the employee address(es).
using System.ComponentModel.DataAnnotations;
namespace WebApiEntityFramework.Models
{
public class Address
{
[Key]
public int AddressId { get; set; }
public string AddressType { get; set; }
public string FullAddress { get; }
public string City { get; set; }
public string State { get; set; }
public string PostalCode { get; set; }
public string Country { get; set; }
}
}
Add Foreign key relationship (1:N) between Employee and Address entities
An employee can have multiple addresses such as home, work, mailing etc. So there exists a 1:N (one-to-many) relationship between Employee and Address.
If you imagine Employee table and Address table in a database, then Address table will have a foreign key column EmployeeId which will establish the relationship. However, in an EF model, we also need to provide navigation properties in both classes.
Navigational properties provide a more natural and intuitive way to navigate relationships between entities. Instead of writing complex SQL queries or joining tables manually, you can simply use properties of your entity classes to access related data.
Let us add the foreign key and navigation properties.
Employee.cs
public class Employee
{
[Key]
public string EmployeeId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int Age { get; set; }
public string EmailAddress { get; set; }
//navigation property
public ICollection<Address> Addresses { get; set; }
}
Since one employee can have multiple addresses, the new navigation property Addresses provides a way to access the associated addresses for a given employee
Address.cs
public class Address
{
[Key]
public int AddressId { get; set; }
public string AddressType { get; set; }
public string FullAddress { get; }
public string City { get; set; }
public string State { get; set; }
public string PostalCode { get; set; }
public string Country { get; set; }
[ForeignKey("Employee")]
public string EmployeeId { get; set; } //foreign key
public Employee Employee { get; set; } //navigation property
}
We have the EmployeeId as the foreign key, marked with the [ForeignKey] attribute. Then we have the navigation property Employee to allow us to access the Employee associated with this address.
Add DbContext
Next, let us add our DbContext class, which will connect to the underlying database. Since we are connecting to the In-Memory database, I will name our class as InMemoryDbContext
using Microsoft.EntityFrameworkCore;
using WebApiEntityFramework.Models;
namespace WebApiEntityFramework.DatabaseContext
{
public class InMemoryDbContext : DbContext
{
public DbSet<Employee> Employees { get; set; }
public DbSet<Address> Addresses { get; set; }
}
}
In this DbContext class, we've defined a DbSet property for the Employee entity, and another DbSet property for the Address entity, allowing Entity Framework to create tables for employees and Addresses in our database.
Let us add a OnConfiguring() function to specify that our db context class is going to connect to an In-Memory database.
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
//specify in-memory database
optionsBuilder.UseInMemoryDatabase(databaseName: "EmployeeDb");
}
Let us also add a unique key constraint on our employees table in the OnModelCreating() function (this is an optional step as I just want to show how to add such a constraint if needed)
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
//add unique index
modelBuilder.Entity<Employee>()
.HasIndex(e => new { e.FirstName, e.LastName, e.EmailAddress })
.IsUnique(true);
}
Here, we are adding a unique index on employee's firstname, lastname, and email address.
Our db context is now ready and looks like this:
using Microsoft.EntityFrameworkCore;
using WebApiEntityFramework.Models;
namespace WebApiEntityFramework.DatabaseContext
{
public class InMemoryDbContext : DbContext
{
public DbSet<Employee> Employees { get; set; }
public DbSet<Address> Addresses { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
//specify in-memory database
optionsBuilder.UseInMemoryDatabase(databaseName: "EmployeeDb");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
//add unique index
modelBuilder.Entity<Employee>()
.HasIndex(e => new { e.FirstName, e.LastName, e.EmailAddress })
.IsUnique(true);
}
}
}
Step 3. Create a Repository pattern to perform CRUD operations on the employee database
Implementing a repository pattern is a common practice to abstract data access operations and decouple your controllers from the DbContext. This can make your code more maintainable and testable.
Create a generic Repository Interface
Let us define an interface for our repository. This interface will declare the methods for CRUD operations:
public interface IRepository<TEntity> where TEntity : class { Task<IEnumerable<TEntity>> GetAllAsync(); Task<TEntity> GetByIdAsync(object id); Task CreateAsync(TEntity entity); Task UpdateAsync(TEntity entityToUpdate); Task DeleteAsync(object id); //extra functions Task CreateAsync(IEnumerable<TEntity> entities); }Implement the IRepository interface
Next, create a concrete implementation of the generic
IRepositoryinterface. This implementation will use the DbContext to interact with the database. Let us call thisEFRepository. If in future, we want to implement a non-EF repository, we can easily do that as well, as IRepository interface is not tightly coupled with EF. For example, we can decide to go with MongoRepository, or AzureDbRepository. But for now, we will go with EFRepository.public class EFRepository<TEntity> : IRepository<TEntity> where TEntity : class { private readonly DbContext _context; private readonly DbSet<TEntity> _dbSet; public EFRepository(InMemoryDbContext context) { _context = context; _dbSet = _context.Set<TEntity>(); } public async Task<IEnumerable<TEntity>> GetAllAsync() { return await _dbSet.ToListAsync(); } public async Task<TEntity> GetByIdAsync(object id) { return await _dbSet.FindAsync(id); } public async Task CreateAsync(TEntity entity) { _dbSet.AddAsync(entity); await _context.SaveChangesAsync(); } public async Task UpdateAsync(TEntity entityToUpdate) { _dbSet.Attach(entityToUpdate); _context.Entry(entityToUpdate).State = EntityState.Modified; await _context.SaveChangesAsync(); } public async Task DeleteAsync(object id) { var entity = await _dbSet.FindAsync(id); if (entity != null) { _dbSet.Remove(entity); await _context.SaveChangesAsync(); } } //extra functions public async Task CreateAsync(IEnumerable<TEntity> entities) { await _dbSet.AddRangeAsync(entities); await _context.SaveChangesAsync(); } }The repository pattern often uses generic types to provide a flexible and reusable data access mechanism. There are several reasons for using generic types in the repository pattern:
Reusability: Generic repositories can be used with different entities/models without the need to create a separate repository for each entity. This promotes code reuse and reduces the need to write repetitive data access code. In our case, we can reuse the same repository class for our Employee and Address entities.
Type Safety: Generics provide strong typing, which means you can specify the type of entities you want to work with when creating a repository instance. This helps catch type-related errors at compile time rather than runtime.
Flexibility: With generic repositories, you can work with various types of entities while maintaining a consistent and uniform interface for CRUD operations. This simplifies the codebase and makes it more maintainable.
Reduced Boilerplate Code: Using generics eliminates the need to write separate repository methods for each entity. Instead, you can create a single generic repository class that handles CRUD operations for any entity type.
Create IEmployeeRepository (and IAddressRepository interfaces if needed)
This IEmployeeRepository simply inherits from IRepository, and is specifically meant for Employee related CRUD operations. This will help us during Dependency injection for Employee and Address repositories.
public interface IEmployeeRepository : IRepository<Employee> { }Similarly, we can create
IAddressRepository, although we don't need this repo as of now.public interface IAddressRepository : IRepository<Address> { }Implement the IEmployeeRepository interface
The implementation of IEmployeeRepository will inherit all the functions of EFRepository, the sole purpose of this design is to allow easy dependency injection for Employee and Address repositories.
public class EmployeeEFRepository : EFRepository<Employee>, IEmployeeRepository { public EmployeeEFRepository(InMemoryDbContext context) : base(context) { } }However, we will have to override the Get functions in this repo, because we want to include Addresses field also as part of Employee data fetch. By default, EF does not fetch nested entities, so we will need to add the
Includefunction to force it to include the Addresses property.public class EmployeeEFRepository : EFRepository<Employee>, IEmployeeRepository { public EmployeeEFRepository(InMemoryDbContext context) : base(context) { } public override async Task<IEnumerable<Employee>> GetAllAsync() { return await _dbSet.Include(e => e.Addresses).ToListAsync(); } public override async Task<Employee> GetById(string id) { return await _dbSet.Include(e => e.Addresses).FirstOrDefaultAsync(e => e.EmployeeId == id); } }But this also means we will have to change the accessibility qualifiers in the EFRepository class
public class EFRepository<TEntity> : IRepository<TEntity> where TEntity : class { protected readonly DbContext _context; //added protected protected readonly DbSet<TEntity> _dbSet; //added protected //added virtual public virtual async Task<IEnumerable<TEntity>> GetAllAsync() { ... } //added virtual public virtual async Task<TEntity> GetById(string id) { ... } //rest of the code }Add the newly created classes to Dependency Injection
In the Program class, add the DI as shown below
// Add dbContext builder.Services.AddDbContext<InMemoryDbContext>(); // Add repositories builder.Services.AddScoped<IEmployeeRepository, EmployeeEFRepository>(); builder.Services.AddScoped<IAddressRepository, AddressEFRepository>();
Step 4. Implementing CRUD Operations in Controller
Now that we have set up our project and configured Entity Framework with an in-memory database, let's implement CRUD operations.
Let us create an EmployeesController class which will hold an instance of EmployeeRepository to perform CRUD operations.
[ApiController]
[Route("[controller]")]
public class EmployeesController : ControllerBase
{
private readonly IEmployeeRepository _employeeRepository;
private readonly ILogger<EmployeesController> _logger;
public EmployeesController(ILogger<EmployeesController> logger, IEmployeeRepository employeeRepository)
{
_logger = logger;
_employeeRepository = employeeRepository;
}
}
Get all employees
/// <summary>
/// Returns a list of all employees
/// </summary>
/// <returns></returns>
[HttpGet]
[ProducesResponseType(typeof(EmployeeResponseDto), 200)]
public async Task<IActionResult> GetEmployees()
{
var employees = await _employeeRepository.GetAllAsync();
var response = employees.Select(emp => (EmployeeResponseDto)emp).ToList();
return Ok(response);
}
If you notice, we are returning employee data in the form of EmployeeResponseDto which is a class we have defined as shown below. The purpose of a DTO (data transfer object) class is to create separation between the data entities from EF and the response we are sending to the user.
EmployeeResponseDto.cs
public class EmployeeResponseDto
{
public string EmployeeId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int Age { get; set; }
public string EmailAddress { get; set; }
public IEnumerable<AddressDto>? Addresses { get; set; }
public static implicit operator EmployeeResponseDto(Employee employee)
{
return new EmployeeResponseDto
{
EmployeeId = employee.EmployeeId,
FirstName = employee.FirstName,
LastName = employee.LastName,
Age = employee.Age,
EmailAddress = employee.EmailAddress,
Addresses = employee.Addresses.Select(addr => (AddressDto)addr).ToList()
};
}
public static implicit operator Employee(EmployeeResponseDto employeeDto)
{
return new Employee
{
EmployeeId = employeeDto.EmployeeId,
FirstName = employeeDto.FirstName,
LastName = employeeDto.LastName,
Age = employeeDto.Age,
EmailAddress = employeeDto.EmailAddress
};
}
}
We are using implicit converters to allow conversion between Employee and EmployeeResponseDto
Similarly, we have an AddressDto class
AddressDto.cs
public class AddressDto
{
public string AddressType { get; set; }
public string AddressLine1 { get; set; }
public string? AddressLine2 { get; set; }
public string City { get; set; }
public string State { get; set; }
public int PostalCode { get; set; }
public string Country { get; set; }
public static implicit operator Address(AddressDto addressDto)
{
return new Address
{
AddressType = addressDto.AddressType,
AddressLine1 = addressDto.AddressLine1,
AddressLine2 = addressDto.AddressLine2,
City = addressDto.City,
State = addressDto.State,
PostalCode = addressDto.PostalCode,
Country = addressDto.Country
};
}
public static implicit operator AddressDto(Address address)
{
return new AddressDto
{
AddressType = address.AddressType,
AddressLine1 = address.AddressLine1,
AddressLine2 = address.AddressLine2,
City = address.City,
State = address.State,
PostalCode = address.PostalCode,
Country = address.Country
};
}
}
Get employee by id
/// <summary>
/// Returns details of a single employee based on input employeeId
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
[HttpGet("{id}")]
[ProducesResponseType(typeof(EmployeeResponseDto), 200)]
[ProducesResponseType(404)]
public async Task<IActionResult> GetEmployeeById(string id)
{
EmployeeResponseDto employee = await _employeeRepository.GetById(id);
if (employee == null)
{
_logger.LogInformation("Unable to find Employee with requested employee id", id);
return NotFound();
}
return Ok(employee);
}
Create a new employee record
Here, we should not allow end user to pass an object of type EmployeeInfo because this object has id property as well, which we don't want user to define. Not only that, but we also need to enforce some rules on what kind of data is allowed to be passed by the user via data-annotations. To do all this, we will create new DTO (data transfer object) models, called EmployeeRequestDto and AddressDto
EmployeeRequestDto.cs
public class EmployeeRequestDto
{
[Required]
[StringLength(50, MinimumLength = 1)]
public string FirstName { get; set; }
[Required]
[StringLength(50, MinimumLength = 1)]
public string LastName { get; set; }
public int Age { get; set; }
[Required]
[EmailAddress]
public string EmailAddress { get; set; }
public IEnumerable<AddressDto> Addresses { get; set; }
public static implicit operator Employee(EmployeeRequestDto employeeDto)
{
return new Employee
{
FirstName = employeeDto.FirstName,
LastName = employeeDto.LastName,
Age = employeeDto.Age,
EmailAddress = employeeDto.EmailAddress,
Addresses = employeeDto.Addresses.Select(addr => (Address) addr).ToList()
};
}
}
Now let's add an endpoint to create a new employee record.
EmployeeController.cs
/// <summary>
/// Adds a new employee to employee table
/// </summary>
/// <param name="employee"></param>
/// <returns></returns>
[HttpPost]
[ProducesResponseType(201)]
[ProducesResponseType(400)]
public async Task<IActionResult> AddEmployee(EmployeeRequestDto employeeRequest)
{
if (!ModelState.IsValid)
{
return BadRequestErrorMessages();
}
Employee employee = employeeRequest;
employee.EmployeeId = Guid.NewGuid().ToString();
employee.Addresses.ToList().ForEach(addr =>
{
addr.EmployeeId = employee.EmployeeId;
addr.AddressId = Guid.NewGuid().ToString();
});
var isUnique = await IsRecordUnique(employee);
if (isUnique)
{
await _employeeRepository.CreateAsync(employee);
EmployeeResponseDto employeeResponse = employee;
_logger.LogInformation("New Employee added with employee id", employee.EmployeeId);
return CreatedAtAction(nameof(GetEmployeeById), new { id = employeeResponse.EmployeeId }, employeeResponse);
}
else
{
return Conflict("Employee with same details already exists");
}
}
Notice how we are calling IsRecordUnique function to check if the employee is unique, based on firstname, lastname, and email address. If you remember, we have already added a unique constraint in our Entity Framework's InMemoryDbContext. However, for in memory database like this, the unique constraints are not enforced by EF - it only works for real databases. So, for now, we will call this function as a workaround to check for uniqueness. In the next article when we add a real database, we won't need this check.
/// <summary>
/// This is a workaround function to check for uniqueness
/// This is being used because adding constraints in Entity Framework
/// doesn't work for In-Memory Database, which is what we are using here
/// </summary>
/// <param name="employee"></param>
/// <param name="id"></param>
/// <returns></returns>
private async Task<bool> IsRecordUnique(EmployeeInfo employee, string id = "")
{
var existingEmployees = await _employeeRepository.GetAllAsync();
var isAlreadyPresent = existingEmployees.Any(emp => emp.FirstName == employee.FirstName
&& emp.LastName == employee.LastName
&& emp.EmailAddress == employee.EmailAddress && emp.EmployeeId != id);
return !isAlreadyPresent;
}
Notice also, how if the incoming request is bad, we are calling a BadRequestErrorMessages function. Below is the definition of this function
private IActionResult BadRequestErrorMessages()
{
var errMsgs = ModelState.Values.SelectMany(v => v.Errors.Select(e => e.ErrorMessage));
_logger.LogInformation("Bad request received", errMsgs);
return BadRequest(errMsgs);
}
Update an employee record
/// <summary>
/// Update an employee's details based on employee id
/// </summary>
/// <param name="id"></param>
/// <param name="employee"></param>
/// <returns></returns>
[HttpPut("{id}")]
[ProducesResponseType(204)]
[ProducesResponseType(400)]
[ProducesResponseType(404)]
public async Task<IActionResult> UpdateEmployee(string id, EmployeeRequestDto employeeRequest)
{
if (!ModelState.IsValid)
{
return BadRequestErrorMessages();
}
var employeeToUpdate = await _employeeRepository.GetById(id);
if (employeeToUpdate == null)
{
_logger.LogInformation("UpdateEmployee: Unable to find Employee with employee id", id);
return NotFound();
}
var isUnique = await IsRecordUnique(employeeRequest, id);
if (isUnique)
{
employeeToUpdate.FirstName = employeeRequest.FirstName;
employeeToUpdate.LastName = employeeRequest.LastName;
employeeToUpdate.Age = employeeRequest.Age;
employeeToUpdate.EmailAddress = employeeRequest.EmailAddress;
employeeToUpdate.Addresses = employeeRequest.Addresses.Select(addr => (Address)addr).ToList();
employeeToUpdate.Addresses.ToList().ForEach(addr =>
{
addr.EmployeeId = id;
addr.AddressId = Guid.NewGuid().ToString();
});
await _employeeRepository.UpdateAsync(employeeToUpdate);
}
else
{
return Conflict("Employee with same details already exists");
}
return NoContent();
}
Delete an employee record
/// <summary>
/// Delete an employee by id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
[HttpDelete("{id}")]
[ProducesResponseType(204)]
[ProducesResponseType(400)]
[ProducesResponseType(404)]
[ProducesResponseType(500)]
public async Task<IActionResult> Deleteemployee(string id)
{
var employeeToDelete = await _employeeRepository.GetById(id);
if (employeeToDelete == null)
{
_logger.LogInformation("DeleteEmployee: Unable to find Employee with employee id", id);
return NotFound();
}
await _employeeRepository.DeleteAsync(employeeToDelete);
return NoContent();
}
And viola, your EF API is ready. When you run the project, it should open a Swagger page on your browser:
You can try out the various operations by clicking on each option. Note that there is some additional logic such as adding seed data, adding global error handler, that you can check out from the repo that I have shared at the beginning of this article.