Sunday, March 4, 2012

Most efficient way to update with LINQ to SQL

Shout it kick it on DotNetKicks.com
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.

Answer

I find following work around to this problem :

1) fetch and update entity (i am going to use this way because it 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) disble 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);
        }
    }

     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 you data and call it by db context

Find Actual Question and Answer at : http://stackoverflow.com/questions/2872380/most-efficient-way-to-update-with-linq-to-sql

No comments:

Post a Comment