Querying and Saving Related Data
Julie Lerman
MOST TRUSTED AUTHORITY ON ENTITY FRAMEWORK
@julielerman thedatafarm.com
Inserting, update & deleting related data
Module Saving related data that wasn’t tracked
Overview Eager loading queries and shaping results
with projections
Loading related data for objects in memory
Filtering queries with related data
Querying and persisting across many-to-
many relationships
Querying and persisting across one-to-one
relationships
Inserting Related Data
Change Tracker Response
to New Child of Existing Parent
As child’s key value is not set, Child’s FK value to parent
state will automatically be (e.g. Quote.SamuraiId) is set
“Added” to parent’s key
DbContext/DbSet Tracking Methods
Add
Update
Remove
Attach
EF Core’s Default Entity State of Graph Data
Has Key Value No Key Value
Add(graph) Added* Added
Update(graph) Modified Added
Attach(graph) Unchanged Added
*Database will throw an exception if IDENTITY INSERT is illegal (default)
“Foreign keys? NEVER!
They will make my classes dirty!”
“Foreign keys in my classes
make my life so much simpler!”
Eager Loading Related Data
Methods to Load Related Data
Eager Loading Query Projections
Include related objects in query Define the shape of query results
Explicit Loading Lazy Loading*
Request related data of
objects in memory On-the-fly retrieval of related data
*Arrived with EF Core 2.1
SQL Generated from Includes
Query is broken
Single Query up into multiple
with Left Join(s) queries sent in a
single command
Default With AsSplitQuery()
Eager Loading with Include
EF->EF6 ( 2008 -> today) EF Core 5 (2020)
Include returns 100% of related objects Filtered Include!
EF Core 1, 2 & 3 (2016-2019)
Include returns 100% of related objects
Eager Loading with Include
EF->EF6 ( 2008 -> today) EF Core 5 (2020)
Include returns 100% of related objects Filtered Include!
EF Core 1, 2 & 3 (2016-2019)
Include returns 100% of related objects
Eager Loading with Include
EF->EF6 ( 2008 -> today) EF Core 5 (2020)
Include returns 100% of related objects Filtered Include!
EF Core 1, 2 & 3 (2016-2019)
Include returns 100% of related objects
Eager Loading with Include
EF Core 5 (2020)
Filtered Include!
Eager Loading with Include
EF Core 5 (2020)
Filtered Include!
Query Workflow
Receives tabular results
Materializes results
Samurais Quotes as objects
for those
Samurais
Adds tracking details
to DbContext instance
DbContext connects
the relationships
More Ways to Use Include
_context.Samurais t Get quotes for the samurai
.Include(s => s.Quotes) t Then get the translations for those
quotes
.ThenInclude(q=>q.Translations)
.FirstOrDefault();
t Get quotes for samurais
_context.Samurais t Also get the clan for samurais
.Include(s => s.Quotes)
.Include(s=>s.Clan)
.FirstOrDefault();
_context.Samurais
.Include(s=>s.Quotes) t Include child objects
_context.Samurais
t Include children & grandchildren
.Include(s=>s.Quotes)
.ThenInclude(q=>q.Translations)
_context.Samurais t Include just grandchildren
.Include(s=>s.Quotes.Translations)
_context.Samurais t Include different children
.Include(s=>s.Quotes)
.Include(s=>s.Clan)
…Various combinations…
Projecting Related Data in Queries
EF Core Can Only Track Entities
Recognized by the DbContext
Entities that are
Anonymous types properties of an
are not tracked anonymous type
are tracked
Loading Related Data for Objects Already
in Memory
Methods to Load Related Data
Eager Loading Query Projections
Include related objects in query Define the shape of query results
Explicit Loading Lazy Loading*
Request related data of
objects in memory On-the-fly retrieval of related data
*Arrived with EF Core 2.1
With samurai object already in memory
_context.Entry(samurai).Collection(s => s.Quotes).Load();
_context.Entry(samurai).Reference(s => s.Horse).Load();
Explicit Loading
Explicitly retrieve related data for objects already in memory
DbContext.Entry().Collection().Load()
DbContext.Entry().Reference().Load
More on Explicit Loading
You can only load from a single object
Profile to determine if LINQ query would be better
performance
Filter loaded data using the Query method
var happyQuotes = context.Entry(samurai)
.Collection(b => b.Quotes)
.Query()
.Where(q => q.Quote.Contains(“Happy”)
.ToList();
Lazy Loading
is
OFF
by default
Lazy Loading
Happens implicitly by mention of the navigation
Enable with these requirements:
Every navigation property in every entity must be virtual
Microsoft.EntityFramework.Proxies package
OnConfiguring optionsBuilder.UseLazyLoadingProxies()
Many “gotchas” to be wary of
foreach(var q in samurai.Quotes) t This will send one command to
{ retrieve all of the Quotes for that
samurai, then iterate through them
Console.WriteLine(q.Text);
}
t This will retrieve all of the quote
objects from the database and
var qCount=samurai.Quotes.Count(); materialize them and then give you
the count.
Data bind a grid to lazy-loaded t This happened a lot in ASP.NET pages.
data The grid populate one row at a time
and lazy loads the related data for that
row, then the next, then the next. N+1
commands sent to the database!
Lazy loading when no context in t No data is retrieved
place
Using Related Data to Filter Objects
Modifying Related Data
Connected Disconnected
Connected Disconnected
DbContext
DbContext
has no clue
is aware
about
of all changes
history of objects
made to objects
before they are
that is it tracking
attached
The Challenge
Edited
Quote
Samurai
Quote Quote
The Challenge
_context.Entry
( Edited
Quote
)
.State
Samurai
Quote Quote
The Challenge
_context.Entry
( Edited
Quote
)
.State
Samurai
Quote Quote
Working with Many-to-Many Relationships
More Likely to Join Existing Objects
Existing Battle Existing Battle New Battle
+ + +
Existing Samurai New Samurai New Samurai
Experienced samurai Rookie Samurai joins Rookie Samurai
joins an ongoing an ongoing battle started a war
battle
More Likely to Join Existing Objects
Existing Class Existing class New class
+ + +
Existing student New student New student
Registered student Unregistered student Unregistered student
signs up for a signs up for a schedules a new class
scheduled class scheduled class
Start with Our First Many-to-Many Setup
Samurai Battle
• Id • BattleId
• Name • Name
• Quotes (List<>) • StartDate
• Horse • EndDate
• Battles (List<>)
* * : • Samurais (List<>)
Simplest convention
No mappings needed
Table is inferred by DbContext
One-to-Many vs. Many-to-Many
Quote Samurai Battle
• Id • Id • BattleId
• Text • Name • Name
• SamuraiId • Quotes (List<>) • StartDate
• Samurai • Horse • EndDate
• Battles (List<>) • Samurais (List<>)
One Samurai to Many Quotes Many Samurais to Many Quotes
Let’s focus on the SQL
generated for our many-to-
many relationships
EF Core supports optimistic
concurrency. See docs for
details on how to handle
exceptions like this.
Altering or Removing Many-to-Many Joins
Battle of Nagashino Battle of Anegawa
Changing a Join: Preferred Workflow
Remove the original join
Then create the new join
Be mindful of side effects from your business logic
Deleting a M2M Relationship is Easier With
Explicit
Stored
M2M
Procedure
Mapping
Adding a Many-to-Many Payload to
Existing Join Table & Data
Migrating to explicit
mapping won’t impact
existing data …
Migrating to explicit
mapping won’t impact
existing data …
UNLESS….
Beware of Migration Conventions Conflicts
Inferred Table Explicit Class & Table Mapping
Combine class names DbSet name (doesn’t exist)
Table name alphabetically: or
BattleSamurai Class Name (BattleSamurai)
Table name + PK name: Property names from class
Column names SamuraisId SamuraiId
BattlesBattleId BattleId
Beware of Migration Conventions Conflicts
Inferred Table Explicit Class & Table Mapping
Combine class names DbSet name (doesn’t exist)
Table name alphabetically: or
BattleSamurai Class Name (BattleSamurai)
Table name + PK name: Property names from class
Column names SamuraisId SamuraiId
BattlesBattleId BattleId
Working with the
Many-to-Many Payload Data
Skip Navigations Can Evolve Safely
AppV1 AppV2
Breath easy
Includes a *:* with skip Evolve *:* to include
*:* code still works
navigation payload data
Production Production
Deploy AppV1 Deploy AppV2
Timeline of Events with Fade Transition
AppV1 AppV2
Breath easy
Includes a *:* with skip Evolve *:* to include
*:* code still works
navigation payload data
Production Production
Deploy AppV1 Deploy AppV2
Timeline of Events with Fade Transition
AppV1 AppV2
Breath easy
Includes a *:* with skip Evolve *:* to include
*:* code still works
navigation payload data
Production Production
Deploy AppV1 Deploy AppV2
Timeline of Events with Fade Transition
AppV1 AppV2
Breath easy
Includes a *:* with skip Evolve *:* to include
*:* code still works
navigation payload data
Production Production
Deploy AppV1 Deploy AppV2
Timeline of Events with Fade Transition
AppV1 AppV2
Breath easy
Includes a *:* with skip Evolve *:* to include
*:* code still works
navigation payload data
Production Production
Deploy AppV1 Deploy AppV2
Adding payload mapping to
existing skip navigation
doesn’t break existing code
Explicit Many-to-Many Skip Navigation
Join Entity Join Skip
w Optional Mapping Navigation
Payload
Guidance for Editing Many-to-Many
Edit the join between Delete the existing join
two many-to-many
objects Add a new join
Edit the payload data in Query using Set<>,
a many-to-many join modify and save
Querying Across Many-to-Many
Relationships
Persisting Data in One-to-One Relationships
Changing the Child of an Existing Parent
In EF Core 2: Mappings Course
Querying One-to-One Relationships
“Clean” entities may be
more difficult to work with,
requiring more advanced
skills with EF Core
You can eager load related data with
Include or load after the fact
Pay attention to lazy loading behavior
Review
EF Core 5 finally gives us filtered include
Understand behavior of untracked graphs
Review
DbContext.Entry() isolates the object you
care about
Intelligent SQL for *:* skip navigations
*:* payload data won’t harm your code
Lots of rules to understand for *:* and 1:1
Up Next!
Raw SQL,
Stored Procedures &
Database Views
Resources
Entity Framework Core on GitHub github.com/dotnet/efcore
EF Core Documentation docs.microsoft.com/ef
EF Core Power Tools on GitHub github.com/ErikEJ/EFCorePowerTools/wiki
EF Core 2: Mappings (Pluralsight course) bit.ly/2LppcMj
Handling Concurrency Conflicts in EF Core docs.microsoft.com/en-
us/ef/core/saving/concurrency
Arthur Vickers’ (EF team) Many-to-Many examples:
github.com/ajcvickers/ManyToManySamples
Querying and Saving Related Data
Julie Lerman
MOST TRUSTED AUTHORITY ON ENTITY FRAMEWORK
@julielerman thedatafarm.com
