使用 LINQ 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:

尝试附加或添加一个不是新的实体，可能是从另一个 DataContext 加载的.这不受支持.

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.

推荐答案

我找到以下解决此问题的方法:

I find following work around to this problem :

1) 获取和更新实体(我将使用这种方式，因为它对我来说没问题)

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) 禁用 ObjectTrackingEnabled 如下

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) 分离所有相关对象

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) 在列中使用时间戳

4) use Time stamp in the column

 http://www.51sjk.com/Upload/Articles/1/0/339/339550_20221213104151407.aspx

5) 创建用于更新数据的存储过程并通过数据库上下文调用它

5) Create stored procedure for updating your data and call it by db context