LINQ for Visual C# 2008

by Fabio Claudio Ferracchiati

Over the past 20 years object-oriented programming languages have evolved to become the premier tools for enterprise application development. They've been augmented by frameworks, APIs, and rapid application-development tools. Yet what's been missing is a way to intimately tie object-oriented programs to relational databases (and other data that doesn't exist as objects). The object paradigm is conceptually different from the relational one and this creates significant impedance between the objects programs use and the tables where data resides. ADO.NET provides a convenient interface to relational data, but not an object-oriented one. For example, this pseudocode would be really cool:

// A class representing a table of employees
Employees e = new Employees();

// Set the row identifier to one
e.ID = 1;

// Retrieve the row where ID=1
e.Retrieve();

// Change the Name column value to Alan
e.Name = "Alan";

// Modify the database data
e.Upate();

The pseudocode shows an object-oriented approach to data management; no query or SQL statement is visible to developers. You need to think about only what you have to do, not how to do it. This approach to combining object-oriented and relational technologies has been called the Object-Relational Mapping (ORM) model.

Although Microsoft has embedded ORM capabilities in its Dynamics CRM 3.0 application server and should soon do the same in ADO.NET 3.0, it doesn't yet provide this programming model to .NET developers. To run a simple SQL query, ADO.NET programmers have to store the SQL in a Command object, associate the Command with a Connection object and execute it on that Connection object, then use a DataReader or other object to retrieve the result set. For example, the following code is necessary to retrieve the single row accessed in the pseudocode presented earlier.

// Specify the connection to the DB
SqlConnection c = new SqlConnection(...);

// Open the connection
c.Open();

// Specify the SQL Command
SqlCommand cmd = new SqlCommand(@"
   SELECT
      *
   FROM
      Employees e
   WHERE
      e.ID = @p0
");

// Add a value to the parameter
cmd.Parameters.AddWithValue("@p0", 1);

// Excute the command
DataReader dr = c.Execute(cmd);

// Retrieve the Name column value
while (dr.Read()) {
   string name = dr.GetString(0);
}

// Update record using another Command object
...

// Close the connection
c.Close();

Not only is this a lot more code than the ORM code, but there's also no way for the C# compiler to check our query against our use of the data it returns. When we retrieve the employee's name we have to know the column's position in the database table to find it in the result. It's a common mistake to retrieve the wrong column and get a type exception or bad data at run time.

ADO.NET moved toward ORM with strongly typed DataSets. But we still have to write the same kind of code, using a DataAdapter instead of a Command object. The DataAdapter contains four Command objects, one for each database operation—

SELECT

DELETE

INSERT

UPDATE

.NET can also handle XML and nonrelational data sources, but then we have to know other ways to query information, such as XPath or XQuery. SQL and XML can be made to work together but only by shifting mental gears at the right time.