ASP.NET MVC Entity Framework code-first: no errors but tables aren't populated correctly

Question

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!

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.

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.

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

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.

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

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

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

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

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

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

   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


        }
    }

Red Red · Accepted Answer · 2016-03-04T12:49:12

First Note:

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

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

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

Second Note:

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

Third Note:

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

Fourth Note:

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

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.

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/en-us/data/jj591583

Configuring/Mapping Properties and Types with the Fluent API

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

ASP.NET MVC Entity Framework code-first: no errors but tables aren't populated correctly

1 Answers