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.

public class Actor
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Create a class inheriting from DbContext.

public class Database : DbContext
{
    public Database(DbContextOptions<Database> options) : base(options) { }

    public DbSet<Actor> Actors { get; set; }
}

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

[HttpGet]
public async Task<IActionResult> GetList()
{
    var entities = await context.Actors.ToListAsync();
    var outputModel = entities.Select(entity => new
    {
        entity.Id,
        entity.Name,
    });
    return Ok(outputModel);
}

[HttpGet("{id}", Name = "GetActor")]
public IActionResult GetItem(int id)
{
    var entity = context.Actors
                        .Where(e => e.Id == id)
                        .FirstOrDefault();
    if (entity == null)
        return NotFound();

    var outputModel = new
    {
        entity.Id,
        entity.Name,
    };
    return Ok(outputModel);
}

To insert a new item

[HttpPost]
public async Task<IActionResult> Create([FromBody] ActorCreateInputModel inputModel)
{
    if (inputModel == null)
        return BadRequest();

    var entity = new Actor
    {
        Name = inputModel.Name
    };

    this.context.Actors.Add(entity);
    await this.context.SaveChangesAsync();

    var outputModel = new
    {
        entity.Id,
        entity.Name
    };

    return CreatedAtRoute("GetActor", new { id = outputModel.Id }, outputModel);
}

To update an existing item

[HttpPut("{id}")]
public IActionResult Update(int id, [FromBody] ActorUpdateInputModel inputModel)
{
    if (inputModel == null || id != inputModel.Id)
        return BadRequest();

    var entity = new Actor
    {
        Id = inputModel.Id,
        Name = inputModel.Name
    };

    this.context.Actors.Update(entity);
    this.context.SaveChanges();

    return NoContent();
}

To delete an item

[HttpDelete("{id}")]
public IActionResult Delete(int id)
{
    var entity = context.Actors
                        .Where(e => e.Id == id)
                        .FirstOrDefault();

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

    this.context.Actors.Remove(entity);
    this.context.SaveChanges();

    return NoContent();
}

Configure the DbContext in Startup

public void ConfigureServices(IServiceCollection services)
{
    var connection = "Data Source=...";

    services.AddDbContext<Database>(options =>
        options.UseSqlServer(connection));

    services.AddMvc();
}

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.

services.AddScoped(factory =>
{
    var builder = new DbContextOptionsBuilder<Database>();
    builder.UseSqlServer(connection);

    return new Database(builder.Options);
});

Saving

Your 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

this.context.Actors.Add(entity1);
this.context.Actors.Add(entity2);
this.context.Actors.Add(entity3);
await this.context.SaveChangesAsync();

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

Querying

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

var entities = from movie in this.context.Movies
               join director in this.context.Directors on movie.DirectorId equals director.Id
               select new
               {
                   movie.Id,
                   movie.Title,
                   movie.ReleaseYear,
                   movie.Summary,
                   Director = director.Name,
                   Actors = (
                       from actor in this.context.Actors
                       join movieActor in this.context.MovieActors on actor.Id equals movieActor.ActorId
                       where movieActor.MovieId == movie.Id
                       select actor.Name + " as " + movieActor.Role
                   )
               };

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 the ToList() method.

Transactions

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

using (var transaction = this.context.Database.BeginTransaction())
{
    try
    {
        // build movie entity
        this.context.Movies.Add(movieEntity);
        this.context.SaveChanges();

        foreach (var actor in inputModel.Actors)
        {
            // build actor entity
            this.context.MovieActors.Add(actorEntity);
        }
        this.context.SaveChanges();

        transaction.Commit();

        // ...
    }
    catch (System.Exception ex)
    {
        transaction.Rollback();
        // ...
    }
}

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

public class EfLoggerProvider : ILoggerProvider
{
    public ILogger CreateLogger(string categoryName)
    {
        if (categoryName == typeof(IRelationalCommandBuilderFactory).FullName)
        {
            return new EfLogger();
        }

        return new NullLogger();
    }

    public void Dispose() { }

    #region " EF Logger "

    private class EfLogger : ILogger
    {
        public IDisposable BeginScope<TState>(TState state) => null;

        public bool IsEnabled(LogLevel logLevel) => true;

        public void Log<TState>(
            LogLevel logLevel, EventId eventId, TState state,
            Exception exception, Func<TState, Exception, string> formatter)
        {
            Console.WriteLine(formatter(state, exception));
        }
    }

    #endregion

    #region " Null Logger "

    private class NullLogger : ILogger
    {
        public IDisposable BeginScope<TState>(TState state) => null;

        public bool IsEnabled(LogLevel logLevel) => false;

        public void Log<TState>(
            LogLevel logLevel, EventId eventId, TState state,
            Exception exception, Func<TState, Exception, string> formatter)
        { }
    }

    #endregion
}

You can now add this logger to the factory

public Startup(ILoggerFactory loggerFactory)
{
    loggerFactory.AddProvider(new EfLoggerProvider());
}

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

Note.Use the EnableSensitiveDataLogging() method on DbContextOptionsBuilder to enable the 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 a concurrency token and annotate with [Timestamp] attribute

public class Actor
{
    public int Id { get; set; }
    public string Name { get; set; }
    [Timestamp]
    public byte[] Timestamp { get; set; }
}

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

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Actor>()
                .Property(actor => actor.Timestamp)
                .ValueGeneratedOnAddOrUpdate()
                .IsConcurrencyToken();
}

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

try
{
    this.context.Actors.Update(entity);
    this.context.SaveChanges();
}
catch (DbUpdateConcurrencyException ex)
{
    var inEntry = ex.Entries.Single();
    var dbEntry = inEntry.GetDatabaseValues();

    if (dbEntry == null)
        return StatusCode(StatusCodes.Status500InternalServerError, 
            "Actor was deleted by another user");

    var inModel = inEntry.Entity as Actor;
    var dbModel = dbEntry.ToObject() as Actor;

    var conflicts = new Dictionary<string, string>();

    if (inModel.Name != dbModel.Name)
        conflicts.Add("Actor", 
            $"Changed from '{inModel.Name}' to '{dbModel.Name}'");

    return StatusCode(StatusCodes.Status412PreconditionFailed, conflicts);
}

Source Code