以多对多的关系编辑关联表中的记录

Question

这件事让我困扰了一会儿，我终于想要得到答案。

假设我有员工和部门。这两者之间存在多对多关系，并且有一个名为EmployeeDepartments的关联表。

Employees 
------------------- 
EmployeeID (PK) 

Departments 
------------------- 
DepartmentID (PK) 

EmployeeDepartments 
------------------- 
EmployeeID (FK) 
DepartmentID (FK) 

我有一个页面，用户可以编辑员工。有一个复选框列表显示所有部门，并且该员工所属的部门将被检查。然后用户可以选中/取消选中该员工属于哪个部门。

当我编辑员工并去保存数据时，我该如何处理这些部门？

我一直在做的方式是删除该员工的EmployeeDepartments表中的每条记录。然后，对于用户选择的每个部门，我将其添加到EmployeeDepartments。

它的工作原理，但必须有一个更有效的方法来做到这一点，它似乎是错误的。如果员工在5个部门中，并且我将该员工添加到其他部门，则必须从EmployeeDepartments中删除5条记录，然后添加6条记录。

Answer 1

只处理更改三角洲。也就是说，查看当前“选定”的项目，然后查看要选择的项目，然后添加或删除适当的项目。这可以使用set操作来计算（这在LINQ中很平常）。在old - new

1. 删除项目（差集，或Except）
2. 添加在new - old项目（差集，或Except）
3. （在new^old项目应不受影响）

大部分如果需要，这项工作可以交给数据库。 （除了在...以外的地方删除，如果不存在则插入...例如，另请参阅标准SQL MERGE声明和非标准等效项和扩展项）。

确保正确使用数据库事务以确保一致性。

快乐编码。

Answer 2

如果你的SQL产品支持它，你可以使用标准SQL的MERGE。如果你的SQL产品是SQL Server，那么你很幸运：它的MERGE有一个漂亮的扩展IF NOT MATCHED BY SOURCE它允许你除了INSERT和UPDATEDELETE。

下面是一个简单的例子（例如，编辑您将值在一个临时表后

CREATE TABLE EmployeeDepartments 
(
EmployeeID INTEGER NOT NULL, 
DepartmentID INTEGER NOT NULL, 
UNIQUE (DepartmentID, EmployeeID) 
); 

INSERT INTO EmployeeDepartments (EmployeeID, DepartmentID) 
    VALUES (1, 1), 
      (1, 2); 

说：忽略参照完整性约束）

CREATE TABLE StagingTable 
(
EmployeeID INTEGER NOT NULL, 
DepartmentID INTEGER NOT NULL, 
UNIQUE (DepartmentID, EmployeeID) 
); 

INSERT INTO StagingTable (EmployeeID, DepartmentID) 
    VALUES (1, 1), 
      (1, 3); 

用简单的英语，排{1, 3}将被插入，行{1, 2}将被删除，排{1, 1}将保持：

MERGE INTO EmployeeDepartments 
    USING StagingTable AS S1 
     ON EmployeeDepartments.EmployeeID = S1.EmployeeID 
     AND EmployeeDepartments.DepartmentID = S1.DepartmentID 
WHEN NOT MATCHED THEN 
    INSERT (EmployeeID, DepartmentID) 
     VALUES (EmployeeID, DepartmentID) 
WHEN NOT MATCHED BY SOURCE THEN 
    DELETE;