Posts
208
Comments
1144
Trackbacks
51
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 Print
Comments
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Sidar Ok
2/4/2008 3:18 PM
Hi Steven, thanks for the great post - though I have a few questions :


When I am doing this, I am getting this error : "An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext" .

As anyone first considers, I am using a datacontext for all objects. The ideal and the method that everybody suggests is to seperate them into different datacontext, per functionality. But the problem here is every context comes with their set of entity classes, which means that I am getting a bunch of compilation errors if I want to use a pre-used class.

If I want to use a different name for the objects, then I will need to do the mapping.

So, do you have any suggestion on this ?
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Steve
2/4/2008 4:04 PM
Sidar - It sounds like you haven't correctly set your timestamp column. See my other posts on this blog regarding this error - in particular this one: http://geekswithblogs.net/michelotti/archive/2007/12/25/117984.aspx
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Sidar Ok
2/5/2008 4:24 AM
Hi Steve- thanks for the quick response ! Actually I wrote the question after reading those couple of articles, thanks for them.

But since I am working in a multitiered solution, my entity is already detached, because I am creating a new context in each call !

My timestamps are in the table, timestamp types and automatically generated by linq, and i am not getting the "you need a timestamp or disable the update check policy" exception. What else do I need to do for that matter?

Thanks a lot,

Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Steve
2/6/2008 10:53 PM
Sidar - the other problem that often arises with the exception that you're seeing is that you have some relationship in your data context that you're not really using. Typically you can delete those associations without deleting the classes. At this point, I think I'd have to see your DataContext or code to see what's going on.
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Sidar Ok
2/7/2008 6:55 AM
Hi Steve, I sent you an email about the solution of the problem. Thanks a lot.
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Ankit Verma
10/3/2008 2:20 AM
I got error : Cannot attach an entity that already exists.
how can i resolve it?
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Riccardo
10/15/2008 1:03 PM
hi steve

first let me thank you for your post here. i have the problem with attaching objects into the datacontext. i checked your solution, but i habe to say, if i do this i will still get an "An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext"-exception.

the problem is that my entity contact also know about all his addresses. and as long these aren't attached i am not able to attache the contact. the same problem when i first attach the address, it finds the contact which is still not attached.
it won't work as long i have the circular relashionships between contact and addresses. and i can't get rid of it, cause my datacontext generates these. have you any solution ?

thank you very much
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Pankaj
10/29/2008 8:02 AM
You can work around "An attempt has been made to Attach or Add an entity that is not new, perhaps having been loaded from another DataContext" exception by turning off deferred loading when you retrieve the entity. Like this

public client GetById(int clientId)
{
using (MyDataContext dc = new MyDataContext())
{
dc.DeferredLoadingEnabled = false;

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<client>(c => c.client_addresses);
dc.LoadOptions = dlo;

return dc.Clients.FirstOrDefault(c => c.client_id == clientId);
}
}


When you query for an object, only the object is retrieved. Related objects are not fetched at the same time (loading is deferred). They are retrieved as soon as you attempt to access them. Deferred loading keeps the DataContext alive until related objects are fetched.

If you wrap the DataContext in using statement with deferred loading turned on you get "ObjectDisposedException – Cannot access a disposed object. DataContext accessed after Dispose", when you try accessing any related object outside the scope of the DataContext.

You cannot attach an entity to another DataContext, until the underlying DataContext disposed.

The above code instructs the DataContext to fetch related object (client_address) when client is queried. The using statement forces DataContext to dispose. You can then pass the entity between various layers in your application and attach it another DataContext.

Just make sure you use the overloaded method of Attach() that takes boolean parameter "asModified", when it comes to updating the entity.

dc.Clients.Attach(entity, true);
dc.ClientAddresses.AttachAll(entity.client_addresses, true);

I hope this helps.

Pankaj
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Sunil
10/30/2008 1:55 PM
How can I show ConflictChanges to user if two users are trying to update same record??

Any Idea??
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Sunil
10/30/2008 2:01 PM
Here is my source code for updating Object :

public SubscriberOperationResult UpdateSubscriber(Subscriber subscriber)
{

SubscriberOperationResult subscriberOperationResult = new SubscriberOperationResult();
subscriberOperationResult.Status = false;

// this is just to avoid Caching issue with LINQ..
//because cann't set "ObjectTrackingEnabled" to "False"
if (this.DataContext != null)
{
this.DataContext = null;
}

LINQLogger linqLogger = new LINQLogger(log);

this.DataContext.Log = linqLogger;

Subscriber subscriberToBeUpdated = this.DataContext.Subscribers.SingleOrDefault(s => s.Oid == subscriber.Oid);

if (!subscriberToBeUpdated.RowVersion.Equals(subscriber.RowVersion))
{

subscriberOperationResult.IsChangeConflictsOccurred = true;
subscriberOperationResult.Status = false;
return subscriberOperationResult;
}

subscriberToBeUpdated.Name = subscriber.Name;

this.UpdateAndDeleteCommunicationChannelDetails(subscriber, subscriberToBeUpdated);

this.AddCommunicationChannels(subscriber, subscriberToBeUpdated);

try
{
this.DataContext.SubmitChanges(ConflictMode.FailOnFirstConflict);
}
catch (ChangeConflictException ccEcxeption )
{
throw ccEcxeption;

}

subscriberOperationResult.Status = true;


if (this.DataContext != null)
{
this.DataContext = null;
}

return subscriberOperationResult;
}

How can I show ConflictChanges to user if two users are trying to update same record which has child records and parent doesn't have any changes and only child has.??
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Fawad Rashid
5/18/2009 6:39 AM
Hi I have a Order object which has a OrderStatus child entity.

When i try to update the Order entity without changing the OrderStatus the following function works fine but when i change the OrderStatus i.e. item.OrderStatusId = 1; i get an error telling me

Value of member 'Id' of an object of type 'OrderStatus' changed.
A member defining the identity of the object cannot be changed.
Consider adding a new object with new identity and deleting the existing one


public void UpdateOrder(Order item)
{

context.SubmitChanges();

}


Any idea how i can fix this issue ?
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Steve
5/18/2009 8:50 AM
@Fawad - I might have to see the complete code to understand how all your LINQ attributes are set. This other post might help as well (http://geekswithblogs.net/michelotti/archive/2007/12/27/118022.aspx) since it sounds like you might not need the OrderStatus object directly in your data context (since you may only need the OrderStatusId on your Order object). Can you remove your OrderStatus object from your data context as the second link suggests?
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Lew
8/5/2009 10:57 AM
Thanks for a very nice article.

About deletes, couldn't we find the set of addresses that exist currently in the db but aren't part of the contact.Addresses (probably with the Except set operator); and that set would be the Addresses to delete?

Thanks again for your contributions.
Lew
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Steve
8/5/2009 1:08 PM
@Lew - Thanks for the comment. In my scenario, there's a ContactID foreign key in the Address table pointing back to the Contact table so it would be *impossible* for addresses to exist the in DB that aren't attached to a Contact.

But in a DB design that didn't have this relationship, you could certainly delete addresses more directly via the PK of the Address table.
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Anoosh
8/6/2009 6:50 AM
Hi,

In my case, several threads are modifying the same DB row. I've tried a different approach and it fails! Any idea why?

Pass the entity ID as an argument
Create a new data context and read the object using the passin in ID
Call Refresh() method on it to get around object caching in the context
Modify the object
Save and handle optimistic concurrency exceptions

Looks like Linq steps all over itself when I call refresh.
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Steve
8/7/2009 12:56 AM
@Anoosh - I'm not sure I can be much help without actually seeing the code. It sounds like you may need to take a closer look at how your code is handling thread sychronization. When are you instantiating data contexts, etc.?
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Vitaly
8/7/2009 2:09 AM
Looks like one important point was overlooked in whole discussion but Ankit Verma actually mentioned it - to avoid the problem that he had I found that you have to attach child entities BEFORE you attach parent entity.
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Steve
8/7/2009 2:11 AM
@Vitaly - That's interesting. I never had to attach child entities first.
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Vitaly
8/7/2009 2:16 AM
@Steve - I was trying to attach parent entity first in update scenario but encountered same issue as Ankit Verma mentioned in earlier post - "Cannot attach an entity that already exists."
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Steve
8/7/2009 2:22 AM
@Vitaly - Ah, so that's a slightly different scenario from what I have above. In the above, I'm instantiating the data context as a local variable in the method. In your case, it sounds like your entity was *already* in the data context - meaning the data context was already around in memory.
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Vitaly
8/7/2009 2:31 AM
@Steve - No, actually it's a same disconnected scenario. I am also creating data context, within "using" block, like you do, then attaching parent entity - no problem, but when trying to attach children of that entity - exception "Cannot attach an entity that already exists." So I had to attach child entities first and it worked.
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Steve
8/7/2009 2:42 AM
@Vitaly - Hmm, in that case I have no idea. I'd be curious to see the code though.
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
@Steve
8/7/2009 2:50 AM
Sure, even something easy like this will do:

public void UpdateCustomer( Customer customer )
{

using (MyNorthwindDataContext db = new MyNorthwindDataContext())
{

db.Customers.Attach(customer, true);
db.Orders.AttachAll(customer.Orders, true);

//Customer original = (from cust in db.Customers where cust.CustomerID == customer.CustomerID select cust).Single();


try
{
db.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException)
{

db.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);


}
}
}
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Steve
8/7/2009 2:58 AM
@Vitaly - Yes, that code is virtually identical to the code above. I have no idea offhand why that exception in happening. I'll have to create a data context against northwind and give it a try at some point.
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Vitaly
8/7/2009 3:08 AM
@Steve - Yes, I encountered this exception
"System.InvalidOperationException: Cannot attach an entity that already exists" many times in various scenarios, I am guessing once you attach parent entity it considers all children attached, was trying to say that in order to avoid this problem you have to attach chilren first.
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Steve
8/7/2009 3:24 AM
@Vitaly - Out of curiosity, are your entities using C# code that was autogenerated from the designer or hand-coded? What collection type is used for the child entities? Is it an EntitySet<Address> or List<Address> or something else?
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Vitaly
8/7/2009 3:54 AM
@Steve - Entities are autoghenerated with some properties and helper methods added through partial classes. Most child entities are EntitySet<T> and eager loaded.
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
More Article
1/28/2010 2:18 AM
Thank you for helpful
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Torrent free downloads
7/20/2010 1:22 PM
Out of curiosity, are your entities using C# code that was autogenerated from the designer or hand-coded? What collection type is used for the child entities? Is it an EntitySet<Address> or List<Address> or something else?
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Keyur Patel
9/1/2010 9:16 AM
The easiest way, we can work with entities in multi-tier application, is disable object tracking on the DataContext before retrieving an entity.
e.g.
One of the method on the Data Access Layer(DAL)

public Employee GetById(int id)
{
using(NorthwindDataContext dc = new NorthwindDataContext())
{
dc.ObjectTrackingEnabled = false;
return dc.Employees.Where(emp => emp.EmployeeId == id).Single();

}

}

Since this method is going to called on a different tier that is Business Logic Layer or UI Layer, the retrieved entity has already lost it's attachment with DataContext using which it is retrieved. So practically we have lost all the advantages of ObjectTracking. Then why not disable it and get rid of Attach() issues.
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Steve
9/1/2010 10:00 AM
@Keyur - I'm not sure I follow your last sentence. In a tiered scenario, you're not going to be using ObjectTracking. Hence, you need to use Attach().
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Keyur Patel
9/4/2010 12:08 AM
Steve,
In a muliti-tier scenario, if we explicitly disable ObjextTracking just before the entity is retrieved, we can easily attach our detached entity with new DataContext using any of the Attach(). If we don't do this, we get an error while trying to attach an entity to the new DataContext which can be easily avoided by disabling ObjectTracking.
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Nilesh
2/1/2011 7:42 AM
With this code you can solve your problem.
I am using vs 2010

using (var context = new RegistrationByLinqEntities())
{
var valtemp = context.FornDatas.SingleOrDefault(a => a.id == id);
valtemp.Approve = app;
valtemp.CreateDate = DateTime.Now;
context.SaveChanges();
}
Gravatar
# re: Linq - Handle Insert/Update/Delete of child entity in tiered application
Amin
7/28/2011 3:07 AM
Thank you very much

Post Comment

Title *
Name *
Email
Comment *  
Verification

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