Entity Framework Plus Query IncludeOptimized

Description

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

var orders = ctx.Orders
                .Where(x => x.OrderId == myOrderID) // 1 orders, 20 columns
                .Include(x => x.Items) // 20 items, 10 columns
                .Include(x => x.DeliveredItems) // 10 items, 10 columns
                .ToList();

// return 20 + 10  = 30 rows
// return 20 + 10 + 10 = 40 columns
// total: 30 rows * 40 columns = 1200 cells transferred

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

// SELECT * FROM Order WHERE....
// SELECT * FROM OrderItem WHERE EXISTS (/* previous query */) AND ...
// SELECT * FROM DeliveryItems WHERE EXISTS (/* previous query */) AND ...

var orders = ctx.Orders
                .Where(x => x.OrderId == myOrderID) // 1 orders, 20 columns
                .IncludeOptimized(x => x.Items) // 20 items, 10 columns
                .IncludeOptimized(x => x.DeliveredItems) // 10 items, 10 columns
                .ToList();

// return 1 row * 20 columns = 20 cells
// return 20 rows * 10 columns = 200 cells
// return 10 rows * 10 columns = 100 cells
// total: 20 + 200 + 100 = 320 cells transferred

EF+ Query IncludeOptimized

IncludeOptimized method works like "Include" method but creates an efficient query to reduce the number of data to transfer.

// using Z.EntityFramework.Plus; // Don't forget to include this.
var ctx = new EntitiesContext();

var orders = ctx.Orders.IncludeOptimized(x => x.Items);

Try it

Options

• IncludeOptimized Filter
• IncludeOptimized AllowQueryBatch

IncludeOptimized Filter

Same as with EF+ Query IncludeFilter, it is possible to filter which related entities to load and then to launch a query.

// using Z.EntityFramework.Plus; // Don't forget to include this.
var ctx = new EntitiesContext();

var orders = ctx.Orders.IncludeOptimized(x => x.Items.Where(y => y.IsActive));

Try it

IncludeOptimized AllowQueryBatch

In some scenario, you may want to disable the Query Batch features to execute every query as individual.

// using Z.EntityFramework.Plus; // Don't forget to include this.

QueryIncludeOptimizedManager.AllowQueryBatch = false;

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()"

Limitations

• DO NOT work with AsNoTracking
• DO NOT throw an error when used with SingleOrDefault and more than one row is returned (Issue #822)
• Cannot be mixed with projection
• Cannot be mixed with Include (Include doesn't support projection)
• Cannot be mixed with IncludeFilter
• Many to Many relation:
  • Not supported yet
• Relationship:
  • 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.

// using Z.EntityFramework.Plus; // Don't forget to include this.
var ctx = new EntitiesContext();

ctx.Comments.ToList();

// The posts automatically contain all comments even without using the "Include" method.
ctx.Posts.ToList();

// Trying to load only one comment will not work either.
ctx.Posts.IncludeOptimized(q => q.Comments.Take(1)).ToList();

Conclusion

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? info@zzzprojects.com

We welcome all comments, ideas and suggestions to improve our library.