Welcome to today’s post.
In today’s post I will be discussing how we use Entity Framework Core to query SQL data and to perform CRUD (SELECT, INSERT, UPDATE and DELETE) operations.
In a previous post I showed you how I setup Entity Framework Core within a .NET Core application and how to connect to an existing data source. I also showed how to maintain a data base schema by applying migrations. In this post, I will assume you know how to setup a connection to a data store using Entity Framework Core. I will then who how to use LINQ and the various extension methods within the data context to query and perform CRUD operations on your entity objects.
First, I will show how to apply CRUD operations within a SQL data context.
Useful Namespaces to use for Entity Framework Core Data Operations
Before we can use Entity Framework Core to allow retrieval, saving, updating and removal of data from a SQL database from a data context, the following namespace is required:
using Microsoft.EntityFrameworkCore;
To be able to use LINQ based data queries and standard .NET collections, we must include the following namespaces at the top of our source file:
using System.Collections.Generic;
using System.Linq;
To be able to use thread-safe versions of DbContext extension methods that require async and await keywords, we must include the following namespace:
using System.Threading.Tasks;
In the next section, I will show how to implement CRUD operations with LINQ within Entity Framework Core.
Implementing CRUD Operations with LINQ to Entities
The CRUD operations that are common with SQL databases can be applied with Entity Framework Core, and these are SELECT, INSERT, UPDATE and DELETE.
To apply each of these operations to a data object, we do not need to use raw SQL. We can make use of some the following extension methods that are available within the data context class DBContext within Entity Framework Core:
AddAsync(object)
Update(object)
Remove(object)
When one of the above operations is applied to an entity object, its state will change to one of the following entity states:
Added
Deleted
Modified
Unchanged
The underlying data object within the database will not change unless the following data context method is called after one of the above data context methods is run:
SaveChangesAsync()
The following is an example of how we can use the AddAsync() method to insert a new record and commit the changes through the data context:
public async Task<bool> SaveBook(BookViewModel vm)
{
vm.DateCreated = DateTime.Now;
await _db.AddAsync(vm);
await _db.SaveChangesAsync();
int id = vm.ID;
var book = _db.Books.Where(a => a.ID == id).SingleOrDefault();
if (book == null)
{
return false;
}
return true;
}
After the command:
await _db.AddAsync(vm);
is executed, the object vm within the entity object list has its state changed to Added.
When the data context changes are saved with the following call:
await _db.SaveChangesAsync();
All entities that have a state of Added are inserted and committed into the underlying data schema object within the data context.
The following is an example of how we can use the Update() method to update an entity object, then commit the changes through the data context:
public async Task<BookViewModel> UpdateBook(int Id, BookViewModel vm)
{
BookViewModel book = await _db.Books
.Where(a => a.ID == Id)
.SingleOrDefaultAsync();
if (book != null)
{
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;
book.DateUpdated = DateTime.Now;
_db.Update(book);
await _db.SaveChangesAsync();
}
return book;
}
After the command:
await _db.Update(vm);
is executed, the object vm within the entity object list has its state changed to Modified.
When the data context changes are saved with the following call:
await _db.SaveChangesAsync();
All entities that have a state of Modified are updated and committed into the underlying data schema object within the data context.
The following is an example of how we can use the Remove() method to remove an entity object, then commit the changes through the data context:
public async Task<BookViewModel> DeleteBook(int book)
{
BookViewModel book = await _db.Books
.Where(a => a.ID == Id)
.SingleOrDefaultAsync();
if (book != null)
{
_db.Remove(book);
await _db.SaveChangesAsync();
return book;
}
return null;
}
After the command:
await _db.Remove(book);
is executed, the object book within the entity object list has its state changed to Deleted.
When the data context changes are saved with the following call:
await _db.SaveChangesAsync();
All entities that have a state of Deleted are removed and committed into the underlying data schema object within the data context.
Using LINQ Extensions to Query Data with Entity Framework Core
The use of LINQ to query objects within in-memory collections is common. Once a collection of data is loaded in-memory from a table object within a data context, it can be filtered and converted to a list and used within an application. This is achieved using some useful extension methods from the IQueryable interface.
Some of the extension methods that are commonly used are:
Where()
ToListAsync()
SingleOrDefaultAsync()
The extension method ToListAsync() is a thread safe method that retrieves a list of objects from a data context.
Below is an example of how to use the LINQ extension method ToListAsync() to convert a collection of objects to a list:
public async Task<List<BookViewModel>> GetBooks()
{
return await _db.Books.ToListAsync();
}
Below is an example of how to use the LINQ extension method Where() to find records within a collection:
public async Task<List<BookViewModel>> GetBooksFilter(string filter)
{
return await _db.Books.Where(b => b.Title.Contains(filter)).ToListAsync();
}
Below is an example of how to use the LINQ extension method SingleOrDefaultAsync() to return a single record from a collection of objects:
public async Task<BookViewModel> GetBook(int id)
{
BookLoan.Models.BookViewModel book = await _db.Books
.Where(m => m.ID == id)
.SingleOrDefaultAsync();
if (book == null)
return null;
return book;
}
Querying Data with Entity Framework Core DBContext
Another way to select entity objects is to use the FindAsync<Entity>([keys]) DBContext method.
The FindAsync() method takes any number of key parameters and returns any matching records within the entity data set of type Entity.
Below is an example of how to select a single record using this method:
public async Task<BookViewModel> GetSingleBook(int id)
{
BookLoan.Models.BookViewModel book = await _db.Books.FindAsync(id);
if (book == null)
return null;
return book;
}
Ideally, the above is suitable when implementing a custom Repository that accesses table records from the current data context.
We have seen an overview of the methods we can use to query, filter, and manipulate data with Entity Framework Core and LINQ. I showed how to access and manipulate data using commands that are thread safe with the LINQ extension methods of Entity Framework Core. Finally, we saw how to apply the CRUD commands and thread safe record filtering with the DBContext class.
That is all for today’s post.
I hope you 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.
