Entity Framework does an amazing job at generating queries, however, they are not always optimized and can end up transferring more data than the query really needs.
The SQL generated by EF+ IncludeOptimized performs multiple SELECT but drastically decreases the amount of data transferred.
SQL Generated by Entity Framework Include
Transferring 30 times the header information when only once would be enough may seems insignificant in some query, but as the number of items and “Include” methods grows, it could become a major overhead and could be a critical performance issue in many scenarios and even lead to SQL Timeout.
SQL Generated by EF+ IncludeOptimized
EF+ Query IncludeOptimized
IncludeQuery method works like “Include” method but creates an efficient query to reduce the number of data to transfer.
EF+ Query IncludeOptimized Filter
Same as with EF+ Query IncludeFilter, it is possible to filter which related entities to load and then to launch a query.
In some scenario, you may want to disable the Query Batch features to execute every query as individual.
Real Life Scenarios
- Any supported scenario where you would normally use Include.
Behind the code
Step 1 - Custom Queryable
The first time IncludeOptimized is called, a custom Queryable and a Provider are created for your query and will be used to append all queries.
Step 2 - Iterate or Execute
When an immediate method is invoked to resolve the query, multiple queries are created then combined into one SQL Command. The command is executed and the data reader will set the result for every queries.
- The iterate method is invoked when it is possible to return multiple results like “ToList()” and “ToArray()”
- The execute method is invoked when it is possible to return only one result like “First()” and “Last()”
- DO NOT work with AsNoTracking
- Entity Framework Core:
- Not supported yet.
- Cannot be mixed with Include (Include doesn’t support projection)
- Cannot be mixed with IncludeOptimized
- Many to Many relation:
- Not supported yet
- Entities will contain all previously loaded related entities even if the Query does not return them. It’s a limitation due to how Entity Framework relation work.
- EF+ Query IncludeOptimized: Full version or Standalone version
- Database Provider: All supported
- Entity Framework Version: EF6, EF5
- Minimum Framework Version: .NET Framework 4
EF+ Query IncludeOptimized drastically decreases the amount of data transferred and lets you filter included related entities. In some scenario, this may be the only viable solution to avoid SQL Timeout.
Need help getting started? firstname.lastname@example.org
We welcome all comments, ideas and suggestions to improve our library.