Microsoft.EntityFrameworkCore.DbUpdateException: 'An error occurred while saving the entity cha-CodePudding

I'm having issues when I try and add a record to my DB. The view is registration and the following error occurs on _db.SaveChanges() when trying to add to Secretaries/Student.

SqlException: Invalid column name 'UsersUserID'.

Screenshot of error(1). Screenshot of Error(2)

I manually added changes to the Migration/Migration Snapshot and removed the column UsersUserID as it duplicated FK UserID. I've tried searching for the column but there are no occurrences. Deleting migrations and creating a new DB does not solve the problem either. Does anyone have any idea on how to solve this problem?

Here is my Users Model.

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

        [Required]
        public string Name { get; set; }

        [Required]
        public string Surname { get; set; }        
        
        public string Password { get; set; }
        
        public string Email { get; set; }

        public string AddressLine1 { get; set; }

        public string AddressLine2 { get; set; }

        public int SuburbID { get; set; }

        public string Title { get; set; }

        public int RoleID { get; set; }  

        public string ErrorMessage { get; set; }

        public virtual ICollection<Secretaries> Secretaries { get; set; }
        public virtual ICollection<Student> Student { get; set; }

        public virtual Suburb Suburb { get; set; }

        public virtual Role Role { get; set; }
    }

Here is my Student Model

public class Student
    {
        [Key]
        public int StudentID { get; set; }

        public int UserID { get; set; }

        [Required]
        public DateTime DateJoined { get; set; } = DateTime.Now;

        [StringLength(1)]
        [Required]
        public char Is_Active { get; set; } = 'T';

        public virtual Users Users { get; set; }
    }

Here is my secretaries model

public class Secretaries
    {
        [Key]
        public int SecretaryID { get; set; }

        public int UserID { get; set; }

        [Required]
        public DateTime DateJoined { get; set; } = DateTime.Now;

        [StringLength(1)]
        [Required]
        public char Is_Active { get; set; } = 'T';

        public virtual Users Users { get; set; }
    }

This is the section where I get the error

public IActionResult Registration(Users users)
        {

            try
            {
                //users.Role.RoleID;
                _db.Users.Add(users);
                
                //_db.SaveChanges();
                int role = users.RoleID;
                int id = users.UserID;               

                if (role == 1)
                {
                    Student student= new Student();

                    student.UserID = id;

                    _db.Student.Add(student);
                    _db.SaveChanges();
                }
                else if (role == 2)
                {
                    Secretaries secretary = new Secretaries();

                    secretary.UserID = id;                    
                    
                    _db.Secretaries.Add(secretary);
                    _db.SaveChanges();
                }           

                return RedirectToAction("Index", "Home");
            }
            catch
            {
                return View(users);
            }
        }

CodePudding user response：

To start, naming convention would help around losing the Plural for the entity. "Users" implies multiple, and your naming is inconsistent given "Student". EF can manage details like having a table name "Users" with an entity named "User". Worst case you can use the [Table("Users")] attribute if there are naming issues. The entity can use a meaningful name even if it doesn't match what the table is called.

The issue you are likely running into is that while EF can auto-resolve some relationships between entities and things like FKs by convention, unless you follow the known conventions closely, EF can miss some details requiring you to provide them. Personally I opt to keep configuration pretty deliberate and consistent, not to rely on convention and the occasional surprise when it doesn't work. In your case two likely issues is marrying the User to their declare FK, and then also the bi-directional mapping.

With the entity and property named "Users", EF likely cannot match this to the UserId by convention, at best it would probably be looking for "UsersId", so when it didn't find that it would look for TableNamePKName so "UsersUserID" where "Users" is the table name and "UserID" is the PK in that table. To resolve that:

[ForeignKey("UserID")]
public virtual Users Users { get; set; }

This tells EF where to look. If this is a later version of C# then:

[ForeignKey(nameof(UserID))]

... to avoid magic strings if available.

The next detail that may trip up the mapping will be the bi-directional references where a Secretary has a User and User has a collection of secretaries. Bi-directional references should be avoided unless they serve a very clear and useful purpose. In most cases they are not needed, but where you do you them it helps to declare the inverse side of the relationship:

[ForeignKey("UserID")]
[InverseProperty("Secretaries")]
public virtual Users Users { get; set; }

This tells EF to expect a navigation property called "Secretaries" on the Users entity which will link via this User reference.

Bi-directional references can cause issues and require more attention when updating entities, especially if you have code that does something like serialize an entity. They often aren't needed as you can typically query data from one side of the relationship.

For instance to get all Secretaries associated to a given user you might think you need something like:

var user = context.Users.Include(u => u.Secretaries).Single(u => u.UserID == userId);
var secretaries = user.Secretaries;

... when instead you can use:

var secretaries = context.Secretaries.Where(s => s.User.UserID == userId).ToList(); // or (s => s.UserID == userId) where the UserID FK is exposed.

Depending on the purpose of the association you schema may better be served by inheritance where instead of a "has a" relationship, the Secretary/Student to User becomes an "is a" relationship. Does a User "have" Secretaries/Students, or is it more that a User "Is a" Secretary vs. a Student? Alternatively, is "Student" vs. "Secretary" more of a "Role" that can be held by a User? (Which suits a case where one user might be both a Student and a Secretary)

With the schema you currently have defined, it would allow one user to be potentially associated with many Students, as well as many Secretaries which may, or may not be the desired and intended use. (Also known as Many-to-One relationships)

Edit: To implement a schema where a User can be one of these Types or Roles there are a couple of options.

Inheritance: Where a User "is a" Teacher or "is a" Student etc. EF can support inheritence where you would have something like:

public abstract class User
{
    public int UserId { get; set; }
    // other common fields...
}

public class Student : User
{
    // Student-specific fields...
}

public class Secretary : User
{
   // Secretary-specific fields...
}

From here you can configure EF to either use a single table (Users) with a discriminator column (Think "UserType" or "Role" to indicate Student vs. Secretary, etc.) or to use a User Table plus Student and Secretary tables. You can learn more about inheritance options by looking up "Table-per-Hierarchy" or "Table-per-Concrete Type" in relation to EF. If the different types of users have different fields then Table-per-concrete type is generally a better option. Table-Per-Hierarchy can work but you end up with several null-able-columns as the table needs to cater to the lowest denominator.

If the different types of users consist of the exact same data then you could use something called a Many-to-one relationship between a User table, and a UserType or a UserRole table which would identify whether the user was a Student, Teacher, etc. This can be either Many-to-One, (many users can be the same role, but each user can have only 1 role) or it can be adapted to many-to-many if users could later hold 2 or more roles.

Many-to-one:

public class User
{
    public int UserId { get; set; }
    public int RoleId { get; set; } 

    [ForeignKey(nameof(RoleId))]
    public virtual Role Role { get; set; }
}

public class Role
{
    public int RoleId { get; set; }
    public string Name { get; set; }
}

Many-to-Many

public class User
{
    public int UserId { get; set; }
    public int RoleId { get; set; } 

    public virtual ICollection<Role> Roles { get; set; } = new List<Role>();
}

public class Role
{
    public int RoleId { get; set; }
    public string Name { get; set; }
}

This would need to be configured with a .HasMany(x => x.Roles).WithMany() relationship nominating a joining table (I.e. UserRoles) consisting of a UserId and RoleId. Optionally in a Many-to-Many relationship you can define the joining table as an entity if there are details about the relationship you want to access. (beyond just the keys relating the entities)

The other advantage of using a Many-to-One or Many-to-Many vs. inheritance is that it's easy to express optional roles, where a user might not be any of the roles. You can still adapt something like a Many-to-One implementation to have Student-specific data vs. Teacher-specific data by introducing a One-to-zero-or-one relationship between User and a table like StudentUserDetails on the UserId shared by both tables.

Ultimately there are a number of ways you can manage relational relationships for data in a database where EF can be configured to understand and map those relationships either within, or between entities.