Creating a CRUD with ASP.NET Core & EF Core

• Download source code from GitHub

Problem

This post discusses how to implement CRUD using Entity Framework Core and ASP.NET Core.

Solution

Create a class library and add the following NuGet packages:

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

Create a class representing 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 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 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 DbContext subclass 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

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:

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

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

Querying

Using LINQ, you could query the DbSet and 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 synchronous and asynchronous version of ToList() method.

Transactions

SaveChanges() method on DbContext provides transactional facility, however you could also explicitly create transaction by using DatabaseFacade type on DbContext. For instance, below I am saving movie first (to get its Id) and then saving 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 factory:

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

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 alternate. First, we create a field in the database (and on the entity POCO) to act as 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 [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);
            }