C# – Most efficient way to update with LINQ to SQL

asp.netcdatacontextlinqlinq-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.

Best Answer

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

Related Topic