Databases Reference
In-Depth Information
TABLE 9.3
SQL Generated with Table-per-Hierarchy Mapping
LINQ
SQL
from u in context.Users
SELECT * FROM Users
select u;
from c in context.Users.OfType<Customer>()
SELECT * FROM Users
select c;
WHERE UserType = 'C'
This approach is also made attractive by Microsoft SQL Server's support of
Sparse
Columns
—a storage optimization option for tables with large numbers of nullable
columns, which can help storing entities of different types more effective. However, with
a small number of types in a hierarchy, the discriminator column might not be selective
enough, and the query optimizer might choose to perform a full table scan for each query
even if an index covering this column exists. This approach also violates the third form of
database normalization, which requires each column to represent a fact about the key,
making it unpopular with database designers.
NOTE
Each of the three O/R Mapping approaches discussed here have different trade-offs,
making them appropriate for different scenarios.
Theoretically
, the table-per-type
mapping should work better for shallow hierarchies of significantly different classes,
and the table-per-hierarchy mapping should work better for deep hierarchies of classes
with small differences. The table-per-concrete type mapping should work better in
queries that retrieve objects of leaf types, and the other types of mapping should work
better for queries that retrieve information from base classes. However, the only
reli-
able
way to determine the best mapping type is through measurement of
actual
results.
Performance tuning and optimization are best left for later stages of application devel-
opment, when sufficient information and functionality is available to simulate real-world
usage scenarios. However, if you are certain you will need to use class inheritance in
your entity model, consider starting with the table-per-hierarchy mapping because it
offers the best combination of simplicity and performance.
Keep the Conceptual and Storage Models as Close as Possible
Although being able to fine-tune the object-oriented (conceptual) and the relational
(storage) models of your application seems like a good idea, this optimization comes at
the cost of a significant increase in effort to maintain the two models and mappings
between them. Because the Entity Framework allows this flexibility, it cannot automati-
cally update both models based on a change in one. For instance, if you want to change
the name of a particular column in the database, you also have to rename the correspond-
ing property in the conceptual model. The same goes for renaming or deleting tables,
changing relationships between them, and so on—all of these common changes have to
be done in both models separately.
