As previous part discussed, when defining a LINQ to Entities query represented by IQueryable, an expression tree is built, there is no query execution. The execution is deferred until trying to pull the results from the query.
IQueryable is derived from IEnumerable, so values can be pulled from IQueryable with the standard iterator pattern. When trying to pull the first value, EF Core translates LINQ to Entities query to SQL, and execute SQL in the database. The implementation can be demonstrated with the Iterator type from the LINQ to Objects chapter:
$"| |_Materialize data row to {typeof(TEntity).Name} entity.".WriteLine();
28
return entityIterator.Current;
29
},
30
dispose: () => entityIterator.Dispose(),
31
end: () =>" |_End.".WriteLine()).Start();
32
}
33
}
The following example executes Where and Take query to load 3 products with more than 10 characters in name. It demonstrates how to pull the results from IQueryable with the iterator pattern:
Here for demonstration purpose, the GetEntityIterator extension method of IQueryable is called instead of GetEnumerator. In EF Core, when the iterator is created from IQueryable, the LINQ query expression tree is compiled to database query expression tree. Later, when the iterator’s MoveNext method is called for the first time, the SQL query is generated and executed. In each iteration, an entity is materialized from the SQL execution result.
EF is slightly more deferred then EF Core. The query compilation, SQL generation, and SQL execution all start when the iterator’s MoveNext method is called for the first time.
Deferred execution can be either lazy evaluation or eager evaluation. Internally, EF/Core call ADP.NET APIs to execute query, including DbDataReader, etc. DbDataReader is abstract class. EF/Core SQL database provider actually uses SqlDataReader in ADO.NET, which is derived from DbDataReader, to load the database query results. By default, when SqlDataReader starts to read data, it streams a number of rows to local buffer through TDS (tabular data stream) protocol. So by default, LINQ to Entities’ deferred execution is neither eager (load all rows when pulling the first result), nor totally lazy (load 1 result when pulling each result).
When retry logic is specified for connection resiliency, EF/Core become eager evaluation. When trying to pull the first query result, EF/Core call DbDataReader to load all results from database.
After an entity is queried, its related entities can be loaded through the navigation property. DbContext.Entry method accepts an entity of type TEntity, and returns Microsoft.EntityFrameworkCore.ChangeTracking.EntityEntry, which represents that entity’s tracking and loading information. EntityEntry provides a Reference method to return Microsoft.EntityFrameworkCore.ChangeTracking.ReferenceEntry<TEntity, TProperty> instance, which represents the tracking and loading information of a single related entity from reference navigation property. EntityEntry also provides a Collection method to return Microsoft.EntityFrameworkCore.ChangeTracking.ReferenceEntry.CollectionEntry<TEntity, TProperty>, which represents the tracking and loading information of multiple related entities from collection navigation property. These related entities in the navigation properties can be manually loaded by calling ReferenceEntry<TEntity, TProperty>.Load and CollectionEntry<TEntity, TProperty>.Load:
When the Load method is called, the related entities are queried, and become available through the navigation properties. Besides loading the full entities, explicit lazy loading also support custom query. The following example uses the reference navigation property and collection navigation property as LINQ to Entities data sources, by calling ReferenceEntry<TEntity, TProperty>.Query and CollectionEntry<TEntity, TProperty>.Query:
// WHERE [e].[ProductSubcategoryID] = @__get_Item_0',N'@__get_Item_0 int',@__get_Item_0=1
23
products.WriteLines();
24
}
In EF, the above entry types are named with the Db prefix: DbEntityEntry, DbReferenceEntry<TEntity, TProperty>, DbCollectionEntry<TEntity, TElement>. And they are under System.Data.Entity.Infrastructure namespace.
In explicit loading, after an entity is queried, its related entities are loaded separately. In eager loading, when an entity is queried, its related entities are loaded during the same query. To enable eager loading, call Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions’ Include method, which is an extension method for IQueryable:
Eager loading related entity through reference navigation property is translated to INNER JOIN. Eager loading through collection navigation property is translated to 2 SQL queries for 2 types of entities. More query methods can be chained after calling Include.
As fore mentioned, EF always translates LINQ to Entities queries to remote query. In EF, eager loading is translated to a single LEFT OUTER JOIN query.
In EF Core, ThenInclude can be called for eager loading of multiple levels of related entities:
//LEFT OUTER JOIN (SELECT [Extent2].[ProductID] AS [ProductID], [Extent2].[ProductPhotoID] AS [ProductPhotoID1], [Extent3].[ProductPhotoID] AS [ProductPhotoID2], [Extent3].[LargePhotoFileName] AS [LargePhotoFileName], [Extent3].[ModifiedDate] AS [ModifiedDate]
36
//FROM [Production].[ProductProductPhoto] AS [Extent2]
37
//INNER JOIN [Production].[ProductPhoto] AS [Extent3] ON [Extent2].[ProductPhotoID] = [Extent3].[ProductPhotoID] ) AS [Join1] ON [Extent1].[ProductID] = [Join1].[ProductID]
38
// ) AS [Project1]
39
//ORDER BY [Project1].[ProductID] ASC, [Project1].[C1] ASC
Sometimes lazy loading can cause the “N + 1 queries” problem. The following example queries the subcategories, and pulls each subcategory’s information:
// [Extent1].[ProductCategoryID] AS [ProductCategoryID],
14
// [Extent1].[Name] AS [Name]
15
// FROM [Production].[ProductCategory] AS [Extent1]
16
// WHERE [Extent1].[ProductCategoryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1
17
18
//exec sp_executesql N'SELECT
19
// [Extent1].[ProductCategoryID] AS [ProductCategoryID],
20
// [Extent1].[Name] AS [Name]
21
// FROM [Production].[ProductCategory] AS [Extent1]
22
// WHERE [Extent1].[ProductCategoryID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2
23
24
// ...
25
}
When loading the subcategories, 1 database query is executed. When each subcategory’s related category is pulled through the navigation property, it is loaded instantly, if not loaded yet. So in total there are N queries for related categories + 1 query for subcategories executed. For better performance in this kind of scenario, eager loading or inner join should be used to load all entities and related entities with 1 single query.
There are some scenarios where lazy loading needs to be disabled, like entity serialization. There are several ways to disable lazy loading for different scopes
To globally disable lazy loading for specific navigation properties, just do not mark it as virtual, so that the derived proxy entity cannot override it with the lazy load implementation.
To disable lazy loading for specific DbContext or specific query, call DbContext.Configuration to get a DbConfiguration instance, and set its LazyLoadingEnabled property to false.
