Chapter 7. A Quick Tour of LINQ to SQL
Relational databases contain important data that developers want to query and retrieve in their programs. LINQ to SQL lets developers access relational data as strongly typed objects by translating LINQ to SQL. It also provides a simple API to save to the database all the changes made to an object graph. Both query and changes are implemented by translating LINQ and API calls to SQL commands. Hence, users get the productivity of LINQ and the efficiency of the database query processor.
LINQ to SQL was created to bridge the differences between relational data and CLR objects. SQL is a rich query language, but it is not well integrated with programming languages such as C# and VB.NET. As a result, database developers have always struggled with the “impedance mismatch” between the relational data in their databases and the objects used in their programs running on the .NET framework.
An obvious manifestation of the mismatch is found when developers use string literals to embed SQL in their C# code. The resulting queries are opaque to the compiler. As a result, they are not type-checked and cannot benefit from IntelliSense.
Queries in strings are a serious problem, but it is only one manifestation of a deeper problem. The perennial difference between SQL and objects appears in many other forms:
• Relational tuples or records versus strongly typed objects
• Value-based identity versus reference-based identity
• Foreign key values versus object references
• Tabular results versus object graphs
Developers often have to think about the differences and write plumbing code to integrate relational data into their programs.
Before LINQ, several Object Relational Mapping (ORM) APIs helped transform the rows of data returned from a SQL query into objects and add them to graphs that could be consumed by C# developers. These tools managed identity when the user edited the data. They were smart enough to turn the objects back into rows when it was time to save the changes to the database. Despite their power, these tools continued to rely on string-based or API-based queries that were not fully integrated with the programming language. They also relied on awkward APIs that were poorly integrated with the programming language.
LINQ provides a much simpler way to integrate relational queries with the rest of the program. LINQ to SQL implements the LINQ standard query pattern and addresses the differences just mentioned. As a result, developers get a type-safe, IntelliSense-aware query language and a programming model for relational data that is fully integrated with their programming language.
LINQ to SQL is designed to be nonintrusive. Classes mapped to relational data can be defined just like normal classes. Developers need only decorate them with simple attributes to specify how properties correspond to columns. It is not even necessary to do this by hand. A design-time tool is provided to automate translating preexisting relational database schemas into object definitions. Together, the runtime infrastructure and design-time tools significantly reduce the workload for the database application developer.
Although this chapter uses C# to illustrate concepts and usage patterns, LINQ to SQL is language-agnostic. It can be used in any managed language that supports LINQ. In .NET Framework 3.5, similar code can be written in VB.NET. Other languages are also adding LINQ support and will be able to enable the use of LINQ to SQL.
Let’s look at the key components of a program that uses LINQ to SQL to access relational data. We will begin by exploring a simple class that maps to a table.
Mapping Classes to Tables
LINQ to SQL needs to know how your class maps to a database table. Then you can use the LINQ to SQL API—primarily through a class called DataContext—to query for objects of a mapped class and save the changes. We’ll look at a mapped class and the DataContext class next.
Creating Entity Classes
The following code fragment shows a simple class, Customer, that is mapped to the Customers table in the Northwind sample database. The namespaces added with the using declarations contain the LINQ to SQL API used for data access and mapping, respectively.
using System.Data.Linq;
using System.Data.Linq.Mapping;
[Table(Name="Customers")]
public class Customer
{
[Column(IsPrimaryKey=true)]
public string CustomerID;
[Column]
public string City;
}
A class is mapped to a table by adding the Table attribute. The Table attribute has a Name property that specifies the name of the database table. If no Name property is supplied, the database table name is assumed to be the same name as the class. Instances of classes declared with the Table attribute are considered persistent and are known as entities; the classes themselves are called entity classes.
In addition to mapping classes to tables, each field or property must be mapped to a database column. This can be done by using the Column attribute. If you omit this attribute when declaring a field of your class, this class member is assumed to be unmapped or nonpersistent. This allows you to choose which members are persistent and augment the data from the database with additional, nonpersistent members. In the preceding class declaration, both of the fields of the Customer class are mapped to the corresponding columns in the Customers table.
The Column attribute has a variety of properties you can use to customize the exact mapping between fields and the database’s columns. One property of note is the IsPrimaryKey property. It indicates that the database column is a member of the table’s primary key.
As with the Table attribute, you need to supply information in the Column attribute only if it differs from what can be deduced from your field or property declaration. In this example, the Id property on the Column attribute conveys that the CustomerID field is part of the table’s primary key. Note that you don’t have to specify the exact name or type if the defaults work.
The DataContext
The DataContext is the main conduit by which you retrieve objects from the database and submit changes back to the database. It encapsulates an ADO.NET Connection property that is initialized with an instance of a connection object or a connection string that you supply in the constructor. You can use the DataContext to help retrieve customer objects by writing the following code:
// DataContext takes a connection string
DataContext db = new DataContext("c:\northwind\northwnd.mdf");
// Get a typed table to run queries
Table<Customer> Customers = db.GetTable<Customer>();
// Query for customers from London
IQueryable<Customer> CustomerQuery = from c in Customers
where c.City == "London"
select c;
foreach (var cust in CustomerQuery)
Console.WriteLine("id = {0}, City = {1}", cust.CustomerID, cust.City);
This code creates an instance of the DataContext and then retrieves a set of Customer objects corresponding to rows where the City field is equal to London. The results bring back information for six customers:
id = AROUT, City = London
id = BSBEV, City = London
id = CONSH, City = London
id = EASTC, City = London
id = NORTS, City = London
id = SEVES, City = London
Looking more closely at this code, you can see that a database table is represented as a property of type Table<T>, accessible via the GetTable() method using its entity class T to identify it:
Table<Customer> Customers = db.GetTable<Customer>();
Table<T> is a LINQ to SQL class that implements the key LINQ interface—IQueryable<T>. That is how the LINQ query for customers from London is implemented by LINQ to SQL.
Rather than use this code in the midst of your query, it is recommended that you declare a strongly typed DataContext instead of relying on the basic DataContext class and the GetTable() method. A strongly typed DataContext declares all Table properties as members.
public partial class NorthwindDataContext : DataContext
{
public Table<Customer> Customers;
public NorthwindDataContext(string connection): base(connection) {}
}
The query for customers from London can then be expressed more simply:
NorthwindDataContext db = new
NorthwindDataContext("c:\northwind\northwnd.mdf");
IQueryable<Customer> CustomerQuery = from c in db.Customers
where c.City == "London"
select c;
A derived class such as NorthwindDataContext provides a strongly typed view of your database. Henceforth, we will use the strongly typed NorthwindDataContext class instead of the base DataContext class. Also, we will use a more terse way to declare the query variable—var, where the return type is obvious from the query expression. See the section “Type Inference” in Chapter 4, “C# 3.0 Technical Overview,” for more details.
Working with Relationships
Objects are not islands. They are connected to other objects through relationships. A customer may have orders, an order may have order details, an order detail may refer to a product that is being ordered, and so on. Relationships in relational databases typically are modeled as foreign key values referring to primary keys in other tables. To navigate between them, you must explicitly bring the two tables together using a relational join operation. Objects, on the other hand, refer to each other using property references or collections of references navigated using “dot” notation. Obviously, dotting is simpler than joining, because you don’t need to recall the explicit join condition each time you navigate. Defining relationship properties allows you to navigate using the dot, so you don’t have to use the explicit join operator available in LINQ for the common cases.
Defining Relationships
For data relationships that will remain constant, it becomes convenient to encode them as property references in your entity class. You can apply an Association attribute to a member used to represent such a relationship. An association relationship frequently consists of a foreign key and a primary key. Here is how to declare a class that captures the association between the Customers table and the Orders table:
[Table(Name="Customers")]
public class Customer
{
[Column(IsPrimaryKey=true)]
public string CustomerID;
...
private EntitySet<Order> _Orders;
[Association(Storage="_Orders", OtherKey="CustomerID")]
public EntitySet<Order> Orders {
get { return this._Orders; }
set { this._Orders.Assign(value); }
}
}
The Customer class now has a property that declares the relationship between customers and their orders. The Orders property is of type EntitySet because the relationship is one-to-many—there may be many orders for a customer. We use the OtherKey property in the Association attribute to describe the property in the related class that needs to be matched to set up the association—in this case, the CustomerID member in the Order class. We have omitted the ThisKey property, which lists the key members on this side of the relationship. By default, it is inferred to be the primary key for the containing type—Customer.CustomerID in this case.
Notice how this is reversed in the definition for the Order class:
[Table(Name="Orders")]
public class Order
{
[Column(IsPrimaryKey=true)]
public int OrderID;
[Column]
public string CustomerID;
private EntityRef<Customer> _Customer;
[Association(Storage="_Customer", ThisKey="CustomerID")]
public Customer Customer {
get { return this._Customer.Entity; }
set { this._Customer.Entity = value; }
}
}
The Order class uses the EntityRef type to describe the relationship back to the customer. The Association attribute for the Customer property specifies the ThisKey property to relate Order to its Customer. In the class mapped to table with the foreign key, ThisKey needs to be specified.
The Storage property tells which private member is used to hold the property’s value. This allows LINQ to SQL to bypass your public property accessors. This is useful if you want LINQ to SQL to avoid any custom business logic written into your accessors. If the storage property is not specified, the public accessors are used instead. You may use the Storage property with Column attributes as well.
As soon as you start introducing relationships into your entity classes, the amount of code you need to write grows as you introduce support for notifications and graph consistency. For instance, you might want to add event handlers that are fired when properties are accessed. This kind of work can become quite tedious if you have many fields in your tables. Fortunately, easy-to-use tools can be used to generate all the necessary definitions as partial classes, allowing you to use a mix of generated code and custom business logic. In the rest of this chapter, we assume that a tool has been used to generate a complete Northwind DataContext and all entity classes. Two commonly used tools called the Object Relational Designer and SqlMetal are described later in this chapter.
Querying Across Relationships
Now that an association has been defined between the Customer and Order classes, you can use it when you write queries. To do so, simply refer to the relationship properties defined in your class. In this case, var is not just a matter of terseness or convenience; the result is an anonymous type. Hence, no type name can be used in the declaration.
var CustomerQuery = from c in db.Customers
from o in c.Orders
where c.City == "London"
select new { c.CustomerID, o.OrderID };
This query uses the Orders property to form the cross-product between customers and their orders, producing a new sequence of Customer and Order pairs. The first two results are shown here:
CustomerID=AROUT OrderID=10355
CustomerID=AROUT OrderID=10383
...
It is also possible to do the reverse:
var CustomerQuery = from o in db.Orders
where o.Customer.City == "London"
select new { o.Customer.CustomerID, o .OrderID };
In this example, the orders are queried, and the Customer relationship is used to access and filter on the properties of the associated Customer object. The results are the same as before.
Modifying and Saving Entities
Querying is only one use of relational data. Applications often need to create new relational data, modify existing data, and possibly delete some data too. LINQ to SQL is designed to offer maximum flexibility in manipulating and persisting changes made to your in-memory objects. As soon as entity objects are available, either by retrieving them through a query or constructing them anew, you may manipulate them as normal objects in your application, changing their values or adding them to and removing them from collections as you desire. DataContext tracks all your changes and is ready to transmit them back to the database as soon as you are done.
The following example uses the Customer and Order classes generated by a tool from the metadata of the entire Northwind sample database. To focus on the code for modifying entities, the class definitions are not shown. In the example, two customers are retrieved—one for update and one for deletion. A new order is created for insertion. All the operations are performed on the objects and collections in memory. The changes take effect in the database only when SubmitChanges() is called.
Before running the following code, make a copy of the sample Northwind database—in this case, the northwnd.mdf file. This will allow you to play with the code and make changes without altering the original sample database.
NorthwindDataContext db = new
NorthwindDataContext("c:\northwind\northwnd.mdf");
// Query for a specific customer
string id = "ALFKI";
var cust = db.Customers.Single(c => c.CustomerID == id);
// Change the name of the contact
cust.ContactName = "New Contact";
// Delete an existing Customer
string id2 = "FISSA";
var cust2 = db.Customers.Single(c => c.CustomerID == id2);
db.Customers.DeleteOnSubmit(cust2);
// Create and add a new Order to Orders collection
// LINQ to SQL discovers the new object and infers an insert
Order ord = new Order { OrderDate = DateTime.Now };
cust.Orders.Add(ord);
// Ask the DataContext to save all the changes
db.SubmitChanges();
When SubmitChanges() is called, LINQ to SQL automatically generates and executes SQL commands in order to transmit the changes back to the database. It is also possible to override this behavior with custom logic that can optionally call a stored procedure, as described in Chapter 10.
This completes the lifecycle of objects: creating, reading (querying), updating, and deleting. Collectively these operations are often known by the acronym CRUD. Through language integrated queries and simple API, LINQ to SQL provides CRUD operation support for relational data. This support uses the mapping between classes and tables to generate the necessary SQL to execute the operations in the database. We’ve looked at how this mapping can be added to code using attributes. The next section shows how the mapping can be created using a visual designer in Visual Studio.
Using the Graphical Designer for Mapping
Visual Studio 2008 includes a graphical designer, called the Object Relational Designer, that helps you map tables to classes. The following steps and associated figures outline how you can build a mapping that can be used with the queries in the previous sections:
1. Preparation: Using the View menu in Visual Studio, bring up Server Explorer, as shown in Figure 7.1, and Solution Explorer. In the Server Explorer pane, make sure that you can view the tables in your database. Appendix A contains tips for connecting to a database.
Figure 7.1. Server Explorer showing the Northwind database.
2. Adding a designer file (dbml): Right-click the project in Solution Explorer and select Add, New Item, as shown in Figure 7.2. In the Add New Item window, select Data in the Categories pane. In the Templates pane, select LINQ to SQL Classes. Type in the appropriate name (Northwind.dbml, as shown in Figure 7.3) and click Add. A blank design surface appears for creating classes. The design surface corresponds to a dbml file in Solution Explorer. The file captures the subset of databases you have dragged and dropped on the design surface and your customizations of the classes and mapping.
Figure 7.2. Adding a new item to the project.
Figure 7.3. Adding a dbml file to create a mapping.
3. Mapping tables: Drag and drop tables from Server Explorer onto the design surface. As a table is dropped onto the surface, the designer creates a class corresponding to it, and the class is visible on the design surface. You can edit the name and type of the class members using the property grid. If a newly dropped table is related to one of the previously dropped tables, the designer automatically creates a relationship, as shown in Figure 7.4. For example, when the Customers and Orders tables from the Northwind database are dragged and dropped, a relationship between the corresponding generated Customer and Order classes is inferred.
Figure 7.4. Classes generated from tables.
4. Generating classes: Save the file to trigger generation of C# (or VB.NET) code. The generated file appears in Solution Explorer with a designer.cs extension—Northwind.designer.cs in Figure 7.4. Now the classes are ready for use. We will cover the details of the generated code in the following chapters.
5. Using the generated code: The generated code can be used in lieu of the hand-crafted classes NorthwindDataContext, Customer, and Order to run the queries previously defined.
Using the Command-Line Tool for Mapping
The graphical designer provides an interactive way to customize mapping. However, sometimes it is more convenient to have a command-line tool for doing quick generation of classes and mapping. This allows additional options to be used for customization in an automated build process. It also provides a quicker way to generate an object model for an entire database.
The Windows SDK Version 6.0 includes a command-line tool, SqlMetal.exe, that can be pointed at a database for quick generation of a set of mapped classes. This tool can be found in the directory Program FilesMicrosoft SDKsWindowsV6.0ABin. The following line shows a sample usage for generating C# classes from the Northwind database using SQL Server 2008 Express. The /? switch lists all the options that the tool supports; we will not cover them in depth here.
SqlMetal /server:.sqlexpress /database:northwind /code:northwind.cs
The SqlMetal tool can also be used in two stages—extracting the database schema to produce an intermediate dbml file, and generating code from the dbml file. SqlMetal and the designer share a common code generator that consumes a dbml file and produces C# or VB.NET classes with mapping. Hence, the code and mapping produced by the two tools are consistent. So, you can easily use a designer-generated dbml file to produce code with SqlMetal.
SqlMetal also allows you to create an external XML mapping file instead of mapping attributes. This capability currently is unavailable in the designer. The following is an example of how you can create a dbml file for further manipulation and then create an external mapping file for Northwind. In this case, the classes in the generated file northwind.cs do not contain mapping attributes; the mapping in northwind.xml can be manipulated separately.
SqlMetal /server:.sqlexpress /database:northwind /dbml:northwind.dbml
--- dbml file can be changed with another tool or XML editor as needed ---
SqlMetal /code:nothwind.cs /map:northwind.xml northwind.dbml
The details of an external mapping file and its usage are discussed futher in Chapter 11.
Summary
This chapter has offered a quick tour of basic LINQ to SQL features. In a nutshell, LINQ to SQL lets you query relational data as objects using the LINQ query pattern. It provides a simple but expressive API to save the changed objects. It works with entity classes mapped to tables and relationships between classes mapped to foreign keys in the database. You can write your own classes and map them or use a convenient graphical designer or a command-line tool to generate mapped classes from an existing database. Next we’ll cover the essential LINQ to SQL concepts and features in greater depth, starting with the reading of objects.