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.
Navigation is not natively supported by database servers. It is implemented using APPLY operators to bring in the related data.
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.
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.
When one end of a relationship is referenced, the other end is implicitly brought in using a APPLY operator.
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
The complexity of the expanded view depends solely on the subject model. The following mapping scenarios by definition produce complex queries:
In order to construct a whole entity, rows that represent the ancestor types must be JOIN’ed together.
In order to construct a whole entity UNION ALL operators are used to bring in the rows for derived types.
Similarly, the parts of split entities must be JOIN’ed together.
Navigating a many-to-many relationship requires at least 2 JOIN operators since it can only be modeled with a separate, “link”, table.
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
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