英文:
Configure many-to-many relationship in ASP.NET Core MVC and Entity Framework against
问题
我正在尝试配置两个表之间的多对多关系,即Employee
和Project
之间的关系。
一个Employee
可以参与多个项目,一个项目可以有多个Employees
参与。因此,我创建了两个模型类Employee
和Project
,并添加了表Employee_Project
。
这是我的三个模型类:
namespace WebApp2.Models
{
public class Employee
{
[Key]
public int Emp_Id { get; set; }
public string Emp_Name { get; set; }
public string Emp_Email { get; set; }
public string Emp_Mobile { get; set; }
public virtual ICollection<Employee_Project> Employee_Projects { get; set; }
}
public class Project
{
[Key]
public int Proj_Id { get; set; }
public string Proj_Name { get; set; }
public string Project_Details { get; set; }
public virtual ICollection<Employee_Project> Employee_Projects { get; set; }
}
public class Employee_Project
{
[Key]
[Column(Order = 1)]
public int Emp_Id { get; set; }
[Key]
[Column(Order = 2)]
public int Proj_Id { get; set; }
public virtual Employee Employee { get; set; }
public virtual Project Project { get; set; }
}
}
然后,我添加了这个DbContext
类:
namespace WebApp2.Data
{
public class MyDbContext : DbContext
{
public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
{
}
public DbSet<Employee> Employees { get; set; }
public DbSet<Project> Projects { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Employee_Project>().HasKey(pt => new { pt.Proj_Id, pt.Emp_Id });
modelBuilder.Entity<Employee_Project>()
.HasOne(pt => pt.Employee)
.WithMany(pt => pt.Employee_Projects)
.HasForeignKey(p => p.Emp_Id);
modelBuilder.Entity<Employee_Project>()
.HasOne(pt => pt.Project)
.WithMany(pt => pt.Employee_Projects)
.HasForeignKey(p => p.Proj_Id);
}
public DbSet<Employee_Project> Employee_Projects { get; set; }
}
}
然后,我创建了三个控制器,分别是ProjectController
、EmployeeController
和Emp_ProjController
。
以下是Emp_ProjController
的一部分代码:
public class Emp_ProjController : Controller
{
private readonly MyDbContext _DbContext;
public Emp_ProjController(MyDbContext DbContext)
{
_DbContext = DbContext;
}
// 这里是控制器的其余部分,包括Create方法等。
// 请确保你的代码中没有错误。
}
至于你的问题,它看起来是关于数据库中的主键冲突。错误消息中提到:
SqlException: Violation of PRIMARY KEY constraint 'PK_Employee_Projects'. Cannot insert duplicate key into object 'dbo.Employee_Projects'. Duplicate key value: (1, 1). The instruction has been terminated.
这意味着你正在尝试插入重复的主键值 (1, 1) 到 Employee_Projects
表中。主键应该是唯一的,你需要确保不会插入重复的 (Emp_Id, Proj_Id)
组合。在创建 Employee_Project
对象并将其添加到数据库之前,你应该先检查是否已经存在相同的关联。
如果你仍然无法解决问题,可以提供更多关于如何在Emp_ProjController
控制器中创建Employee_Project
对象的代码,以便我可以更详细地帮助你找到问题。
英文:
I am trying to configure the relationship many-to-many between two tables, Employee
and Project
.
One Employee
can participate in many projects, and one project can have many Employees
working on it. So I created two model classes Employee
and Project
, and I added the table Employee_Project
.
These are my three model classes:
namespace WebApp2.Models
{
public class Employee
{
[Key]
public int Emp_Id { get; set; }
public string Emp_Name { get; set; }
public string Emp_Email { get; set; }
public string Emp_Mobile { get; set; }
public virtual ICollection<Employee_Project> Employee_Projects { get; set; }
}
public class Project
{
[Key]
public int Proj_Id { get; set; }
public string Proj_Name { get; set; }
public string Project_Details { get; set; }
public virtual ICollection<Employee_Project> Employee_Projects { get; set; }
}
public class Employee_Project
{
[Key]
[Column(Order =1)]
public int Emp_Id { get; set; }
[Key]
[Column(Order = 2)]
public int Proj_Id { get; set; }
public virtual Employee Employee { get; set; }
public virtual Project Project { get; set; }
}
}
I then added this DbContext
class:
namespace WebApp2.Data
{
public class MyDbContext:DbContext
{
public MyDbContext(DbContextOptions<MyDbContext> option):base(option)
{
}
public DbSet<Employee> Employees { get; set; }
public DbSet<Project> Projects { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Employee_Project>().HasKey(pt => new { pt.Proj_Id, pt.Emp_Id });
modelBuilder.Entity<Employee_Project>()
.HasOne(pt => pt.Employee)
.WithMany(pt => pt.Employee_Projects)
.HasForeignKey(p => p.Emp_Id);
modelBuilder.Entity<Employee_Project>()
.HasOne(pt => pt.Project)
.WithMany(pt => pt.Employee_Projects)
.HasForeignKey(p => p.Proj_Id);
}
public DbSet<Employee_Project> Employee_Projects { get; set; }
}
}
I created after that the three controllers
public class ProjectController : Controller
{
private readonly MyDbContext _context;
public ProjectController(MyDbContext context)
{
_context = context;
}
public IActionResult Index()
{
return View(_context.projects.ToList());
}
public IActionResult Create()
{
return View();
}
[HttpPost]
public IActionResult Create(Project project)
{
_context.projects.Add(project);
_context.SaveChanges();
return RedirectToAction("Index");
}
}
public class EmployeeController : Controller
{
private readonly MyDbContext _context;
public EmployeeController(MyDbContext context)
{
_context = context;
}
public IActionResult Index()
{
return View(_context.Employees.ToList());
}
public IActionResult Create()
{
return View();
}
[HttpPost]
public IActionResult Create(Employee employee)
{
_context.Employees.Add(employee);
_context.SaveChanges();
return RedirectToAction("Index");
}
}
public class Emp_ProjController : Controller
{
private readonly MyDbContext _DbContext;
public Emp_ProjController(MyDbContext DbContext)
{
_DbContext = DbContext;
}
public IActionResult Index()
{
return View(_DbContext.Employee_Projects.ToList());
}
public IActionResult Create()
{
ViewBag.emp=_DbContext.Employees.ToList();
ViewBag.pro=_DbContext.projects.ToList();
return View();
}
[HttpPost]
public IActionResult Create(int empid, int [] projIds)
{
foreach (var item in projIds)
{
Employee_Project emp = new Employee_Project();
emp.Emp_Id = empid;
emp.Proj_Id = item;
_DbContext.Employee_Projects.Add(emp);
_DbContext.SaveChanges();
}
return RedirectToAction("Index");
}
}
After that foreach Controllers I made the view for the method Index and Create
Emp_Proj
//view Index
@model IEnumerable<WebApp2.Models.Employee_Project>
@{
ViewData["Title"] = "Index";
}
<h1>Index</h1>
<p>
<a asp-action="Create">Create New</a>
</p>
<table class="table">
<thead>
<tr>
<th>
@Html.DisplayNameFor(model => model.Employee.Emp_Name)
</th>
<th>
@Html.DisplayNameFor(model => model.Project.Proj_Name)
</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.Employee.Emp_Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.Project.Proj_Name)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ })
</td>
</tr>
}
</tbody>
</table>
//view Create
<h2>Create</h2>
<form method="post">
<div>
<label>Employee Name</label>
@Html.DropDownList("empid", new SelectList(ViewBag.emp, "Emp_Id","Emp_Email"),"Select Employee")
</div>
<div>
<label>Select Project</label>
@* @Html.DropDownList("proid", new SelectList(ViewBag.pro, "Proj_Id","Proj_Name"),"Select Project")*@
<ul>
@foreach(var item in ViewBag.pro )
{
<li>
<input type="checkbox" name="projIds" value="@item.Proj_Id">@item.Proj_Name
</li>
}
</ul>
<input type="submit" value="SaveData"/>
</div>
</form>
I don't have problem in the Employee and the project, I found the problem when I want to create a Emp_Proj element
enter image description here
It always gives me an error like that:
SqlException: Violation of PRIMARY KEY constraint 'PK_Employee_Projects'. Cannot insert duplicate key into object 'dbo.Employee_Projects'. Duplicate key value: (1, 1).
The instruction has been terminated.
Can someone please help me to find the problem? Thanks in advance.
I try to find the problem, and I appreciate some assistance.
答案1
得分: 0
错误消息已经显示了引发此异常的原因:数据库已经包含这个记录,不能插入重复数据。您只需在插入数据之前检查它是否已经存在于数据库中,请参考这个简单的演示。
[HttpPost]
public IActionResult Create(int empid, int[] projIds)
{
foreach (var item in projIds)
{
// 检查数据库是否已经包含此记录
var empdb = _DbContext.Employee_Projects.Where(x => x.Emp_Id == empid && x.Proj_Id == item).FirstOrDefault();
if (empdb == null)
{
Employee_Project emp = new Employee_Project();
emp.Emp_Id = empid;
emp.Proj_Id = item;
_DbContext.Employee_Projects.Add(emp);
}
}
_DbContext.SaveChanges();
return RedirectToAction("Index");
}
英文:
The error message has show the reason why cause this exception: The database already contains this record, you can't insert duplicate data. You just need to check if it already exists in the database before inserting data, Please refer to this simple demo.
[HttpPost]
public IActionResult Create(int empid, int[] projIds)
{
foreach (var item in projIds)
{
//check if the database already has this record
var empdb = _DbContext.Employee_Projects.Where(x => x.Emp_Id == empid && x.Proj_Id == item).FirstOrDefault();
if (empdb==null)
{
Employee_Project emp = new Employee_Project();
emp.Emp_Id = empid;
emp.Proj_Id = item;
_DbContext.Employee_Projects.Add(emp);
}
}
_DbContext.SaveChanges();
return RedirectToAction("Index");
}
答案2
得分: 0
首先,尝试完全移除 "Employee_Project" 实体,看看是否 EF Core 可以自动创建它。只要两个实体上设置了两个导航属性,EF Core 就应该能够自动生成关联表。请注意,这两个实体的导航属性应该相互引用,如下所示:
在 Employee 实体中:
public virtual ICollection<Project> Projects { get; set; }
在 Project 实体中:
public virtual ICollection<Employee> Employees { get; set; }
如果这种方法不起作用,可以像这样修改你的 Employee_Project 类:
public class Employee_Project
{
[Key]
[Column(Order = 1)]
public int EmployeeId { get; set; }
[Key]
[Column(Order = 2)]
public int ProjectId { get; set; }
public virtual Employee Employee { get; set; }
public virtual Project Project { get; set; }
}
列的命名可能是问题的根本原因。
英文:
First off, try to remove "Employee_Project" entity entirely, and see if EF Core can auto-create it. EF Core should be able to auto-generate the association table as long as the two navigational properties are set on the two Entities. Note that navigational properties of the two entities are supposed to reference each other. like so;
in Employee;
public virtual ICollection<Project> Projects { get; set; }
and in Project;
public virtual ICollection<Employee> Employees { get; set; }
In case that doesn't work, modify your Employee_Project class like this;
public class Employee_Project
{
[Key]
[Column(Order =1)]
public int EmployeeId { get; set; }
[Key]
[Column(Order = 2)]
public int ProjectId { get; set; }
public virtual Employee Employee { get; set; }
public virtual Project Project { get; set; }
}
The naming of the columns might have been the problem.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论