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
Products.Insert
( 1579124585
, “The Right Stuff”
, “Tom Wolfe”
, 1979
, 320
);
Keywords.Insert
( 4711, “Book”
, 1579124585
);
Keywords.Insert
( 1843, “Hardcover”
, 1579124585
);
Keywords.Insert
( 2012, “American”
, 1579124585
);
Ratings.Insert
( 787, “****”
, 1579124585
);
Ratings.Insert
( 747, “ 4 stars”
, 1579124585
);