ASP.NET A-Strong.com

LINQ to SQL: .NET Language-Integrated Query for Relational Data

Introduction

Most programs written today manipulate data in one way or another and often this data is stored in a relational database. Yet there is a huge divide between modern programming languages and databases in how they represent and manipulate information. This impedance mismatch is visible in multiple ways. Most notable is that programming languages access information in databases through APIs that require queries to be specified as text strings. These queries are significant portions of the program logic. Yet they are opaque to the language, unable to benefit from compile-time verification and design-time features like IntelliSense.

Of course, the differences go far deeper than that. How information is represented—the data model—is quite different between the two. Modern programming languages define information in the form of objects. Relational databases use rows. Objects have unique identity as each instance is physically different from another. Rows are identified by primary key values. Objects have references that identify and link instances together. Rows are left intentionally distinct requiring related rows to be tied together loosely using foreign keys. Objects stand alone, existing as long as they are still referenced by another object. Rows exist as elements of tables, vanishing as soon as they are removed.

It is no wonder that applications expected to bridge this gap are difficult to build and maintain. It would certainly simplify the equation to get rid of one side or the other. Yet relational databases provide critical infrastructure for long-term storage and query processing, and modern programming languages are indispensable for agile development and rich computation.

Until now, it has been the job of the application developer to resolve this mismatch in each application separately. The best solutions so far have been elaborate database abstraction layers that ferry the information between the applications domain-specific object models and the tabular representation of the database, reshaping and reformatting the data each way. Yet by obscuring the true data source, these solutions end up throwing away the most compelling feature of relational databases; the ability for the data to be queried.

LINQ to SQL, a component of Visual Studio Code Name "Orcas", provides a run-time infrastructure for managing relational data as objects without losing the ability to query. It does this by translating language-integrated queries into SQL for execution by the database, and then translating the tabular results back into objects you define. Your application is then free to manipulate the objects while LINQ to SQL stays in the background tracking your changes automatically.

LINQ to SQL is designed to be non-intrusive to your application.
- It is possible to migrate current ADO.NET solutions to LINQ to SQL in a piecemeal fashion (sharing the same connections and transactions) since LINQ to SQL is simply another component in the ADO.NET family. LINQ to SQL also has extensive support for stored procedures, allowing reuse of the existing enterprise assets.
LINQ to SQL applications are easy to get started.
- Objects linked to relational data can be defined just like normal objects, only decorated with attributes to identify how properties correspond to columns. Of course, it is not even necessary to do this by hand. A design-time tool is provided to automate translating pre-existing relational database schemas into object definitions for you.

Together, the LINQ to SQL run-time infrastructure and design-time tools significantly reduce the workload for the database application developer. The following chapters provide an overview of how LINQ to SQL can be used to perform common database-related tasks. It is assumed that the reader is familiar with Language-Integrated Query and the standard query operators.

LINQ to SQL is language-agnostic. Any language built to provide Language-Integrated Query can use it to enable access to information stored in relational databases. The samples in this document are shown in both C# and Visual Basic; LINQ to SQL can be used with the LINQ-enabled version of the Visual Basic compiler as well.

A Quick Tour

The first step in building a LINQ to SQL application is declaring the object classes you will use to represent your application data. Let's walk through an example.

Creating Entity Classes

We will start with a simple class Customer and associate it with the customers table in the Northwind sample database. To do this, we need only apply a custom attribute to the top of the class declaration. LINQ to SQL defines the Table attribute for this purpose.

[Table(Name="Customers")]
public class Customer
{
   public string CustomerID;
   public string City;
}

Visual Basic

<Table(Name:="Customers")> _
Public Class Customer
   Public CustomerID As String
   Public City As String
End Class

The Table attribute has a Name property that you can use to specify the exact name of the database table. If no Name property is supplied, LINQ to SQL will assume the database table has the same name as the class. Only instances of classes declared as tables will be stored in the database. Instances of these types of classes are known as entities. The classes themselves are known as entity classes.

In addition to associating classes to tables you will need to denote each field or property you intend to associate with a database column. For this, LINQ to SQL defines the Column attribute.

[Table(Name="Customers")]
public class Customer
{
   [Column(IsPrimaryKey=true)]
   public string CustomerID;
   [Column]
   public string City;
}

Visual Basic

<Table(Name:="Customers")> _
Public Class Customer
   <Column(IsPrimaryKey:=true)> _
   Public CustomerID As String

   <Column> _
   Public City As String

End Class

The Column attribute has a variety of properties you can use to customize the exact mapping between your fields and the database columns. One property of note is the Id property. It tells LINQ to SQL that the database column is part of the primary key in the table.

As with the Table attribute, you only need to supply information in the Column attribute if it differs from what can be deduced from your field or property declaration. In this example, you need to tell LINQ to SQL that the CustomerID field is part of the primary key in the table, yet you don't have to specify the exact name or type.

Only fields and properties declared as columns will be persisted to or retrieved from the database. Others will be considered as transient parts of your application logic.

The DataContext

For example, you can use the DataContext to retrieve customer objects whose city is London as follows:

// 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
var q =
   from c in Customers
   where c.City == "London"
   select c;
foreach (var cust in q)
   Console.WriteLine("id = {0}, City = {1}", cust.CustomerID, cust.City);

Visual Basic

' DataContext takes a connection string 
Dim db As DataContext  = New DataContext("c:\northwind\northwnd.mdf")
' Get a typed table to run queries
Dim Customers As Customers(Of Customer) = db.GetTable(Of Customer)()
' Query for customers from London
Dim londonCustomers = From customer in Customers _
                      Where customer.City = "London" _
                      Select customer
For Each cust in londonCustomers
   Console.WriteLine("id = " & cust.CustomerID & ", City = " & cust.City)
Next

Each database table is represented as a Table collection, accessible via the GetTable() method using its entity class to identify it. 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 collections as members of the context.

public partial class Northwind : DataContext
{
   public Table<Customer> Customers;
   public Table<Order> Orders;
   public Northwind(string connection): base(connection) {}
}

Visual Basic

Partial Public Class Northwind 
              Inherits DataContext

   Public Customers As Table(Of Customers)
   Public Orders As Table(Of Orders)
         Public Sub New(ByVal connection As String)
            MyBase.New(connection)
   End Sub
End Class

The query for customers from London can then be expressed more simply as:

Northwind db = new Northwind("c:\\northwind\\northwnd.mdf");
var q =
   from c in db.Customers
   where c.City == "London"
   select c;
foreach (var cust in q)
   Console.WriteLine("id = {0}, City = {1}",cust.CustomerID, cust.City);

Visual Basic

Dim db = New Northwind("c:\northwind\northwnd.mdf")
Dim londonCustomers = From cust In db.Customers _
                      Where cust.City = "London" _
                      Select cust
For Each cust in londonCustomers
   Console.WriteLine("id = {0}, City = {1}", cust.CustomerID, cust.City) 

Next

We will continue to use the strongly typed Northwind class for the remainder of the overview document.

A-STRONG.COM

ASP.NET 3.5 Web Site

LINQ to SQL: .NET Language-Integrated Query for Relational Data

A Quick Tour

Creating Entity Classes

The DataContext