Creating A CRUD With ASP.NET Core And EF Core

Problem

How to implement CRUD using Entity Framework Core and ASP.NET Core.

Solution

Create a class library and add the following NuGet package.

  • Microsoft.EntityFrameworkCore
  • Microsoft.EntityFrameworkCore.Relational
  • Microsoft.EntityFrameworkCore.SqlServer

Create a class representing the database entity.

Create a class inheriting from DbContext.

Create ASP.NET Core web application and an API controller for CRUD operations. To retrieve a list and single item.

To insert a new item -

To update an existing item -

To delete an item -

Configure the DbContext in Startup -

Discussion

Entity Framework Core (EF) is an ORM that makes it simpler to work with the database by using POCO classes that map to database entities and DbContext to interact with them.

Configuring DbContext

As shown in the code (Solution section), the best (and testable) way to configure DbContextsubclass is to inject DbContextOptions in its constructor. NuGet package Microsoft.EntityFrameworkCore provides an extension method AddDbContext to setup custom DbContext (as shown in Solution section). You could, however, also add the dependency like this.

Saving

You subclass of DbContext will have a DbSet of entities, through which you could add, update and remove database records. SaveChanges() method on DbContext triggers the database changes, which means that you could also add/update/remove multiple items in one transaction:

Note
There is synchronous and asynchronous version of SaveChanges() method.

Querying

Using LINQ, you could query the DbSet that is a really powerful feature of EF. Here is a bit more complex query to retrieve movies along with their director and actors:

Note
This query can be written in several different ways, including the navigation properties however, as I’ve not discussed them here, I am using simple LINQ.

Note
There is a synchronous and asynchronous version of ToList() method.

Transactions

SaveChanges() method on DbContext provides transactional facility however you could also explicitly create a transaction by using DatabaseFacade type on DbContext. For instance, below I am saving movie first (to get its Id) and then saving actors:

Logging

You could use ASP.NET Core logging features to view/log the SQL being sent to the SQL Server. In order to do this you need to use ILoggerProvider and ILogger interface:

You can now add this logger to factory:

Running through command line, you get the SQL being generated:

Note

Use EnableSensitiveDataLogging() method on DbContextOptionsBuilder to enable logging of parameters, by default they will not show.

Concurrency

We can use ETag to implement optimistic concurrency, as discussed here. However, sometimes we want more control over the process and EF provides another alternative. First, we create a field in the database (and on the entity POCO) to act as concurrency token and annotate with [Timestamp] attribute:

If you prefer using Fluent API for building model settings, instead of [Timestamp] attribute you could use:

Then, you’ll catch the DbUpdateConcurrencyException exception to handle concurrency conflicts.

Source Code

 

 

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now