How to: Choose the Optimal Query Splitting Behavior in Entity Framework Core
- 3 minutes to read
XAF does not explicitly configure query splitting behavior for EF Core DB Contexts. If you do not configure this setting in your application, it defaults to single query mode, in which EF Core uses joins to load collection property data from multiple database tables in a single query. In some cases, this mode may lead to excessive data rows loaded with some requests and result in noticeable drops in performance. In such cases, you can use split query mode. In this mode, multiple separate queries are used to load data. Refer to the following topic for more information: Single vs. Split Queries.
To enable split query mode in your application, modify the WithDbContext method call in the code that configures the EF Core DB Context as follows:
File: MySolution.Blazor.Server/Startup.cs., MySolution.Win/Startup.cs, MySolution.WebApi/Startup.cs
// ...
builder.ObjectSpaceProviders
.AddSecuredEFCore(options => options.PreFetchReferenceProperties())
.WithDbContext<DXApplication61.Module.BusinessObjects.DXApplication61EFCoreDbContext>((serviceProvider, options) => {
// ...
options.(connectionString, o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
// ...
})
// ...
Performance Considerations
The XAF Security System loads all security data (users, roles, and permissions) eagerly. For this reason, loading security data can become a performance bottleneck in applications with complex security policies. Because of this, the information below focuses on loading of security data as a popular example. However, bear in mind that it is applicable to eager loading of any business object’s collection properties.
Consider the following case: your application has a Users role assigned to 3 users. This role is assigned 6 Type Permissions, 6 Navigation Permissions, and 4 Denied Actions. Each Type Permission defines 2 Member Permissions and 2 Object Permissions. See Type, Object and Member Permissions for more information on permissions in the XAF Security System. When the Users role is eagerly loaded, you can calculate the total number of loaded rows as follows.
- In single query mode: 1 query with over 1720 data rows is loaded. In this mode, data is always loaded in a single query, but this mode is suboptimal in terms of the amount of data transferred over the network.
- In split query mode: 6 queries with 43 total data rows are loaded. This mode saves network traffic and system memory, but it is suboptimal in terms of the total number of requests to the server.
As you can see, there is no single correct answer on which mode to use, because this decision must be based on your application’s business model architecture and network infrastructure. The following section demonstrates the strategy that you can follow, to chose the mode that is optimal for your application.
A Strategy to Choose the Optimal Query Splitting Mode
Look through the list of possible cases below and see which one best describes your application and network infrastructure.
- The system has many users (in multiples of 10). User roles have many permissions (in multiples of 10) of various types.
- Use the split query mode.
- There are not many users in the system, and/or user roles do not have many permissions.
- Consider your network’s performance to decide which mode to use. See the possible options below.
- Your network is fast and network latency is low.
- Use split query mode.
- Your server is hosted in a cloud service; the network is slow and has considerable latency (tens of milliseconds).
- You need to experiment with various modes.