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