Most efficient way to update with LINQ to SQL

Question

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.

in second method you attach entity to context, because it is already populated from it. Just call SubmitChanges() — Sharique, Jun 01 '10 at 09:46

score 9 · Accepted Answer · edited Nov 13 '13 at 04:39

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

score 3 · Answer 2 · edited Sep 16 '10 at 19:13

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();
}

right now i am using the same thing coping data of modified to original — Pranay Rana, Jun 01 '10 at 09:50
so that is the way I use to not have additional request for current state of a record in the database. The difference is that I attach an *original* entity to the DataContext and then modify attached entity before submit — dh., Jun 01 '10 at 09:53
and the error you're getting I think is because somewhere in your app (where you read data) you have not disposed the datacontext and your employee entity is attached to it. So if you use the same instance of the datacontext (which was used for retrieval) for save then it should also work for you. — dh., Jun 01 '10 at 10:01

score 2 · Answer 3 · answered Dec 11 '12 at 19:46

2

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"

answered Dec 11 '12 at 19:46

yoel halb

12,188
3
57
52

score 2 · Answer 4 · edited Feb 09 '12 at 17:45

2

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

edited Feb 09 '12 at 17:45

Pranay Rana

175,020
35
237
263

answered May 20 '10 at 11:25

Devart

119,203
23
166
186

score 1 · Answer 5 · answered Oct 21 '14 at 11:21

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

score 0 · Answer 6 · edited Aug 18 '17 at 12:12

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.

Most efficient way to update with LINQ to SQL

6 Answers6

Linked