Communications - April 2011

In C# (or any other modern object-oriented language) we can model products using the following class declaration, which for each product has scalar properties for title, author, publication date, and number of pages, and which contains two nested collections—one for keywords and another for ratings:

class Product
{
string Title;
string Author;
int Year;
int Pages;
IEnumerable<string> Keywords;
IEnumerable<string> Ratings;

var q = Products.Where(product==>
product.Ratings.
Any(rating rat-
ing == “****”)).
Select(product==>
new{ product.Title, product.
Keywords });

}

Given this class declaration, we can use object initializers to create a product and insert it into a collection using collection initializers:

var _ 1579124585 = new Product
{
Title = “The Right Stuff”,
Author = “Tom Wolfe”,
Year = 1979,
Pages = 320,
Keywords = new[]{ “Book”, “Hardcover”,
“American” },
Ratings = new[]{ “****”, “ 4 stars” },
}
var Products = new[]{ _ 1579124585 };

and Ratings. Later we will decorate these class declarations with additional metadata to reflect the underlying database tables.

In most commercial relational database systems, tables are defined by executing imperative CREATE TABLE DDL (data definition language) statements.

As usual in the relational world, we do not model the individual collections of keywords and ratings for each product as separate entities, but instead we directly associate multiple keywords and ratings to a particular product. This shortcut works only for one-to-many relationships. The standard practice for many-to-many relationships (multivalued functions) requires intersection tables containing nothing but pairs of foreign keys to link the related rows.

Perhaps surprisingly for a “ declarative” language, SQL does not have expressions that denote tables or rows directly. Instead we must fill the three tables in an imperative style using loosely typed DML statements, which we express in C# as shown in Figure 4.

These DML statements create three tables filled with the rows as shown in Figure 5.

An important consequence of nor-
malizing a single type into separate
tables is that the database must main-
(written here as rating==>rating ==
“****”), or inner-classes such as Objec-
tive-C, Ruby, Python, JavaScript, Java,
or C++. The C# compiler translates the
previous query to the following de-sug-
ared target expression:
The various values in the query re-
sult, in particular the Keywords col-
lection, are fully shared with the origi-
nal object graph, and the result is a
perfectly valid object graph, shown in
Figure 2.

Figure 3. Data declaration for Product database.

The program produces in memory the object graph shown in Figure 1.

Note that the two nested collections for the Keywords and Ratings properties are both represented by actual objects with their own identity.

Using the LINQ (Language Integrated Query) comprehension syntax introduced in C# 3.0, 11 we can find the titles and keywords of all products that have four-star ratings using the following query:

class Products
{
int ID;
string Title;
string Author;
int Year;
int Pages;
}
class Keywords
{
int ID;
string Keyword;
int ProductID;
}
class Ratings
{
int ID;
string Rating;
int ProductID;

Figure 4. inserting values into Product database.

var q = from product in Products
where product.Ratings.Any(rating
rating == “****”)
select new{ product.Title, prod
uct.Keywords };

The LINQ comprehension syntax is just syntactic sugar for a set of standard query operators that can be defined in any modern programming language with closures, lambda expressions