EntityFramework Core Fundamental
Basic EntityFramework Core
Summary some basic points of EntityFramework Core
- Overview
- Querying the database
- Changing the database content
- Speed up query development — use a LINQ mapper
- Relationship configuration
I. Overview
- Demo Project with SQLite
(1) Create Project
// Create a new project
$ dotnet new console -o 01EFGetStarted// Install Entity Framework Core
$ cd 01EFGetStarted// Install Entity Framework Core
$ dotnet add package Microsoft.EntityFrameworkCore.Sqlite// Create the model (Model.cs)
(2) Create the Models (Model.cs)
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;namespace EFGetStarted
{
public class BloggingContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
public DbSet<Post> Posts { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder options)
=> options.UseSqlite("Data Source=blogging.db");
} public class Blog
{
public int BlogId { get; set; }
public string Url { get; set; } public List<Post> Posts { get; } = new List<Post>();
} public class Post
{
public int PostId { get; set; }
public string Title { get; set; }
public string Content { get; set; } public int BlogId { get; set; }
public Blog Blog { get; set; }
}
}
(3) Create Database
$ dotnet tool install --global dotnet-ef
$ dotnet add package Microsoft.EntityFrameworkCore.Design
$ dotnet ef migrations add InitialCreate
$ dotnet ef database update(4) Create, read, update & delete actions
using System;
using System.Linq;namespace EFGetStarted
{
class Program
{
static void Main()
{
using (var db = new BloggingContext())
{
// Create
Console.WriteLine("Inserting a new blog");
db.Add(new Blog { Url = "http://blogs.msdn.com/adonet" });
db.SaveChanges(); // Read
Console.WriteLine("Querying for a blog");
var blog = db.Blogs
.OrderBy(b => b.BlogId)
.First(); // Update
Console.WriteLine("Updating the blog and adding a post");
blog.Url = "https://devblogs.microsoft.com/dotnet";
blog.Posts.Add(
new Post
{
Title = "Hello World",
Content = "I wrote an app using EF Core!"
});
db.SaveChanges(); // Delete
Console.WriteLine("Delete the blog");
db.Remove(blog);
db.SaveChanges();
}
}
}
}
(5) Run the app
$ dotnet run2. Demo Project with SQL Server
(1) Create new Project
$ dotnet new console -o 02MyFirstEFCoreApp
$ cd 02MyFirstEFCoreApp(2) Add SQL Server Provider
// .NET Core CLI
$ dotnet add package Microsoft.EntityFrameworkCore.SqlServer
// (OR) Visual Studio NuGet Package Manager Dialog
Project > Manage NuGet Packages
// (OR) Visual Studio NuGet Package Manager Console
Tools > NuGet Package Manager > Package Manager Console
Install-Package Microsoft.EntityFrameworkCore.SqlServer [-Version x.x.x]
(3) Add EF Core Tool
// NET CLI
$ dotnet add package Microsoft.EntityFrameworkCore.Design// (OR) Package Manager Console
$ Install-Package Microsoft.EntityFrameworkCore.Tools
What’s next?
- Create the classes that you want EF Core to map to the tables in your database
- Create the application’s DbContext, which is the primary class that you’ll use to configure and access the database
DbContext holds the information EF Core needs to configure that database mapping and is also the class you use in your code to access the database.
Modeling the database
This modeling is EF Core’s way of working out what the database looks like by looking at the classes and other EF Core configuration data.
You might have noticed that figure 1.6 shows no database. This is because when EF Core is building its internal model, it doesn’t look at the database. I emphasize that to show how important it is to build a good model of the database you want; otherwise, problems could occur if a mismatch exists between what EF Core thinks the database looks like and what the actual database is like.
Create Database
$ dotnet ef migrations add InitialCreate
$ dotnet ef database updateGenerated Database
Reading data from Database
The LINQ query db.Books.AsNoTracking().Include(a => a.Author) accesses the DbSet<Book> property in the application’s DbContext and adds a .Include (a => a.Author) at the end to ask that the Author parts of the relationship are loaded too. This is converted by the database provider into an SQL command to access the database. The resulting SQL is cached to avoid the cost of retranslation if the same database access is used again.
After the database provider has read the data, EF Core puts the data through a process that (a) creates instances of the .NET classes and (b) uses the database relational links, called foreign keys, to correctly link the .NET classes together by reference — called a relationship fixup. The result is a set of .NET class instances linked in the correct way. In this example, two books have the same author, Martin Fowler, so the Author property of those two books points to one Author class.
Because the code includes the command AsNoTracking, EF Core knows to suppress the creation of a tracking snapshot. Tracking snapshots are used for spotting changes to data; you’ll see this in the example of editing the WebUrl. Because this is a read-only query, suppressing the tracking snapshot makes the command faster.
Updating the Database
II. Querying the database
The book app’s relational database
- One-to-one relationship: PriceOffer to a Book
- One-to-many relationship: Reviews to a Book
- Many-to-many relationship: Books to Authors
(1) Create new Project
$ dotnet new mvc -o 03EfCoreDemo
$ cd 03EfCoreDemo
$ dotnet add package Microsoft.EntityFrameworkCore.SqlServer
$ dotnet add package Microsoft.EntityFrameworkCore.Design*You can refer code here: https://github.com/quangvt/03EfCoreDemo
Now we will create code for Model & DbContext
Earlier I showed you how to set up the application’s DbContext by overriding its OnConfiguring method. The downside of that approach is that the connection string is fixed. In this chapter, you’ll use another approach, because we want to use a different database for development and unit testing. You’ll use a method that provides that via the application’s DbContext constructor.
(2) Create Models
Review.cs
namespace _03EfCoreDemo.Model
{
public class Review
{
public int ReviewId { get; set; }
public string VoterName { get; set; }
public int NumStars { get; set; }
public string Comment { get; set; } //-----------------------------------------
//Relationships public int BookId { get; set; }
}
}
PriceOffer.cs
namespace _03EfCoreDemo.Model
{
public class PriceOffer
{
public int PriceOfferId { get; set; }
public decimal NewPrice { get; set; }
public string PromotionalText { get; set; } //-----------------------------------------------
//Relationships public int BookId { get; set; }
}
}
Author.cs
using System.Collections.Generic;namespace _03EfCoreDemo.Model
{
public class Author
{
public int AuthorId { get; set; }
public string Name { get; set; } //------------------------------
//Relationships public ICollection<BookAuthor> BooksLink { get; set; }
}
}
Book.cs
using System;
using System.Collections.Generic;namespace _03EfCoreDemo.Model
{
public class Book
{
public int BookId { get; set; } public string Title { get; set; }
public string Description { get; set; }
public DateTime PublishedOn { get; set; } public string Publisher { get; set; }
public decimal Price { get; set; } /// <summary>
/// Holds the url to get the image of the book
/// </summary>
public string ImageUrl { get; set; } public PriceOffer Promotion { get; set; }
public ICollection<Review> Reviews { get; set; }
public ICollection<BookAuthor> AuthorsLink { get; set; }
}
}
BookAuthor.cs
namespace _03EfCoreDemo.Model
{
public class BookAuthor
{
public int BookId { get; set; }
public int AuthorId { get; set; }
public byte Order { get; set; } //-----------------------------
//Relationships public Book Book { get; set; }
public Author Author { get; set; }
}
}
Infrastructure/EfCoreContext.cs
using _03EfCoreDemo.Models;
using Microsoft.EntityFrameworkCore;namespace _03EfCoreDemo.Infrastructure
{
public class EfCoreContext : DbContext
{
public EfCoreContext(DbContextOptions<EfCoreContext> options)
: base(options)
{
} public DbSet<Book> Books { get; set; }
public DbSet<Author> Authors { get; set; }
public DbSet<PriceOffer> PriceOffers { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<BookAuthor>()
.HasKey(x => new { x.BookId, x.AuthorId }); modelBuilder.Entity<Book>()
.Property<decimal>(t => t.Price)
.HasColumnType("decimal(18,2)"); modelBuilder.Entity<PriceOffer>()
.Property<decimal>(t => t.NewPrice)
.HasColumnType("decimal(18,2)");
}
}
}
Startup.cs
using System.Text.Json.Serialization;
using _03EfCoreDemo.Infrastructure;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;namespace _03EfCoreDemo
{
public class Startup
{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
} public IConfiguration Configuration { get; } // This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddControllersWithViews()
.AddJsonOptions(opts =>
{
opts.JsonSerializerOptions.Converters.Add(new JsonStringEnumConverter());
}); var connection = Configuration.GetConnectionString("DefaultConnection"); services.AddDbContext<EfCoreContext>(options => options.UseSqlServer(connection)); services.AddHttpContextAccessor();
} // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
else
{
app.UseExceptionHandler("/Home/Error");
} app.UseStaticFiles(); app.UseRouting(); app.UseEndpoints(endpoints =>
{
endpoints.MapControllerRoute(
name: "default",
pattern: "{controller=Home}/{action=Index}/{id?}");
});
}
}
}
Add this to appsetings.Development.json
"ConnectionStrings": {
"DefaultConnection": "Server=tcp:localhost,1430;Database=03EfCoreDemoe;User Id=YourUserName;Password=YourPassword;"
},(3) Update Database Structure
There are a few ways to create a database using EF Core, but the normal way is to use EF Core’s migrations feature. This uses your application’s DbContext and the entity classes, like the ones I’ve just described, as the model for the database structure. The Add-Migration command first models your database and then, using that model, build commands to create a database that fits that model.
$ dotnet ef migrations add InitialMigration
$ dotnet ef database update- You can update
dotnet ef toolby command:
$ dotnet tool update --global dotnet-ef --version 3.1.4
Understanding database queries
In EF Core, a query is executed against the database when
- It’s enumerated by a foreach statement.
- It’s enumerated by a collection operation such as ToArray, ToDictionary, ToList, ToListAsync, and so forth.
- LINQ operators such as First or Any are specified in the outermost part of the query.
- You use certain EF Core commands, such as Load, which you’ll use in the explicit loading of a relationship later in this chapter.
Loading Related Data
You can load data by methods: Eager Loading, Explicit Loading, Select Loading, and Lazy Loading (from EF Core 2.1).
You need to be aware that EF Core won’t load any relationships in an entity class unless you ask it to. If you load a Book class, each of the relationship properties in the Book entity class (Promotion, Reviews, and AuthorsLink) will be null by default.
This default behavior of not loading relationships is correct because it means that EF Core minimizes the database accesses. If you want to load a relationship, you need to add code to tell EF Core to do that. The next sections describe the three approaches, with their pros and cons, to get EF Core to load a relationship.
- Eager Loading: Loading relationships with the primary entity class
SELECT
[t].[BookId], [t].[Description], [t].[ImageUrl], [t].[Price],
[t].[PublishedOn], [t].[Publisher], [t].[Title], [t0].[BookId],
[t0].[AuthorId], [t0].[Order], [t0].[AuthorId0], [t0].[Name],
[r].[ReviewId], [r].[BookId], [r].[Comment], [r].[NumStars],
[r].[VoterName], [p].[PriceOfferId], [p].[BookId], [p].[NewPrice],
[p].[PromotionalText]
FROM (
SELECT TOP(1)
[b].[BookId], [b].[Description], [b].[ImageUrl],
[b].[Price], [b].[PublishedOn], [b].[Publisher],
[b].[Title]
FROM [Books] AS [b]
) AS [t]
LEFT JOIN (
SELECT
[b0].[BookId], [b0].[AuthorId], [b0].[Order],
[a].[AuthorId] AS [AuthorId0], [a].[Name]
FROM [BookAuthor] AS [b0]
INNER JOIN [Authors] AS [a] ON [b0].[AuthorId] = [a].[AuthorId]
) AS [t0] ON [t].[BookId] = [t0].[BookId]
LEFT JOIN [Review] AS [r] ON [t].[BookId] = [r].[BookId]
LEFT JOIN [PriceOffers] AS [p] ON [t].[BookId] = [p].[BookId]
ORDER BY [t].[BookId], [t0].[BookId], [t0].[AuthorId],
[t0].[AuthorId0], [r].[ReviewId], [p].[PriceOfferId]- Explicit loading: loading relationships after the primary entity class
The second approach to loading data is explicit loading; after you’ve loaded the primary entity class, you can explicitly load any other relationships you want.
Explicit loading has an extra command that allows a query to be applied to the relation- ship, rather than just loading it. Listing 2.6 shows use of the explicit-loading method Query to obtain the count of the number of reviews and to load all the star ratings of each review. You can use any standard LINQ command after the Query method; for instance, Where, OrderBy, and so forth.
The advantage of explicit loading is that you can load a relationship of an entity class later. I’ve found this useful when using a library that loads only the primary entity class and I need one of its relationships. Explicit loading can also be useful if you need that related data only in some circumstances. You might also find explicit loading useful in complex business logic because you can leave the job of loading the specific relationships to the parts of the business logic that need it.
- Select loading: loading specific parts of primary entity class and any relationships
The third approach to loading data is to use the LINQ Select method to specifically pick out the data you want, which I call select loading.
SELECT TOP(1) [p].[Title], [p].[Price], ( SELECT COUNT(*)
FROM [Review] AS [r0]
WHERE [p].[BookId] = [r0].[BookId] )
FROM [Books] AS [p]- Lazy loading: coming in EF Core version 2.1
The downside of lazy loading is that it requires more database accesses to lazy load data, which can make your queries slow
Using client vs. server evaluation: moving part of your query into software
WARNING: EF Core will issue an error when a client vs. server evaluation occurs.
All the queries you’ve seen so far are ones that EF Core can convert to commands that can be run on the database server. But EF Core has a feature called client vs. server evaluation, which allows you to include methods in your query that can’t be run on the database — for example, on relational databases, methods that EF Core can’t convert to SQL commands. EF Core runs these non-server-runnable commands after the data has come back from the database.
One extreme example of the problem is that client vs. server evaluation allows you to sort on a client-side evaluated property, which means the sorting is done in the client rather than in the database server. I tried this by replacing the First command with .Sort(p => p. AuthorsString) in listing 2.8 and returning a list of books. In that case, EF Core produces SQL code that reads all the books, then reads each row individually, twice, which is definitely not optimal.
Building Complex Query
Note: To work with EF Core’s select loading, the class that’s going to receive the data must have a default constructor (it can be created without needing to provide any properties to the constructor), the class must not be static, and the properties must have public setters.
Next, you’ll build a select query that fills in every property in BoolListDto. Because you want to use this with other query parts, such as sort, filter, and paging, you’ll use the IQueryable<T> type to create a method called MapBookToDto that takes in IQueryable<Book> and returns IQueryable<BookListDto>.
Add Sorting/Filtering/Paging
Other Filters
You can access another SQL command, called LIKE, through the EF.Function.Like method. This provides a simple pattern-matching approach using _ (underscore) to match any letter, and % to match zero-to-many characters.
var books = context.Books
.Where(p => EF.Functions.Like(p.Title, "The ___ sat on the %.")).ToList();The other important thing to know is that the case sensitivity of a string search executed by SQL commands depends on a setting in the database called collation.
Combine all together:
III. Changing the database content
EF Core’s entity State
When you read in an entity, it’s tracked by EF Core by default. This is known as a tracked entity, and EF Core holds extra information on the entity.
Definition: Tracked entities are entity instances that have been read in from the database by using a query that didn’t include the AsNoTracking method. Alter- natively, after an entity instance has been used as a parameter to EF Core meth- ods (such as Add, Update, or Delete), it becomes tracked.
The State of an entity can be obtained using the following EF command
context.Entry(someEntityInstance).State
Here’s a list of the possible states and what happens if SaveChanges is called:
- Added — The entity doesn’t yet exist in the database. SaveChanges inserts it.
- Unchanged — The entity exists in the database and hasn’t been modified on the client. SaveChanges ignores it.
- Modified — The entity exists in the database and has been modified on the client. SaveChanges updates it.
- Deleted — The entity exists in the database but should be deleted. SaveChanges deletes it.
- Detached — The entity you provided isn’t tracked. SaveChanges doesn’t see it.
1. Creating a new row in a database table
1.1 Add Single Record not has a relationship.
(1) Add to Views/Home/Index.cshtml
<div>
<a asp-controller="Home" asp-action="AddSingle">AddSingle</a>
</div>(2) Update Controllers/HomeController.cs
private readonly EfCoreContext _context;
...
public HomeController(ILogger<HomeController> logger,
EfCoreContext context)
{
_logger = logger;
_context = context;
}
...
public IActionResult AddSingle()
{
var rand = new Random(); var itemToAdd = new Author
{
Name = "Hello World " + rand.Next()
}; _context.Add(itemToAdd);
_context.SaveChanges(); return RedirectToAction(nameof(Index));
}
(3) And Start Run/Debug project
Note: If you can not run with SSL, you can change the configuration of Properties/launchSettings.json
...
"_03EfCoreDemo": {
"commandName": "Project",
"launchBrowser": true,
"applicationUrl": "http://localhost:5000",
"environmentVariables": {
"ASPNETCORE_ENVIRONMENT": "Development"
}
}
...And you can check the result in database
Generated SQL Commands
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (39ms) [Parameters=[@p0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
INSERT INTO [Authors] ([Name])
VALUES (@p0);
SELECT [AuthorId]
FROM [Authors]
WHERE @@ROWCOUNT = 1 AND [AuthorId] = scope_identity();How to log the generated SQL Command to Console? In order to log these SQL Commands to Console, you have to configure Logging for EF Core as following:
Update Infrastructure/EfCoreContext.cs
public static readonly ILoggerFactory MyLoggerFactory
= LoggerFactory.Create(builder => { builder.AddConsole(); }); protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder.UseLoggerFactory(MyLoggerFactory);
Because you add the entity instance itemToAdd that wasn’t originally tracked, EF Core starts to track it and sets its State to Added. After SaveChanges is called, EF Core finds a tracked entity of type Author with a State of Added, so it’s added as a new row in the database table associated with the Author class.
1.2 Creating a book with a review
Next, you’ll look at a create that includes relationships — in this case, adding a new book with a review.
The thing to note from this is that you add only the Book entity class to the application’s DbContext property Books, but the related Review entity class is also written to the database. This is because EF Core follows all the relational links and finds the other entity classes.
Now, I will show you how to update the project to run this sample.
(1) Update Views/Home/Index.cshtml
<div><a asp-controller="Home" asp-action="AddBook">AddBook</a></div>(2) Update Controllers/HomeController.cs
public IActionResult AddBook()
{
var rand = new Random();var book = new Book
{
Title = "Test Book" + rand.Next(),
PublishedOn = DateTime.Today,
Reviews = new List<Review>()
{
new Review
{
NumStars = 5,
Comment = "Great test book!" + rand.Next(),
VoterName = "Mr U Test" + rand.Next()
}
}
}; _context.Add(book);
_context.SaveChanges(); return RedirectToAction(nameof(Index));
}
(3) Run/Debug Project
(4) Generated SQL
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (39ms) [Parameters=[@p0='?' (Size = 4000), @p1='?' (Size = 4000), @p2='?' (DbType = Decimal), @p3='?' (DbType = DateTime2), @p4='?' (Size = 4000), @p5='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
INSERT INTO [Books] ([Description], [ImageUrl], [Price], [PublishedOn], [Publisher], [Title])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5);
SELECT [BookId]
FROM [Books]
WHERE @@ROWCOUNT = 1 AND [BookId] = scope_identity();
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (6ms) [Parameters=[@p6='?' (DbType = Int32), @p7='?' (Size = 4000), @p8='?' (DbType = Int32), @p9='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
INSERT INTO [Review] ([BookId], [Comment], [NumStars], [VoterName])
VALUES (@p6, @p7, @p8, @p9);
SELECT [ReviewId]
FROM [Review]
WHERE @@ROWCOUNT = 1 AND [ReviewId] = scope_identity();(5) Database data
1.3 An example that has one instance already in the database
The other situation you may need to deal with is creating a new entity containing a navigational property that uses another entity already in the database. If you want to create a new Book entity that has an Author that already exists in the database, you need to obtain a tracked instance of the Author entity you want to add to your new Book entity.
And we do the same 5 steps above
(1) Update Views/Home/Index.cshtml
<div><a asp-controller="Home" asp-action="AddBookWithExistedAuthor">AddBookWithExistedAuthor</a></div>(2) Update Controllers/HomeController.cs
public IActionResult AddBookWithExistedAuthor()
{
var rand = new Random(); var author = _context.Authors.First(); var book = new Book
{
Title = "Test Book" + rand.Next(),
PublishedOn = DateTime.Today
}; book.AuthorsLink = new List<BookAuthor>
{
new BookAuthor
{
Book = book,
Author = author
}
}; _context.Add(book);
_context.SaveChanges(); return RedirectToAction(nameof(Index));
}
(3) Run/Debug Project
(4) Generated SQL
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (21ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [a].[AuthorId], [a].[Name]
FROM [Authors] AS [a]
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (26ms) [Parameters=[@p0='?' (Size = 4000), @p1='?' (Size = 4000), @p2='?' (DbType = Decimal), @p3='?' (DbType = DateTime2), @p4='?' (Size = 4000), @p5='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
INSERT INTO [Books] ([Description], [ImageUrl], [Price], [PublishedOn], [Publisher], [Title])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5);
SELECT [BookId]
FROM [Books]
WHERE @@ROWCOUNT = 1 AND [BookId] = scope_identity();
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (3ms) [Parameters=[@p6='?' (DbType = Int32), @p7='?' (DbType = Int32), @p8='?' (Size = 1) (DbType = Byte)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
INSERT INTO [BookAuthor] ([BookId], [AuthorId], [Order])
VALUES (@p6, @p7, @p8);(5) Database data
2. Updating existing rows in a database table for two types of applications
Updating a database row is achieved in three stages:
- Read the data (database row), possibly with some relationships.
- Change one or more properties (database columns).
- Write the changes back to the database (update the row).
In this section, you’ll ignore any relationships and focus on the three stages. In the next section, you’ll learn how to update relationships by adding more commands to each stage.
When the SaveChanges method is called, it runs a method called DetectChanges, which compares the tracking snapshot copy with the copy that it handed to the application when the query was originally executed. From this, it decides that only the PublishedOn property has been changed, and EF Core builds the SQL to update that.
Handling disconnected updates in a web application
As you learned in the previous section, an update is a three-stage process, needing a read, an update, and a SaveChanges call to all be executed using the same instance of the application’s DbContext. The problem is that for certain applications, such as websites and RESTful APIs, using the same instance of the application’s DbContext isn’t possible. In these types of applications an update consists of two stages:
- The first stage is an initial read, done in one instance of the application’s DbContext.
- The second stage then applies the update using a new instance of the application’s DbContext.
In EF Core, this is called a disconnected update, whereas the update example in listing 3.5 is known as a connected update. Here are the two main ways of handling disconnected updates:
- You send only the data you need to update back from the first stage
- You send all the data needed to re-create the entity class back from the first stage
Now I’ll give you an example of each approach for handling disconnected updates.
- Disconnected update, with reload
There are several ways of controlling what data is returned/accepted by the webserver. In ASP.NET Core, you have two attributes, BindRequired and BindNever, that you can apply to properties in a class to require or stop, respectively, the data being input to the second stage. A more general approach, and one I prefer, is to use a special class that contains only properties that should be sent/received. This class is referred to as a DTO or ViewModel.
The Find command
When you want to update a specific entity and you have its primary key, the Find command is a quick way of loading the entity. This command has two forms:
DbSet’s Find(key(s)); for instance, context.Book.Find(key)
DbContext’s Find<T>(key(s)); for instance, context.Find<Book>(key)
Both Find methods take one key (see listing 3.8) or multiple keys, known as composite keys (the BookAuthor entity has a composite key, consisting of the BookId and the AuthorId). The key parameters must be in the same order that the composite key is defined in. Find returns null if no matching entity with that key is found.
Also, Find checks the current application’s DbContext to see whether the required entity instance has already been loaded, which can save access to the database. This makes the Find methods efficient to use when you want to load only a specific entity.
The advantage of this reload update approach is it’s more secure (in our example, send- ing/returning the price of the book over HTTP would allow someone to alter it) and it’s faster because of fewer data. The downside is you have to write code to copy over the specific properties you want to update.
- Disconnected update, sending all the data
In some cases, all the data may be sent back, so there’s no reason to reload the original data. This can happen for simple entity classes, in some RESTful APIs, or process-to-process communication.
The plus side of this approach is that the database update is quicker, because you don’t have the extra read of the original data. You also don’t have to write code to copy over the specific properties you want to update, which you did need to do in the previous approach.
The downside is that more data can be transferred and, unless the API is carefully designed, it can be difficult to reconcile the data you receive to the data already in the database. Also, you’re trusting the external system to correctly remember all the data, especially the primary keys of your system.
3. Updating entities with one-to-one, one-to-many, and many-to-many relationships
Now that we’ve established the three basic steps to update the database, it’s time to look at updating relationships between entity classes — for example, adding a new review to a book.
The terms principal and dependent are used in EF to define parts of a relationship:
- Principal entity—Contains the key property(s) that the dependent relationship refers to via a foreign key(s)
- Dependent entity—Contains the foreign key property(s) that refers to the principal entity
3.1 Updating one-to-one relationships—adding a PriceOffer to a book
Connected State Update
The connected state update assumes you’re using the same context for both the read and the update. Listing 3.12 shows an example of the code, which has three stages:
- Load the Book entity with any existing PriceOffer relationship.
- Set the relationship to the new PriceOffer entity you want to apply to this book.
- Call SaveChanges to update the database.
What happens if there’s an existing promotion on the book (the Promotion property in the Book entity class isn’t null)? That’s why the Include(p => p.Promotion) command in the query that loaded the Book entity class is so important. Because of that Include method, EF Core will know there’s an existing PriceOffer assigned to this book and will delete that before adding the new version.
Disconnected State Update
In the disconnected state, the information to define which book to update and what to put in the PriceOffer entity class would be passed back from stage 1 to stage 2.
Alternative way of updating the relationship — Creating a new row directly
We’ve approached this update as changing a relationship in the Book entity class, but you can also approach it as creating/deleting a row in the PriceOffers table. This listing creates a PriceOffer entity (section 3.5 covers deletion).
NOTE The PriceOffer entity class doesn’t have a relational property link back to the Book class (public Book BookLink {get; set;}). If it did, you could set the BookLink to the book entity class instead of setting the foreign key. Either setting the foreign key(s) or setting a relational link back to the principal entity will tell EF Core to set up the relationship.
The advantage of creating the dependent entity class is that it saves you from needing to reload the principal entity class (in this case, Book) in a disconnected state. The downside is that EF Core doesn’t help you with the relationships. For instance, in this case, if there was an existing PriceOffer on the book and you added another, SaveChanges would fail because you’d have two PriceOffer rows with the same key.
I prefer updating the principal entity class’s relationship in most one-to-one cases.
3.2 Updating one-to-many relationships—adding a review to a book
The one-to-many relationship in the book app database is represented by book reviews: a user of the site can add a review to a book. There can be any number of reviews, from none to a lot. This listing shows the Review-dependent entity class, which links to the Books table via the foreign key called BookId.
Connected State Update
Altering/Replacing All the One-To-Many Relationships
EF Core makes replacing the whole collection easy. If you assign a new collection to a one-to-many relationship that has been loaded with tracking (for instance, by using the Include method), EF Core will replace the whole collection with the new one. If the items in the collection can be linked to only the principal class (the dependent class has a non-nullable foreign key), then, by default, EF Core will delete the items that were in the collection that have been removed.
Disconnected State Update
Alternative way of updating relationship — Creating a new row directly
Adding a row directly to the database has some advantages, because loading all the one-to-many relationships might turn out to be a lot of data if you have lots of items and/or they’re big. Therefore, keep this approach in mind if you have performance issues.
NOTE My experiments show that not loading the relationship, and then assigning a new collection to a one-to-many relationship, is equivalent to creating a new row directly. But I don’t recommend doing this because it’s not the normal update pattern, and someone else (or even yourself) might come back later and misread your intentions.
3.3 Updating many-to-many relationships — changing a book’s authors
In EF Core, we talk about many-to-many relationships, but a relational database doesn’t directly implement many-to-many relationships. Instead, we’re dealing with two one-to-many relationships, as shown in figure 3.4.
The change from the other examples is that the original author of Quantum Networking, Future Person, isn’t deleted when the link to him is removed (Future Person has, in my test data, written only Quantum Networking). That’s because it’s the one end of a one-to-many relationship, and these aren’t dependent on the book directly; in fact, the Author class is a principal entity, with the BookAuthor classes being dependent on it.
What’s deleted is the BookAuthor row that used to link the Quantum Networking book to its author, Future Person, and a new BookAuthor row is added to link Martin Fowler to Quantum Networking.
Alternative way of updating relationship — Creating a new row directly
Again, you could create/delete a BookAuthor entity class directly, but you’d still need a tracked instance of both the Book and Author entity classes so it won’t save on database access. Another simpler approach to building/finding the linking class (for instance, BookAuthor) is to use the primary keys of the Books and Authors rows
3.4 Advanced feature—updating relationships via foreign keys
Up to this point, I’ve shown you how to update relationships by using the entity classes themselves; for instance, when you added a review to a book, you loaded the Book entity with all its Reviews. That’s fine, but in a disconnected state, you have to load the Book and all its Reviews from the book’s primary key that came back from the browser/RESTful API. In many situations, you can cut out the loading of the entity classes and set the foreign keys instead.
This applies to most of the disconnected updates I’ve shown before, but let me give you an example of moving a review from one book to another (I know, not a likely scenario in the real world, but it makes a simple example). The following listing carries out the update after the user has typed in the request. The code assumes that the ReviewId of the Review the user wants to change, and new BookId that they want to attach the review to, are returned in a variable called dto.
The benefit of this technique is that you don’t have to load the Book entity class, nor use an Include command to load all the Reviews associated with this book. In our example book app, these entities aren’t that big, but in a real application, the principal and dependent entities could be quite large. In disconnected systems, where we often send just the primary keys over the disconnect, this can be a useful approach to cut down on database accesses and hence improve performance.
NOTE When updating relationships via foreign keys, you may need to access entities that don’t have a DbSet<T> property in the application’s DbContext, so how can you read in the data? Listing 3.21 uses the Find<T> method, but if you need a more complex query, you can access any entity via the Set<T> method; for instance, context.Set<Review>().Where(p => p.NumVotes > 5).
4. Deleting single entities, and entities with relationships, from a database
4.1 Using a soft delete—using model-level query filters to “hide” entities
One school of thought says that you shouldn’t delete anything from a database, but use status to hide it, known as a soft delete. (See Udi Dahan’s post, “Don’t Delete — Just Don’t” at http://udidahan.com/2009/09/01/dont-delete-just-dont/). I think this is a sensible approach, and EF Core provides a feature called model-level query filters that allow a soft delete to be simply implemented.
The thinking behind a soft delete is that in real-world applications, data doesn’t stop being data: it transforms into another state. To show you how this works, you’ll add the soft-delete feature to the list of Book entities. To do so, you need to do two things:
- Add a boolean property called SoftDeleted to the Book entity class. If that property is true, the Book entity instance is soft deleted; it shouldn’t be found in a normal query.
- Add a model-level query filter via EF Core fluent configuration commands. The effect of this is to apply an extra Where filter to any access to the Books table.
public class Book
{
//... other properties left out for clarity
public bool SoftDeleted { get; set; }
}Adding the model-level query filter to the DbSet<Book>Books property means adding a fluent configuration command to the application’s DbContext.
To soft delete a Book entity, you need to set the SoftDeleted property to true and call SaveChanges. Then any query on the Book entities will exclude the Book entities that have the SoftDeleted property set to true.
NOTE If you want to access all the entities that have a model-level filter, you add the IgnoreQueryFilters method to the query — or instance, context.Books.IgnoreQueryFilters(). This bypasses any model-level filter on an entity. Also, the Find method isn’t affected by a model-level filter.
Now that we’ve covered the soft-delete approach, let’s cover the ways to delete an entity from the database. We’ll start with a straightforward example and work up to deleting an entity that has relationships.
4.2 Deleting a dependent-only entity — no relationships
Calling the Remove method sets the State of the entity provided as the parameter to Deleted. Then when you call SaveChanges, EF Core finds the entity marked as Deleted and creates the correct database commands to delete the appropriate row from the table the entity referred to (in this case, a row in the PriceOffers table).
The SQL command that EF Core produces for SQL Server is shown in the following snippet:
SET NOCOUNT ON;
DELETE FROM [PriceOffers] WHERE [BookId] = @p0;
SELECT @@ROWCOUNT;4.3 Deleting a principal entity that has relationships
Relational databases need to keep referential integrity, so if you delete a row in a table that other rows are pointing to via a foreign key, something has to happen to stop referential integrity from being lost.
The following are three ways that you can set a database to keep referential integrity when you delete a principal entity with dependent entities:
- You can tell the database server to delete the dependent entities that rely on the principal entity. This is known as cascade deletes.
- You can tell the database server to set the foreign keys of the dependent entities to null if the column allows that.
- If neither of those rules are set up, the database server will raise an error if you try to delete a principal entity with dependent entities.
Deleting a book with its dependent relationships
Here you’re going to delete a Book entity, which is a principal entity with three dependent relationships: Promotion, Reviews, and AuthorsLink. These three dependent entities can’t exist without the Book entity; they have a foreign key that’s non-nullable that points to a specific Book row.
By default, EF Core uses cascade deletes for dependent relationships with non-nullable foreign keys. Cascade deletes make deleting principal entities easier from the developer’s point of view, because the other two rules need extra code to handle deleting the dependent entities. But in many business applications, this may not be the appropriate approach. This chapter uses the cascade delete approach because it’s EF Core’s default.
With that in mind, let’s see this in action by deleting a book that has relationships using the default cascade delete setting. This listing loads the Promotion (PriceOffer entity class) and Reviews relationships with the Book entity class before deleting that Book.
That last statement, indicating that all are deleted by EF Core, is an important point. Because you put in the three Includes, EF Core knew about the dependent entities and performed the delete. If you didn’t incorporate the Includes in your code, EF Core wouldn’t know about the dependent entities and couldn’t delete the three dependent entities. In that case, the problem of keeping referential integrity would fall to the database server, and its response would depend on how the DELETE ON part of the foreign-key constraint was set up. Databases created by EF Core would, by default, be set to use cascade deletes.
IV. Speed up query development — use a LINQ mapper
Noticed that a large percentage (>85%) of the database queries I built needed to extract data from multiple entities. The most efficient way to achieve this, both in performance and speed of development, was to use EF Core’s Select method to pick the exact properties from an entity class and map the related data into a DTO. Although writing a LINQ Select statement to map to a DTO isn’t hard, it’s time-consuming, can be error-prone, and most important, it’s boring!
The answer to writing a LINQ Select statement quickly and accurately is to use an object-to-object mapper (I refer to this as a mapper from now on), with certain characteristics, to automatically build the Select statement.
Definition: An object-to-object mapper is a piece of software that transfers data from one object (class), including any nested object, to another object. A map- per can work out the mapping between the two objects in several ways; the most useful ones do so automatically.
To be able to use a mapper with EF Core, the mapper library must support IQueryable mapping; the mapper must be able to produce a LINQ query capable of copying the data from one IQueryable source to another. Quite a few mappers support IQueryable, but the best known, and possibly best supported, mapper that handles IQueryable mapping is AutoMapper
The next listing builds the Select method by using AutoMapper and then uses a query to create an EF Core query. The code uses AutoMapper’s ProjectTo method which, instead of copying the data directly, produces the LINQ commands needed to copy that data. You then use those LINQ commands to access the database via EF Core.
It’s much quicker to build Select queries by using AutoMapper than the hand-coded way shown next, especially if you have more properties to copy. It’s also a lot less boring.
AutoMapper has many features, including allowing the developer to include custom LINQ mapping for mappings that are too complex for AutoMapper to automatically map. Nearly all Select queries can be built with the help of AutoMapper.
NOTE: The range of features and settings of AutoMapper can take some time to get used to. A good start is https://github.com/AutoMapper/AutoMapper/ wiki/Getting-started.
V. Relationship Configurations
There are some configurations are not described in this article please refer to Microsoft document (https://docs.microsoft.com/en-us/ef/core/)
- Config by Convention, by Data Annotation, by Fluent API
- Configure Nonrelationship: Index, Column Name, Column Type, Column Size, nullable, TableName, Shadow property, Backing field, Exclude (property, class), model-level query filter…
*Convention/Data Annotation/Fluent API - Configure Relationship: Convention/Data Annotation/Fluent API
- Advance Features: DbFunction/Computed Column/Setting default value/Sequences/Database-generated properties/Simultaneous Update
- Deeper into the DbContext: tracks changes/raw SQL command/Context.Model to access EF Core’view of data/Handling database connection problems:
- FromSql method, which allows you to use a raw SQL command in an EF Core query
- ExecuteSqlCommand method, which executes a nonquery command
- Reload command, used to refresh an EF Core–loaded entity that has been changed by an ExecuteSqlCommand method
- EF Core’s GetDbConnection method, which provides low-level database access libraries to access the database directly
WARNING If you form the command string by using the C# 6 string interpolation feature outside the FromSql command, you lose the SQL injection attack detection built into the FromSql method.
The FromSql method has some limitations:
- The column names in the result set must match the column names that properties are mapped to.
- If you’re loading to an entity class, the SQL query must return data for all properties of the entity type. This is because the entity will be tracked after the query.
You can use an Include method with the FromSql method, if you’re querying an entity class and not executing a stored procedure. The following listing shows an example in which you call an SQL user-defined function. In this case, you use the Include method to eager load the Book’s Reviews collection.
Done. There are some basics about Entity Framework Core. Hope this help.
References
- Entity Framework Core in Action — John P Smith
- Source Code for this article: https://github.com/quangvt/03EfCoreDemo
