Create CRUD API endpoints with ASP.NET Core & Entity Framework

Building up an API can involve a lot of create, read, update and delete methods. And, if your API has a lot of endpoints associated with it, all with similar methods, it can build up a lot of duplicate code.

We did a live stream, where we built up generic entities, services and controllers. This allowed us to reduce duplicate code and make it quicker to set up when we want to set up an entity with CRUD endpoints.

We are now going to run through the steps on how to do this.

Creating Projects

The first thing we need to do is to create an ASP.NET Core API project in Visual Studio 2019. Selecting ASP.NET Core Web Application and API will allow us to do this.

This will create our solution and API. We now need to add a new project to the solution. We named the solution "RoundTheCode.CrudApi" and the project "RoundTheCode.CrudApi.Web".

Data Class Library

Next, we need to add a new .NET Core Class Library to our solution. We will call it "RoundTheCode.CrudApi.Data".

Now that we've got that set up, we can go ahead and implements our entities and DbContext into our "RoundTheCode.CrudApi.Data" project.

Base Class and Interface

The next step we need to take is to build up our Base class and interface. Now the Base class will store all the properties that we need for all our entities. Examples include a unique ID and a created timestamp.

// IBase.cs
public interface IBase
{
	int Id { get; set; }

	DateTimeOffset Created { get; set; }

	DateTimeOffset? LastUpdated { get; set; }

	DateTimeOffset? Deleted { get; set; }
}

// Base.cs
public abstract class Base : IBase
{
	public int Id { get; set; }

	public DateTimeOffset Created { get; set; }

	public DateTimeOffset? LastUpdated { get; set; }

	public DateTimeOffset? Deleted { get; set; }
}

You may have noticed that we made the Base class abstract. This is because this class will not be used as an entity directly in our DbContext. Instead, it will be used as a class that is inherited from an entity in our DbContext.

Installing Entity Framework

Afterwards, we need to install Microsoft Entity Framework Core into our "RoundTheCode.CrudApi.Data" project. Now, there are a number of ways you can do this. For this example, we used the Package Manager Console. You can find it in Visual Studio 2019 by going to Tools, Nuget Package Manager and selecting Package Manager Console.

From there, we can run the following Powershell commands to install Entity Framework. These need to be installed into our "RoundTheCode.CrudApi.Data" project.

Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools

As well as that, we need to install another package. The "Microsoft.Extensions.Configuration.Json" package allows us to build up a configuration from our appsettings.json file.

Install-Package Microsoft.Extensions.Configuration.Json

Preparing the DbContext

Now that we have the necessary Nuget packages installed, we can go ahead and build up our DbContext.

We are going to name it CrudApiDbContext. We need to provide two constructors for our DbContext.

// CrudApiDbContext.cs
public class CrudApiDbContext : DbContext {
	protected readonly IConfiguration _configuration;

	public CrudApiDbContext()
	{
		_configuration = new ConfigurationBuilder().AddJsonFile(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + @"\appsettings.json").Build();
	}
	public CrudApiDbContext([NotNull] IConfiguration configuration)
	{
		_configuration = configuration;
	}
	...
}

The parameterless constructor is if we are wanted to test our DbContext directly using TDD. Or, if we want to use a program like LINQPad. What the parameterless constructor does is that it takes the appsettings.json file and builds up a configuration.

If you are using the DbContext in an ASP.NET Core project, you will already have a configuration set up. So, the other constructor passes in the configuration through dependency injection.

When adding entities to our DbContext, we are going to use reflection. So, we need to store a list of assemblies where we want to get our entities from.

// CrudApiDbContext.cs
public class CrudApiDbContext : DbContext {
	...
	 
	protected virtual IList<Assembly> Assemblies
	{
		get
		{
			return new List<Assembly>()
		{
			{
				Assembly.Load("RoundTheCode.CrudApi.Data")
			}
		};
		}
	}
	 
	...
}

Adding our Entities to our DbContext

Now we are going to override the OnModelCreating method in DbContext. Using Reflection, we are going to get all the classes that inherit IBase. These classes must be public, but not abstract. That means that our Base class will not be included in this selection.

It will loop through each class and see if there is a static OnModelCreating method. If there is, it will invoke it. The whole point of the OnModelCreating method is to set options for our properties, such as the maximum length of a property.

Next, it will check and see if it inherits a class. It will check to see if it inherits the Base class. If it does, it will invoke the static OnModelCreating method found in the Base class.

// CrudApiDbContext.cs
public class CrudApiDbContext : DbContext {
	...
	 
	protected override void OnModelCreating(ModelBuilder builder)
		{
			foreach (var assembly in Assemblies)
			{
				// Loads all types from an assembly which have an interface of IBase and is a public class
				var classes = assembly.GetTypes().Where(s => s.GetInterfaces().Any(_interface => _interface.Equals(typeof(IBase)) && s.IsClass && !s.IsAbstract && s.IsPublic));

				foreach (var _class in classes)
				{
					// On Model Creating
					var onModelCreatingMethod = _class.GetMethods().FirstOrDefault(x => x.Name == "OnModelCreating" && x.IsStatic);

					if (onModelCreatingMethod != null)
					{
						onModelCreatingMethod.Invoke(_class, new object[] { builder });
					}

					// On Base Model Creating
					if (_class.BaseType == null || _class.BaseType != typeof(Base))
					{
						continue;
					}

					var baseOnModelCreatingMethod = _class.BaseType.GetMethods().FirstOrDefault(x => x.Name == "OnModelCreating" && x.IsStatic);

					if (baseOnModelCreatingMethod == null)
					{
						continue;
					}

					var baseOnModelCreatingGenericMethod = baseOnModelCreatingMethod.MakeGenericMethod(new Type[] { _class });

					if (baseOnModelCreatingGenericMethod == null)
					{
						continue;
					}

					baseOnModelCreatingGenericMethod.Invoke(typeof(Base), new object[] { builder });
				}
			}
		}
	 
	...
}

Other DbContext Methods

Now we need to point the DbContext to a connection string. The way we are doing it is overriding the OnConfiguring method in our DbContext.

// CrudApiDbContext.cs
public class CrudApiDbContext : DbContext {
	...
	 
	protected override void OnConfiguring(DbContextOptionsBuilder builder)
	{
		// Sets the database connection from appsettings.json
		if (_configuration["ConnectionStrings:CrudApiDbContext"] != null)
		{
			builder.UseSqlServer(_configuration["ConnectionStrings:CrudApiDbContext"]);
		}
	}    
	...
}

Finally, we don't want to explicitly set the created and last updated timestamp's every time a create or update method is performed. So, we can override the OnSaveChangesAsync method and set the relevant properties to the current time.

// CrudApiDbContext.cs
public class CrudApiDbContext : DbContext {
	...
	 
	public async override Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
	{
		foreach (var entry in ChangeTracker.Entries())
		{
			if (entry.Entity is IBase)
			{
				if (entry.State == EntityState.Added)
				{
					entry.Property("Created").CurrentValue = DateTimeOffset.Now;
				}
				else if (entry.State == EntityState.Modified)
				{
					entry.Property("LastUpdated").CurrentValue = DateTimeOffset.Now;
				}
			}
		}

		return await base.SaveChangesAsync(cancellationToken);
	}
}

So the full code for our CrudApiDbContext.

// CrudApiDbContext.cs
public class CrudApiDbContext : DbContext
{
	protected readonly IConfiguration _configuration;

	public CrudApiDbContext()
	{
		_configuration = new ConfigurationBuilder().AddJsonFile(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + @"\appsettings.json").Build();
	}
	public CrudApiDbContext([NotNull] IConfiguration configuration)
	{
		_configuration = configuration;
	}

	protected virtual IList<Assembly> Assemblies
	{
		get
		{
			return new List<Assembly>()
		{
			{
				Assembly.Load("RoundTheCode.CrudApi.Data")
			}
		};
		}
	}

	protected override void OnModelCreating(ModelBuilder builder)
	{
		foreach (var assembly in Assemblies)
		{
			// Loads all types from an assembly which have an interface of IBase and is a public class
			var classes = assembly.GetTypes().Where(s => s.GetInterfaces().Any(_interface => _interface.Equals(typeof(IBase)) && s.IsClass && !s.IsAbstract && s.IsPublic));

			foreach (var _class in classes)
			{
				// On Model Creating
				var onModelCreatingMethod = _class.GetMethods().FirstOrDefault(x => x.Name == "OnModelCreating" && x.IsStatic);

				if (onModelCreatingMethod != null)
				{
					onModelCreatingMethod.Invoke(_class, new object[] { builder });
				}

				// On Base Model Creating
				if (_class.BaseType == null || _class.BaseType != typeof(Base))
				{
					continue;
				}

				var baseOnModelCreatingMethod = _class.BaseType.GetMethods().FirstOrDefault(x => x.Name == "OnModelCreating" && x.IsStatic);

				if (baseOnModelCreatingMethod == null)
				{
					continue;
				}

				var baseOnModelCreatingGenericMethod = baseOnModelCreatingMethod.MakeGenericMethod(new Type[] { _class });

				if (baseOnModelCreatingGenericMethod == null)
				{
					continue;
				}

				baseOnModelCreatingGenericMethod.Invoke(typeof(Base), new object[] { builder });
			}
		}
	}

	protected override void OnConfiguring(DbContextOptionsBuilder builder)
	{
		// Sets the database connection from appsettings.json
		if (_configuration["ConnectionStrings:CrudApiDbContext"] != null)
		{
			builder.UseSqlServer(_configuration["ConnectionStrings:CrudApiDbContext"]);
		}
	}

	public async override Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
	{
		foreach (var entry in ChangeTracker.Entries())
		{
			if (entry.Entity is IBase)
			{
				if (entry.State == EntityState.Added)
				{
					entry.Property("Created").CurrentValue = DateTimeOffset.Now;
				}
				else if (entry.State == EntityState.Modified)
				{
					entry.Property("LastUpdated").CurrentValue = DateTimeOffset.Now;
				}
			}
		}

		return await base.SaveChangesAsync(cancellationToken);
	}
}

A Change to Our Base Class

Remember that we are invoking an OnModelCreating static method in our Base class? We did that when overriding the OnModelCreating method in our DbContext. The problem at the moment is that method does not exist in our Base class. We need to add it in to our Base class.

// Base.cs
public abstract class Base : IBase
{
	public int Id { get; set; }

	public DateTimeOffset Created { get; set; }

	public DateTimeOffset? LastUpdated { get; set; }

	public DateTimeOffset? Deleted { get; set; }

	public static void OnModelCreating<TEntity>(ModelBuilder modelBuilder)
		where TEntity : class, IBase
	{
		modelBuilder.Entity<TEntity>().HasKey(entity => entity.Id);
	}
}

This is a generic method that passes in a type of TEntity. The TEntity will be the entity that we've added to our DbContext. The OnModelCreating method ensures that the ID property is set as the primary key.

Creating a Team Entity

With all the boilerplate code set up, we can now create an actual entity for our DbContext. We are going to call it "Team".

// Team.cs
[Table("Team", Schema = "lge")]
public class Team : Base
{
	public virtual string Name { get; set; }

	public virtual string ShirtColour { get; set; }

	public virtual string Location { get; set; }

	public static void OnModelCreating(ModelBuilder modelBuilder)
	{
		modelBuilder.Entity<Team>().Property(property => property.Name).HasMaxLength(200);

		modelBuilder.Entity<Team>().Property(property => property.ShirtColour).HasMaxLength(30);

		modelBuilder.Entity<Team>().Property(property => property.Location).HasMaxLength(100);
	}
}

We are using the Table attribute in Entity Framework Core to dictate the table name and schema in our SQL Server database.

In addition, an OnModelCreating static method has been created. Going back to our DbContext, this is invoked inside our overriding of the OnModelCreating method. For this entity, we are ensuring that certain properties only have a certain number of characters.

Migrations

As we now have an entity set up, we can use EF Migrations. EF Migrations allows us to import an entity into our SQL Server database.

First, we need to add a reference to "RoundTheCode.CrudApi.Data" inside our API project.

Next, we need to modify our appsettings.json file. We need to add a connection string so our DbContext knows how to connect to our SQL Server database.

// appsettings.json
{
	...
	"ConnectionStrings": {
	"CrudApiDbContext":  "Server=localhost; Database=CrudApi; Trusted_Connection=true; MultipleActiveResultSets=true; Integrated Security=true;"
	}
}

If you remember, we set the name of this connection string when overriding the OnConfiguring method in our DbContext.

Now, in-order to run EF migrations, we need to install a Nuget package into our API. We can do that by running the following code in Package Manager Console.

Install-Package Microsoft.EntityFrameworkCore.Design

Don't forget to set the project to "RoundTheCode.CrudApi.Web".

Lastly, before we run our migrations, we need to include CrudApiDbContext through dependency injection in our API. We can do that by opening up our Startup class in "RoundTheCode.CrudApi.Web" and adding the following to the ConfigureServices method.

// Startup.cs
public class Startup
{
	...

	// This method gets called by the runtime. Use this method to add services to the container.
	public void ConfigureServices(IServiceCollection services)
	{
		...

		services.AddDbContext<CrudApiDbContext>();
	}

	...
}

Running Migrations

Running migrations is relatively easy to do. Open up the Package Manager Console and ensure that the default project is set to "RoundTheCode.CrudApi.Data". To add the migration, run the following command:

Add-Migration Init

This will create migration scripts for our Team entity. To update it to the SQL Server database, run the following command in Package Manager Console:

Update-Database

What this command will do is take the connection string in our appsettings.json file. From there, it will create the database if it does not exist. Finally, it will create the "Team" table into our SQL Server database.

Services

Next, we need to add a new .NET Core class library. We are going to call it "RoundTheCode.CrudApi.Services".

Like with "RoundTheCode.CrudApi.Data" assembly, we need to create a generic service. This generic service will have a CRUD methods, integrated with Entity Framework.

We go ahead and create our BaseService class, which will inherit the IBaseService interface. Each pass in a generic type, which will be an entity that inherits IBase (like Team).

// IBaseService.cs
public interface IBaseService<TEntity>
            where TEntity : class, IBase
{
	Task<TEntity> CreateAsync(TEntity entity);

	Task<TEntity> ReadAsync(int id, bool tracking = true);

	Task<TEntity> UpdateAsync(int id, TEntity updateEntity);

	Task DeleteAsync(int id);
}

// BaseService.cs
public abstract class BaseService<TEntity> : IBaseService<TEntity>
            where TEntity : class, IBase
{
	protected CrudApiDbContext _crudApiDbContext;

	protected BaseService([NotNull] CrudApiDbContext crudApiDbContext)
	{
		_crudApiDbContext = crudApiDbContext;
	}

	public virtual async Task<TEntity> CreateAsync(TEntity entity)
	{
		await _crudApiDbContext.Set<TEntity>().AddAsync(entity);
		await _crudApiDbContext.SaveChangesAsync();

		return entity;
	}

	public virtual async Task<TEntity> ReadAsync(int id, bool tracking = true)
	{
		var query = _crudApiDbContext.Set<TEntity>().AsQueryable();

		if (!tracking)
		{
			query = query.AsNoTracking();
		}

		return await query.FirstOrDefaultAsync(entity => entity.Id == id && !entity.Deleted.HasValue);
	}

	public virtual async Task<TEntity> UpdateAsync(int id, TEntity updateEntity)
	{
		// Check that the record exists.
		var entity = await ReadAsync(id);

		if (entity == null)
		{
			throw new Exception("Unable to find record with id '" + id + "'.");
		}

		// Update changes if any of the properties have been modified.
		_crudApiDbContext.Entry(entity).CurrentValues.SetValues(updateEntity);
		_crudApiDbContext.Entry(entity).State = EntityState.Modified;

		if (_crudApiDbContext.Entry(entity).Properties.Any(property => property.IsModified))
		{
			await _crudApiDbContext.SaveChangesAsync();
		}
		return entity;
	}

	public virtual async Task DeleteAsync(int id)
	{
		// Check that the record exists.
		var entity = await ReadAsync(id);

		if (entity == null)
		{
			throw new Exception("Unable to find record with id '" + id + "'.");
		}

		// Set the deleted flag.
		entity.Deleted = DateTimeOffset.Now;
		_crudApiDbContext.Entry(entity).State = EntityState.Modified;

		// Save changes to the Db Context.
		await _crudApiDbContext.SaveChangesAsync();
	}
}

Like with the Base class, the BaseService is an abstract class, so we cannot initialise it directly. So, if we are going to set these CRUD methods up for our Team entity, we need to set up a TeamService class.

And that's what we are going to do. We will also set up an ITeamService interface, and the TeamService class will inherit the BaseService, passing in the Team entity as it's generic type.

// ITeamService.cs
public interface ITeamService : IBaseService<Team>
{
}

// TeamService.cs
public class TeamService : BaseService<Team>, ITeamService
{
	public TeamService(CrudApiDbContext crudApiDbContext) : base(crudApiDbContext) { }
}

As the BaseService only has a constructor with a parameter, we need to set up our own constructor in TeamService. We pass in our CrudApiDbContext as the parameter, and the use it by calling the constructor in our BaseService class.

Controllers and Endpoints

So we've set up an entity. We also set up a service that has methods to run our CRUD operations. We can now set up our API endpoints through controllers.

But first, we need to add our TeamService class into our API. We can do this through dependency injection. Open up the Startup class in "RoundTheCode.CrudApi.Web" and add the following to the ConfigureServices method. You will need to add a reference to "RoundTheCode.CrudApi.Services".

// Startup.cs
public class Startup
{
	...

	// This method gets called by the runtime. Use this method to add services to the container.
	public void ConfigureServices(IServiceCollection services)
	{
		...
		services.AddScoped<ITeamService, TeamService>();
	}

	...
}

In addition, we are going to do a partial update. I detailed the reasons for doing a partial update in an earlier article. In-order to make this happen, we need to install a couple of Nuget packages into "RoundTheCode.CrudApi.Web".

Install-Package Microsoft.AspNetCore.JsonPatch
Install-Package Microsoft.AspNetCore.Mvc.NewtonsoftJson

As well, we need to make a slight change to the AddControllers method used in the ConfigureServices method in the Startup class.

// Startup.cs
public class Startup
{
	...

	// This method gets called by the runtime. Use this method to add services to the container.
	public void ConfigureServices(IServiceCollection services)
	{
		services.AddControllers().AddNewtonsoftJson();

		...
	}

	...
}

Finally, we can set up our endpoints. Like with the service class, we created a BaseController. The BaseController is abstract, so cannot be called directly as an endpoint. However, it does store all our CRUD endpoints, calling the relevant BaseService method.

// BaseController.cs
[ApiController]
public abstract class BaseController<TEntity> : Controller
    where TEntity : class, IBase
{
	protected readonly IBaseService<TEntity> _service;

	protected BaseController([NotNull] IBaseService<TEntity> service)
	{
		_service = service;
	}

	[HttpPost]
	public async Task<IActionResult> CreateAsync(TEntity entity)
	{
		entity = await _service.CreateAsync(entity);

		return Ok(entity);
	}

	[HttpGet("{id:int}")]
	public async Task<IActionResult> ReadAsync(int id)
	{
		var entity = await _service.ReadAsync(id);

		if (entity == null)
		{
			return NotFound();
		}

		return Ok(entity);
	}

	[HttpPatch("{id:int}")]
	public async Task<IActionResult> UpdatePartialAsync(int id, [FromBody] JsonPatchDocument<TEntity> patchEntity)
	{
		var entity = await _service.ReadAsync(id, false);

		if (entity == null)
		{
			return NotFound();
		}

		patchEntity.ApplyTo(entity, ModelState);
		entity = await _service.UpdateAsync(id, entity);

		return Ok(entity);
	}

	[HttpDelete("{id:int}")]
	public async Task<IActionResult> DeleteAsync(int id)
	{
		var entity = await _service.ReadAsync(id);

		if (entity == null)
		{
			return NotFound();
		}

		await _service.DeleteAsync(id);

		return Ok(entity);
	}
}

Lastly, we need to set up a TeamController. This will inherit the BaseController, passing in the Team entity as it's generic type. We also need to set up an endpoint of "api/team". This is so all our methods go through the "api/team" endpoint.

// TeamController.cs
[Route("api/team")]
public class TeamController : BaseController<Team>
{
	public TeamController(ITeamService teamService) : base(teamService)
	{

	}
}

Testing in Postman and Creating New Entities

Using Postman is a great way to test these API endpoints. We can just to see if the functionality works as it should be.

In addition, this infrastructure allows us to easily add new entities in minimal time.

You can watch back our live stream. We test our API in Postman and set up a "League" entity to demonstrate how easy it is to create a new entity.

And, if you want to check out additional information, check out this article by Aram Tchekrekjian entitled "Build RESTful APIs Using ASP.NET Core and Entity Framework Core".