ASP.NET MVC 实体框架代码优先:没有错误,但表未正确填充
本文关键字:有错误 填充 实体 MVC NET 框架 代码 ASP | 更新日期: 2023-09-27 18:37:18
我目前正在使用 MVC4 实体框架制作一个票务系统。我已经在我的 SQL 服务器中填充了我的表,但只有几列不正确。首先,我想为代码量道歉,但它们都需要引用!
我只希望人们检查我是否正确映射了我的模型,因为在 image(1) 中缺少一些值。
查看下面提供的两张图片非常重要,以便更清楚地了解我想要完成的工作。
这里的图像(1)显示了我希望我的表是什么样子,并显示了每个类的所有主键和外键。
这张图片(2)这里显示了我的实体图所以我的理想情况是:用户可以创建多个工单,他们只能将它们分配给一个管理员(这解决了工单上的问题)。
每个工单将由工单ID标识,因此在管理表上它应该有管理员ID,用户ID(提交工单的人),工单ID和管理员角色。
每个管理员 ID 可以分配给一个用户,但并非所有用户都必须分配给一个管理员 ID
仓库ID,部门ID和类别非常简单。必须将仓库 ID 和部门 ID 分配给用户,每个用户只能有 1 个仓库 ID 和部门 ID
必须为每个工单分配类别 ID,以便每个工单只能有一个类别 ID
用户.cs
public class User
{
public int UserID { get; set; }
[StringLength(50, MinimumLength = 1)]
public string LastName { get; set; }
[StringLength(50, MinimumLength = 1, ErrorMessage = "First name cannot be longer than 50 characters.")]
[Column("FirstName")]
public string FirstMidName { get; set; }
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
public DateTime EnrollmentDate { get; set; }
public string FullName
{
get { return LastName + ", " + FirstMidName; }
}
// public int AdministratorID { get; set; }
// [ForeignKey("AdministratorID")]
// public virtual Administrator Administrator { get; set; }
public int AdminID { get; set; }
[ForeignKey("AdminID")]
public virtual Administrator Administrator { get; set; }
public int DepartmentID { get; set; }
[ForeignKey("DepartmentID")]
public virtual Department Department { get; set; }
public int DepotID { get; set; }
[ForeignKey("DepotID")]
public virtual Depot Depot { get; set; }
public int TicketID { get; set; }
public virtual ICollection<Ticket> Tickets { get; set; }
}
票.cs
public class Ticket
{
public string Issue { get; set; }
[DisplayFormat(NullDisplayText = "No Priority")]
public Priority? Priority { get; set; }
public int CategoryID { get; set; }
[ForeignKey("CategoryID")]
public virtual Category Category { get; set; }
public int TicketID { get; set; }
public int AdminID { get; set; }
public virtual ICollection<Administrator> Administrators { get; set; }
public int UserID { get; set; }
//[ForeignKey("TicketID")]
public virtual User User { get; set; }
}
仓库.cs
public class Depot
{
public int DepotID { get; set; }
[StringLength(50, MinimumLength = 1)]
public string Name { get; set; }
public virtual ICollection<User> Users { get; set; }
}
部门.cs
public class Department
{
public int DepartmentID { get; set; }
[StringLength(50, MinimumLength = 1)]
public string Name { get; set; }
public virtual ICollection<User> Users { get; set; }
}
类别.cs
public class Category
{
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int CategoryID { get; set; }
public string Title { get; set; }
public virtual ICollection<Ticket> Tickets { get; set; }
}
管理员.cs
public class Administrator
{
[Key, ForeignKey("User")]
public int UserID { get; set; }
public int AdminID { get; set; }
public int TicketID { get; set; }
[StringLength(50)]
public string AdminRole { get; set; }
public virtual ICollection<Ticket> Tickets { get; set; }
public virtual User User { get; set; }
}
配置.cs(种子法)
public Configuration()
{
AutomaticMigrationsEnabled = false;
}
protected override void Seed(RecreationalServicesTicketingSystem.DAL.IssueContext context)
{
var departments = new List<Department>
{
new Department { DepartmentID = 1, Name = "IT"},
new Department { DepartmentID = 2, Name = "Admin" },
new Department { DepartmentID = 3, Name = "Human Resources"},
new Department { DepartmentID = 4, Name = "Mechanics" },
new Department { DepartmentID = 5, Name = "Directors" },
new Department { DepartmentID = 6, Name = "Operations"}
};
departments.ForEach(s => context.Departments.AddOrUpdate(p => p.Name, s));
context.SaveChanges();
var depots = new List<Depot>
{
new Depot { DepotID = 1, Name = "Porana"},
new Depot { DepotID = 2, Name = "Far North"},
};
depots.ForEach(s => context.Depots.AddOrUpdate(p => p.Name, s));
context.SaveChanges();
var users = new List<User>
{
new User { FirstMidName = "Jason", LastName = "Wan",
EnrollmentDate = DateTime.Parse("2016-02-18"), DepartmentID = 1, DepotID = 1},
new User { FirstMidName = "Andy", LastName = "Domagas",
EnrollmentDate = DateTime.Parse("2016-02-18"), DepartmentID = 1,DepotID = 1},
new User { FirstMidName = "Denis", LastName = "Djohar",
EnrollmentDate = DateTime.Parse("2016-02-18"), DepartmentID = 1 ,DepotID = 1},
new User { FirstMidName = "Christine", LastName = "West",
EnrollmentDate = DateTime.Parse("2016-02-18"), DepartmentID = 1, DepotID = 1},
};
users.ForEach(s => context.Users.AddOrUpdate(p => p.FirstMidName, s));
context.SaveChanges();
users.ForEach(s => context.Users.AddOrUpdate(p => p.LastName, s));
context.SaveChanges();
var administrator = new List<Administrator>
{
new Administrator {AdminID = 1, AdminRole = "Administrator LVL1", User = users.Single ( s => s.UserID == 1),
Tickets = new List<Ticket>() },
new Administrator {AdminID = 2, AdminRole = "Administrator LVL2", User = users.Single ( s => s.UserID == 2),
Tickets = new List<Ticket>() },
new Administrator {AdminID = 3, AdminRole = "Administrator LVL3", User = users.Single ( s => s.UserID == 3),
Tickets = new List<Ticket>() }
};
administrator.ForEach(s => context.Administrators.AddOrUpdate(p => p.AdminID, s));
context.SaveChanges();
var categories = new List<Category>
{
new Category {CategoryID = 0001, Title = "Desktop"},
new Category {CategoryID = 0002, Title = "Mobile"},
new Category {CategoryID = 0003, Title = "Menzits"},
new Category {CategoryID = 0004, Title = "XMPRO"},
new Category {CategoryID = 0005, Title = "Con-X"},
new Category {CategoryID = 0006, Title = "Promapp"},
new Category {CategoryID = 0007, Title = "QGIS"},
};
categories.ForEach(s => context.Categories.AddOrUpdate(p => p.Title, s));
context.SaveChanges();
var tickets = new List<Ticket>
{
new Ticket {
UserID = users.Single(s => s.LastName == "Wan").UserID,
CategoryID = categories.Single(c => c.Title == "Con-X" ).CategoryID,
Issue = ("Test Error NEW TEST"),
AdminID = 1,
Priority = Priority.High
},
new Ticket {
UserID = users.Single(s => s.LastName == "Wan").UserID,
CategoryID = categories.Single(c => c.Title == "Desktop" ).CategoryID,
Issue = ("Test Error 2"),
AdminID = 2,
Priority = Priority.Med
},
};
foreach (Ticket e in tickets)
{
var ticketInDataBase = context.Tickets.Where(
s =>
s.User.UserID == e.UserID &&
s.Category.CategoryID == e.CategoryID).SingleOrDefault();
if (ticketInDataBase == null)
{
context.Tickets.Add(e);
}
}
context.SaveChanges();
}
}
问题上下文.cs
public class IssueContext : DbContext
{
public DbSet<User> Users { get; set; }
public DbSet<Ticket> Tickets { get; set; }
public DbSet<Category> Categories { get; set; }
public DbSet<Department> Departments { get; set; }
public DbSet<Administrator> Administrators { get; set; }
public DbSet<Depot> Depots { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
modelBuilder.Entity<Ticket>()
.HasMany(c => c.Administrators).WithMany(i => i.Tickets)
.Map(t => t.MapLeftKey("TicketID")
.MapRightKey("AdministratorID")
.ToTable("AdministratorsTickets"));
modelBuilder.Entity<Administrator>()
.HasKey(e => e.UserID);
modelBuilder.Entity<User>()
.HasOptional(s => s.Administrator) // Mark StudentAddress is optional for Student
.WithRequired(ad => ad.User); // Create inverse relationship
}
}
第一个音符:
您的Administrator
实体映射不正确。您将 UserId 标记为主键,因此当将实体插入到管理员表时,它会自动将 UserId 设置为新标识,因此在您的图片中,您会看到 UserId 递增。相反,您应该将管理员 ID 标记为主键:
[Key]
public int AdminID { get; set; }
[ForeignKey("User")]
public int UserID { get; set; }
还应将用户实体的用户 ID 标记为主键:
[Key]
public int UserID { get; set; }
第二个注意事项:
您的管理员和用户使用一对多关系映射到票证,这意味着 1 个管理员有 X 个票证,1 个用户有 Y 票证。这意味着,该票证表包含管理员实体和用户实体的外键:图片中的管理员 ID 和用户 ID。你可以在你的图片上看到它。如果你把TicketId留在两个类中,那将意味着你有一个一对一的关系以及一对多的关系,这真的很奇怪,而且行不通。 因此,您需要从用户和管理员表中完全删除 TicketId
第三点注意:
您还需要从 Ticket 实体添加对用户和管理员的直接引用,[ForeignKey]
属性添加到每个属性,并指定用作它们的外键的属性。您还需要删除管理员集合,因为如果您以这种方式离开它,您的关系将是多对多:
public class Ticket
{
public int TicketID { get; set; }
public string Issue { get; set; }
[DisplayFormat(NullDisplayText = "No Priority")]
public Priority? Priority { get; set; }
[ForeignKey("CategoryID")]
public virtual Category Category { get; set; }
public int CategoryID { get; set; }
[ForeignKey("AdminID")]
public Administrator Admin { get; set; }
public int AdminID { get; set; }
[ForeignKey("UserID")]
public User User { get; set; }
public int UserID { get; set; }
}
第四个注意事项:
此外,用户和管理员之间还有必需-可选一对一关系,其映射方式如下:
您的管理员实体具有 int UserId
,但您的用户实体没有管理员 ID,实体框架将从管理员端正确映射此关系。如果您像现在一样保留循环引用 - 管理员有 UserId 和用户有管理员 ID - 它会导致很多问题,因为据我所知,EF 以正确的方式不支持它。
应仔细阅读有关生成数据库架构的实体框架文档,否则将无法转换 SQL 架构以更正 EF 实体:
实体框架中的数据批注:
https://msdn.microsoft.com/en-us/data/jj591583
使用流畅的 API 配置/映射属性和类型
https://msdn.microsoft.com/en-us/data/jj591617.aspx