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.

Solution 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

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

Solution 3

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"

Solution 4

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

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

View more solutions

Comments

[Sharique]

in second method you attach entity to context, because it is already populated from it. Just call SubmitChanges()

[Pranay Rana]

right now i am using the same thing coping data of modified to original

[dh.]

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.]

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.