What do I need to add into OnModelCreating(DbModelBuilder modelBuilder) function to define relations between Person and Role?

Question

I'm using EntityFramework version 5.0 in WinForms project, .net 4.5.

I have created 2 for me important Entities

    public class Role
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public string Name { get; set; }
        public bool StockPermission { get; set; }
        public bool ItemPermission { get; set; }
        public bool OrderPermission { get; set; }
        public bool PersonPermission { get; set; }
        public bool StatisticPermission { get; set; }
    }

    public class Person
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        public String Name { get; set; }
        public String Nickname { get; set; }
        public String Contact { get; set; }
        public System.DateTime Created { get; set; }
        public String Pincode { get; set; }

        public virtual ICollection<Role> Role { get; set; }
        public virtual Person Creator { get; set; }
    }

and dbContext class:

    public class SusibarDbContext : DbContext
    {
        public DbSet<Entity.Role> Roles { get; set; }
        public DbSet<Entity.Person> Persons { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            //base.OnModelCreating(modelBuilder);
        }
    }

please, can you help me what I need to add into OnModelCreating(DbModelBuilder modelBuilder) function to define relations between Person and Role?

Person can have many Role(s) (but can't be null), different Persons can have same Role(s).

Person can have one "creator" Person (can be null), different Persons can have same "creator"

If you could be so kind, just advise me solution :-(

Solution 1

If you want to use Fluent API for it, look at this topic from MSDN. You should use Many-to-Many relationship and EF will create a table required for case when Person can have many Roles and Role have many Persons. Something like this:

modelBuilder.Entity<Person>().HasMany(x => x.Roles).WithMany();

Also you can create this relationship without using Fluent API. You should create navigation property ICollection<Person> Persons in Role class and EF will create appropriate table and relationship as well.

Solution 2

Something like this should do the job:

Create a POCO called PersonRole. This is intended to model the relationship between a Person and a Role.

public class PersonRole
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public Person Person { get; set; }
    public Role Role { get; set; }
}

In the Person class, replace:

public virtual ICollection<Role> Role { get; set; }

with:

public virtual ICollection<PersonRole> PersonRoles { get; set; }

If you want to, you could add the following to the Role class:

public virtual ICollection<PersonRole> PersonRoles { get; set; }

doing so is optional, though it may be useful if you are wishing to look at all People with a particual Role.

In the OnModelCreating method, use this code to ensure that a PersonRole will enforce non-nullable Person and Role properties.

modelBuilder.Entity<PersonRole>().HasRequired(p => p.Person);
modelBuilder.Entity<PersonRole>().HasRequired(p => p.Role);

Edit:

The reason for creating the PersonRole POCO is to ensure that a Role can be reused across different users. Using the existing public virtual ICollection<Role> Role { get; set; } will work, but it will probably not work as intended.

With the relationship public virtual ICollection<Role> Role { get; set; }, what EF is going to do is augment the Role table with an additional field, e.g., PersonId, which will be used to relate a Person to their Roles. The problem with this is clear: without the linking PersonRole table, you won't be able to give two people the same Role.

Solution 3

Although not an answer to your question directly, use EF naming conventions to avoid Annotaions and keep your entity classes clean. I will demonstrate 2 situations:

One - Many using EF Naming Convention

Role has Many Persons, Person has one Role

One - Many Where No Naming convention Exists (Parent-Child of same Type)

Person has many Created, Person has one Creator)

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

    public virtual ICollection<Person> Persons { get; set; }
}

public class Person
{
    public int PersonId { get; set; }
    public int CreatorId { get; set; } 
    public int RoleId { get; set; } 
    ...

    public virtual Role Role { get; set; }
    public virtual Person Creator { get; set; }
    public virtual ICollection<Person> Created { get; set; }
}

For the Creator-Created relationship in OnModelCreating:

modelBuilder.Entity<Person>()
    .HasOptional(p => p.Creator)
    .WithMany(p => p.Created)
    .HasForeignKey(p => p.CreatorId);

With, "ClassName" + "Id" (case sensitive), EF will assume that it is the Primary Key / Identifier automatically. The Role-Person relationship will be created automatically due to the Virtual Mapping combined with the PrimaryKey "RoleId"

Comments

[eCorke]

Ok, I've solved it! in few minutes I'll add solution (lunch first)

[eCorke]

thanks for you advice, but I don't think that this is the best idea. Creating relation entity in Entity typed model. EntityFramework should take relations problem out, not create new one, and new entities.

[eCorke]

Isn't there any another solution? Some kind of setting, that one role can have many persons (realtion one to many, but make Person to have that roles in list? I know how to create it with realtions entities, but I thought that this can Entity Framework create in backened.

[srcnaks]

@nick_w, how would we ensure the uniqueness of {Person_ID,Role_ID} pair stored in PersonRole table?

[Hedego]

----builder.Entity<TriviaOption>() .HasKey(o => new { o.QuestionId, o.Id }); builder.Entity<TriviaAnswer>() .HasOne(a => a.TriviaOption) .WithMany() .HasForeignKey(a => new { a.QuestionId, a.OptionId }); builder.Entity<TriviaQuestion>() .HasMany(q => q.Options) .WithOne(o => o.TriviaQuestion);