Querying Basics

Query execution workflow

When you execute a query like this:

1context.Inventory.ToList();

Entity Framework (EF) follows these steps:

1. Query preparationThe query is translated by Entity Framework Core to a different representation of the query. The translated query is cached so this processing only happens once.
2. Query to SQLThe result of the query preparation is passed to the database provider. The database provider decides what part of the query can be executed on the server and what parts of the query can only be executed on the client. Then the provider will build the SQL query for the database it works with. For example, the query might become: SELECT * FROM Inventory
3. Query executionThe provider sends this SQL query to the database for execution and returns a result set (a table-like structure).
4. Materialize ResultsEF maps this result set to C# entity objects. (The raw database table rows are converted into instances of your defined Entity Framework model classes.)
5. Start Change TrackingEF begins tracking changes on these objects. This allows EF to detect any modifications (additions, updates, deletions) you make to these objects so that they can later be persisted back to the database.

In the above steps step 1 and 2 are executed when the query is initialised. Step 3 and so on will be triggered at a later step.

Here is a list of the most common query execution triggers:

1. Looping through the query result (for, foreach, while)
2. Calling some of these LINQ methods: ToList(), AsEnumerable(), Count(), Min(), Max(), Find(), First(), FirstOrDefault(), Single(), SingleOrDefault(), Last(), Average(), Sum() and more

Loading a list of results

You can use methods like ToList(), AsEnumerable(), Take() to pull a list of entries from the database.

1var context = new SupermarketContext();
2using (context)
3{
4    var item = context.Inventories.ToList();
5}
6

The result of this code is the following SQL select:

Loading navigational and collection properties

Navigational and collection properties of an entity are not loaded by default. You can include them in your entity using one of the 3 techniques.

1. Eager Loading

   In this method you can use the Include() method to indicate which related properties must be loaded with the entity.

   1var context = new SupermarketContext();
   2 using (context)
   3 {
   4     var results = context.Inventories.Include(item => item.Store).ToList();
   5 }

   In this example each of the inventories in the list will also include the Store navigational property.

   To access nested properties, chain the ThenInclude() method as shown below.

   1var context = new SupermarketContext();
   2using (context)
   3{
   4    var results = context.Inventories
   5        .Include(item => item.Store)
   6            .ThenInclude(store => store.LegalDocuments).ToList();
   7}
   8

   To add multiple navigation properties under the same navigational parent you can call Include() on the same parent multiple times. In the example below I called Include() on the Store twice and then loaded different second level navigation properties (LegalDocuments,Owner).

   1var context = new SupermarketContext();
   2using (context)
   3{
   4    var results = context.Inventories
   5        .Include(item => item.Store)
   6            .ThenInclude(store => store.LegalDocuments)
   7        .Include(item => item.Store)
   8            .ThenInclude(store => store.Owner).ToList();
   9}

2. Explicit Loading

   Another way to load navigation properties is by using the DBContext.Entity() API. The explicit loading can only be done on one entry at a time.

   1var context = new SupermarketContext();
   2using (context)
   3{
   4    var store = context.Stores.Find(1);
   5    var storeEntry = context.Entry<Store>(store);
   6    storeEntry.Collection(s => s.Inventory).Load();
   7    storeEntry.Reference(s => s.Owner).Load();
   8}

   Note: If an entity is already loaded and tracked by the change tracker, you can issue a separate query to load its related records. The change tracker will automatically associate the loaded related entities with the tracked entity and update the navigation properties accordingly.

3. Lazy Loading

   Lazy loading in EF Core is a feature that automatically loads related navigation properties on-demand when they are accessed. By default, all virtual navigation properties are configured for lazy loading. However, starting with EF Core 8, lazy loading is disabled by default. To enable it, you need to install the Microsoft.EntityFrameworkCore.Proxie package and configure your DbContext to use lazy loading by calling UseLazyLoadingProxies in the context's configuration.

   1protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
   2 {
   3     if (optionsBuilder.IsConfigured == false)
   4     {
   5         optionsBuilder.UseSqlServer("Server=localhost\SQLEXPRESS;Initial Catalog=SuperMarket_Dev;Integrated Security=True;TrustServerCertificate=True;");
   6         optionsBuilder.UseLazyLoadingProxies();
   7
   8         base.OnConfiguring(optionsBuilder);
   9     }
   10 }

   Lazy loading will guarantee you that the navigational properties will alway be loaded but have some risks associated with it like the N+1 problem. It's important to note that each time you access a related property, a separate SQL query is triggered. In large-scale applications with extensive related data, this can result in performance bottlenecks due to the increased number of database calls.