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);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
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);
}
}
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
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
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);
}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);
}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);
}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
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();
}
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();
}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();
}
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
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
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
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
EF core adds the ID property automatically to the model that we created after adding the object to the dbset
Employee newEmployee = new Employee
{
Name = model.Name,
Email = model.Email,
Department = model.Department,
// Store the file name in PhotoPath property of the employee object
// which gets saved to the Employees database table
PhotoPath = uniqueFileName
};
_employeeRepository.Add(newEmployee);
return RedirectToAction("details", new { id = newEmployee.Id });
}
_employeeRepository.Add(newEmployee);
return RedirectToAction("details", new { id = newEmployee.Id });
using the create GUID image for display
~/= takes to root of the project
@model HomeDetailsViewModel
@{
ViewBag.Title = "Employee Details";
var photoPath = "~/images/" + (Model.Employee.PhotoPath ?? "noimage.jpg");
}
<img class="card-img-top">
it is using image tag helper ; with asp-append-version
Model.Employee.PhotoPath ?? “noimage.jpg”
if null then say noimage.jpg
