Interacting with Your

EF Core Data Model

Julie Lerman
MOST TRUSTED AUTHORITY ON ENTITY FRAMEWORK

@julielerman thedatafarm.com
 Exploring SQL generated by EF Core

Module Adding EF Core logging to the app

Overview Bulk operation support

Query workflow
Filters and aggregates in queries
Updating and deleting objects
Persisting data in disconnected apps
De-activate tracking in disconnected
apps
Looking at SQL Built by EF Core
 Under the Covers: Tracking Entities

DbContext
context.Samurais.Add(samuraiObject)

EntityEntry
• Entity
• State
• OriginalValues
• etc.
 Under the Covers: Tracking Entities

DbContext
context.Samurais.Add(samuraiObject)

EntityEntry
• Entity
• State
• OriginalValues
• etc.
 Under the Covers: Tracking Entities

DbContext
context.Samurais.Add(samuraiObject)

EntityEntry
•
•
•
Entity
State
OriginalValues
SQL
• etc.
Adding Logging to EF Core’s Workload
 .NET Core Logging

Configure DbContext directly

EF Core

.NET Core: Microsoft.Extensions.Logging

Logging is
so much
better in
EF Core 5
 .NET Core Logging

Configure DbContext directly

EF Core ASP.NET Core

.NET Core: Microsoft.Extensions.Logging

 New DbContextOptionsBuilder.LogTo Method

protected override void OnConfiguring

(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(someconnectionstring)
.LogTo(target);
}
LogTo Target Examples
optionsBuilder t Delegate to Console.WriteLine
.LogTo(Console.WriteLine)

t Delegate to StreamWriter.WriteLine
private StreamWriter _writer
= new StreamWriter
(“EFCoreLog.txt", append: true);

optionsBuilder
.LogTo(_writer.WriteLine)

t Lambda expression for

optionsBuilder Debug.WriteLine
.LogTo(log=>Debug.WriteLine(log));
 Even More Logging Features

Formatting

Detailed query error information

Filter on event types

What information goes in a log message

Show sensitive information e.g., parameters

 Enabling Sensitive Data to Show in Logs

Default: Parameters are hidden [__name_0='?' (Size = 4000)]

Configure with OptionsBuilder

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(connectionString)
.LogTo(Console.WriteLine)
.EnableSensitiveDataLogging(); [__name_0=‘Sampson' (Size = 4000)]
}
 Even More Logging Features

Formatting

Detailed query error information

Filter on event types

What information goes in a log message

Show sensitive information e.g., parameters

Benefiting from Bulk Operations Support
Tracking Methods on DbSet and DbContext

context.Samurais.Add(…) context.Add(…)

context.Samurais.AddRange(…) context.AddRange(…)

Track via DbSet Track via DbContext

DbSet indicates type Context will discover type(s)
Batching can combine
types and operations
 Batch Operation Batch Size

Ø Default size & more is set by database provider

Ø Additional commands will be sent in extra batches

Ø Override batch size in DbContext OnConfiguring

Understanding the Query Workflow
 The Simplest Query

_context.Samurais.ToList()
 Query Workflow
EF Core reads model,
works with provider to
work out SQL
Express & execute query
context.Samurais.ToList() Sends SQL to database

SELECT * from Samurais

Materializes results
as objects
Adds tracking details Receives tabular results
to DbContext instance
 Two Ways to Express LINQ Queries
LINQ Methods LINQ Query Syntax

(from s in context.Samurais
context.Samurais.ToList();
select s).ToList()

context.Samurais (from s in context.Samurais

.Where(s=>s.Name==“Julie”) where s.Name==“Julie”
.ToList() select s).ToList()
 Deferred Query Execution

var query=_context.Samurais;
var query=_context.Samurais; foreach (var s in query)
{
var samurais=query.ToList();
Console.Writeline(s.name);
}
 Deferred Query Execution

var query=_context.Samurais;

var query=_context.Samurais; foreach (var s in

context.Samurais)
var samurais=query.ToList(); {
Console.Writeline(s.name);
}
Database Connection
Remains Open During
Enumeration
foreach (var s in context.Samurais){ t Minimal effort on enumeration, ok
Console.WriteLine(s.Name);
}

foreach (var s in context.Samurais){ t Lots of work for each result.

RunSomeValidator(s.Name); Connection stays open until last
CallSomeService(s.Id); result is fetched.
GetSomeMoreDataBasedOn(s.Id);
}

var samurais=context.Samurais.ToList() t Smarter to get results first

foreach (var s in samurais){
RunSomeValidator(s.Name);
CallSomeService(s.Id);
GetSomeMoreDataBasedOn(s.Id);
}
Filtering in Queries
Coming from EF Core 2?

Be aware of the
GREAT LINQ OVERHAUL
for EF Core 3, which
impacted query behavior
 DbSet.Find(key)

Not a LINQ method

Executes immediately

If key is found in change tracker,

avoids unneeded database query
 Filtering Partial Text LINQ

Like Contains
EF.Functions.Like(property, %abc%) property.Contains(abc)

_context.Samurais.Where(s=> _context.Samurais.Where(s=>
EF.Functions.Like(s.Name,“%abc%”) s.Name.Contains(“abc”)
) )

SQL LIKE(%abc%) SQL LIKE(%abc%)

Aggregating in Queries
 EF Core Parameter Creation

Search value is directly in query Search value is in a variable

...Where(s=>s.Name==“Sampson”) var name=“Sampson”

...Where(s=>s.Name==name)

No parameter is created in SQL Parameter is created in SQL

SELECT * FROM T @parameter=‘Sampson’

WHERE T.Name=‘Sampson’
SELECT * FROM T
WHERE T.Name=@parameter
 LINQ to Entities Execution Methods
ToList() ToListAsync()
First() FirstAsync()
FirstOrDefault() FirstOrDefaultAsync()
Single() SingleAsync()
SingleOrDefault() SingleOrDefaultAsync()
Last()* LastAsync()*
LastOrDefault()* LastOrDefaultAsync()*
Count() CountAsync()
LongCount() LongCountAsync()
Min(), Max() MinAsync(), MaxAsync()
Average(), Sum() AverageAsync(), SumAsync()
AsAsyncEnumerable**

Not a LINQ method, but a DbSet method that will execute:

Find(keyValue) FindAsync(keyValue)

*Last methods require query to have an OrderBy() method otherwise will return full set then pick last in memory
*First/Single will
 Execution Method Pointers

Last methods require query to have an OrderBy() method otherwise

will return full set then pick last in memory

Single methods expect only one match and will throw if there are
none or more than one

First methods return the first of any matches

First/Single/Last will throw if no results are returned

FirstOrDefault/SingleOrDefault/LastOrDefault will return a null if no

results are returned
Updating Simple Objects
 Skip & Take for Paging
1. Aardvark 11. African Penguin
2. Abyssinian 12. African Tree Toad
3. Adelie Penguin 13. African Wild Dog
4. Affenpinscher 14. Ainu Dog
5. Afghan Hound 15. Airedale Terrier
6. African Bush Elephant 16. Akbash
7. African Civet 17. Akita
8. African Clawed Frog 18. Alaskan Malamute
9. African Forest Elephant 19. Albatross
10. African Palm Civet 20. Aldabra Giant Tortoise

Get first 10 animals Get next 10 animals

Skip(0).Take(10) Skip(10).Take(10
)
Deleting Simple Objects
Deleting May Seem a Little Weird

Context Sends
needs to Set its state relevant SQL
track the to “Deleted” to db on
entity SaveChanges
_context.Samurais.Add(samurai) t DbSet Add, AddRange
_context.Samurais.AddRange(samuraiList)

_context.Add(samurai) t DbContext Add, AddRange

_context.AddRange(samurai, battle)

_context.Samurais.Update(samurai) t DbSet Update,

_context.Samurais.UpdateRange(samuraiList) UpdateRange
_context.Update(samurai)
_context.UpdateRange(samurai, battle) t DbContext Update,
UpdateRange
_context.Samurais.Remove(samurai) t DbSet Remove,
_context.Samurais.RemoveRange(samuraiList) RemoveRange
_context.Remove(samurai)
_context.RemoveRange(samurai, battle) t DbContext Remove,
RemoveRange
 Workarounds for Required Object to Delete

Fake object with key Stored procedure via Soft delete via
property filled: watch EF Core raw SQL feature Global Query Filters
out for possible side Link in resources
Further on in this course
effects
Understanding Disconnected Scenarios
 Working in a Single DbContext Instance

Retrieve Modify Save

Data Objects Changes

Context starts tracking Context updates state of

state of each returned object tracked objects
before determining SQL
Connected Data Access
Disconnected Clients

Data

Request Data Data

In disconnected scenarios,
it’s up to you to inform the
context about object state.
Persisting Data in Disconnected Scenarios
Ignoring the *:* right now.

Next module works with

relationships.
 Update Methods

Is DbContext
tracking this
Yes
object?

Mark as
No
modified

Start
tracking
 Update causes all
properties to be updated
whether they were
edited or not
Enhancing Performance in Disconnected
Apps with No-Tracking Settings
 No Track Queries and DbContext

var samurai = _context.Samurais.AsNoTracking().FirstOrDefault();

AsNoTracking returns a query, not a DbSet

public class SamuraiContextNoTrack: DbContext

{
public SamuraiContextNoTrack()
{
ChangeTracker.QueryTrackingBehavior=QueryTrackingBehavior.NoTracking;
}

All queries on SamuraiContextNoTrack will default to no tracking

Use DbSet.AsTracking() for special queries to be tracked
Change Tracking Is Expensive
 Log EF Core SQL commands
Inserts, updates and deletes
Review
Bulk operations
Comprehend EF Core querying
Review
Filtering and aggregating in queries
Persisting in disconnected apps e.g., web
site
Improve performance when tracking is not
needed
 Resources
Entity Framework Core on GitHub github.com/dotnet/efcore

EF Core Documentation docs.microsoft.com/ef

Article about Merge Joins

brentozar.com/archive/2017/05/case-entity-framework-cores-odd-sql/

EF Core 5.0: Building on the Foundation codemag.com/Article/20100412

EF Core 3.0: A Foundation for the Future codemag.com/Article/1911062

Entity Framework in the Enterprise, Pluralsight course bit.ly/PS_EFEnt

Logging SQL and Change-Tracking Events in EF Core, MSDN Mag Oct 2019
msdn.microsoft.com/magazine/mt830355

SqlServerModificationCommandBatch.cs on GitHub bit.ly/3jgrRss

Soft Delete in EF Core Docs docs.microsoft.com/en-us/ef/core/querying/filters

Interacting with Your
EF Core Data Model

Julie Lerman
MOST TRUSTED AUTHORITY ON ENTITY FRAMEWORK

@julielerman thedatafarm.com