Data Retrieval and CUD Operations in N-Tier Applications (LINQ to SQL)

Microsoft Developer Network

Data Retrieval and CUD Operations in N-Tier App...

LINQ to SQL

When you serialize entity objects such as Customers or Orders to a client over a network, those entities are detached from their data context. The data context no longer tracks their changes or their associations with other objects. This is not an issue as long as the clients are only reading the data. It is also relatively simple to enable clients to add new rows to a database. However, if your application requires that clients be able to update or delete data, then you must attach the entities to a new data context before you call DataContext..::.SubmitChanges. In addition, if you are using an optimistic concurrency check with original values, then you will also need a way to provide the database both the original entity and the entity as modified. The Attach methods are provided to enable you to put entities into a new data context after they have been detached.

Even if you are serializing proxy objects in place of the LINQ to SQL entities, you still have to construct an entity on the data access layer (DAL), and attach it to a new System.Data.Linq..::.DataContext, in order to submit the data to the database.

LINQ to SQL is completely indifferent about how entities are serialized. For more information about how to use the Object Relational Designer and SQLMetal tools to generate classes that are serializable by using Windows Communication Foundation (WCF), see How to: Make Entities Serializable (LINQ to SQL).

Note:
Only call the Attach methods on new or deserialized entities. The only way for an entity to be detached from its original data context is for it to be serialized. If you try to attach an undetached entity to a new data context, and that entity still has deferred loaders from its previous data context, LINQ to SQL will thrown an exception. An entity with deferred loaders from two different data contexts could cause unwanted results when you perform insert, update, and delete operations on that entity. For more information about deferred loaders, see Deferred versus Immediate Loading (LINQ to SQL).

Note:

Only call the Attach methods on new or deserialized entities. The only way for an entity to be detached from its original data context is for it to be serialized. If you try to attach an undetached entity to a new data context, and that entity still has deferred loaders from its previous data context, LINQ to SQL will thrown an exception. An entity with deferred loaders from two different data contexts could cause unwanted results when you perform insert, update, and delete operations on that entity. For more information about deferred loaders, see Deferred versus Immediate Loading (LINQ to SQL).

Retrieving Data

Client Method Call

The following examples show a sample method call to the DAL from a Windows Forms client. In this example, the DAL is implemented as a Windows Service Library:

Visual Basic

Private Function GetProdsByCat_Click(ByVal sender As Object, ByVal e _
    As EventArgs)

    ' Create the WCF client proxy.
    Dim proxy As New NorthwindServiceReference.Service1Client

    ' Call the method on the service.
    Dim products As NorthwindServiceReference.Product() = _
        proxy.GetProductsByCategory(1)

    ' If the database uses original values for concurrency checks,
    ' the client needs to store them and pass them back to the
    ' middle tier along with the new values when updating data.

    For Each v As NorthwindClient1.NorthwindServiceReference.Product _
        In products
        ' Persist to a List(Of Product) declared at class scope.
        ' Additional change-tracking logic is the responsibility
        ' of the presentation tier and/or middle tier.
        originalProducts.Add(v)
    Next

    ' (Not shown) Bind the products list to a control
    ' and/or perform whatever processing is necessary.
End Function

private void GetProdsByCat_Click(object sender, EventArgs e)
{
    // Create the WCF client proxy.
    NorthwindServiceReference.Service1Client proxy = 
    new NorthwindClient.NorthwindServiceReference.Service1Client();

    // Call the method on the service.
    NorthwindServiceReference.Product[] products = 
    proxy.GetProductsByCategory(1);

    // If the database uses original values for concurrency checks, 
    // the client needs to store them and pass them back to the 
    // middle tier along with the new values when updating data.
    foreach (var v in products)
    {
        // Persist to a list<Product> declared at class scope.
        // Additional change-tracking logic is the responsibility
        // of the presentation tier and/or middle tier.
        originalProducts.Add(v);
    }

    // (Not shown) Bind the products list to a control
    // and/or perform whatever processing is necessary.
    }

Middle Tier Implementation

The following example shows an implementation of the interface method on the middle tier. The following are the two main points to note:

The DataContext is declared at method scope.
The method returns an IEnumerable collection of the actual results. The serializer will execute the query to send the results back to the client/presentation tier. To access the query results locally on the middle tier, you can force execution by calling ToList or ToArray on the query variable. You can then return that list or array as an IEnumerable.

Visual Basic

Public Function GetProductsByCategory(ByVal categoryID As Integer) _
    As IEnumerable(Of Product)

    Dim db As New NorthwindClasses1DataContext(connectionString)
    Dim productQuery = _
    From prod In db.Products _
    Where prod.CategoryID = categoryID _
    Select prod

    Return productQuery.AsEnumerable()

End Function

public IEnumerable<Product> GetProductsByCategory(int categoryID)
{
    NorthwindClasses1DataContext db = 
    new NorthwindClasses1DataContext(connectionString);

    IEnumerable<Product> productQuery =
    from prod in db.Products
    where prod.CategoryID == categoryID
    select prod;

    return productQuery.AsEnumerable(); 
}

An instance of a data context should have a lifetime of one "unit of work." In a loosely-coupled environment, a unit of work is typically small, perhaps one optimistic transaction, including a single call to SubmitChanges. Therefore, the data context is created and disposed at method scope. If the unit of work includes calls to business rules logic, then generally you will want to keep the DataContext instance for that whole operation. In any case, DataContext instances are not intended to be kept alive for long periods of time across arbitrary numbers of transactions.

This method will return Product objects but not the collection of Order_Detail objects that are associated with each Product. Use the DataLoadOptions object to change this default behavior. For more information, see How to: Control How Much Related Data Is Retrieved (LINQ to SQL).

Inserting Data

To insert a new object, the presentation tier just calls the relevant method on the middle tier interface, and passes in the new object to insert. In some cases, it may be more efficient for the client to pass in only some values and have the middle tier construct the full object.

Middle Tier Implementation

On the middle tier, a new DataContext is created, the object is attached to the DataContext by using the InsertOnSubmit method, and the object is inserted when SubmitChanges is called. Exceptions, callbacks, and error conditions can be handled just as in any other Web service scenario.

Visual Basic

' No call to Attach is necessary for inserts.
Public Sub InsertOrder(ByVal o As Order)

    Dim db As New NorthwindClasses1DataContext(connectionString)
    db.Orders.InsertOnSubmit(o)

    ' Exception handling not shown.
    db.SubmitChanges()

End Sub

// No call to Attach is necessary for inserts.
    public void InsertOrder(Order o)
    {
        NorthwindClasses1DataContext db = new NorthwindClasses1DataContext(connectionString);
        db.Orders.InsertOnSubmit(o);

        // Exception handling not shown.
        db.SubmitChanges();
    }

Deleting Data

To delete an existing object from the database, the presentation tier calls the relevant method on the middle tier interface, and passes in its copy that includes original values of the object to be deleted.

Delete operations involve optimistic concurrency checks, and the object to be deleted must first be attached to the new data context. In this example, the Boolean parameter is set to false to indicate that the object does not have a timestamp (RowVersion). If your database table does generate timestamps for each record, then concurrency checks are much simpler, especially for the client. Just pass in either the original or modified object and set the Boolean parameter to true. In any case, on the middle tier it is typically necessary to catch the ChangeConflictException. For more information about how to handle optimistic concurrency conflicts, see Optimistic Concurrency Overview (LINQ to SQL).

When deleting entities that have foreign key constraints on associated tables, you must first delete all the objects in its EntitySet<(Of <(TEntity>)>) collections.

Visual Basic

' Attach is necessary for deletes.
Public Sub DeleteOrder(ByVal order As Order)
    Dim db As New NorthwindClasses1DataContext(connectionString)

    db.Orders.Attach(order, False)
    ' This will throw an exception if the order has order details.
    db.Orders.DeleteOnSubmit(order)

    Try
        ' ConflictMode is an optional parameter.
        db.SubmitChanges(ConflictMode.ContinueOnConflict)

    Catch ex As ChangeConflictException
        ' Get conflict information, and take actions
        ' that are appropriate for your application.
        ' See MSDN Article "How to: Manage Change
        ' Conflicts (LINQ to SQL).

    End Try
End Sub

// Attach is necessary for deletes.
public void DeleteOrder(Order order)
{
    NorthwindClasses1DataContext db = new NorthwindClasses1DataContext(connectionString);

    db.Orders.Attach(order, false);
    // This will throw an exception if the order has order details.
    db.Orders.DeleteOnSubmit(order);
    try
    {
        // ConflictMode is an optional parameter.
        db.SubmitChanges(ConflictMode.ContinueOnConflict);
    }
    catch (ChangeConflictException e)
    {
       // Get conflict information, and take actions
       // that are appropriate for your application.
       // See MSDN Article How to: Manage Change Conflicts (LINQ to SQL).
    }
}

Updating Data

LINQ to SQL supports updates in these scenarios involving optimistic concurrency:

Optimistic concurrency based on timestamps or RowVersion numbers.
Optimistic concurrency based on original values of a subset of entity properties.
Optimistic concurrency based on the complete original and modified entities.

You can also perform updates or deletes on an entity together with its relations, for example a Customer and a collection of its associated Order objects. When you make modifications on the client to a graph of entity objects and their child (EntitySet) collections, and the optimistic concurrency checks require original values, the client must provide those original values for each entity and EntitySet<(Of <(TEntity>)>) object. If you want to enable clients to make a set of related updates, deletes, and insertions in a single method call, you must provide the client a way to indicate what type of operation to perform on each entity. On the middle tier, you then must call the appropriate Attach method and then InsertOnSubmit, DeleteAllOnSubmit, or InsertOnSubmit()()() (without Attach, for insertions) for each entity before you call SubmitChanges. Do not retrieve data from the database as a way to obtain original values before you try updates.

For more information about optimistic concurrency, see Optimistic Concurrency Overview (LINQ to SQL). For detailed information about resolving optimistic concurrency change conflicts, see How to: Manage Change Conflicts (LINQ to SQL).

The following examples demonstrate each scenario:

Optimistic concurrency with timestamps

Visual Basic

' Assume that "customer" has been sent by client.
' Attach with "true" to say this is a modified entity
' and it can be checked for optimistic concurrency
' because it has a column that is marked with the
' "RowVersion" attribute.

db.Customers.Attach(customer, True)

Try
    ' Optional: Specify a ConflictMode value
    ' in call to SubmitChanges.
    db.SubmitChanges()
Catch ex As ChangeConflictException
    ' Handle conflict based on options provided.
    ' See MSDN article "How to: Manage Change
    ' Conflicts (LINQ to SQL)".
End Try

// Assume that "customer" has been sent by client.
// Attach with "true" to say this is a modified entity
// and it can be checked for optimistic concurrency because
//  it has a column that is marked with "RowVersion" attribute
db.Customers.Attach(customer, true)
try
{
    // Optional: Specify a ConflictMode value
    // in call to SubmitChanges.
    db.SubmitChanges();
}
catch(ChangeConflictException e)
{
    // Handle conflict based on options provided
    // See MSDN article How to: Manage Change Conflicts (LINQ to SQL).
}

With Subset of Original Values

In this approach, the client returns the complete serialized object, together with the values to be modified.

Visual Basic

Public Sub UpdateProductInventory(ByVal p As Product, ByVal _
    unitsInStock As Short?, ByVal unitsOnOrder As Short?)

    Using db As New NorthwindClasses1DataContext(connectionString)
        ' p is the original unmodified product
        ' that was obtained from the database.
        ' The client kept a copy and returns it now.
        db.Products.Attach(p, False)

        ' Now that the original values are in the data context,
        ' apply the changes.
        p.UnitsInStock = unitsInStock
        p.UnitsOnOrder = unitsOnOrder

        Try
            ' Optional: Specify a ConflictMode value
            ' in call to SubmitChanges.
            db.SubmitChanges()

        Catch ex As Exception
            ' Handle conflict based on options provided.
            ' See MSDN article "How to: Manage Change Conflicts
            ' (LINQ to SQL)".
        End Try
    End Using
End Sub

public void UpdateProductInventory(Product p, short? unitsInStock, short? unitsOnOrder)
{
    using (NorthwindClasses1DataContext db = new NorthwindClasses1DataContext(connectionString))
    {
        // p is the original unmodified product
        // that was obtained from the database.
        // The client kept a copy and returns it now.
        db.Products.Attach(p, false);

        // Now that the original values are in the data context, apply the changes.
        p.UnitsInStock = unitsInStock;
        p.UnitsOnOrder = unitsOnOrder;
        try
        {
             // Optional: Specify a ConflictMode value
             // in call to SubmitChanges.
             db.SubmitChanges();
        }
        catch (ChangeConflictException e)
        {
            // Handle conflict based on provided options.
            // See MSDN article How to: Manage Change Conflicts
            // (LINQ to SQL).
        }
    }
}

With Complete Entities

Visual Basic

Public Sub UpdateProductInfo(ByVal newProd As Product, ByVal _
    originalProd As Product)

    Using db As New NorthwindClasses1DataContext(connectionString)
        db.Products.Attach(newProd, originalProd)

        Try
            ' Optional: Specify a ConflictMode value
            ' in call to SubmitChanges.
            db.SubmitChanges()

        Catch ex As Exception
            ' Handle potential change conflicgt in whatever way
            ' is appropriate for your application.
            ' For more information, see the MSDN article
            ' "How to: Manage Change Conflicts (LINQ to
            ' SQL)".
        End Try

    End Using
End Sub

public void UpdateProductInfo(Product newProd, Product originalProd)
{
     using (NorthwindClasses1DataContext db = new
        NorthwindClasses1DataContext(connectionString))
     {
         db.Products.Attach(newProd, originalProd);
         try
         {
               // Optional: Specify a ConflictMode value
               // in call to SubmitChanges.
               db.SubmitChanges();
         }
        catch (ChangeConflictException e)
        {
            // Handle potential change conflict in whatever way
            // is appropriate for your application.
            // For more information, see the MSDN article
            // How to: Manage Change Conflicts (LINQ to SQL)/
        } 
    }
}

To update a collection, call AttachAll instead of Attach.

Expected Entity Members

As stated previously, only certain members of the entity object are required to be set before you call the Attach methods. Entity members that are required to be set must fulfill the following criteria:

Be part of the entity’s identity.
Be expected to be modified.
Be a timestamp or have its UpdateCheck attribute set to something besides Never.

If a table uses a timestamp or version number for an optimistic concurrency check, you must set those members before you call Attach. A member is dedicated for optimistic concurrency checking when the IsVersion property is set to true on that Column attribute. Any requested updates will be submitted only if the version number or timestamp values are the same on the database.

A member is also used in the optimistic concurrency check as long as the member does not have UpdateCheck set to Never. The default value is Always if no other value is specified.

If any one of these required members is missing, a ChangeConflictException is thrown during SubmitChanges ("Row not found or changed").

State

After an entity object is attached to the DataContext instance, the object is considered to be in the PossiblyModified state. There are three ways to force an attached object to be considered Modified.

Attach it as unmodified, and then directly modify the fields.
Attach it with the Attach overload that takes current and original object instances. This supplies the change tracker with old and new values so that it will automatically know which fields have changed.
Attach it with the Attach overload that takes a second Boolean parameter (set to true). This will tell the change tracker to consider the object modified without having to supply any original values. In this approach, the object must have a version/timestamp field.

For more information, see Object States and Change-Tracking (LINQ to SQL).

If an entity object already occurs in the ID Cache with the same identity as the object being attached, a DuplicateKeyException is thrown.

When you attach with an IEnumerable set of objects, a DuplicateKeyException is thrown when an already existing key is present. Remaining objects are not attached.

Concepts

N-Tier and Remote Applications with LINQ to SQL

Other Resources

Background Information (LINQ to SQL)