Entity Framework Core Flashcards Preview

Technical Performance / Best Practices > Entity Framework Core > Flashcards

Flashcards in Entity Framework Core Deck (33)
Loading flashcards...
1

Disabling Change Tracking for a query

when EF Core queries a database, it stores the snapshot of the results in memory;
any modification that you are going to be making to your entities are going to be made against that snapshot and later written to the database

in readonly scenario, we could forgo that snapshot and converse some system resources

products = await _context.Products.AsNoTracking().
FirstorDefaultAsync(m=>m.Id==id)

2

Loading related entities at runtime - eager loading

Customer = await _context.Customers
.Include(c=>c.Orders)
.SingleAsync(C=>c.id==id);

the related orders should be loaded on the same database query as the customers

3

Loading related entities at runtime - lazy loading

https://csharp.christiannagel.com/2019/01/30/lazyloading/#:~:
text=With%20lazy%20loading%2C%20you%20do,is
%20available%20since%20version%202.1.

loading the related entities only when needed

we need to install
Microsoft.EntityFramework.Proxies package

we are going to add UseLazyLoadingProxies to the options

services.AddDbContext(options=>
options.UseLazyLoadingProxies()
.UseSqlServer("Data Source= (localdb)");



--we need mark the navigation properties as Virtual

public virtual ICollection Orders {get;set;} so that EF core can override them with the proxies

order will not be requested from the database untill they are needed from our code

Behind the scenes, the created proxy class derives from the customer class and overrides these properties. The proxy then loads the data needed on first access of the property.

With the EF Core context class BooksContext, the fluent API is used to specify the relations. The book has a list of chapters, and a chapter belongs to one book – this is specified with HasMany and WithOne. One user is associated with multiple books with the relations WrittenBooks, ReviewedBooks, and EditedBooks.

public class BooksContext : DbContext
{
public BooksContext(DbContextOptions options)
: base(options) { }

public DbSet Books { get; private set; }
public DbSet Chapters { get; private set; }
public DbSet Users { get; private set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity()
.HasMany(b => b.Chapters)
.WithOne(c => c.Book)
.OnDelete(DeleteBehavior.Cascade);
modelBuilder.Entity()
.HasOne(b => b.Author)
.WithMany(a => a.WrittenBooks)
.HasForeignKey(a => a.AuthorId)
.OnDelete(DeleteBehavior.Restrict);
modelBuilder.Entity()
.HasOne(b => b.Reviewer)
.WithMany(r => r.ReviewedBooks)
.HasForeignKey(b => b.ReviewerId)
.OnDelete(DeleteBehavior.Restrict);
modelBuilder.Entity()
.HasOne(b => b.Editor)
.WithMany(e => e.EditedBooks)
.HasForeignKey(b => b.EditorId)
.OnDelete(DeleteBehavior.Restrict);

modelBuilder.Entity()
.HasOne(c => c.Book)
.WithMany(b => b.Chapters)
.HasForeignKey(c => c.BookId);

modelBuilder.Entity()
.HasMany(a => a.WrittenBooks)
.WithOne(nameof(Book.Author))
.OnDelete(DeleteBehavior.Restrict);
modelBuilder.Entity()
.HasMany(r => r.ReviewedBooks)
.WithOne(nameof(Book.Reviewer))
.OnDelete(DeleteBehavior.Restrict);
modelBuilder.Entity()
.HasMany(e => e.EditedBooks)
.WithOne(nameof(Book.Editor))
.OnDelete(DeleteBehavior.Restrict);

SeedData(modelBuilder);
}
}
view raw


The sample code uses the fluent API to specify the relation. Relations can also be specified using annotations. The book Professional C# 7 and .NET Core 2.0 covers all variants.

Lazy Loading
To access books, LINQ queries can be done like the one shown passing a where clause. After iterating the books, the references to chapters, authors, reviewers, and editors are done just by accessing the properties of the Book type:

public void GetBooksWithLazyLoading()
{
var books = _booksContext.Books.Where(b => b.Publisher.StartsWith("Wrox"));

foreach (var book in books)
{
Console.WriteLine(book.Title);
foreach (var chapter in book.Chapters)
{
Console.WriteLine($"{chapter.Number}. {chapter.Title}");
}
Console.WriteLine($"author: {book.Author?.Name}");
Console.WriteLine($"reviewer: {book.Reviewer?.Name}");
Console.WriteLine($"editor: {book.Editor?.Name}");
}
}
view raw
BooksService.cs
hosted with ❤ by GitHub
Accessing the books, from the LINQ statement a SQL query is generated to access different columns from the Books table:

1
2
3
SELECT [b].[BookId], [b].[AuthorId], [b].[EditorId], [b].[Publisher], [b].[ReviewerId], [b].[Title]
FROM [Books] AS [b]
WHERE [b].[Publisher] LIKE N'Wrox' + N'%' AND (LEFT([b].[Publisher], LEN(N'Wrox')) = N'Wrox')
With the first query, other tables than the Books table are not accessed. However, accessing the Chapters property, this SQL query is done:

1
2
3
SELECT [e].[ChapterId], [e].[BookId], [e].[Number], [e].[Title]
FROM [Chapters] AS [e]
WHERE [e].[BookId] = @__get_Item_0
Later on in the code, accessing the Author, Reviewer, and Editor relations, more queries are done.

1
2
3
SELECT [e].[UserId], [e].[Name]
FROM [Users] AS [e]
WHERE [e].[UserId] = @__get_Item_0
When the data was not loaded previously, every time a property is accessed that maps to a related table, another query to the database is done. Behind the scenes, the query doesn’t return the defined Book types, but instead the class Castle.Proxies.BookProxy is returned. This class derives from the base class Book and overrides virtual properties.

4

Explicit Loading

With lazy loading, just properties need to be accessed from the C# code to get the data from the database as needed. This is easy to do, but you might miss better performance by reducing the number of queries. Using explicit loading you do have the same number of queries, it’s just easier to detect from the source code. Using the Collection method from the EntityEntry type, 1:n relations can be retrieved from the program invoking the Load method. Here, the same SQL statement is generated accessing the book chapters as with lazy loading. Accessing a 1:1 relation, the Reference method is used – again with the Load method.

public void GetBooksWithExplicitLoading()
{
var books = _booksContext.Books.Where(b => b.Publisher.StartsWith("Wrox"));

foreach (var book in books)
{
Console.WriteLine(book.Title);
EntityEntry entry = _booksContext.Entry(book);
entry.Collection(b => b.Chapters).Load();

foreach (var chapter in book.Chapters)
{
Console.WriteLine($"{chapter.Number}. {chapter.Title}");
}

entry.Reference(b => b.Author).Load();
Console.WriteLine($"author: {book.Author?.Name}");

entry.Reference(b => b.Reviewer).Load();
Console.WriteLine($"reviewer: {book.Reviewer?.Name}");

entry.Reference(b => b.Editor).Load();
Console.WriteLine($"editor: {book.Editor?.Name}");
}
}
view raw
BooksService.cs
hosted with ❤ by GitHub
You can also use the IsLoaded property to see if the related data is already loaded. The implementation of the Load method itself checks if the related data is already loaded to not query the database another time if the data is already in memory.

With explicit loading the source code gets more complex when accessing the objects from the EF Core database context. Related data needs to be explicit loaded using the Load method from the CollectionEntry or the ReferenceEntry returned from the Collection and Reference methods. The advantage using explicit loading is that you see it from the C# source code that additional SQL queries are done. Also, the model type doesn’t need special treatment. Here, virtual properties are no longer needed.

5

Eager Loading

In case you already know in advance the needed loaded relations, eager loading can be used. This should be the preferred way to get the data from the database. You can get deep relations with just one query. Defining the LINQ query, you now add calls to the Include method and specify the relations that should be included. The Include method is an extension to the IQueryable type defined in the Microsoft.EntityFrameworkCorenamespace.

public void GetBooksWithEagerLoading()
{
var books = _booksContext.Books
.Where(b => b.Publisher.StartsWith("Wrox"))
.Include(b => b.Chapters)
.Include(b => b.Author)
.Include(b => b.Reviewer)
.Include(b => b.Editor);

foreach (var book in books)
{
Console.WriteLine(book.Title);
foreach (var chapter in book.Chapters)
{
Console.WriteLine($"{chapter.Number}. {chapter.Title}");
}
Console.WriteLine($"author: {book.Author?.Name}");
Console.WriteLine($"reviewer: {book.Reviewer?.Name}");
Console.WriteLine($"editor: {book.Editor?.Name}");
}
}
view raw
BooksService.cs
hosted with ❤ by GitHub
In case you have deeper relations, and need to access relation by relation, e.g. by accessing another relation from the Chapter type, you can use the ThenInclude method.

The SQL statement now becomes more complex. Not just the Books table is queried, but with my current SQL Server provider two SELECT statements are done accessing the Books, Chapters, and Users table using LEFT JOIN and INNER JOIN. Now just one time information from the database is retrieved instead when accessing every single book:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT [b].[BookId], [b].[AuthorId], [b].[EditorId], [b].[Publisher], [b].[ReviewerId], [b].[Title], [b.Editor].[UserId], [b.Editor].[Name], [b.Reviewer].[UserId], [b.Reviewer].[Name], [b.Author].[UserId], [b.Author].[Name]
FROM [Books] AS [b]
LEFT JOIN [Users] AS [b.Editor] ON [b].[EditorId] = [b.Editor].[UserId]
LEFT JOIN [Users] AS [b.Reviewer] ON [b].[ReviewerId] = [b.Reviewer].[UserId]
LEFT JOIN [Users] AS [b.Author] ON [b].[AuthorId] = [b.Author].[UserId]
WHERE [b].[Publisher] LIKE N'Wrox' + N'%' AND (LEFT([b].[Publisher], LEN(N'Wrox')) = N'Wrox')
ORDER BY [b].[BookId]
SELECT [b.Chapters].[ChapterId], [b.Chapters].[BookId], [b.Chapters].[Number], [b.Chapters].[Title]
FROM [Chapters] AS [b.Chapters]
INNER JOIN (
SELECT DISTINCT [b0].[BookId]
FROM [Books] AS [b0]
LEFT JOIN [Users] AS [b.Editor0] ON [b0].[EditorId] = [b.Editor0].[UserId]
LEFT JOIN [Users] AS [b.Reviewer0] ON [b0].[ReviewerId] = [b.Reviewer0].[UserId]
LEFT JOIN [Users] AS [b.Author0] ON [b0].[AuthorId] = [b.Author0].[UserId]
WHERE [b0].[Publisher] LIKE N'Wrox' + N'%' AND (LEFT([b0].[Publisher], LEN(N'Wrox')) = N'Wrox')
) AS [t] ON [b.Chapters].[BookId] = [t].[BookId]
ORDER BY [t].[BookId]
Instead of accessing the database with every property accessing a relation, the data is loaded early with less SQL statements sent to the database. Similar to explicit loading, the model doesn’t need special treatment. This scenario can also be used to return the model type and have all the associated data as needed when you can’t access the context instance to get additional data.

6

Eager Loading vs Lazy Loading vs Explicit loading

Using models to load related data is easy as long as the context is available. However, you need to pay attention not to create too many queries accessing the data from the database. If you know the relations needed in advance, you can use eager loading.
With lazy loading, proxy classes are created that derive from the model type. The proxy class overrides virtual properties to retrieve data needed dynamically.

7

From SQL Raw and From SQL interpolated

sometime when working with entity framework core, you might need to use your own sql rather than the sql that it generates for you

EF core converts string into parameterized sql statemetns which prevents against sql inject attacks

var minPrice=5.00m;
Product = await _context.Products
.FromSqlInterpolated($"select * from dbo.products where Price >{minPrice}")
.ToListAsync();

8

Reduce query to database with inMemory snapshot query
find or FindAsync

Entity framework core uses inmemory snapshots to track changes to our entities. if we have entity caching memory, we could save ourself trip to database
with find or find async method

Earlier:
product = await _context.Products.FirstOrDefault
async(m=>m.Id==id);

if you already have it in the snapshot, you could retrieve with: FindAsync and looking up it with its primary key
Product = await _context.Products.FindAsync(id);

if it is not in the snapshot, EF core will go to get from the database

9

Reduce database context overhead

whenever we use databasecontext, there is certain amount overhead involved in creating and destroying the object. we could bypass that overhead by using databaseContextPooling to reuse our database context object over and over again

in ConfigureService method on StartUppage:

Services.AddDbContextPool; instead of AddDbContext

10

removing from change tracker - if it is just for readonly or huge amount of data

For get for huge data set we could have asNoTracking

for Post then we are tracking with change tracker with EF core and save the data

removing from change tracker - if it is just for readonly or huge amount of data

For get for huge data set we could have asNoTracking

for Post then we are tracking with change tracker with EF core and save the data

11

certain triggers that let query to be executed against the data and they do not happen immediately

when we create a linq query in EF core nothing happens

AsQueryable() - this tells EF Core that at somepoint of time, you are going to run a query against this

query exectues:
when it is iterated over (like foreach)
if you call ToList()
when you bind to a datagrid

we should know when to trigger the query execution'
because if you have adding query, you do not want to execute in the beginning and get the whole data in memory and do operations on it; instead send query to database based on filters


internal void LinqExecutionTiming()
{
//Nothing Happens
IQueryable query = _context.Person.AsQueryable();
//Now query executes
List list = query.ToList();
//Also here:
foreach (var p in query) { }
//And here:
_ = query.FirstOrDefault();
//And here:
_ = query.SingleOrDefault(x => x.BusinessEntityId == 1);
//And here
_ = _context.Person.Find(1);
}

12

Keep in mind the timing of the query execution

querable is over loaded, we have the linq query which is defined by the iqueryable
in linq to objects - it is just linq with some extension for EF Core
when you execute the linq query, the dbContext send the linq statement to the database provider for the linq translation engine to turn that into SQL or NO SQL depending on the provider

if the provider is sql server, this linq statement gets converted into sql query to run against the database

13

setting font to cascadia on visual studio

this gives == and != (/=)

14

Query on the existing query; this gives power to chain things based on condition in the form submitted
if person type is not null then
if promtion is not null then

we could chain the querys

Chaning is always AND in query

public void SelectWithMultipleClauses()
{
//All in one statement
var query1 = _context.Person
.Where(x => x.PersonType == "em" && x.EmailPromotion == 1);
//Chained statements
var query2 = _context.Person
.Where(x => x.PersonType == "em").Where(x => x.EmailPromotion == 1);
//Built up over disparate calls
var query3 = _context.Person.Where(x => x.PersonType == "em");
query3 = query3.Where(x => x.EmailPromotion == 1);

//Or's can't be chained
var query4 = _context.Person
.Where(x => x.PersonType == "em" || x.EmailPromotion == 1);
}

none of the above statements are executed as we have not iterated them over

15

//Or's can't be chained

var query4 = _context.Person
.Where(x => x.PersonType == "em" || x.EmailPromotion == 1);

16

orderBy returned IOrderedQuerable instead of Iqueyable

var designed for linq

IOrderedQuerable - multiple types could be much more complex

orderBy returned IOrderedQuerable instead of Iqueyable
as a developer you do not care about what i coming, you would want to assign it to view model or grid

17

LastOrDefault - this could not be translated into SWL by SQL translation engine

it fails at run time but not at design time. so we should have integration test or unit tests

public void ProblemQueries()
{
try
{
//This can't be translated into SQL by the SQL Server tranlsation engine
_ = _context.Person.LastOrDefault(x => x.PersonType == "em");
}
catch (InvalidOperationException ex)
{
//Console.WriteLine(ex);
}
try
{
//This will throw an error
_ = _context.Person.SingleOrDefault(x => x.BusinessEntityId <= 2);
/*
Executed Query:
SELECT TOP(2) * -- actual fields listed in real query
FROM [Person].[Person] AS [p]
WHERE [p].[BusinessEntityID] <= 2
*/
}
catch (InvalidOperationException ex)
{
//Two records were returned - this happens client-side
//Console.WriteLine(ex);
}

}

18

in EF Core 5 it is going to have extension method of of your query that will be executed

in EF Core 5 it is going to have extension method of of your query that will be executed

19

if the query can not be translated it throws invalid operator exception and does not come to sql profiler

if the query can not be translated it throws invalid operator exception and does not come to sql profiler

20

Find - first checks the change tracker to see if the dbset is already loaded, if not it will go to database; this could be slight memory boost unlike where statment that goes to db first

internal void GetByPrimaryKey()
{
//Get by primary key with immediate execution
_ = _context.Person.Find(1);
//Complex PK with immediate execution
_ = _context.ProductVendor.Find(2, 1688);
}

21

using FirstOrDefault with out where if it is the same condition

internal void GetSingleRecord()
{
//All immediate execution
//NOTE: should use an order by with these
_ = _context.Person.Where(x => x.BusinessEntityId == 1).FirstOrDefault();
_ = _context.Person.FirstOrDefault(x => x.BusinessEntityId == 1);
//Using Single - Exception if more than one is found
_ = _context.Person.SingleOrDefault(x => x.BusinessEntityId == 1);
}

22

SingleOrDefault - try to eliminate if possible;

on the server side it will top 2 records; pull those records
if there are more than 1 then it thrwos exception

it is actually pull 2 records from the server
and checking if it is more than 1 on the client side

if this behavior is needed use it ; if not try to avoid it

internal void GetSingleRecord()
{
//All immediate execution
//NOTE: should use an order by with these
_ = _context.Person.Where(x => x.BusinessEntityId == 1).FirstOrDefault();
_ = _context.Person.FirstOrDefault(x => x.BusinessEntityId == 1);
//Using Single - Exception if more than one is found
_ = _context.Person.SingleOrDefault(x => x.BusinessEntityId == 1);
}

23

Databases are optimized based on tables
applications are mapped based on Domain objects
ORM - for mapping these

Databases are optimized based on tables
applications are mapped based on Domain objects
ORM - for mapping these

24

using var for include or query results

include - does left outer join
it generates iIncludableQueryable

do not worry about the return just take it into var and enumerate on it with toList()

internal void GetPersonAndRelatedData()
{
//Get collections (many of many to one)
_ = _context.Person.Include(x => x.EmailAddress);
//Get Parent (one of one to one)
_ = _context.Person.Include(x => x.BusinessEntity);
//Get Chain of related
var q = _context.Person
.Include(x => x.Employee)
.ThenInclude(x => x.SalesPerson);
q.ToList();
}

25

explicit loading is fine for wpf apps where they are always connected to the data
but do not use explicit loading for the web world to reduce the calls between app server and database server (use include with implicit loading for web world)

internal void ExplicitlyLoadRelatedData()
{
var p = _context.Person.FirstOrDefault(x => x.BusinessEntityId == 1);
_context.Entry(p).Reference(p => p.Employee).Load();
_context.Entry(p).Collection(p => p.EmailAddress).Load();
}

26

Projects with select

it is better to use viewModel instead of anonymous objectes

we could not pass anonymous objects out of the method as they are not strongly typed as var is not a return type; but you could iterate only with in the method
we either need to have dynamic or cast it into person viewModel

anonymous objects are beneficial; but better to have viewModel
parts of different models squished together to be more beneficial to the consume

viewModel or ViewEntity - is putting the data of different tables/models/entities together for transporation for better usage


internal void CreateProjections()
{
//Create list of anonymous objects
var newAnonList = _context.Person
.Select(x => new
{
x.FirstName,
x.MiddleName,
x.LastName,
x.EmailAddress
})
.ToList();
IQueryable> result1 = _context.Person.Select(x => x.EmailAddress);
//Select Many flattens the list
IQueryable result2 = _context.Person.SelectMany(x => x.EmailAddress);

//Project to a ViewModel
List newVMList = _context.Person
.Select(x => new PersonViewModel
{
FirstName = x.FirstName,
MiddleName = x.MiddleName,
LastName = x.LastName
})
.ToList();
}

27

in case of projects try not to use var instead use strongly typed returns, so you know what is coming out

in case of projects try not to use var instead use strongly typed returns, so you know what is coming out

28

Deleting in web applications world
var person =_context.Person.Find(1);
//This is not in memory => retrived from database
_context.Entry(person).state= EntityState.Deleted;

//this must be in memory => retrieved from database
_context.person.Remove(person);

in WPF world, it is always connected and dbcontext is not recycled on every

we could also do it with update and add method but is not worth the effort

to use the Remove method, we need to query that record from the database into the dbset to call remove on it; there is extra database call to turn around and delete it

in web world, once your do a get for the records, we are disconnected. we have it displayed but do not have it in the dbContext.at the end of that request, dbcontext gets recycled

when you do a post; due to DI container in asp.net core, it is creating a new instance of DbContext or getting it from the pool either way it is clean, there is nothing in it

we say make me a new person with this id and with this row version and set the state to deleted and save changes

29

Batch size
EF Core decides batcing automatically

but we coudl set the batch size
builder.UserSqlSer(connectionstring, options=>options.MaxBatchSize(1)) - batch size 1 is similar to running multiple queries to database

EF Core uses batch size
if there are multiple inserts EF core puts all the inserts in one length string and does only one call to the database for insert

it takes really less time for running queries much faster in EF Core

Linq also does caching and database also does caching

30

Use DbContextPooling to improve the performance: .Net Core feature

1
services.AddDbContext(options => options.UseSqlServer(connection));

So in case of AddDbContext, a new instance will be created for each request and would get disposed once the work is done.

services.AddDbContextPool(options => options.UseSqlServer(connection));
AddDbContextPool accepts the lambda expression defining the connection string
One more parameter is the integer containing the value of the maximum number of instances in the DbContext pool
The default value is 128
Instead of disposing of the instances completely, what it does is that it returns to the pool and resets the instance to its default state