Relational Database Mapping in MVC

2019-07-17 07:38发布

问题:

I apologize for the amount of code but I just need some confirmation of my mapping and usage of [ForeignKey("XXX") is correct or not.

In classes like Department and Depot do I need the lines public int DepartmentID { get; set; } or do they automatically number themselves when data is inserted in the database. (DepartmentID = 1 (Human Resources), DepotID = 2 (Los Angeles)

This is my Entity Diagram.

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 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; }
    //Setting up relationships A use can apply for any number of tickets, so Tickets is defined as a collection of Ticket entities.
    public virtual ICollection<Ticket> Users { get; set; }

}

Ticket.cs

public class Ticket
{
    public string Issue { get; set; } 
    [DisplayFormat(NullDisplayText = "No Priority")]
    public Priority? Priority { get; set; }
    //Category One to Many Ticket
    public int CategoryID { get; set; }
    [ForeignKey("CategoryID")]
    public virtual Category Category { get; set; }
    //User (One to Many) Ticket
    public int UserID { get; set; }
    public int TicketID { get; set; }
    [ForeignKey("TicketID")]
    public virtual User User { get; set; }
    public int AdminID { get; set; }
    public virtual ICollection<Administrator> Administrators { get; set; }

}

Depot.cs

public class Depot
{
    public int DepotID { get; set; }

    [StringLength(50, MinimumLength = 3)]
    public string Name { get; set; }

    public virtual ICollection<User> Users { get; set; }

}

Department.cs

public class Department
{
    public int DepartmentID { get; set; }

    [StringLength(50, MinimumLength = 3)]
    public string Name { get; set; }

    public virtual ICollection<User> Users { get; set; }
}

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

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

回答1:

The ID property will become the primary key column of the database table that corresponds to this class. By default, the Entity Framework interprets a property that's named ID or classnameID as the primary key.

See below for Depot and User relationships (notice how DepotID and Depot in User are marked as virtual to enable lazy loading)

public class Depot
{      
    public int DepotID { get; set; }

    [StringLength(50, MinimumLength = 3)]
    public string Name { get; set; }

    public virtual ICollection<User> Users { get; set; }

}

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; }
    public virtual Administrator Administrator { get; set; }

    public int DepartmentID { get; set; }
    public virtual Department Department { get; set; }

    public virtual int DepotID { get; set; }

    [ForeignKey("DepotID")]
    public virtual Depot Depot { get; set; }

    public int TicketID { get; set; }
    public virtual ICollection<Ticket> Users { get; set; }

}