c# - 在 Entity Framework 中处理主数据表和相关表

Question

我有3张 table

Employee(ID,FirstName,LastName) - 我们有所有员工，包括经理等，

EmployeeRole(RoleID,Role) - 这里我们定义了员工的角色

Project(ProjectName,Manager,Employee,Date..) - 这里是分配给所有员工的项目的详细信息。

1. 在项目表中，我有 Emmployee、Manager 等列，这两列都是 Employee 表的外键。问题是我有像(名字，姓氏)这样的输入，我如何找到员工的 ID。还是表结构有误？

2. 当我尝试在项目表中插入数据时，EmployeeRole 表也在更新。它不应该更新 EMployeeRole 表。它是一个主数据。

请给我建议解决方案？

Answer 1

您的逻辑数据模型不正确(恕我直言)。

一个项目可以有很多员工。员工可以作为特定角色从事一个或多个项目。

所以它是 Employee 和 Project 之间的多对多，交集表具有 Role 类型。

例如

CREATE TABLE [dbo].[Employee](
    [EmployeeID] [int] NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [RoleID] [int] NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [EmployeeID] ASC
)) 
GO

CREATE TABLE [dbo].[Project](
    [ProjectID] [int] NOT NULL,
    [ProjectName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED 
(
    [ProjectID] ASC
)) 
GO

CREATE TABLE [dbo].[ProjectEmployee](
    [ProjectID] [int] NOT NULL,
    [EmployeeID] [int] NOT NULL,
    [RoleID] [int] NOT NULL,
 CONSTRAINT [PK_ProjectEmployee] PRIMARY KEY CLUSTERED 
(
    [ProjectID] ASC,
    [EmployeeID] ASC,
    [RoleID] ASC
)) 
GO

CREATE TABLE [dbo].[Role](
    [RoleID] [int] NOT NULL,
    [RoleName] [int] NOT NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
    [RoleID] ASC
)) 
GO

ALTER TABLE [dbo].[ProjectEmployee]  WITH CHECK ADD  CONSTRAINT [FK_ProjectEmployee_Employee] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employee] ([EmployeeID])
GO

ALTER TABLE [dbo].[ProjectEmployee]  WITH CHECK ADD  CONSTRAINT [FK_ProjectEmployee_Project] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[Project] ([ProjectID])
GO

ALTER TABLE [dbo].[ProjectEmployee]  WITH CHECK ADD  CONSTRAINT [FK_ProjectEmployee_Role] FOREIGN KEY([RoleID])
REFERENCES [dbo].[Role] ([RoleID])
GO

然后你的 LinqToEntity 看起来像这样:

// Add a new Role
Role role = new Role();
role.RoleID = 1;   // TODO: make identity in database
role.RoleName = "Role 1";
db.Roles.Add(role);
db.SaveChanges();

// Add a new Employee
Employee employee = new Employee();
employee.EmployeeID = 1;   // TODO: make identity in database
employee.FirstName = "Carl";
employee.LastName = "Prothman";
db.Employee.Add(employee);
db.SaveChanges();

// Add a new Project
Project project = new Project();
project.ProjectID = 1;   // TODO: make identity in database
project.ProjectName = "Create new data model";
db.SaveChanges();

// Add employee to project as role1
ProjectEmployee projectEmployee = new ProjectEmployee();
projectEmployee.ProjectID = project.ProjectID;
projectEmployee.EmployeeID = employee.EmployeeID;
projectEmployee.RoleID = role.RoleID;
db.ProjectEmployees.Add(projectEmployee);
db.SaveChanges();