Skip to main content

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.

Comments

Văn Sát said…
This comment has been removed by a blog administrator.

Popular posts from this blog

Migrating Legacy Apps to the New SimpleMembership Provider

Asp.Net MVC4 uses the new SimpleMembership provider, changes the table structure and adds a new hashing algorithm. The reasons for the changes can be found in this article by Jon Galloway. This article shows how to migrate your existing apps to the new provider.I’m assuming that you stored your passwords in the unrecoverable SHA-1 format. If you didn’t, then you’ll have to change a couple of things. All of my apps are done this way so… I’m also assuming that you have created the basic skeleton of the new app and ran it once so the correct tables will be created.First, we’ll look at the new tables. Previously, we had all of those aspnet_xxxxxx tables. Here’s the new ones.UserProfileContains all of the elements relevant to the user. This is a combination of the aspnet_Users table and the aspnet_Profiles table.webpages_MembershipStores the password info when not using OAuth, Live, Facebook, etc. This table is somewhat of a match to the aspnet_Membership table.webpages_OAuthMembershipStor…

JavaScript function to automatically add slashes to date

In converting an old Windows app to a browser app, the user wanted to be able to enter dates without the slashes. Here's a simple jscript: 1:// Function to convert short date string (MMddyy) 2:// or (MMddyyyy) to a date string (mm/dd/yyyy). 3:// txtBox is the actual textbox control 4:// with the value to be processed. 5:function FixShortDate(txtBox) { 6:if (txtBox == null) { 7:return'' } 8: 9:var re = new RegExp(/(\d{6})(\d{2})?/); 10: 11:if (re.test(txtBox.value)) 12: { 13:if (txtBox.value.length == 8) { 14: txtBox.value = txtBox.value.substring(0, 2) + '/' + txtBox.value.substring(2, 4) + '/' + txtBox.value.substring(4, 8) 15: } 16: 17:if (txtBox.value.length == 6) { 18:if (txtBox.value.substring(4, 6) < 20)

Get Asp.Net Profile properties from Sql

Ever wanted to include the profile information from an Asp.Net profile in a query? It’s not that hard once you understand the structure. I’ve written a little function that does all the work. Note: I’m using Sql Server as my repository.

First we need to understand how the profile data is stored. Looking at the aspnet_Profile table, we can see that it stores the information in two columns: PropertyNames and PropertyValuesString.

Looking at PropertyNames we can see that it has a basic structure of Property Name, Data Type, Starting Position and Length. For example, in the string “FirstName:S:0:4:Phone:S:4:10:LastName:S:14:5:” we can see that FirstName is of type string, starts at position 0 and has a length of 4. Notice the zero base for the starting position, we need to correct for that in our function. This means in the PropertyValuesString “John2175551212Smith”, we would start with the first position and proceed 4 characters to get the name.

You might be thinking …