Communications - April 2011

relational) mappers that attempt to bridge the gap between the two worlds.

A more skeptical view of O/R mappers is that they are undoing the damage caused by normalizing the original object model into multiple tables. For our running example this means that we have to add back information to the various tables to recover the relationships that existed in the original model. In this particular case we use the LINQ-to-SQL custom metadata annotations; other O/R mappers use similar annotations, which often can be inferred from naming conventions of types and properties.

is necessarily more complex than we started with since we are forced to introduce explicit representations for Rating and Keyword collections that did not exist in our original object model. The existence of the various foreign- and primary-key properties is further evidence that the O/R mapping abstraction is leaky.

Aside from those small differences,
the net result of all this work is that
we can now write the query to find all
products nearly as concisely as we did
before normalization:
to the relationship between Prod-
ucts and Ratings and Products
and Keywords, respectively. For each
product in the Products table, the rat-
ings index contains the collection of all
related ratings:

from rating in Ratings where rating.
ProductID == product.ID
select rating;

Similarly, for each product in the Product table, the keywords index contains the collection of all keywords related to that product:

[Table(name=“Products”)]
class Product
{
[Column(PrimaryKey=true)]int ID;

[Column]string Title;

[Column]string Author;

[Column]int Year;

[Column]int Pages; private EntitySet<Rating> _Rat-ings;

[Association( Storage=“ _ Ratings”,
ThisKey=“ID”,OtherKey=“ProductID“
,DeleteRule=“ONDELETECASCADE”)]
ICollection<Rating> Ratings{ … }
var q = from product in Products
where product.Ratings.Any(rating
rating.Rating == “****”)
select new{ product.Title, prod-
uct.Keywords };

from keyword in Keywords where key- word.ProductID == product.ID select keyword;

private EntitySet<Keyword> _Key-words;

[Association( Storage=“ _ Keywords”,
ThisKey=“ID”
,OtherKey=“ProductID”,
DeleteRule=“ONDELETECASCADE”)]
ICollection<Keyword> Keywords{ … }
}

Since the results must be rendered as object graphs, the O/R mapper will make sure that the proper nested result structures are created. Unfortunately, not every O/R mapper does this efficiently. 9

It is not only the programmer who needs to recover the original structure of the code. The database implementer must also jump through hoops to make queries execute efficiently by building indexes that avoid the potential cubic effect that we observed earlier. For one-to-many relationships, indexes are nothing more than nested collections resulting from precom-puting joins between tables to quickly find all the rows whose foreign keys point to a row with a particular primary key. Since the relational model is not closed under composition, however, the notion of index has to be defined outside the model.

Two natural indexes correspond
If we visualize the indexes as addi-
tional columns on the Products table,
the reversal of the original relation-
ships between the tables becomes ap-
parent. Each row in the Products table
now has a collection of foreign keys
pointing to the Keywords and Ratings
tables much as the original object
graph, as shown in Figure 7.