Welcome to today’s post.
In today’s post I will discuss some new features in Entity Framework 7 (Also known as Entity Framework Core 7), which was part of the .NET 7 release in Microsoft Visual 2022.
In previous versions of Entity Framework, they have all used the common methods of update and deletion of entity objects within a data context. These methods include the update method UpdateAsync() and the deletion method DeleteAsync(). What all these asynchronous methods have in common is that they can be applied to database objects within a data context under a transaction. In addition, we can also apply multiple commands under the same transaction context. Should any of the commands fail under the same transaction context, then any commands executed under the same data context will be rolled back.
With the above commands, the code required to run them requires additional validation checking such as finding out if there is an existing record with the same identifier before deletion of the record. In addition, the commit of the transaction to write the change to the database is also required.
The new entity framework 7 commands for updating and deletion of records, ExecuteUpdateAsync() and ExecuteDeleteAsync() will differ from the update method UpdateAsync() and the deletion method DeleteAsync() with multiple calls to ExecuteUpdateAsync() and ExecuteDeleteAsync() will not be automatically wrapped in a transaction context.
For commands wrapped within transactions, we can continue using UpdateAsync() and DeleteAsync(). For single command committed transactions, we can use ExecuteUpdateAsync and ExecuteDeleteAsync.
I first will show how to convert an existing sequence of commands into using the new commands.
The first command I will look at is the ExecuteUpdateAsync() command.
I will explain how this is used in the next section.
Updating Records with ExecuteUpdateAsync()
In a Book service, suppose we have a service method that updates a book from its identifier and an instance of the book object.
The service method I would like to optimize in terms of code size is shown below:
public async Task<BookViewModel> UpdateBook(int Id, BookViewModel vm)
{
BookViewModel book = await _bookRepository.GetByIdAsync(Id);
if (book != null)
{
string originalEdition = book.Edition;
book.Title = vm.Title;
book.Author = vm.Author;
book.Edition = vm.Edition;
book.Genre = vm.Genre;
book.ISBN = vm.ISBN;
book.Location = vm.Location;
book.YearPublished = vm.YearPublished;
await _bookRepository.UpdateAsync(book);
await this._bookRepository.UnitOfWork.SaveChangesAsync();
}
return book;
}
I will then look to optimise the following two lines of code that update the record in memory then commit by writing the record changes to the database:
await _bookRepository.UpdateAsync(book);
await this._bookRepository.UnitOfWork.SaveChangesAsync();
The updated service method will be:
public async Task<BookViewModel> UpdateBook(int Id, BookViewModel vm)
{
BookViewModel book = await _bookRepository.GetByIdAsync(Id);
if (book != null)
{
string originalEdition = book.Edition;
book.Title = vm.Title;
book.Author = vm.Author;
book.Edition = vm.Edition;
book.Genre = vm.Genre;
book.ISBN = vm.ISBN;
book.Location = vm.Location;
book.YearPublished = vm.YearPublished;
await this._bookRepository.UpdateAndCommitAsync(book);
}
return book;
}
In the book repository interface, I add the functional prototype for the new method as the last member as shown:
IBookRepository:
public interface IBookRepository
{
IUnitOfWork UnitOfWork { get; }
Task<List<BookViewModel>> GetAll();
Task<List<BookViewModel>> GetAllNoTracking();
Task<BookViewModel> GetByIdAsync(int id);
Task<BookViewModel> GetByTitle(string title);
Task<BookViewModel> AddAsync(BookViewModel entity);
Task<BookViewModel> UpdateAsync(BookViewModel entity);
Task<BookViewModel> DeleteAsync(BookViewModel entity);
Task<BookViewModel> UpdateAndCommitAsync(BookViewModel entity);
}
In the book repository, we have an existing method UpdateAsync(), in the RepositoryUot<T> class, which has validation and exception handling as shown:
public async Task<T> UpdateAsync(T entity)
{
if (entity == null)
throw new ArgumentNullException("UpdateAsync(): entity parameters is null.");
try
{
_db.Update(entity);
return entity;
}
catch (Exception ex)
{
throw new Exception($"UpdateAsync(): cannot update data : {ex.Message}");
}
}
In BookRepository, the new UpdateAndCommitAsync() method is added with the EF method ExecuteUpdateAsync() used as follows:
public async Task<BookViewModel> UpdateAndCommitAsync(BookViewModel entity)
{
int result = await _db.Books.Where(b => b.ID == entity.ID)
.ExecuteUpdateAsync(c => c
.SetProperty(d => d.ISBN, entity.ISBN)
.SetProperty(e => e.Author, entity.Author)
.SetProperty(f => f.Title, entity.Title)
.SetProperty(f => f.Location, entity.Location)
.SetProperty(g => g.YearPublished, entity.YearPublished)
.SetProperty(h => h.DateCreated, entity.DateCreated)
.SetProperty(i => i.DateUpdated, DateAndTime.Now)
.SetProperty(j => j.Edition, entity.Edition)
.SetProperty(k => k.Genre, entity.Genre)
.SetProperty(l => l.MediaType, entity.MediaType));
return entity;
}
On execution, the SQL debug log shows the SQL update with a parameter for each field that has set in the SetProperty() method within the ExecuteUpdateAsync() command:
Request started: "PUT" http://localhost:25138/api/Book/Update/1
[23:53:00 INF] Executed DbCommand (1,150ms) [Parameters=[@__p_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [b].[ID], [b].[Author], [b].[DateCreated], [b].[DateUpdated], [b].[Edition], [b].[Genre], [b].[ISBN], [b].[Location], [b].[MediaType], [b].[Title], [b].[YearPublished]
FROM [Books] AS [b]
WHERE [b].[ID] = @__p_0
[23:53:39 INF] Executed DbCommand (778ms) [Parameters=[@__entity_MediaType_9='?' (Size = 4000), @__entity_Genre_8='?' (Size = 4000), @__entity_Edition_7='?' (Size = 4000), @__Now_6='?' (DbType = DateTime2), @__entity_DateCreated_5='?' (DbType = DateTime2), @__entity_YearPublished_4='?' (DbType = Int32), @__entity_Title_3='?' (Size = 4000), @__entity_Author_2='?' (Size = 4000), @__entity_ISBN_1='?' (Size = 4000), @__entity_ID_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
UPDATE [b]
SET [b].[MediaType] = @__entity_MediaType_9,
[b].[Genre] = @__entity_Genre_8,
[b].[Edition] = @__entity_Edition_7,
[b].[DateUpdated] = @__Now_6,
[b].[DateCreated] = @__entity_DateCreated_5,
[b].[YearPublished] = @__entity_YearPublished_4,
[b].[Title] = @__entity_Title_3,
[b].[Author] = @__entity_Author_2,
[b].[ISBN] = @__entity_ISBN_1
FROM [Books] AS [b]
WHERE [b].[ID] = @__entity_ID_0
[23:53:41 INF] HTTP PUT /api/Book/Update/1 responded 200 in 1844.8333 ms
Response sent: http://localhost:25138/api/Book/Update/1 with HTTP status 200.0
The part of the command that makes it less optimal in terms of code size are the additional SetProperty() methods that are required to assign each table field with the new value. You save a command but the code size increases.
In the next section I will show how to use the ExecuteDeleteAsync() command.
Removing Records with ExecuteDeleteAsync()
The service method I would like to optimize in terms of code size is shown below:
public async Task DeleteBook(int id)
{
BookViewModel book = await _bookRepository.GetByIdAsync(id);
if (book != null)
{
await _bookRepository.DeleteAsync(book);
}
}
I will then look to optimise the following two lines of code that update the record in memory then commit by writing the record changes to the database:
BookViewModel book = await _bookRepository.GetByIdAsync(id);
and
await _bookRepository.DeleteAsync(book);
The updated service method will be:
public async Task<bool> DeleteBook(int id)
{
return await _bookRepository.DeleteAndCommitAsync(id);
}
In the book repository interface, I add the functional prototype for the new method as the last member as shown:
IBookRepository:
public interface IBookRepository
{
IUnitOfWork UnitOfWork { get; }
Task<List<BookViewModel>> GetAll();
Task<List<BookViewModel>> GetAllNoTracking();
Task<BookViewModel> GetByIdAsync(int id);
Task<BookViewModel> GetByTitle(string title);
Task<BookViewModel> AddAsync(BookViewModel entity);
Task<BookViewModel> UpdateAndCommitAsync(BookViewModel entity);
Task<BookViewModel> UpdateAsync(BookViewModel entity);
Task<BookViewModel> DeleteAsync(BookViewModel entity);
Task<BookViewModel> DeleteAndCommitAsync(int id);
}
In BookRepository, the new DeleteAndCommitAsync() method is added with the EF method ExecuteDeleteAsync() used as follows:
public async Task<bool> DeleteAndCommitAsync(int id)
{
return await _db.Books.Where(b => b.ID == id)
.ExecuteDeleteAsync() > 0;
}
On execution, the SQL debug log shows the SQL delete with a parameter corresponding to the key of the record that is to be removed within the ExecuteDeleteAsync() command:
Request started: "POST" http://localhost:25138/api/Book/Delete/228
[00:00:50 INF] Executed DbCommand (196ms) [Parameters=[@__id_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
DELETE FROM [b]
FROM [Books] AS [b]
WHERE [b].[ID] = @__id_0
[00:01:00 INF] HTTP POST /api/Book/Delete/228 responded 200 in 32924.2091 ms
Response sent: http://localhost:25138/api/Book/Delete/228 with HTTP status 200.0
Compared to the ExecuteUpdateAsync() command, the ExecuteDeleteAsync() command is more optimized in terms of code size. Both commands are provided for performance optimization in providing an update or deletion without requiring a validation check on the number of affected records. In addition both commands run under a single transaction, so should be executed in the particular case where the method the commands are run within are not dependent on other application data transactions.
In future posts I will provide overviews of other features within Entity Framework 7 that you may find useful.
That is all for today’s post.
I hope you have found this post useful and informative.
Andrew Halil is a blogger, author and software developer with expertise of many areas in the information technology industry including full-stack web and native cloud based development, test driven development and Devops.