Most efficient way to update with LINQ to SQL

Can I update my employee record as given in the function below or do I have to make a query of the employee collection first and then update the data?

public int updateEmployee(App3_EMPLOYEE employee) 
{
    DBContextDataContext db = new DBContextDataContext();
    db.App3_EMPLOYEEs.Attach(employee);
    db.SubmitChanges();
    return employee.PKEY;
}

Or do I have to do the following?

public int updateEmployee(App3_EMPLOYEE employee) 
{
    DBContextDataContext db = new DBContextDataContext();
    App3_EMPLOYEE emp = db.App3_EMPLOYEEs
        .Single(e => e.PKEY == employee.PKEY);
        
    db.App3_EMPLOYEEs.Attach(employee, emp);
    db.SubmitChanges();
    return employee.PKEY;
}

But I don’t want to use the second option. Is there any efficient way to update data?

I am getting this error by using both ways:

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

I find following work around to this problem :

1) fetch and update entity (I am going to use this way because it’s ok for me )

public int updateEmployee(App3_EMPLOYEE employee)
{
    AppEmployeeDataContext db = new AppEmployeeDataContext();
    App3_EMPLOYEE emp = db.App3_EMPLOYEEs.Single(e => e.PKEY == employee.PKEY);
    emp.FIRSTNAME = employee.FIRSTNAME;//copy property one by one 
    db.SubmitChanges();
    return employee.PKEY;
}

2) disable ObjectTrackingEnabled as following

// but in this case lazy loading is not supported


    public AppEmployeeDataContext() : 
                    base(global::LinqLibrary.Properties.Settings.Default.AppConnect3DBConnectionString, mappingSource)
            {
                this.ObjectTrackingEnabled = false;
                OnCreated();
            }

3) Detach all the related objects

partial class App3_EMPLOYEE
{
    public void Detach()
    {
        this._APP3_EMPLOYEE_EXTs = default(EntityRef<APP3_EMPLOYEE_EXT>);
    }
}

 public int updateEmployee(App3_EMPLOYEE employee)
{
    AppEmployeeDataContext db = new AppEmployeeDataContext();
    employee.Detach();
    db.App3_EMPLOYEEs.Attach(employee,true);
    db.SubmitChanges();
    return employee.PKEY;
}

4) use Time stamp in the column

 http://www.west-wind.com/weblog/posts/135659.aspx

5) Create stored procedure for updating your data and call it by db context

Method 2

You cannot attach a modified entity to a DataContext when there is no RowVersion column. Instead you could store original entity in your application as long as maintaining a copy for data changes. Then when changes need to be saved you could attach original entity to a DataContext, change its values to match the modified entity values and submit changes.

Here is an example:

public int updateEmployee(App3_EMPLOYEE employee, App3_EMPLOYEE originalEmployee)
{
    DBContextDataContext db = new DBContextDataContext();
    db.App3_EMPLOYEEs.Attach(originalEmployee);

    // TODO: Copy values from employee to original employee

    db.SubmitChanges();
    return employee.PKEY;
}

Update:

There is a table in the database with columns ID, Name, Notes

// fetch an employee which will not be changed in the application
Employee original;
using(var db = new TestDbDataContext())
{
  original = db.Employees.First(e => e.ID == 2);
}

// create an instance to work with
var modified = new Employee {ID = original.ID, Name = original.Name, Notes = original.Notes};

// change some info
modified.Notes = string.Format("new notes as of {0}", DateTime.Now.ToShortTimeString());  
// update
using(var db = new TestDbDataContext())
{
  db.Employees.Attach(original);
  original.Notes = modified.Notes;
  db.SubmitChanges();
}

Method 3

There is a discussion on this topic here at MSDN s
recommend you to use an IsVersion field and the Attach method

Method 4

You can attach a unattached modified entity, by using this overload:

db.App3_EMPLOYEEs.Attach(employee, true);//Attach as modfieied

Note that for this to work you need in your table a “Version” column of type “timestamp”

Method 5

This is a function in my Repository class which I use to update entities

protected void Attach(TEntity entity)
{
   try
    {
       _dataContext.GetTable<TEntity>().Attach(entity);
       _dataContext.Refresh(RefreshMode.KeepCurrentValues, entity);
    }
    catch (DuplicateKeyException ex) //Data context knows about this entity so just update values
    {
       _dataContext.Refresh(RefreshMode.KeepCurrentValues, entity);
    }
}

Where TEntity is your DB Class and depending on you setup you might just want to do

_dataContext.Attach(entity);

Method 6

Use this extend method for update all properties that are column attributes:

public static void SaveToOriginal<T>(this T original, T actual)
    {
        foreach (var prop in typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance)
            .Where(info => info.GetCustomAttribute<System.Data.Linq.Mapping.ColumnAttribute>() != null))
        {
            prop.SetValue(original, prop.GetValue(actual));
        }
    }

I mean, first you recover the original from database, use the method to map all columns attributes from new element to original, and at last do a submit.
I hope this helps.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0