[SOLVED] Most efficient way to update with LINQ to SQL

Issue

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

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

Answered By – Pranay Rana

Answer Checked By – Marie Seifert (BugsFixing Admin)

Leave a Reply

Your email address will not be published. Required fields are marked *