Problem modelling relationship in Entity Framework using code first

Question

I'm trying to learn code first within the Entity Framework and am having trouble modelling a relationship. It's a basic HR database which for the sake of this has two entities, Employees and Departments.

The Employee belongs to a department and the department has a Team Administrator and a Manager, both of whom are in effect employees. I've tried to model this using the following:

EMPLOYEE

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

Context:

modelBuilder.Entity<Employee>().HasOptional(x => x.Department);

DEPARTMENT

public class Department
{
    [Required]
    public int DepartmentID { get; set; }

    [Required(ErrorMessage = "The description is required.")]
    public string Description { get; set; }

    public int? ManagerID { get; set; }
    public virtual Employee Manager { get; set; }

    public int? TeamAdministratorID { get; set; }
    public virtual Employee TeamAdministrator { get; set; }
}

Context:

modelBuilder.Entity<Department>().HasOptional(x => x.Manager);
modelBuilder.Entity<Department>().HasOptional(x => x.TeamAdministrator);

Obviously I would want the Department table to have only four columns - DepartmentID, Description, ManagerID and TeamAdministratorID but it is generating an extra two for the relationship, namely Manager_EmployeeID and Team_Administrator_EmployeeID. Also, in the Employee table the column Department_DepartmentID is generated to store the DepartmentID instead of it using the DepartmentID column I specified in the entity.

What am I doing wrong? How do I need to define the fields and relationships to avoid having code first ignore what I specify and generate it's own navigation fields in the database?

Ladislav Mrnka · Accepted Answer · 2011-06-12 13:37:00Z

9

That because your model configuration is incomplete - you started your own mapping with Fluent API so you must tell EF that these properties are indeed FKs for relations. For employee use:

modelBuilder.Entity<Employee>()
            .HasOptional(x => x.Department)
            .WithMany()
            .HasForeignKey(x => x.DepartmentID);

And for department use:

modelBuilder.Entity<Department>()
            .HasOptional(x => x.Manager)
            .WithMany()
            .HasForeignKey(x => x.ManagerID);
modelBuilder.Entity<Department>()
            .HasOptional(x => x.TeamAdministrator);
            .WithMany()
            .HasForeignKey(x => x.TeamAdministratorID);

Btw. without collection navigation properties on opposite side of relations it will be hard to use model (all WithMany are empty). At least Department should have:

public virtual ICollection<Employee> Employees { get; set;}

And mapping should be modified to:

modelBuilder.Entity<Employee>()
            .HasOptional(x => x.Department)
            .WithMany(y => y.Employees)
            .HasForeignKey(x => x.DepartmentID);

answered Jun 12, 2011 at 13:37

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MC76 Over a year ago

Now I see it written like that it makes far more sense to me. Thanks so much for that, it's all working perfectly now.

danludwig Over a year ago

Also, if you want to avoid having foreign key properties in your model, you can use .Map(x => x.MapKey("ForeignKeyColumnName")) instead of .HasForeignKey(x => x.ForeignKeyPropertyName)

oleksii · Accepted Answer · 2011-06-12 13:25:56Z

0

See your employee class

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

In order to show the relationship between an employee and a department you used ID and Department. In reality you only need to do this once - via Department. EF by default searches for the ID property and links two classes for you. So your classes should only include one ID - ID of the class itself. Try removing IDs to the other classes.

answered Jun 12, 2011 at 13:25

oleksii

36.1k16 gold badges98 silver badges173 bronze badges

Collectives™ on Stack Overflow

Problem modelling relationship in Entity Framework using code first

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related