Query Complexity Factors
A query issued through Entity Framework undergoes three main transformations:
2. Query view expansion
3. Native SQL generation
Compilation is the process of producing a Command Tree.
1.1. Entity SQL
1.1.1. Navigation Properties
Navigation is not natively supported by database servers. It is implemented using APPLY operators to bring in the related data.
1.1.2. Queries Nested in Projection
Entity SQL supports nesting full-blown queries in the projection clause. However, that is not commonly supported by data stores. Instead, it is implemented via a pattern of JOIN’ing the nested query with the main source.
1.2.1. Entity Span
Entity Span (Include) in ObjectQuery is the equivalent of navigation. Similarly ObjectQuery needs to compile one or more APPLY operators to bring in the spanned over entities.
1.2.2. Relationship Span
When one end of a relationship is referenced, the other end is implicitly brought in using a APPLY operator.
1.3. LINQ To Entities
LINQ has a variety of operators that don’t exist in Command Trees and that are compiled to patterns of Command Tree constructs:
· Aggregates and GroupBy
· Anonymous types
· Entity collections
2. Query View Expansion
The complexity of the expanded view depends solely on the subject model. The following mapping scenarios by definition produce complex queries:
2.1. Inheritance (Table-Per-Type)
In order to construct a whole entity, rows that represent the ancestor types must be JOIN’ed together.
2.2. Inheritance (Table-Per-Concrete Type)
In order to construct a whole entity UNION ALL operators are used to bring in the rows for derived types.
2.3. Entity Splitting
Similarly, the parts of split entities must be JOIN’ed together.
2.4. Many-To-Many Relationships
Navigating a many-to-many relationship requires at least 2 JOIN operators since it can only be modeled with a separate, “link”, table.
3. Native SQL Generation
3.1. Store Capabilities
Command Trees are designed with the capabilities of SQL Server 2005 in mind. And their capabilities go even further. Other stores, like SQL Server 2000, have to translate some Command Tree operations to complex patterns. Such operations may be:
· SKIP (paging)
· Query nesting
· Canonical functions
4. Other Aspects of Complexity
The data store may not be able to carry on with a relatively simple native SQL query if the query requires an intermediate temporary storage and that intermediate projection exceeds the store’s capabilities. That may happen as a result of too many JOIN operators as well as of few JOINs of large projections.
Program Manager, Data Programmability Runtime