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

LINQ to SQL: .NET Language-Integrat...

Dinesh Kulkarni, Luca Bolognese, Matt Warren, Anders Hejlsberg, Kit George

March 2007

Applies to:
Visual Studio Code Name "Orcas"
.Net Framework 3.5

Summary: LINQ to SQL provides a runtime infrastructure for managing relational data as objects without losing the ability to query. Your application is free to manipulate the objects while LINQ to SQL stays in the background tracking your changes automatically. (119 printed pages)

Introduction
A Quick Tour
   Creating Entity Classes
   The DataContext
   Defining Relationships
   Querying Across Relationships
   Modifying and Saving Entities
Queries In-Depth
   Query Execution
   Object Identity
   Relationships
   Joins
   Projections
   Compiled Queries
   SQL Translation
The Entity Lifecycle
   Tracking Changes
   Submitting Changes
   Simultaneous Changes
   Transactions
   Stored Procedures
Entity Classes In-Depth
   Using Attributes
   Graph Consistency
   Change Notifications
   Inheritance
Advanced Topics
   Creating Databases
   Interoperating with ADO.NET
   Change Conflict Resolution
   Stored Procedures Invocation
   The Entity Class Generator Tool
   Generator Tool DBML Reference
   Multi-tier Entities
   External Mapping
   NET Framework Function Support and Notes
   Debugging Support

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

The DataContext is the main conduit by which you retrieve objects from the database and resubmit changes. You use it in the same way that you would use an ADO.NET Connection. In fact, the DataContext is initialized with a connection or connection string you supply. The purpose of the DataContext is to translate your requests for objects into SQL queries made against the database and then assemble objects out of the results. The DataContext enables language-integrated query by implementing the same operator pattern as the standard query operators such as Where and Select.

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.

Defining Relationships

Relationships in relational databases are typically 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, since you need not recall the explicit join condition each time you navigate.

For data relationships such as these that will always be the same, it becomes quite convenient to encode them as property references in your entity class. LINQ to SQL defines an Association attribute you can apply to a member used to represent a relationship. An association relationship is one like a foreign-key to primary-key relationship that is made by matching column values between tables.

[Table(Name="Customers")]
public class Customer
{
   [Column(Id=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); }
   }
}

Visual Basic

<Table(Name:="Customers")> _
Public Class Customer

   <Column(Id:=true)> _
   Public CustomerID As String
   ...
   Private _Orders As EntitySet(Of Order)
                  <Association(Storage:="_Orders", OtherKey:="CustomerID")> _
         Public Property Orders() As EntitySet(Of Order)
            Get
               Return Me._Orders
            End Get
            Set(ByVal value As EntitySet(Of Order))
            End Set
   End Property

End Class

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. We use the OtherKey property in the Association attribute to describe how this association is done. It specifies the names of the properties in the related class to be compared with this one. There was also a ThisKey property we did not specify. Normally, we would use it to list the members on this side of the relationship. However, by omitting it we allow LINQ to SQL to infer them from the members that make up the primary key.

Notice how this is reversed in the definition for the Order class.

[Table(Name="Orders")]
public class Order
{
   [Column(Id=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; }
   }
}

Visual Basic

<Table(Name:="Orders")> _
Public Class Order

   <Column(Id:=true)> _
   Public OrderID As String
   <Column> _
   Public CustomerID As String
   Private _Customer As EntityRef(Of Customer)
         <Association(Storage:="_Customer", ThisKey:="CustomerID")> _
         Public Property Customer() As Customer
            Get
               Return Me._Customer.Entity
            End Get
            Set(ByVal value As Customer)
               Me._Customers.Entity = value
            End Set
   End Property
End Class

The Order class uses the EntityRef type to describe the relationship back to the customer. The use of the EntityRef class is required to support deferred loading (discussed later). The Association attribute for the Customer property specifies the ThisKey property since the non-inferable members are now on this side of the relationship.

Also take a look at the Storage property. It tells LINQ to SQL which private member is used to hold the value of the property. This allows LINQ to SQL to bypass your public property accessors when it stores and retrieves their value. This is essential 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 will be used instead. You may use the Storage property with Column attributes as well.

Once you introduce relationships in your entity classes, the amount of code you need to write grows as you introduce support for notifications and graph consistency. Fortunately, there is a tool (described later) that 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.

For the rest of this document, we assume the tool has been used to generate a complete Northwind data context and all entity classes.

Querying Across Relationships

Now that you have relationships, you can use them when you write queries simply by referring to the relationship properties defined in your class.

var q =
   from c in db.Customers
   from o in c.Orders
   where c.City == "London"
   select new { c, o };

Visual Basic

Dim londonCustOrders = From cust In db.Customers, ord In cust.Orders _
                       Where cust.City = "London" _
                       Select Customer = cust, Order = ord

The above query uses the Orders property to form the cross product between customers and orders, producing a new sequence of Customer and Order pairs.

It's also possible to do the reverse.

var q =
   from o in db.Orders
   where o.Customer.City == "London"
   select new { c = o.Customer, o };

Visual Basic

Dim londonCustOrders = From ord In db.Orders _
                       Where ord.Customer.City = "London" _
                       Select Customer = ord.Customer, Order = ord

In this example, the orders are queried and the Customer relationship is used to access information on the associated Customer object.

Modifying and Saving Entities

Few applications are built with only query in mind. Data must be created and modified, too. LINQ to SQL is designed to offer maximum flexibility in manipulating and persisting changes made to your 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 and removing them from collections as you see fit. LINQ to SQL tracks all your changes and is ready to transmit them back to the database as soon as you are done.

The example below uses the Customer and Order classes generated by a tool from the metadata of the entire Northwind sample database. The class definitions have not been shown for brevity.

Northwind db = new Northwind("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";
// Create and add a new Order to Orders collection
Order ord = new Order { OrderDate = DateTime.Now };
cust.Orders.Add(ord);
// Ask the DataContext to save all the changes
db.SubmitChanges();

Visual Basic

Dim db As New Northwind("c:\northwind\northwnd.mdf")
' Query for a specific customer
Dim id As String = "ALFKI"
Dim targetCustomer = (From cust In db.Customers _ 
                     Where cust.CustomerID = id).First
' Change the name of the contact
targetCustomer.ContactName = "New Contact"
' Create and add a new Order to Orders collection
Dim id = New Order With { .OrderDate = DateTime.Now }
targetCustomer.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. The custom logic may call a database stored procedure.

Queries In-Depth

LINQ to SQL provides an implementation of the standard query operators for objects associated with tables in a relational database. This chapter describes the LINQ to SQL-specific aspects of queries.

Query Execution

Whether you write a query as a high-level query expression or build one out of the individual operators, the query that you write is not an imperative statement executed immediately. It is a description. For example, in the declaration below the local variable q refers to the description of the query not the result of executing it.

var q =
   from c in db.Customers
   where c.City == "London"
   select c;
foreach (Customer c in q)
   Console.WriteLine(c.CompanyName);

Visual Basic

Dim londonCustomers = From cust In db.Customers _
                       where cust.City = "London"
For Each cust  In londonCustomers
   Console.WriteLine(cust.CompanyName) 
Next

The actual type of q in this instance is IQueryable<Customer>. It's not until the application attempts to enumerate the contents of the query that it actually executes. In this example the foreach statement causes the execution to occur.

An IQueryable object is similar to an ADO.NET command object. Having one in hand does not imply that a query was executed. A command object holds onto a string that describes a query. Likewise, an IQueryable object holds onto a description of a query encoded as a data structure known as an Expression. A command object has an ExecuteReader() method that causes execution, returning results as a DataReader. An IQueryable object has a GetEnumerator() method that causes the execution, returning results as an IEnumerator<Customer>.

Therefore, it follows that if a query is enumerated twice it will be executed twice.

var q =
   from c in db.Customers
   where c.City == "London"
   select c;
// Execute first time
foreach (Customer c in q)
   Console.WriteLine(c.CompanyName);
// Execute second time
foreach (Customer c in q)
   Console.WriteLine(c.CompanyName);

Visual Basic

Dim londonCustomers = From cust In db.Customers _
                       where cust.City = "London"
' Execute first time
For Each cust In londonCustomers
   Console.WriteLine(cust.CompanyName) 
Next
' Execute second time
For Each cust In londonCustomers
   Console.WriteLine(cust.CustomerID) 
Next

This behavior is known as deferred execution. Just like with an ADO.NET command object it is possible to hold onto a query and re-execute it.

Of course, application writers often need to be very explicit about where and when a query is executed. It would be unexpected if an application were to execute a query multiple times simply because it needed to examine the results more than once. For example, you may want to bind the results of a query to something like a DataGrid. The control may enumerate the results each time it paints on the screen.

To avoid executing multiple times convert the results into any number of standard collection classes. It is easy to convert the results into a list or array using the standard query operators ToList() or ToArray().

var q =
   from c in db.Customers
   where c.City == "London"
   select c;
// Execute once using ToList() or ToArray()
var list = q.ToList();
foreach (Customer c in list)
   Console.WriteLine(c.CompanyName);
foreach (Customer c in list)
   Console.WriteLine(c.CompanyName);

Visual Basic

Dim londonCustomers = From cust In db.Customers _
                       where cust.City = "London"
' Execute once using ToList() or ToArray()
Dim londonCustList = londonCustomers.ToList()
' Neither of these iterations re-executes the query
For Each cust In londonCustList
   Console.WriteLine(cust.CompanyName)
Next
For Each cust In londonCustList
   Console.WriteLine(cust.CompanyName)
Next

One benefit of deferred execution is that queries may be piecewise constructed with execution only occurring when the construction is complete. You can start out composing a portion of a query, assigning it to a local variable and then sometime later continue applying more operators to it.

var q =
   from c in db.Customers
   where c.City == "London"
   select c;
if (orderByLocation) {
   q =
      from c in q
      orderby c.Country, c.City
      select c;
}
else if (orderByName) {
   q =
      from c in q
      orderby c.ContactName
      select c;
}
foreach (Customer c in q)
   Console.WriteLine(c.CompanyName);

Visual Basic

Dim londonCustomers = From cust In db.Customers _
                       where cust.City = "London"
if orderByLocation Then
   londonCustomers = From cust in londonCustomers _
                     Order By cust.Country, cust.City

Else If orderByName Then
   londonCustomers = From cust in londonCustomers _
                     Order By cust.ContactName
End If
For Each cust In londonCustList
   Console.WriteLine(cust.CompanyName)
Next

In this example, q starts out as a query for all customers in London. Later on it changes into an ordered query depending on application state. By deferring execution the query can be constructed to suit the exact needs of the application without requiring risky string manipulation.

Object Identity

Objects in the runtime have unique identity. If two variables refer to the same object, they are actually referring to the same object instance. Because of this, changes made via a path through one variable are immediately visible through the other. Rows in a relational database table do not have unique identity. However, they do have a primary key and that primary key may be unique, meaning no two rows may share the same key. Yet this only constrains the contents of the database table. Therefore, as long as we only interact with the data through remote commands, it amounts to about the same thing.

However, this is rarely the case. Most often data is brought out of the database and into a different tier where an application manipulates it. Clearly, this is the model that LINQ to SQL is designed to support. When the data is brought out of the database as rows, there is no expectation that two rows representing the same data actually correspond to the same row instances. If you query for a specific customer twice, you get two rows of data, each containing the same information.

Yet with objects, you expect something quite different. You expect that if you ask the DataContext for the same information again, it will in fact give you back the same object instance. You expect this because objects have special meaning for your application and you expect them to behave like normal objects. You designed them as hierarchies or graphs and you certainly expect to retrieve them as such, without hordes of replicated instances merely because you asked for the same thing twice.

Because of this, the DataContext manages object identity. Whenever a new row is retrieved from the database, it is logged in an identity table by its primary key and a new object is created. Whenever that same row is retrieved again, the original object instance is handed back to the application. In this way, the DataContext translates the databases concept of identity (keys) into the languages concept (instances). The application only ever sees the object in the state that it was first retrieved. The new data, if different, is thrown away.

You might be puzzled by this, since why would any application throw data away? As it turns out this is how LINQ to SQL manages integrity of the local objects and is able to support optimistic updates. Since the only changes that occur after the object is initially created are those made by the application, the intent of the application is clear. If changes by an outside party have occurred in the interim they will be identified at the time SubmitChanges() is called. More of this is explained in the Simultaneous Changes section.

Note that, in the case that the database contains a table without a primary key, LINQ to SQL allows queries to be submitted over the table, but it doesn't allow updates. This is because the framework cannot identify which row to update given the lack of a unique key.

Of course, if the object requested by the query is easily identifiable by its primary key as one already retrieved no query is executed at all. The identity table acts as a cache storing all previously retrieved objects.

Relationships

As we saw in the quick tour, references to other objects or collections of other objects in your class definitions directly correspond to foreign-key relationships in the database. You can use these relationships when you query by simply using dot notation to access the relationship properties, navigating from one object to another. These access operations translate to more complicated joins or correlated sub-queries in the equivalent SQL, allowing you to walk through your object graph during a query. For example, the following query navigates from orders to customers as a way to restrict the results to only those orders for customers located in London.

var q =
   from o in db.Orders
   where o.Customer.City == "London"
   select o;

Visual Basic

Dim londonOrders = From ord In db.Orders _
                       where ord.Customer.City = "London"

If relationship properties did not exist you would have to write them out manually as joins just as you would do in a SQL query.

var q =
   from c in db.Customers
   join o in db.Orders on c.CustomerID equals o.CustomerID
   where c.City == "London"
   select o;

Visual Basic

Dim londonOrders = From cust In db.Customers _
                            Join ord In db.Orders _
                            On cust.CustomerID Equals ord.CustomerID _
                   Where ord.Customer.City = "London" _
                   Select ord

The relationship property allows you to define this particular relationship once enabling the use of the more convenient dot syntax. However, this is not the reason why relationship properties exist. They exist because we tend to define our domain-specific object models as hierarchies or graphs. The objects we choose to program against have references to other objects. It's only a happy coincidence that since object-to-object relationships correspond to foreign key style relationships in databases that property access leads to a convenient way to write joins.

Therefore, the existence of relationship properties is more important on the results side of a query than as part of the query itself. Once you have your hands on a particular customer, its class definition tells you that customers have orders. So when you look into the Orders property of a particular customer you expect to see the collection populated with all the customer's orders, since that is in fact the contract you declared by defining the classes this way. You expect to see the orders there even if you did not particularly ask for orders up front. You expect your object model to maintain an illusion that it is an in-memory extension of the database, with related objects immediately available.

LINQ to SQL implements a technique called deferred loading in order to help maintain this illusion. When you query for an object you actually only retrieve the objects you asked for. The related objects are not automatically fetched at the same time. However, the fact that the related objects are not already loaded is not observable since as soon as you attempt to access them a request goes out to retrieve them.

var q =
   from o in db.Orders
   where o.ShipVia == 3
   select o;
foreach (Order o in q) {
   if (o.Freight > 200)
      SendCustomerNotification(o.Customer);
   ProcessOrder(o);
}

Visual Basic

Dim shippedOrders = From ord In db.Orders _
                    where ord.ShipVia = 3
For Each ord In shippedOrders
   If ord.Freight > 200 Then
      SendCustomerNotification(ord.Customer) 
      ProcessOrder(ord)
   End If
Next

For example, you may want to query for a particular set of orders and then only occasionally send an email notification to particular customers. You would not necessary need to retrieve all customer data up front with every order. Deferred loading allows you to defer the cost of retrieving extra information until you absolutely have to.

Of course, the opposite might also be true. You might have an application that needs to look at customer and order data at the same time. You know you need both sets of data. You know your application is going to drill down through each customer's orders as soon as you get them. It would be unfortunate to fire off individual queries for orders for every customer. What you really want to happen is to have the order data retrieved together with the customers.

var q =
   from c in db.Customers
   where c.City == "London"
   select c;
foreach (Customer c in q) {
   foreach (Order o in c.Orders) {
      ProcessCustomerOrder(o);
   }
}

Visual Basic

Dim londonCustomers = From cust In db.Customer _
                   Where cust.City = "London"
For Each cust In londonCustomers
   For Each ord In cust.Orders
      ProcessCustomerOrder(ord) 
   End If
Next

Certainly, you can always find a way to join customers and orders together in a query by forming the cross product and retrieving all the relative bits of data as one big projection. But then the results would not be entities. Entities are objects with identity that you can modify while the results would be projections that cannot be changed and persisted. Worse, you would be retrieving a huge amount of redundant data as each customer repeats for each order in the flattened join output.

What you really need is a way to retrieve a set of related objects at the same time—a delineated portion of a graph so you would never be retrieving any more or any less than was necessary for your intended use.

LINQ to SQL allows you to request immediate loading of a region of your object model for just this reason. It does this by allowing the specification of a DataShape for a DataContext. The DataShape class is used to instruct the framework about which objects to retrieve when a particular type is retrieved. This is accomplished by using the LoadWith method as in the following:

DataShape ds = new DataShape();
ds.LoadWith<Customer>(c => c.Orders);
db.Shape = ds;
var q = 
   from c in db.Customers
   where c.City == "London"
   select c;

Visual Basic

Dim ds As DataShape = New DataShape()
ds.LoadWith(Of Customer)(Function(c As Customer) c.Orders)
db.Shape = ds
Dim londonCustomers = From cust In db.Customers _
                      Where cust.City = "London" _
                      Select cust

In the previous query, all the Orders for all the Customers who live in London are retrieved when the query is executed, so that successive access to the Orders property on a Customer object doesn't trigger a database query.

The DataShape class can also be used to specify sub-queries that are applied to a relationship navigation. For example, if you want to retrieve just the Orders that have been shipped today, you can use the AssociateWith method on the DataShape as in the following:

DataShape ds = new DataShape();
ds.AssociateWith<Customer>(
   c => c.Orders.Where(p => p.ShippedDate != DateTime.Today));
db.Shape = ds;
var q = 
   from c in db.Customers
   where c.City == "London"
   select c;
foreach(Customer c in q) {
   foreach(Order o in c.Orders) {}
}

Visual Basic

Dim ds As DataShape = New DataShape()
ds.AssociateWith(Of Customer)( _
         Function(cust As Customer) From cust In db.Customers _
                                 Where order.ShippedDate <> Today _
                                 Select cust)
db.Shape = ds
Dim londonCustomers = From cust In db.Customers _
                      Where cust.City = "London" _
                      Select cust
For Each cust in londonCustomers
   For Each ord In cust.Orders …
   Next
   Next

In the previous code, the inner foreach statement iterates just over the Orders that have been shipped today, because just such orders have been retrieved from the database.

It is important to notice two facts about the DataShape class:

After assigning a DataShape to a DataContext, the DataShape cannot be modified. Any LoadWith or AssociateWith method call on such a DataShape will return an error at run time.

It is impossible to create cycles by using LoadWith or AssociateWith. For example, the following generates an error at run time:

DataShape ds = new DataShape();
ds.AssociateWith<Customer>(
         c=>c.Orders.Where(o=> o.Customer.Orders.Count() < 35);

Visual Basic

Dim ds As DataShape = New DataShape()
ds.AssociateWith(Of Customer)( _
         Function(cust As Customer) From ord In cust.Orders _
                      Where ord.Customer.Orders.Count() < 35)

Joins

Most queries against object models heavily rely on navigating object references in the object model. However, there are interesting "relationships" between entities that may not be captured in the object model as references. For example Customer.Orders is a useful relationship based on foreign key relationships in the Northwind database. However, Suppliers and Customers in the same City or Country is an ad hoc relationship that is not based on a foreign key relationship and may not be captured in the object model. Joins provide an additional mechanism to handle such relationships. LINQ to SQL supports the new join operators introduced in LINQ.

Consider the following problem—find suppliers and customers based in the same city. The following query returns supplier and customer company names and the common city as a flattened result. This is the equivalent of the inner equi-join in relational databases:

var q = 
   from s in db.Suppliers
   join c in db.Customers on s.City equals c.City
   select new {
      Supplier = s.CompanyName,
      Customer = c.CompanyName,
      City = c.City
   };

Visual Basic

Dim customerSuppliers = From sup In db.Suppliers _
                        Join cust In db.Customers _
                              On sup.City Equals cust.City _
                        Select Supplier = sup.CompanyName, _
                        CustomerName = cust.CompanyName, _
                        City = cust.City

The above query eliminates suppliers that are not in the same city as a certain customer. However, there are times when we don't want to eliminate one of the entities in an ad hoc relationship. The following query lists all suppliers with groups of customers for each of the suppliers. If a particular supplier does not have any customer in the same city, the result is an empty collection of customers corresponding to that supplier. Note that the results are not flat—each supplier has an associated collection. Effectively, this provides group join—it joins two sequences and groups elements of the second sequence by the elements of the first sequence.

var q = 
   from s in db.Suppliers
   join c in db.Customers on s.City equals c.City into scusts
   select new { s, scusts };

Visual Basic

Dim customerSuppliers = From sup In db.Suppliers _
                        Group Join cust In db.Customers _
                              On sup.City Equals cust.City _
                              Into supCusts _
                        Select Supplier = sup, _
                        Customers = supCusts

Group join can be extended to multiple collections as well. The following query extends the above query by listing employees that are in the same city as the supplier. Here, the result shows a supplier with (possibly empty) collections of customers and employees.

var q = 
   from s in db.Suppliers
   join c in db.Customers on s.City equals c.City into scusts
   join e in db.Employees on s.City equals e.City into semps
   select new { s, scusts, semps };

Visual Basic

Dim customerSuppliers = From sup In db.Suppliers _
                        Group Join cust In db.Customers _
                              On sup.City Equals cust.City _
                              Into supCusts _
                        Group Join emp In db.Employees _
                              On sup.City Equals emp.City _
                              Into supEmps _
                        Select Supplier = sup, _
                        Customers = supCusts, Employees = supEmps

The results of a group join can also be flattened. The results of flattening the group join between suppliers and customers are multiple entries for suppliers with multiple customers in their city—one per customer. Empty collections are replaced with nulls. This is equivalent to a left outer equi-join in relational databases.

var q = 
   from s in db.Suppliers
   join c in db.Customers on s.City equals c.City into sc
   from x in sc.DefaultIfEmpty()
   select new {
      Supplier = s.CompanyName, 
      Customer = x.CompanyName, 
      City = x.City 
   };

Visual Basic

Dim customerSuppliers = From sup In db.Suppliers _
                        Group Join cust In db.Customers _
                              On sup.City Equals cust.City _
                              Into supCusts _
                        Select Supplier = sup, _
                        CustomerName = supCusts.CompanyName, sup.City

The signatures for underlying join operators are defined in the standard query operators document. Only equi-joins are supported and the two operands of equals must have the same type.

Projections

So far, we have only looked at queries for retrieving entities—objects directly associated with database tables. We need not constrain ourselves to just this. The beauty of a query language is that you can retrieve information in any form you want. You will not be able to take advantage of automatic change tracking or identity management when you do so. However, you can get just the data you want.

For example, you may simply need to know the company names of all customers in London. If this is the case there is no particular reason to retrieve entire customer objects merely to pick out names. You can project out the names as part of the query.

var q =
   from c in db.Customers
   where c.City == "London"
   select c.CompanyName;

Visual Basic

Dim londonCustomerNames = From cust In db.Customer _
                          Where cust.City = "London" _
                          Select cust.CompanyName

In this case, q becomes a query that retrieves a sequence of strings.

If you want to get back more than just a single name, but not enough to justify fetching the entire customer object, you can specify any subset you want by constructing the results as part of your query.

var q =
   from c in db.Customers
   where c.City == "London"
   select new { c.CompanyName, c.Phone };

Visual Basic

Dim londonCustomerInfo = From cust In db.Customer _
                         Where cust.City = "London" _
                         Select cust.CompanyName, cust.Phone

This example uses an anonymous object initializer to create a structure that holds both the company name and phone number. You may not know what to call the type, but with implicitly typed local variable declaration in the language you do not necessarily need to.

var q =
   from c in db.Customers
   where c.City == "London"
   select new { c.CompanyName, c.Phone };
foreach(var c in q)
   Console.WriteLine("{0}, {1}", c.CompanyName, c.Phone);

Visual Basic

Dim londonCustomerInfo = From cust In db.Customer _
                         Where cust.City = "London" _
                         Select cust.CompanyName, cust.Phone
For Each cust In londonCustomerInfo 
   Console.WriteLine(cust.CompanyName & ", " & cust.Phone) 
Next

If you are consuming the data immediately, anonymous types make a good alternative to explicitly defining classes to hold your query results.

You can also form cross products of entire objects, though you might rarely have a reason to do so.

var q =
   from c in db.Customers
   from o in c.Orders
   where c.City == "London"
   select new { c, o };

Visual Basic

Dim londonOrders = From cust In db.Customer, _
                   ord In db.Orders _
                   Where cust.City = "London" _
                   Select Customer = cust, Order = ord

This query constructs a sequence of pairs of customer and order objects.

It's also possible to make projections at any stage of the query. You can project data into newly constructed objects and then refer to those objects' members in subsequent query operations.

var q =
   from c in db.Customers
   where c.City == "London"
   select new {Name = c.ContactName, c.Phone} into x
   orderby x.Name
   select x;

Visual Basic

Dim londonItems = From cust In db.Customer _
                  Where cust.City = "London" _
                  Select Name = cust.ContactName, cust.Phone _
                  Order By Name

Be wary of using parameterized constructors at this stage, though. It is technically valid to do so, yet it is impossible for LINQ to SQL to track how constructor usage affects member state without understanding the actual code inside the constructor.

var q =
   from c in db.Customers
   where c.City == "London"
   select new MyType(c.ContactName, c.Phone) into x
   orderby x.Name
   select x;

Visual Basic

Dim londonItems = From cust In db.Customer _
                  Where cust.City = "London" _
                  Select MyType = New MyType(cust.ContactName, cust.Phone) _
                  Order By MyType.Name

Because LINQ to SQL attempts to translate the query into pure relational SQL locally defined object types are not available on the server to actually construct. All object construction is actually postponed until after the data is retrieved back from the database. In place of actual constructors, the generated SQL uses normal SQL column projection. Since it is not possible for the query translator to understand what is happening during a constructor call, it is unable to establish a meaning for the Name field of MyType.

Instead, the best practice is to always use object initializers to encode projections.

var q =
   from c in db.Customers
   where c.City == "London"
   select new MyType { Name = c.ContactName, HomePhone = c.Phone } into x
   orderby x.Name
   select x;

Visual Basic

Dim londonCustomers = From cust In db.Customer _
                      Where cust.City = "London" _
                      Select Contact = New With {.Name = cust.ContactName, _
                      .Phone = cust.Phone} _
                      Order By Contact.Name

The only safe place to use a parameterized constructor is in the final projection of a query.

var e =
   new XElement("results",
      from c in db.Customers
      where c.City == "London"
      select new XElement("customer",
         new XElement("name", c.ContactName),
         new XElement("phone", c.Phone)
      )
   );

Visual Basic

      Dim x = <results>
                  <%= From cust In db.Customers _
                      Where cust.City = "London" _
                      Select <customer>
                         <name><%= cust.ContactName %></name>
                         <phone><%= cust.Phone %></phone>
                      </customer> 
                 %>
        </results>

You can even use elaborate nesting of object constructors if you desire, like this example that constructs XML directly out of the result of a query. It works as long as it's the last projection of the query.

Still, even if constructor calls are understood, calls to local methods may not be. If your final projection requires invocation of local methods, it is unlikely that LINQ to SQL will be able to oblige. Method calls that do not have a known translation into SQL cannot be used as part of the query. One exception to this rule is method calls that have no arguments dependent on query variables. These are not considered part of the translated query and instead are treated as parameters.

Still elaborate projections (transformations) may require local procedural logic to implement. For you to use your own local methods in a final projection you will need to project twice. The first projection extracts all the data values you'll need to reference and the second projection performs the transformation. In between these two projections is a call to the AsEnumerable() operator that shifts processing at that point from a LINQ to SQL query into a locally executed one.

var q =
   from c in db.Customers
   where c.City == "London"
   select new { c.ContactName, c.Phone };
var q2 =
   from c in q.AsEnumerable()
   select new MyType {
      Name = DoNameProcessing(c.ContactName),
      Phone = DoPhoneProcessing(c.Phone)
   };

Visual Basic

Dim londonCustomers = From cust In db.Customer _
                      Where cust.City = "London" _
                      Select cust.ContactName, cust.Phone

Dim processedCustomers = From cust In londonCustomers.AsEnumerable() _
                         Select Contact = New With { _
                         .Name = DoNameProcessing(cust.ContactName), _
                         .Phone = DoPhoneProcessing(cust.Phone)}

Note The AsEnumerable() operator, unlike ToList() and ToArray(), does not cause execution of the query. It is still deferred. The AsEnumerable() operator merely changes the static typing of the query, turning a IQueryable<T> (IQueryable (ofT) in Visual Basic) into an IEnumerable<T> (IEnumerable (ofT) in Visual Basic), tricking the compiler into treating the rest of the query as locally executed.

Compiled Queries

It is common in many applications to execute structurally similar queries many times. In such cases, it is possible to increase performance by compiling the query once and executing it several times in the application with different parameters. This result is obtained in LINQ to SQL by using the CompiledQuery class. The following code shows how to define a compiled query:

static class Queries
{
   public static Func<Northwind, string, IQueryable<Customer>>
      CustomersByCity = CompiledQuery.Compile((Northwind db, string city) =>
         from c in db.Customers where c.City == city select c);
}

Visual Basic

Class Queries
   public Shared Function(Of Northwind, String, IQueryable(Of Customer)) _      CustomersByCity = CompiledQuery.Compile( _
                Function(db As Northwind, city As String) _
                From cust In db.Customers Where cust.City = city)
End Class

The Compile method returns a delegate that can be cached and executed afterward several times by just changing the input parameters. The following code shows an example of this:

public IEnumerable<Customer> GetCustomersByCity(string city) {
         Northwind db = new Northwind();
         return Queries.CustomersByCity(myDb, city);
}

Visual Basic

Public Function GetCustomersByCity(city As String) _ 
               As IEnumerable(Of Customer)
         Dim db As Northwind = New Northwind()
         Return Queries.CustomersByCity(myDb, city)
End Function

SQL Translation

LINQ to SQL does not actually execute queries; the relational database does. LINQ to SQL translates the queries you wrote into equivalent SQL queries and sends them to the server for processing. Because execution is deferred, LINQ to SQL is able to examine your entire query even if assembled from multiple parts.

Since the relational database server is not actually executing IL (aside from the CLR integration in SQL Server 2005); the queries are not transmitted to the server as IL. They are in fact transmitted as parameterized SQL queries in text form.

Of course, SQL—even T-SQL with CLR integration—is incapable of executing the variety of methods that are locally available to your program. Therefore the queries you write must be translated into equivalent operations and functions that are available inside the SQL environment.

Most methods and operators on .Net Framework built-in types have direct translations into SQL. Some can be produced out of the functions that are available. The ones that cannot be translated are disallowed, generating run-time exceptions if you try to use them. There is a section later in the document that details the framework methods that are implemented to translate into SQL.

The Entity Lifecycle

LINQ to SQL is more than just an implementation of the standard query operators for relational databases. In addition to translating queries, it is a service that manages your objects throughout their lifetime, aiding you in maintaining the integrity of your data and automating the process of translating your modifications back into the store.

In a typical scenario, objects are retrieved through one or more queries and then manipulated in some way or another until the application is ready to send the changes back to the server. This process may repeat a number of times until the application no longer has use for this information. At that point, the objects are reclaimed by the runtime just like normal objects. The data, however, remains in the database. Even after being erased from their run-time existence, objects representing the same data can still be retrieved. In this sense, the object's true lifetime exists beyond any single run-time manifestation.

The focus of this chapter is the entity lifecycle where a cycle refers to the time span of a single manifestation of an entity object within a particular run-time context. The cycle starts when the DataContext becomes aware of a new instance and ends when the object or DataContext is no longer needed.

Tracking Changes

After entities are retrieved from the database, you are free to manipulate them as you like. They are your objects; use them as you will. As you do this, LINQ to SQL tracks changes so that it can persist them into the database when SubmitChanges() is called.

LINQ to SQL starts tracking your entities the moment they are retrieved from the database, before you ever lay your hands on them. Indeed, the identity management service discussed earlier has already kicked in as well. Change tracking costs very little in additional overhead until you actually start making changes.

Customer cust = db.Customers.Single(c => c.CustomerID == "ALFKI");
cust.CompanyName = "Dr. Frogg's Croakers";

Visual Basic

' Query for a specific customer
Dim id As String = "ALFKI"
Dim targetCustomer = (From cust In db.Customers _ 
                      Where cust.CustomerID = id).First
targetCustomer.CompanyName = "Dr. Frogg's Croakers"

As soon as the CompanyName is assigned in the example above, LINQ to SQL becomes aware of the change and is able to record it. The original values of all data members are retained by the change tracking service.

The change tracking service also records all manipulations of relationship properties. You use relationship properties to establish the links between your entities, even though they may be linked by key values in the database. There is no need to directly modify the members associated with the key columns. LINQ to SQL automatically synchronizes them for you before the changes are submitted.

Customer cust1 = db.Customers.Single(c => c.CustomerID == custId1);
foreach (Order o in db.Orders.Where(o => o.CustomerID == custId2)) {
   o.Customer = cust1;
}

Visual Basic

Dim targetCustomer = (From cust In db.Customers _ 
                      Where cust.CustomerID = custId1).First

For Each ord In (From o In db.Orders _ 
                 Where o.CustomerID = custId2) 
   o.Customer = targetCustomer
Next

You can move orders from one customer to another by simply making an assignment to their Customer property. Since the relationship exists between the customer and the order, you can change the relationship by modifying either side. You could have just as easily removed them from the Orders collection of cust2 and added them to the orders collection of cust1, as shown below.

Customer cust1 = db.Customers.Single(c => c.CustomerID == custId1);
Customer cust2 = db.Customers.Single(c => c.CustomerID == custId2); 
// Pick some order
Order o = cust2.Orders[0]; 
// Remove from one, add to the other
cust2.Orders.Remove(o);
cust1.Orders.Add(o);
// Displays 'true'
Console.WriteLine(o.Customer == cust1);

Visual Basic

Dim targetCustomer1 = (From cust In db.Customers _ 
                       Where cust.CustomerID = custId1).First
Dim targetCustomer2 = (From cust In db.Customers _ 
                       Where cust.CustomerID = custId1).First
' Pick some order
Dim o As Order = targetCustomer2.Orders(0) 
' Remove from one, add to the other
targetCustomer2.Orders.Remove(o)
targetCustomer1.Orders.Add(o)
' Displays 'True'
MsgBox(o.Customer = targetCustomer1)

Of course, if you assign a relationship the value of null, you are in fact getting rid of the relationship completely. Assigning a Customer property of an order to null actually removes the order from the customer's list.

Customer cust = db.Customers.Single(c => c.CustomerID == custId1); 
// Pick some order
Order o = cust.Orders[0];
// Assign null value
o.Customer = null;
// Displays 'false'
Console.WriteLine(cust.Orders.Contains(o));

Visual Basic

Dim targetCustomer = (From cust In db.Customers _ 
                       Where cust.CustomerID = custId1).First
' Pick some order
Dim o As Order = targetCustomer.Orders(0)
' Assign null value
o.Customer = Nothing
' Displays 'False'
Msgbox(targetCustomer.Orders.Contains(o))

Automatic updating of both sides of a relationship is essential for maintaining consistency of your object graph. Unlike normal objects, relationships between data are often bidirectional. LINQ to SQL allows you to use properties to represent relationships. However, it does not offer a service to automatically keep these bidirectional properties in sync. This is a level of service that must be baked directly into your class definitions. Entity classes generated using the code generation tool have this capability. In the next chapter, we will show you how to do this to your own handwritten classes.

It is important to note, however, that removing a relationship does not imply that an object has been deleted from the database. Remember, the lifetime of the underlying data persists in the database until the row has been deleted from the table. The only way to actually delete an object is to remove it from its Table collection.

Customer cust = db.Customers.Single(c => c.CustomerID == custId1); 
// Pick some order
Order o = cust.Orders[0];
// Remove it directly from the table (I want it gone!)
db.Orders.Remove(o);
// Displays 'false'.. gone from customer's Orders
Console.WriteLine(cust.Orders.Contains(o));
// Displays 'true'.. order is detached from its customer
Console.WriteLine(o.Customer == null);

Visual Basic

Dim targetCustomer = (From cust In db.Customers _ 
                          Where cust.CustomerID = custId1).First
' Pick some order
Dim o As Order = targetCustomer.Orders(0)
' Remove it directly from the table (I want it gone!)
db.Orders.Remove(o)
' Displays 'False'.. gone from customer’s Orders
Msgbox(targetCustomer.Orders.Contains(o))
' Displays 'True'.. order is detached from its customer
Msgbox(o.Customer = Nothing)

Like with all other changes, the order has not actually been deleted. It just looks that way to us since it has been removed and detached from the rest of our objects. When the order object was removed from the Orders table, it was marked for deletion by the change tracking service. The actually deletion from the database will occur when the changes are submitted on a call to SubmitChanges(). Note that the object itself is never deleted. The runtime manages the lifetime of object instances, so it sticks around as long as you are still holding a reference to it. However, after an object has been removed from its Table and changes submitted it is no longer tracked by the change tracking service.

The only other time an entity is left untracked is when it exists before the DataContext is aware of it. This happens whenever you create new objects in your code. You are free to use instances of entity classes in your application without ever retrieving them from a database. Change tacking and identity management only apply to those objects that the DataContext is aware of. Therefore neither service is enabled for newly created instances until you add them to the DataContext.

This can occur in one of two ways. You can call the Add() method on the related Table collection manually.

Customer cust =
   new Customer {
      CustomerID = "ABCDE",
      ContactName = "Frond Smooty",
      CompanyTitle = "Eggbert's Eduware",
      Phone = "888-925-6000"
   };
// Add new customer to Customers table
db.Customers.Add(cust);

Visual Basic

Dim targetCustomer = New Customer With { _
         .CustomerID = “ABCDE”, _
         .ContactName = “Frond Smooty”, _
         .CompanyTitle = “Eggbert’s Eduware”, _
         .Phone = “888-925-6000”}
' Add new customer to Customers table
db.Customers.Add(cust)

Alternatively, you can attach a new instance to an object that the DataContext is already aware of.

// Add an order to a customer's Orders
cust.Orders.Add(
   new Order { OrderDate = DateTime.Now }
);

Visual Basic

' Add an order to a customer's Orders
targetCustomer.Orders.Add( _
   New Order With { .OrderDate = DateTime.Now } )

The DataContext will discover your new object instances even if they are attached to other new instances.

// Add an order and details to a customer's Orders
Cust.Orders.Add(
   new Order {
      OrderDate = DateTime.Now,
      OrderDetails = {
         new OrderDetail {
            Quantity = 1,
            UnitPrice = 1.25M,
            Product = someProduct
         }
      }
   }
);

Visual Basic

' Add an order and details to a customer's Orders
targetCustomer.Orders.Add( _
   New Order With { _
      .OrderDate = DateTime.Now, _
      .OrderDetails = New OrderDetail With { _
               .Quantity = 1,
               .UnitPrice = 1.25M,
               .Product = someProduct 
      }
   } )

Basically, the DataContext will recognize any entity in your object graph that is not currently tracked as a new instance, whether or not you called the Add() method.

Using a read-only DataContext

Many scenarios don't necessitate updating the entities retrieved from the database. Showing a table of Customers on a Web page is one obvious example. In all such cases, it is possible to improve performance by instructing the DataContext not to track the changes to the entities. This is achieved by specifying the ObjectTracking property on the DataContext to be false as in the following code:

      db.ObjectTracking = false;
      
      var q = db.Customers.Where( c => c.City = "London");
      foreach(Customer c in q)
         Display(c);

Visual Basic

db.ObjectTracking = False
      
      Dim londonCustomers = From cust In db.Customer _
                      Where cust.City = "London"
      For Each c in londonCustomers
         Display(c)
Next

Submitting Changes

Regardless of how many changes you make to your objects, those changes were only made to in-memory replicas. Nothing has yet happened to the actual data in the database. Transmission of this information to the server will not happen until you explicitly request it by calling SubmitChanges() on the DataContext.

Northwind db = new Northwind("c:\\northwind\\northwnd.mdf");
// make changes here
db.SubmitChanges();

Visual Basic

Dim db As New Northwind("c:\northwind\northwnd.mdf")
' make changes here
db.SubmitChanges()

When you do call SubmitChanges(), the DataContext will attempt to translate all your changes into equivalent SQL commands, inserting, updating, or deleting rows in corresponding tables. These actions can be overridden by your own custom logic if you desire, however the order of submission is orchestrated by a service of the DataContext known as the change processor.

The first thing that happens when you call SubmitChanges() is that the set of known objects are examined to determine if new instances have been attached to them. These new instances are added to the set of tracked objects. Next, all objects with pending changes are ordered into a sequence of objects based on dependencies between them. Those objects whose changes depend on other objects are sequenced after their dependencies. Foreign key constraints and uniqueness constraints in the database play a big part in determining the correct ordering of changes. Then, just before any actual changes are transmitted, a transaction is started to encapsulate the series of individual commands unless one is already in scope. Finally, one by one the changes to the objects are translated into SQL commands and sent to the server.

At this point, any errors detected by the database will cause the submission process to abort and an exception will be raised. All changes to the database will be rolled back as if none of the submissions ever took place. The DataContext will still have a full recording of all changes so it is possible to attempt to rectify the problem and resubmit them by calling SubmitChanges() again.

Northwind db = new Northwind("c:\\northwind\\northwnd.mdf");
// make changes here 
try {
   db.SubmitChanges();
}
catch (Exception e) {
   // make some adjustments
   ...
   // try again
   db.SubmitChanges();
}

Visual Basic

Dim db As New Northwind("c:\northwind\northwnd.mdf")
' make changes here 
Try 
   db.SubmitChanges()
Catch e As Exception
   ' make some adjustments
   ...
   ' try again
   db.SubmitChanges()
End Try

When the transaction around the submission completes successfully, the DataContext will accept the changes to the objects by simply forgetting the change tracking information.

Simultaneous Changes

There are a variety of reasons why a call to SubmitChanges() may fail. You may have created an object with an invalid primary key; one that's already in use, or with a value that violates some check constraint of the database. These kinds of checks are difficult to bake into business logic since they often require absolute knowledge of the entire database state. However, the most likely reason for failure is simply that someone else made changes to the objects before you.

Certainly, this would be impossible if you were locking each object in the database and using a fully serialized transaction. However, this style of programming (pessimistic concurrency) is rarely used since it is expensive and true clashes seldom occur. The most popular form of managing simultaneous changes is to employ a form of optimistic concurrency. In this model, no locks against the database rows are taken at all. That means any number of changes to the database could have occurred between the time you first retrieved your objects and the time you submitted your changes.

Therefore, unless you want to go with a policy that the last update wins, wiping over whatever else occurred before you, you probably want to be alerted to the fact that the underlying data was changed by someone else.

Product Families

Resources

About Microsoft

Popular Places

Popular Searches

Popular Downloads

Contents

Introduction

A Quick Tour

Creating Entity Classes

The DataContext

Defining Relationships

Querying Across Relationships

Modifying and Saving Entities

Queries In-Depth

Query Execution

Object Identity

Relationships

Joins

Projections

Compiled Queries

SQL Translation

The Entity Lifecycle

Tracking Changes

Using a read-only DataContext

Submitting Changes

Simultaneous Changes