Entity Framework Core Flashcards
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)
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
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.
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.
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.
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.
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();
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
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
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
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);
}
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
setting font to cascadia on visual studio
this gives == and != (/=)
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
//Or’s can’t be chained
var query4 = _context.Person
.Where(x => x.PersonType == "em" || x.EmailPromotion == 1);
