ASP.NET MVC实体框架代码优先：没有错误，但表未正确填充 [英] ASP.NET MVC Entity Framework code-first: no errors but tables aren't populated correctly

问题描述

我目前正在使用MVC4实体框架制作票务系统。我已经在SQL服务器中填充了表格，但是只有几列是不正确的。首先，我想对代码量表示歉意，但是它们都是引用所必需的！

I'm currently making a ticketing system using MVC4 Entity Framework. I've populated my tables in my SQL server but there's only a few columns that aren't correct. Firstly I want to apologise for the amount of code but they are all needed for referencing!

我只希望人们检查我是否正确映射了模型，因为在图像中（1）缺少某些值。

I only want people to check if I'm mapping my model properly because in image(1) some values are missing.

非常重要的是，您可以看一下下面提供的两张图片，以更清晰地了解我要完成的工作。

It is very important that you look at the two images provided below for a clearer picture as to what I'm trying to accomplish.

图片（1）此处显示我希望我的表看起来像什么，并显示每个类的所有主键和外键。

The image(1) Here shows what I want my tables to be like and shows all the Primary and Foreign keys for each of my classes.

此图像（2）这里显示了我的实体图
所以我的理想情况是：
用户可以创建多张票证并且只能将它们分配给一位管理员（解决了票证上的问题）。

This image(2)Here shows my entity diagram So my ideal situation is : User can create multiple tickets and they can only assign them to one admin(Which fixes the issue the is on the ticket).

每个票证都将由TicketID标识，因此在Admin表上应该具有AdminID，UserID（提交票证），TicketID和AdminRole。

Each Tickets will be identified by the TicketID so on the Admin table it should have the AdminID,UserID(The person who submitted the ticket), TicketID and AdminRole.

可以将每个AdminID分配给一个用户，但并非必须将所有用户都分配给一个AdminID

Each AdminID can be assigned to one user but not all user must be assigned to one AdminID

DepotID， DepartmentID和Category非常简单。
必须将DepotID和DepartmentID分配给一个用户，每个用户只能有1个DepotID和DepartmentID

DepotID,DepartmentID and Category are pretty straight forward. DepotID and DepartmentID must be assigned to a user and each user can only have 1 DepotID and DepartmentID

必须为每张票证分配CategoryID，因此每张票证只能拥有一个CategoryID

CategoryID must be assigned to each ticket so each ticket can only have one CategoryID

User.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; }

}

Ticket.cs

Ticket.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; }

}

Depot.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; }
}

Department.cs

Department.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; }
}

Category.cs

Category.cs

public class Category
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int CategoryID { get; set; }
    public string Title { get; set; }

    public virtual ICollection<Ticket> Tickets { get; set; }
}

Administrator.cs

Administrator.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; }
}

Configuration.cs（种子方法）

Configuration.cs(Seed method)

    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();
    }
}

IssueContext.cs

IssueContext.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


        }
    }

推荐答案

首先注意：

您的管理员实体映射不正确。您将UserId标记为 PrimaryKey ，因此，当实体插入Administrator表时，它会自动将UserId设置为新标识，因此在您的图片中您会看到UserId逐渐增加。相反，您应该将AdminID标记为主键：

Your Administrator entity mapping is incorrect. You are marking UserId as a PrimaryKey, so when an entity is inserted to Administrator table, it's automatically sets UserId as a new identity, so in your picture you see UserId increasing incrementally. Instead you should mark your AdminID as primary key:

[Key]
public int AdminID { get; set; }

[ForeignKey("User")]
public int UserID { get; set; } 

还应将User实体的UserID标记为主键：

You should also mark UserID for your User entity as a primary key:

[Key]
public int UserID { get; set; }

第二注：

您的管理员和用户使用一对多关系映射到票证，这意味着 1位管理员拥有X张票证，而1位用户拥有Y张票证。这意味着，票证表包含指向管理员实体和用户实体的外键：图片中的AdminID和UserID。您可以在图片上看到它。如果您在这两个类中都保留TicketId，则意味着您与一对一的关系以及一对多的关系确实很怪异，无法正常工作。因此，您需要从用户和管理员表中完全删除TicketId

Your Administrator and User are mapped to tickets using one to many relationship, which means that 1 administrator has X tickets, and 1 user has Y tickets. That means, that Ticket table contains foreign keys to Administrator entity and to User entity: AdminID and UserID in your picture. You can see it on your picture. If you leave TicketId in both classes, that would mean that you have a one to one relationship along with one to many which is really weird and not gonna work. So you need to remove TicketId fully from your User and Administrator tables

第三条注意：

您还需要要从您的票证实体添加对User和Administrator的直接引用，请在每个属性中添加 [ForeignKey] 属性，并为它们指定用作外键的属性。您还需要删除Administrators集合，因为如果这样离开，您的关系将多对多：

You would also need to add direct references to User and to Administrator from your Ticket entity, add [ForeignKey] attribute to each and specify the property you are using as a foreign key for them. You also need to remove Administrators collection, since if you leave it that way your relationship will be many to many:

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; }    
}

第四注：

您还具有用户和管理员之间的必选（一对一）关系，该关系按以下方式映射：

Also you have Required-Optional one to one relationship between User and Administrator, which maps in the following way:

您的管理员实体具有 int UserId ，但是您的User实体没有AdministratorId，Entity Framework将从Administrator端正确映射此关系。如果您像现在那样保持循环引用-管理员具有UserId和用户具有AdministratorId-就会引起很多问题，因为据我记得，EF并没有以正确的方式支持它。

Your Administrator entity has int UserId, but your User entity does not have AdministratorId, Entity Framework will correctly map this relationship from the side of Administrator. If you keep circular reference like you have now - Administrator has UserId and User has AdministratorId - it will cause a lot of issues, since it's unsupported by EF in a correct way, as far as I remember.

您应该仔细阅读有关生成数据库架构的Entity Framework文档，否则您将无法转换SQL架构以更正EF实体：

You should carefully read Entity Framework documentation about generating database schema, otherwise you will not be able to transform your SQL schema to correct EF entities:

实体框架中的数据注释：

Data annotations in Entity Framework:

https://msdn.microsoft.com/zh-cn/data/jj591583

使用Fluent API配置/映射属性和类型

Configuring/Mapping Properties and Types with the Fluent API

https://msdn.microsoft。 com / en-us / data / jj591617.aspx

这篇关于ASP.NET MVC实体框架代码优先：没有错误，但表未正确填充的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！