Posts
201
Comments
1110
Trackbacks
51
December 2007 Entries
Linq - Handle Insert/Update/Delete of child entity in tiered application

Recently I've done a series of posts all related to using Linq in a tiered application:

The various posts (which have been influenced by this MSDN article) have focused on a DataContext that looks like the diagram below.  The Contact class generated has a child collection property of Addresses which is of type EntitySet<Address>.  This distinction is important because the example deals with a complex object that has a collection of child objects rather than a single object with a bunch of primitives.  You must take care to handle the items in the child collection properly.

The SaveContact() method looks like this:

   1:  public static void SaveContact(Contact contact)
   2:  {
   3:      using (PimDataContext dataContext = CreateDataContext())
   4:      {
   5:          if (contact.ContactID == 0)
   6:          {
   7:              dataContext.Contacts.InsertOnSubmit(contact);
   8:          }
   9:          else
  10:          {
  11:              dataContext.Contacts.Attach(contact, true);
  12:              dataContext.Addresses.AttachAll(contact.Addresses, true);
  13:          }
  14:          dataContext.SubmitChanges();
  15:      }
  16:  

This code works fine when passing a Contact object into your data access tier for any of the following conditions:

  • A brand new Contact to be inserted with no child addresses.
  • A brand new Contact to be inserted with one or multiple child addresses.
  • An existing Contact to be updated with no child addresses.
  • An existing Contact to be updated with one or multiple existing child addresses to be updated.

At first, all seems well.  The problem is that there are actually a couple of gaping holes here. Consider this scenario:

  1. A user of the application creates a brand new Contact (with no addresses) and the code inserts it just fine.
  2. The user then goes to update the existing Contact they previously created. But the change they want to make is the ADD a new address to this existing contact.

The above code will throw this exception:  "System.Data.Linq.ChangeConflictException: Row not found or changed."

This is obviously bad.  What is happening is that the AttachAll() on the contact's Addresses is failing because the Attach methods are meant to be used for updates to rows that are already existing in the database.  In this case, although the Contact is existing, this is a brand new Address that we are trying to insert. So what we need to do is to call AttachAll() if there are existing Addresses or the InsertAllOnSubmit() method if they are brand new addresses. One simple way to accomplish this is by adding a property to the Address via a partial class: 

   1:  public partial class Address
   2:  {
   3:      public bool IsNew
   4:      {
   5:          get
   6:          {
   7:              return this.Timestamp == null;
   8:          }
   9:      }
  10:  }

Here we've utilized our Timestamp column (used for Optimistic concurrency) to determine if this is brand or or existing (but this could alternatively have been done with a simple Boolean). This means our SaveContact() method can now look like this:

   1:  public static void SaveContact(Contact contact)
   2:  {
   3:      using (PimDataContext dataContext = CreateDataContext())
   4:      {
   5:          if (contact.ContactID == 0)
   6:          {
   7:              dataContext.Contacts.InsertOnSubmit(contact);
   8:          }
   9:          else
  10:          {
  11:              dataContext.Contacts.Attach(contact, true);
  12:              dataContext.Addresses.AttachAll(contact.Addresses.Where(a => !a.IsNew), true);
  13:              dataContext.Addresses.InsertAllOnSubmit(contact.Addresses.Where(a => a.IsNew));
  14:          }
  15:          dataContext.SubmitChanges();
  16:      }
  17:  }

Now the code works fine regardless of whether you're adding a new address to an existing contact.

So now, we're surely good to go, right?  Unfortunately, we still have one big gaping hole. Consider this next scenario:

  1. A user creates a brand new contact and this contact has an address. User saves and everything is fine.
  2. The user then goes to update the existing Contact they previously created. But the change they want to make is the DELETE the existing address from this existing contact.

Now you've got a couple of different choices for how you want to handle this scenario. IF you have control over the client, you can keep track of whether the address was deleted or not. You can add a new IsDeleted property to your Address partial class:

   1:  public partial class Address
   2:  {
   3:      public bool IsNew
   4:      {
   5:          get
   6:          {
   7:              return this.Timestamp == null && !this.IsDeleted;
   8:          }
   9:      }
  10:   
  11:      public bool IsDeleted { get; set; }
  12:  }

But again, the responsibility is on the client to keep track of correctly setting this property. While it is not difficult, it is not totally trivial either. If you do that, then you can now update your SaveContact() method to this:

   1:  public static void SaveContact(Contact contact)
   2:  {
   3:      using (PimDataContext dataContext = CreateDataContext())
   4:      {
   5:          if (contact.ContactID == 0)
   6:          {
   7:              dataContext.Contacts.InsertOnSubmit(contact);
   8:          }
   9:          else
  10:          {                    
  11:              dataContext.Contacts.Attach(contact, true);
  12:              dataContext.Addresses.AttachAll(contact.Addresses.Where(a => !a.IsNew), true);
  13:              dataContext.Addresses.InsertAllOnSubmit(contact.Addresses.Where(a => a.IsNew));
  14:              dataContext.Addresses.DeleteAllOnSubmit(contact.Addresses.Where(a => a.IsDeleted));
  15:          }
  16:          dataContext.SubmitChanges();
  17:      }
  18:  }

You now finally have a SaveContact() method that takes care of all permutations.  And all in all, it's a pretty straightforward 10 lines of code.

If you don't have very much control over the client you might have a situation where the absence of an Address could mean either it was deleted or it never existed to begin with. In that scenario, you're going to have to blindly do delete/insert on the addresses (i.e., you'd never run an update on an Address) which would most likely have to rely on looping over the address types and running a line of code like this for each iteration:

dataContext.ExecuteCommand("DELETE FROM addresses WHERE ContactID={0} and AddressTypeID={1}", contactID, addressTypeID);

While it works, it's pretty clumsy and bordering on a code smell whereas the first solution was much more graceful. But either way, I continue to be impressed with the flexibility of implementation choices that Linq provides.

Posted On Sunday, December 30, 2007 11:10 PM | Comments (34)
Be mindful of your DataContext's "context"

In a previous post here, I discussed implementation of Attaching Linq entities to a DataContext.  In that post, I showed an implementation of utilizing a Detach() method that I originally based on this post here.  The implementation boils down to the need to reset EntityRef<> references back to their default - otherwise, it will try to attach the parent objects (which is often not the goal of your code as this is often just reference data). Consider the DataContext below:

The fundamental problem here is that State and AddressType should NOT be in this data context at all!  Doing so causes the auto-generated classes to include EntityRef<> references to them in the Address class.  In my actual Address business objects, all I really care about it the StateID.  The database takes care of enforcing the foreign key to the State table.  My application does need to query the State table directly for populating the State drop down list but it should be in its own data context.  So I need to have 2 different DataContexts.  The first should contain only Contact and Address from the diagram above. The second (call it ReferenceDataContext for my State and AddressType reference data) should simply look like this:

Bottom line: don't stuff everything in your database into a single DataContext dumping ground - be mindful of the context.  If you have a DataContext where the only items you're updating contain business entity and not their reference data then do not include them - this is much simplier and allows you to avoid having to write Detach() methods at all!

Posted On Thursday, December 27, 2007 8:48 PM | Comments (8)
Linq - Attach an entity that is not new, perhaps having been loaded from another DataContext.

This exception using the Linq Attach() method is somewhat perplexing at first:

System.NotSupportedException: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported.

This blog post here *sort of* pointed me in the right direction. But I found the *WHY* confusing and I found the example confusing.  The following is my implementation of the suggested solution from the previous post.  First, consider the following diagram:

The Contact is the primary object and it has a collection of Addresses.  The State and AddressType tables are simply master tables for reference data. Next we create a public method to handle database modifications:

   1:  public static void SaveContact(Contact contact)
   2:  {
   3:      using (PimDataContext dataContext = CreateDataContext())
   4:      {
   5:          if (contact.ContactID == 0)
   6:          {
   7:              dataContext.Contacts.InsertOnSubmit(contact);
   8:          }
   9:          else
  10:          {            
  11:              dataContext.Contacts.Attach(contact, true);
  12:          }
  13:          dataContext.SubmitChanges();
  14:      }
  15:  }

This code works fine for inserts of a Contact with or without any addresses in its child list of addresses.  However, it only works for updates if the contact does not have any child addresses.  If it does have addresses, then it throws the dreaded: "System.NotSupportedException: An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext. This is not supported."  When I blindly followed the example from the previously referenced post, I essentially set everything in the Contact object to the default.  For example, this._Addresses = default(EntitySet<Address>);.  But this doesn't do me much good because if I actually made a modification to any addresses, then those changes are now lost as I'm setting the collection of Addresses back to a default empty EntitySet reference.  Additionally, I found myself asking the question, "WHICH entity is it complaining about?" Everything works fine when it's a stand-alone Contact object so it didn't seem feasible for it to be complaining about the Contact object. So I concluded it must be *something* in the Address object - but what?

A close examination of the generated code for the Address object showed that their were actually 3 EntityRef<> members - Contact, AddressType, and State.  So it seemed it was actually trying to attach my AddressType and State entity (which was never my intent).  Using that information, I found I could make everything work by setting just the EntityRef objects back to the default reference:

   1:  public partial class Contact
   2:  {
   3:      public void Detach()
   4:      {
   5:          foreach (Address address in this.Addresses)
   6:          {
   7:              address.Detach();
   8:          }
   9:      }
  10:  }
  11:   
  12:  public partial class Address
  13:  {
  14:      public void Detach()
  15:      {
  16:          this._AddressType = default(EntityRef<AddressType>);
  17:          this._State = default(EntityRef<State>);
  18:      }
  19:  }

This allowed my calling code to now just look like this and everything now worked perfectly:

   1:  public static void SaveContact(Contact contact)
   2:  {
   3:      using (PimDataContext dataContext = CreateDataContext())
   4:      {
   5:          if (contact.ContactID == 0)
   6:          {
   7:              dataContext.Contacts.InsertOnSubmit(contact);
   8:          }
   9:          else
  10:          {
  11:              contact.Detach();
  12:              
  13:              dataContext.Contacts.Attach(contact, true);
  14:              dataContext.Addresses.AttachAll(contact.Addresses, true);
  15:          }
  16:          dataContext.SubmitChanges();
  17:      }
  18:  }

Notice on line 11 I am calling the new Detach() method.  Also notice that I'm line 14, I am now explicitly calling the AttachAll() method for the collection of Addresses.

Looking at the final solution, it all now seems simple. But the troubleshooting that went in to getting there was not simple. While trivial, having the write Detach() methods for all my entity objects is not particularly appealing. Using Linq with stored procedures and explicit function calls would have eliminated much of the mystery as to what was going on behind the scenes trying to figure out why this exception was being thrown (though obviously more work writing stored procedures). These are the types of things that, so far, have me concluding two things: 1) Linq is extremely flexible and powerful, and 2) I still prefer using Linq with my own Stored Procedures.

Update: check out this post here for an example implementation that is even simplier and does not require Detach() methods at all.

Posted On Tuesday, December 25, 2007 2:20 PM | Comments (26)
Linq Table Attach() based on timestamp or row version

In a previous post here, I showed an example of using the Attach() method in conjunction with a Timestamp column in your database table.  In listing options that are supported, Microsoft's documentation states: "Optimistic concurrency based on timestamps or RowVersion numbers."  So what are some alternatives to using a Timestamp column in your SQL Server database?  It turns out, this is pretty simple.  Two other alternatives are using a DateTime or a unique identifier column.

DateTime Last Updated

The key here is to create a LastUpdated DateTime column with a default value of getdate() and an AFTER UPDATE trigger which inserts the current getdate() any time there is a modification.

   1:  CREATE TABLE Contacts(
   2:   ContactID int IDENTITY(1,1) NOT NULL,
   3:   FirstName varchar(50),
   4:   LastName varchar(50),
   5:   LastUpdated datetime NOT NULL default (getdate()),
   6:   CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED (ContactID ASC)
   7:  ) 
   8:  GO
   9:   
  10:  CREATE TRIGGER trig_ContactsVersion
  11:  ON Contacts
  12:  AFTER UPDATE
  13:  AS
  14:  BEGIN
  15:      UPDATE    Contacts
  16:      SET    LastUpdated = getdate()
  17:      WHERE    ContactID IN (SELECT ContactID FROM inserted);
  18:  END;

And the corresponding properties must be configured:

 

 

Unique Identifier

The key here is to create a Version unique identifier column with a default value of newid() and an AFTER UPDATE trigger which inserts a new guid any time there is a modification.

   1:  CREATE TABLE Contacts(
   2:   ContactID int IDENTITY(1,1) NOT NULL,
   3:   FirstName varchar(50),
   4:   LastName varchar(50),
   5:   Version uniqueidentifier NOT NULL default (newid()),
   6:   CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED (ContactID ASC)
   7:  ) 
   8:   
   9:  CREATE TRIGGER trig_ContactsVersion
  10:  ON Contacts
  11:  AFTER UPDATE
  12:  AS
  13:  BEGIN
  14:      UPDATE    Contacts
  15:      SET        Version = newid()
  16:      WHERE    ContactID IN (SELECT ContactID FROM inserted);
  17:  END;

And the corresponding properties must be configured:

 

So you actually have a lot of flexibility here.  If you don't like the SQL Server Timestamp data type, no problem.  Just use your Optimistic concurrency implementation of choice.  Of course, these implementations can all be used with a stored procedure approach as well.

Posted On Tuesday, December 18, 2007 10:51 PM | Comments (1)
Linq Table Attach()

The ability to use live Linq queries right in your UI makes for great demo's, but it doesn't bear a striking resemblance to a real-world, professional application which uses tiers.  In traditional n-tier applications, you want to have a strong "separation of concerns" and encapsulate your business layer, your data layer, and your UI layer distinctly.  One of the nice things about Linq is that the flexibility is huge.  If you want to do live queries in your UI, fine.  If you want to encapsulate Linq queries in your data layer, that's fine too.

Having said that, the biggest problem I faced when using the RTM for the first time was trying to update an object that had been created by a "different" data contact.  I continually ran into one of these dreaded exceptions:  "System.InvalidOperationException: An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy."  The other one was: "An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext."

Microsoft has documentation here that is meant to describe how to properly implement this scenario.  The key to the update are these bullet points: 

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.

But they never really gave any concrete example of implementation.  So here is a quick example of how to avoid this.

OK, here is my (ridiculously simple) table:

CREATE TABLE Contacts(
ContactID int IDENTITY(1,1) NOT NULL,
FirstName varchar(50),
LastName varchar(50),
[Timestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED (ContactID ASC)
)

Next, drag out the table from the Server Explorer onto a dbml surface:

If right-click on the Timestamp column in the dbml above and select "Properties", you'll see this Properties window:

Notice the Auto Generated Value and Time Stamp properties are both set to true. This is key.

Now let's suppose I create a ContactManager class that is going to be my public API that will encapsulate all of my CRUD functionality.  (In fact, I can make my Linq data context classes all Internal so my UI truly does not know about them)

   1:  public static class ContactManager
   2:  {
   3:      public static Contact GetContact(int contactID)
   4:      {
   5:          using (ContactsDataContext dataContext = new ContactsDataContext())
   6:          {
   7:              return dataContext.Contacts.SingleOrDefault(c => c.ContactID == contactID);
   8:          }
   9:      }
  10:   
  11:      public static void SaveContact(Contact contact)
  12:      {
  13:          using (ContactsDataContext dataContext = new ContactsDataContext())
  14:          {
  15:              if (contact.ContactID == 0)
  16:              {
  17:                  dataContext.Contacts.InsertOnSubmit(contact);
  18:              }
  19:              else
  20:              {
  21:                  dataContext.Contacts.Attach(contact, true);
  22:              }
  23:              dataContext.SubmitChanges();
  24:          }
  25:      }
  26:  }

Notice that I'm disposing my data context each time so I truly can support a stateless n-tier service.  Also, notice I am calling the Attach() method (line 21 above) and giving the second parameter as "true" - meaning, attach as modified.  I have to call Attach() here because the original data context that created my object isn't around anymore.  I have to attach it as modified so that the framework will understand that my intent is to perform an update here.  Additionally, a look at the data context's Log property shows the SQL that was actually emitted during run-time execution:

   1:  UPDATE [dbo].[TempContacts]
   2:  SET [FirstName] = @p2, [LastName] = @p3
   3:  WHERE ([ContactID] = @p0) AND ([Timestamp] = @p1)

So, the timestamp is taken into account as well so that full Optimistic concurrency is supported.

Posted On Monday, December 17, 2007 9:37 PM | Comments (36)

View Steve Michelotti's profile on LinkedIn

profile for Steve Michelotti at Stack Overflow, Q&A for professional and enthusiast programmers




Google My Blog

Tag Cloud