Update a disconnected object (DataContext .Attach)

Question

After upgrading to RTM, I am getting exceptions when trying to update a disconnected object.  This code was previously working in Beta 2

 

Code Block

 

public static void UpdateEmployee(Employee employee)

{

using (HRDataContext dataContext = new HRDataContext())

{

//Get original employee

Employee originalEmployee = dataContext.Employees.Single(e=>e.EmployeeId==employee.EmployeeId);

 

//attach to datacontext

dataContext.Employees.Attach(employee, originalEmployee);

 

//save changes

dataContext.SubmitChanges();

}

}

 

 

The exception:

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.

Isn't that what Attach is for?

 

This has been discussed in other posts/blogs but most refer to Beta 2.   

 

Answer 1

originalEmployee already is attached to the context.  Attach is strictly for use with entities that have never been attached (entities resulting from a query are implicitly attached).

 

There are a few immediate ways I think you could approach this:

1. Copy property-by-property in your UpdateEmployee method.  Safe, precise, but tedious to write all the assignments for each entity type.
2. Create an extension to object which uses reflection to copy property values to the target.  Unfortunately, extensions to object are not the best.  Perhaps extend INotifyPropertyChanged objects only:
   
   public static void CopyFrom<T>(this T target, T source) where T: INotifyPropertyChanged
   {
       // Use reflection, locate all property values on source and copy to target.
       // Will have to avoid copying primary key values, of course.
   }
   
   Then do:
   
   originalEmployee.CopyFrom(employee);
   dataContext.SubmitChanges();
3. Same as (2), but use a regular (non-extension) method.  You could lift the restriction on INotifyPropertyChanged without cluttering the API of every type.

Answer 2

 

The intended use of the Attach API is to specify instances of objects that were deserialized from another tier, not to use instances that are already associated with the DataContext.  When you queried for the old version you not only got an instance that was already part of the DataContext you introduced that object's primary key into the DataContext's cache, so even if you copied that original object's data into a new instance you still would not be able to call Attach since the primary key for the object is already in use.

 

It's also not a good idea to even attempt to fetch the old version.  By doing that you are in effect turning off optimistic concurrency, so unless you intended that this is a bad approach.  What you need to do is round trip both the original state and the current state of the object.

Answer 3

Can you offer some guidance on the proper way to implement a layered ASP.NET application using LINQ?  ScottGu's blog indicates that the Attach method is the answer for updating objects. 

 

<ScottGu>

... if you don't want to use the LINQDataSource control then you can use the Attach() method on Tables to re-attach a disconnected entity to a DataContext.  This enables you to perform changes and updates across post-backs, web-services, and/or any scenario where you don't have the same DataContext

</ScottGu>

 

I realize that his post is old, and obviously doesn't reflect the RTM, but I wrote a large amount of code based on this and need to learn the proper way to implement entity updates in a layered app.

 

Thanks for your help!

 

Answer 4

Matt's point about optimistic concurrency is well-taken.  I'm ashamed that I forgot about that.

 

You could package the old version into viewstate, and restore it on postback.  Alternatively, if you were using a timestamp column, you could use Attach(newVersion, true).

Answer 5

Ok, i have an object that i fetch from DB when a user logs in. Now when user goes through pages we need to update the object in Database.

Can someone tell me how to do it? It was working before in Beta 2.

Answer 6

Could someone explain why this works only sometimes?? Sometimes it passes and sometimes .. Not!!

 

Code Block

 

Contact ctc = new Contact();

ctc.FirstName = "Bob";

ctc.LastName = "Binette";

ctc = ContactManager.SaveContact(ctc);

 

ctc.Mobile = "514 577 6062";

ctc = ContactManager.SaveContact(ctc);

 

 

where

 

Code Block

public static Contact SaveContact(Contact contact)

{

  using (IMyDBDataContext db = new IMyDBDataContext())

  {

    if (contact.ContactId == 0)

    {

      db.Contacts.InsertOnSubmit(contact);

    }

    else

    {

      db.Contacts.Attach(contact, true);

    }

    db.SubmitChanges();

  }

  return contact;

}

 

 

An exception is thrown :

 

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.

 

What is the correct way to update an object in database when you don't have the context that loaded it ?

 

Thanks

Answer 7

Here's a way to do this. First, create a partial class that adds a Detach method to Employee class. This method will detach the object from it's data context and detach associated objects.

 

Code Block

public parial class Employee
{
  public void Detach()
  {
    this.PropertyChanged = null;

    this.PropertyChanging = null;

    // Assuming there's a foreign key from Employee to Boss
    this.Boss = default(EntityRef<Boss>);
    // Similarly set child objects to default as well

    this.Subordinates = default(EntitySet<Subordinate>);
  }
}

 

 

 

Now during update, call Detach before attaching the object to a DataContext.

 

Code Block

public static void UpdateEmployee(Employee employee)

{

    using (HRDataContext dataContext = new HRDataContext())

    {

        //attach to datacontext

        employee.Detach();

        dataContext.Employees.Attach(employee);

        //save changes

        dataContext.SubmitChanges();

    }

}

 

 

 

This'll work. It assumes the employee object already has its primary key populated.

You might see during update, it's generating a bloated UPDATE statement where each and every property is appearing on the WHERE clause. In that case, set UpdateCheck to Never for all properties of Employee class from the Object Relational Designer.

 

Answer 8

I use a similar approach to what OmarALZabir suggested. I add a property to a partial class extention of the entity which is a copy of the original.  The when the "OnLoad" event is triggered in a query, I use a custom Clone() member (in the partial class extention also) to copy the properties of the freshly loaded object to the new property I added.  This allows me to restore the original values any time I like or to use the Clone() method to obtain a copy of the object without ties to the datacontext.  The only catch is that you cannot attach that cloned object back into the same datacontext, but that is not usually a problem as I often use a new data context.

 

It is handy to always have a cloned copy of the original data even if it does eat up a bit more storage!

 

You can see a bit more on it on my blog post:

 

http://www.rockymoore.com/TheCoder/archive/2007/11/03/LINQ_Disconnected_Attach_no_Change_tracking_fix.aspx

 

Sure would be nice if the designer had an option to generate a Clone() method and a Original state property automatically

 

Answer 9

I try your code to make an update but it doesn't work.

I have the partial class:

partial class country

{

public void Detach()

{

this.PropertyChanged = null;

this.PropertyChanging = null;

this.companies = default(EntitySet<company>);

}

}

 

and then I try to modify the field "name". I haven't any errors but the field don't change!

 

static void Main(string[] args)

{

AnagraficaDataContext dc1 = new AnagraficaDataContext();

country cc = dc1.countries.Where(c => c.id == 1).First();

cc.name = cc.name + "modificata";

UpdateCountry(cc);

}

 

public static void UpdateCountry(country country)

{

using (AnagraficaDataContext dataContext = new AnagraficaDataContext())

{

//attach to datacontext

country.Detach();

dataContext.countries.Attach(country);

//save changes

dataContext.SubmitChanges();

}

}

 

thanks in advance.

Alessio

Answer 10

I believe what I am about to say is significant to this post and so far as I have searched I have not found anybody mentioning this. I came across this thread and many other like it while trying to solve the many issues I've had with performing Updates via LINQ in a multi-tier setup.

What I have done works perfectly for me and I hope that it will work just as I describe for everyone else. The article that got me started thinking the way I am finally thinking is here http://msdn2.microsoft.com/en-us/library/bb546187.aspx. Unfortunately I believe that this article is not completely right and missing some information.

My scenario is basic. I have data in a database that I want to pull in to a page where that data can be edited and upon clicking a submit button, that data is updated in the database all while using a multi-tier approach.

 

The class I'm dealing with is called Article. My .dmbl file is in the App_Code directory and I have created another .cs file in the App_Code directory called Article.cs which contains a partial class definition of Article. Code is below...

 

public partial class Article

{

public Article GetById(int articleId)

{

dbDataContext db = new dbDataContext ();

return (from p in db.Articles where p.ArticleId == articleId select p).Single();

}

 

public void Update(Article article)

{

dbDataContext db = new dbDataContext ();

db.Articles.Attach(article, true);

db.SubmitChanges();

}

}

 

Now in my code-behind I do the following on Page_Load...

 

protected void Page_Load(object sender, EventArgs e)

{

Article article = new Article().GetById(articleId); // articleId comes from QueryString

cbActive.Checked = article.IsActive; // cbActive is asp:CheckBox on .aspx page

txtHeadline.Text = article.Headline; // txtHeadline is asp:TextBox on .aspx page

}

 

Then on Button Submit I do this...

 

protected void btnSubmit_Click(object sender, EventArgs e)

{

Article article = new Article();

article.ArticleId = articleId; // articleId is global and gets set from the QueryString on every page load

article.IsActive = cbActive.Checked;

article.Headline = txtHeadline.Text;

article.Update(article);

}

 

Now at this point I found a ton of people online who were facing the same problems that I was and were getting the "An attempt has been made to Attach or Add an entity that is not new...." error or the "Row not found or changed" error.

The simple solution that stopped all those errors and made my code start working how I hoped LINQ could actually work was setting my ArticleId column to look like this in the dbml file...

 

[Column(Storage="_ArticleId", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true, IsVersion=true)]

 

Actually all I had to do was set the TimeStamp property of that column to True in the Properties GUI in Visual Studio and LINQ rewrote that line for me.

Basically what this does is tell LINQ to look at the ArticleId column as a TimeStamp/RowVersion column. So when you call db.Articles.Attach(article, true); you are attaching an Article that was created on the code-behind and was never attached to another dbDataContext object. Specifying true in the second parameter says that the Article you are attaching is a modified version of an Article that is already in the database and since ArticleId is a TimeStamp column now, LINQ writes the SQL to do a search based on that column and returns the proper results, then it does its own comparison of values to determine what has changed and then performs the update.

 

I really hope I'm not reposting something that has already been discovered but after days of searching and reading I could not find this simple solution anywhere.

 

Steve B

 

UPDATE: I just double checked the sql that gets created when you call db.Articles.Attach(article, true) and this actually creates an update that updates all values in the database, not just the changed values.

Also, some people mentioned to me that this is a hack and LINQ should be able to accomplish this without using a hack. I did some testing and it can. I created a column in my database called Version and set the data type to timestamp (a value that automatically updates itself on each row update and is equilvalent to binary(8) so it actually has no representation of a real time). Setting this column as IsDbGenerated=true, IsVersion=true accomplishes the exact same result as above but with this scenario you have to take the responsibility of holding on to this value throughout the retrieval, update and submission process. This can easily be done by storing it in ViewState or something similar. This solution takes what I did above and does it without a hack.

Also, the whole point of this is maintaining a mutli-tier approach but also note that this only works because I am using the .aspx page as another layer of abstraction/data storage.

Answer 11

Here's an easy solution that does not require you to make that special Detach function and you can attach objects for insert and update any time. WHen you create a data context, just set the DefferedLoadingEnabled = false and you can attach objects from one data context to another.

There's no need to create the Timestamp field either.

 

Code Snippet

public static DataContext GetDataContext(SubsystemEnum subsystem)
        {
            DataContext context = new DataContext(ConfigurationManager.ConnectionStrings[subsystem.ToString()].ConnectionString);
            context.Log = Console.Out;
            context.DeferredLoadingEnabled = false;
            return context;
        }

        public static void UpdateObject<TEntity>(DBHelper.SubsystemEnum subsystem, TEntity obj) where TEntity : class
        {
            DBHelper.InDataContext(subsystem, (data) =>
            {               
                data.GetTable<TEntity>().Attach(obj, true);
                data.SubmitChanges();
            });
        }

public static void Delete<TSource>(SubsystemEnum subsystem, TSource entity) where TSource : class
        {
            DBHelper.InDataContext(subsystem,
                (data) =>
            {
                Action<Table<TSource>> attachAndDelete = (table) =>
                {
                    table.Attach(entity);
                    table.DeleteOnSubmit(entity);
                };

                attachAndDelete(data.GetTable<TSource>());

                data.SubmitChanges();
            });
        }

 

Ignore everything else, just see the bold lines. I can do update and delete perfectly fine when deferred loading is disabled.

 

Answer 12

 

can please show how it would be done in my code. I am still getting error.

 

Code Snippet

public void Update(Customer customer)

{

using (ModelDataContext db = new ModelDataContext())

{

db.Customers.Attach(customer, true);

db.SubmitChanges();

}

}

 

public Customer GetByID(int ID)

{

ModelDataContext db = new ModelDataContext();

db.DeferredLoadingEnabled = false;

return (from c in db.Customers

where c.CustomerID == ID

select c).FirstOrDefault();

}

 

Answer 13

This worked perfectly for me.  Thank you Omar.

 

Answer 14

 

Of course all of this begs the question - where is the ******** documentation that explains the recommended method of using LINQ with ASP ?

 

Why is this so confusing.  It should be simple !

Answer 15

Hi Steve!

 

I understand that it's been a while since the thread and posting, but figured to comment it in case it would be helpful for you (and others) still facing this issue.

 

Not relating to the topic on other than just a major warning using this method of defining ID as a Version field.

 

Code Snippet

[Column(Storage="_ArticleId", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true, IsVersion=true)]

 

 

What you probably didn't realise, that the effect of that making your problems disappear is caused by the fact, that there is effectively no concurrency checking with that object type after the change.

 

It could be argued that defining primary key and version both on the same field should generate an error instead of generating a code with both features enabled on the same property...

 

Basically for short specification, the Version is used effectively in SQL side to lock the update with "where version = your_original_version" and auto-updating the version in that update of yours (or with timestamp columns, the SQL Server does the update).

 

Now that change makes the comparison against ID field, that is always naturally unchanged and the very same you got at loading the object. It will stay the same regardless of how many updates by others have happened between your select and update.

 

This in turn means that your datamodel is not having any locking and "last save" wins always.

 

 

Br,

 

Kalle

Answer 16

It should be noted that if you turn off deferred loading you are turning off the (very useful) Lazy Loading feature of L2S.. see http://dotnetslackers.com/articles/csharp/Load-Lazy-in-LINQ-to-SQL.aspx for more info..

 

 

OmarALZabir wrote:

Here's an easy solution that does not require you to make that special Detach function and you can attach objects for insert and update any time. WHen you create a data context, just set the DefferedLoadingEnabled = false and you can attach objects from one data context to another. There's no need to create the Timestamp field either.   Code Snippet public static DataContext GetDataContext(SubsystemEnum subsystem)         {             DataContext context = new DataContext(ConfigurationManager.ConnectionStrings[subsystem.ToString()].ConnectionString);             context.Log = Console.Out;             context.DeferredLoadingEnabled = false;             return context;         }         public static void UpdateObject<TEntity>(DBHelper.SubsystemEnum subsystem, TEntity obj) where TEntity : class         {             DBHelper.InDataContext(subsystem, (data) =>             {                                data.GetTable<TEntity>().Attach(obj, true);                 data.SubmitChanges();             });         } public static void Delete<TSource>(SubsystemEnum subsystem, TSource entity) where TSource : class         {             DBHelper.InDataContext(subsystem,                 (data) =>             {                 Action<Table<TSource>> attachAndDelete = (table) =>                 {                     table.Attach(entity);                     table.DeleteOnSubmit(entity);                 };                 attachAndDelete(data.GetTable<TSource>());                 data.SubmitChanges();             });         }   Ignore everything else, just see the bold lines. I can do update and delete perfectly fine when deferred loading is disabled.

Answer 17

You could always store a refence to the original datacontext on the entity. So if you create a datacontext propety on you partial class that you set in your static select method, your update method can use this to attach with.

Not sure if this is great from a memoy point of view but it works for me for the time being

public partial class Site

{

    public SitesDataContext DB { get; set; }

    //static select method makes the select, and sets the DB propety on the entity that stores a reference to the datacontext it came from
    public static Site Select(int id)

    {

        SitesDataContext db = new SitesDataContext ();

        Site site = db.Sites.SingleOrDefault(s => s.Id == id);
        site.DB = db;
        return (site);

    }
    //save method on the entity eithe inserts with a new datacontext or uses the DB property stored on it
   public void Save()
    {
        //insert
        if (this.Id == 0){
            SitesDataContext db = new SitesDataContext();
            db.Sites.InsertOnSubmit(this);
        }
        else
        {
            //use DB popety stored on entity
            DB.SubmitChanges();     
        }

       }
}

Answer 18

The post marked as answer says:
    "What you need to do is round trip both the original state and the current state of the object."

OK, sounds like good advice, but how exactly do we do that?  There are many solutions on this thread and elsewhere on the web - some don't work, other have side effects, none is definitive.  

Microsoft, please provide some guidance here.  Please post code or a link to code that illustrates the correct way to use .Attach to update an entity from a disconnected data context. Kindly ensure the code is relevant to and explains the answer provided to this thread.

BTW, the example code from the msdn documentation for attach found here: http://msdn.microsoft.com/en-us/library/bb548972.aspx
is neither complete or relevant.

Edit:  Also, the code shown here  is not working as expected, although it's probably me who missing something:
The relevant code in the aforementioned link is:

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)/

}

}

}

My code:

public static bool Update(ref PlantLocation entity, PlantLocation original, ErrorHandler errorHandler)

{

Context db = new Context();

//db.DeferredLoadingEnabled = false;  // This works

bool success = true;

entity.Signature = Security.Signature();

try

{

//db.PlantLocations.Attach(entity);

db.PlantLocations.Attach(entity, original);

//db.Refresh(RefreshMode.KeepCurrentValues, entity);

db.SubmitChanges();

}

catch (Exception ex)

{

errorHandler("An error occured while updating a plant location record.  The error message is: " + ex.Message);

success = false;

}

return (success);

}

The calling code (test code) actually makes two trips to the database :

if (this.EntityID > 0)

{

entity = BusinessLogic.PlantLocations.SelectByID(this.EntityID).FirstOrDefault();

original = BusinessLogic.PlantLocations.SelectByID(this.EntityID).FirstOrDefault();

}

Thanks,

Sam

Answer 19

bump
Microsoft, please reply to the post above.
Thanks.

Answer 20

How about a Helper: 

You can call this method before Attaching the entity to the datacontext. 

public static void Detach<T>(T entity)

{

Type t = entity.GetType();

System.Reflection.PropertyInfo[] properties = t.GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);

foreach (var property in properties)

{

string name = property.Name;

if (property.PropertyType.IsGenericType &&

property.PropertyType.GetGenericTypeDefinition() == typeof(EntitySet<>))

{

property.SetValue(entity, null, null);

}

}

System.Reflection.FieldInfo[] fields = t.GetFields(System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance);

foreach (var field in fields)

{

string name = field.Name;

if (field.FieldType.IsGenericType &&

field.FieldType.GetGenericTypeDefinition() == typeof(EntityRef<>))

{

field.SetValue(entity, null);

}

}

System.Reflection.EventInfo eventPropertyChanged = t.GetEvent("PropertyChanged");

System.Reflection.EventInfo eventPropertyChanging = t.GetEvent("PropertyChanging");

if (eventPropertyChanged != null)

{

eventPropertyChanged.RemoveEventHandler(entity, null);

}

if (eventPropertyChanging != null)

{

eventPropertyChanging.RemoveEventHandler(entity, null);

}

}

Regards,
Nico

Answer 21

Hello. I am hoping you can help.  I am developing a tiered website using Linq to Sql.  I created a new class(or object) in DBML designer called memberState.  This object is not an actual table in the database.  I have this method in my middle layer:

public override IEnumerable(memberState) GetMembersByState(string @state)

{

using (BulletinWizardDataContext context = DataContext)

{

IEnumerable(memberState) mems = (from m in context.Members

join ma in context.MemberAddresses

on m.UserId equals ma.UserId

join s in context.States

on ma.StateId equals s.StateId

where s.StateName == @state

select new memberState

{

userId = m.UserID,

firstName = m.FirstName,

middleInitial = m.MiddleInitial,

lastName = m.LastName,

createDate = m.CreateDate,

modifyDate = m.ModifyDate

}).ToArray(memberState)();

return mems;

}

}

The tables in my joins (Members, States, and MemberAddresses are actual tables in my Database).  I created the object memberStates so I could use it in the query above (notice the <b>Select New memberState</b>.  When the data is updated on the web page how do I persist the changes back to the Member Table?  My Member Table consists of the following columns: UserId, FirstName, MiddleInitial, LastName, CreateDate, ModifyDate.  I am not sure how save the changes back to the database.

Thanks,

Answer 22

bump
Microsoft, please reply to the post above.
Thanks.

Answer 23

This thread has been running since 2007. It's 2008 now and there's still no definitive reply from MS? I suspect none will be coming. I further suspect that LINQ to SQL does not and will never work elegantly in a disconnected environment.

Answer 24

bump
Microsoft, please reply to the post above. Pleae provide some clarification as per Sam2 's request above.