LINQ To SQL Attach

The Attach method allows updating with a disconnected object that has been updated. The easiest version to use is:

Public Sub UpdateCustomer(updatedCustomer As Customer) 
  Dim dc As New MyDataContext() 
  dc.Customers.Attach(updatedCustomer, True) 
  dc.SubmitChanges
End Sub

The only problem is that this generates a SQL statement that updates all columns rather than just the on that has changed. In other words, we used a little extra bandwidth.

The reason this happens is because when we disconnected the object, we lost all the built in change tracking provided by LINQ (for now).

Option 2 is to get a new copy of the object and update it.

Public Sub UpdateCustomer(updatedCustomer as customer)

  Dim dc as New MyDataContext
  Dim originalCustomer as Customer = from c in
    dc.Customers Where c.PrimaryKey =
    updatedCustomer.PrimaryKey

  originalCustomer.CustomerName =
    updatedCustomer.CustomerName
  originalCustomer.CustomerAddress =
    updatedCustomer.CustomerAddress
  originalCustomer.CustomerCity=
    updatedCustomer.CustomerCity
  originalCustomer.CustomerState=
    updatedCustomer.CustomerState
  originalCustomer.CustomerPostalCode=
    updatedCustomer.CustomerPostalCode

  dc.SubmitChanges
End Sub

If you're wondering how this changes anything since it looks like we're changing all of the fields, look at the code LINQ generates for the properties. You'll notice that it checks to see if the value has changed and if so fires all of the update events. If it hasn't, it basically ignores the change.

The problem here is that we have to code all of the updates ourselves. We also have a round trip to the DB to get the object and then another one to update the object.

A slight modification will reduce the code we write but doesn't reduce the number of DB trips.

Public Sub UpdateCustomer(updatedCustomer as customer)
  Dim originalDC as New MyDataContext
  Dim originalCustomer as Customer = from c in
    dc.Customers Where c.PrimaryKey =
    updatedCustomer.PrimaryKey

  'We have to disconnect the original object
  ' from it's datacontext
  originalDC = Nothing

  Dim newDC as New MyDataContext
  newDC.Customers.Attach(updatedCustomer,
    originalCustomer)
  newDC.SubmitChanges

End Sub

This version of Attach uses the original version to compare against the new version for changes.

If we want to send only the changes we have made and avoid the extra round trip, we need to store a copy of the object before changes are made.

This example is very basic so that you can get the idea.

Public Class Customers

Private Const CustomerCacheKey As
  String = "Customer{0}"

Public Function GetCustomer(customerKey as integer)
  As Customer

  Dim CacheKey As String = String.Format
    (CustomerCacheKey, customerKey.ToString)

  Dim dc As New MyDataContext

  GetCustomer = (From c In dc.Customers Where 
    c.CustomerKey = customerKey Select
    c).SingleOrDefault
  HttpContext.Current.Cache.Add(CacheKey, GetCustomer,
    Nothing, DateTime.Now.AddSeconds(60),
    Cache.NoSlidingExpiration, CacheItemPriority.High,
    Nothing)
End Function

Public Sub UpdateCustomer(updatedCustomer as Customer)

  Dim CacheKey As String = String.Format
    (CustomerCacheKey,
    updatedCustomer.CustomerKey.ToString)

  Dim dc As New MyDataContext
  Dim originalCustomer As Customer =
    HttpContext.Current.Cache(CacheKey)

  dc.Customers.Attach(updatedCustomer, 
    originalCustomer)
  dc.SubmitChanges
End Sub

End Class

The real problem -- you've got to code all over everywhere to handle keeping a copy. If you're in a GridView or other list control, you don't know which one they picked and do you really want to cache a copy of every object. The above example is very weak because the cache is shared by everyone and can become stale very quickly. Someone may update the copy stored in the cache by calling your business layer method (of course you could use the cached copy instead of getting an updated version). I think you get the idea -- we're increasing the complexity in a different layer of our application. Can you say maintenance nightmare?!

So, when is it appropriate to use the different mechanisms? Here's some guidelines (remember, only you know your situation and can determine what's best).

1. Use the basic Attach(MyObject, True) in most cases. It presents the simplest code and is the most easily maintained. The only real cost is the extra data that goes over the wire.

2. Use option 2 where concurrency concerns out weigh the cost of the extra round trip. This allows a better level of control and flexibility and is just plain easier to code and maintain.

3. The last option is best if the payload of the generated SQL update statement above is too large. I would ask if the update payload is so large, doesn't that mean that your cache copy is really large also and now you have two copies?!

Just remember whatever you decide, do it because you have reviewed the options and made a decision as to what fits your environment best.

Oh, what about the basic Attach(MyObject) method? Well, all you're doing here is attaching the object and telling LINQ that it is in the original state. So, when you SubmitChanges nothing will go back to the DB because as far as LINQ is concerned nothing has changed.