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.
C#
[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.
C#
[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:
C#
// 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.
C#
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:
C#
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.