If you represent your data as objects, you'll create a Book class with an attribute of type Author. If you now want to run the query above, you'd say (in c# likelihood, but not a real Entity query):
Database.Set<Book>().filter(author.id = 5).all()
But that instructs the ORM to fetch the author attribute from all books, and only then filter it by id. So, you will end up running the following query:
select * from book join author on book.author_id = author.id where author.id = 5
There is no reasonable way to represent the difference between this query and the one on top with an object representation. And even though both have exactly the same semantics, databases have been historically bad at optimizing them so the second one can be orders of magnitude slower.
EF Core compiles a query for the entirety of the expression provided. I wonder if it actually outputs select * from book join author on book.author_id = author.id where author.id = 5 as you described - this seems like a pit of failure a framework would work hard to prevent from happening.
You grab all Albums. Then you make a table of the albums with name and artist name. The artist name is in a separate table linked with a foreign key.
So the code might be:
for album in Album.objects.all():
print(album.name, album.artist.name)
Django will need to query from the foreign key ID (album.artist_id) into the Artist table to get the name of the artist. This means every loop step does a query.
So "N+1" because it's N queries (every album) plus 1 (the original "all albums" query).
The fix in Django is to do `Album.objects.all().select_related('artist')`. Then there will be just one query.
You have a query like:
select * from book where author_id = 5
If you represent your data as objects, you'll create a Book class with an attribute of type Author. If you now want to run the query above, you'd say (in c# likelihood, but not a real Entity query):
Database.Set<Book>().filter(author.id = 5).all()
But that instructs the ORM to fetch the author attribute from all books, and only then filter it by id. So, you will end up running the following query:
select * from book join author on book.author_id = author.id where author.id = 5
There is no reasonable way to represent the difference between this query and the one on top with an object representation. And even though both have exactly the same semantics, databases have been historically bad at optimizing them so the second one can be orders of magnitude slower.
EF Core compiles a query for the entirety of the expression provided. I wonder if it actually outputs select * from book join author on book.author_id = author.id where author.id = 5 as you described - this seems like a pit of failure a framework would work hard to prevent from happening.
Example:
You grab all Albums. Then you make a table of the albums with name and artist name. The artist name is in a separate table linked with a foreign key.
So the code might be:
Django will need to query from the foreign key ID (album.artist_id) into the Artist table to get the name of the artist. This means every loop step does a query.So "N+1" because it's N queries (every album) plus 1 (the original "all albums" query).
The fix in Django is to do `Album.objects.all().select_related('artist')`. Then there will be just one query.