Entity Framework and relationships

Question

I'm using EF 5 rc on VS 2012 RC and got some issues. Pretty sure it's got to do with my knowledge in databases and EF than the version numbers of the software I use :)

So, I have 3 classes. User, Role and Right.

User class

public class User
{
    [Key]
    public int UserId { get; private set; }

    [Required]
    public string EmailAddress { get; internal set; }

    [Required]
    public string Username { get; internal set; }

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

    // More properties 
}

Right Class

public class Right
{
    public virtual int RightId { get; set; }
    public virtual string Description { get; set; }
}

Role Class

public class Role
{
    public virtual int RoleId { get; set; }
    public virtual string Description { get; set; }

    public virtual ICollection<Right> Rights { get; set; }
}

Context

 class MyContext : DbContext
 {
    public virtual DbSet<User> Users { get; set; }
    public virtual DbSet<Role> Roles { get; set; }
    public virtual DbSet<Right> Rights { get; set; }
 }

Now, I want to add roles to a user, and rights to a role. But I also want to make sure it's possible to add the same Right can be added to different roles.

 var role1 = new Role()
{
     Description = "role1"
};

var role2 = new Role()
{
    Description = "role2"
};

var right = new Right()
{
    Description = "right"
};

context.Rights.Add(right);
context.Roles.Add(role1);
context.Roles.Add(role2);

role1.Rights = new List<Right>();
role2.Rights = new List<Right>();
role1.Rights.Add(right);
role2.Rights.Add(right);

 /**** ERROR ****/
context.SaveChanges();

I'm getting

InvalidOperationException: Multiplicity constraint violated. The role 'Role_Rights_Source' of the relationship 'Role_Rights' has multiplicity 1 or 0..1.

What am I doing wrong ? Also, I don't feel right about creating a new list like

 role1.Rights = new List<Right>();
 role2.Rights = new List<Right>();

What's the recommended way to do this ? Rights property is null. So I can't add anything to it without newing it up.

Ladislav Mrnka · Accepted Answer · 2012-07-23 12:18:02Z

1

The problem is the convention used by EF to infer the relation. It thinks that the relation is one-to-many but you want many-to-many (role can have multiple rights and the right can be used in multiple roles).

There are two options to solve this:

Option 1: Create navigation property in Right:

public class Right
{
    public virtual int RightId { get; set; }
    public virtual string Description { get; set; }

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

Now EF convention will detect the collection on both sides of the relation and correctly use many-to-many multiplicity instead of one-to-many

Option 2: Use Fluent-API to tell EF that you want many-to-many relation:

public class MyContext : DbContext
{
    public virtual DbSet<User> Users { get; set; }
    public virtual DbSet<Role> Roles { get; set; }
    public virtual DbSet<Right> Rights { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Role>()
                    .HasMany(r => r.Rights)
                    .WithMany();
    }
}

Now EF knows that the Right can be assigned to multiple roles even through the Right doesn't have navigation property to Role.

If the Role can be assigned to multiple users you will have to use many-to-many relation as well

edited Jul 23, 2012 at 12:18

answered Jul 23, 2012 at 11:04

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

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

2 Comments

Madushan Over a year ago

Wow, you just solved the issue keeping me awake for the last 3 days. (shows how great I am with EF.) I assume that, with option 1 if I add a role to a right, EF will also map that the Role will have the right ?

Ladislav Mrnka Over a year ago

Yes - EF will internally create junction table in database to work with many-to-many relation.

Judo · Accepted Answer · 2012-07-23 11:09:56Z

1

Ladislav's Answer should work well for the error.

On your issue of being uncomfortable with using:

role1.Rights = new List<Right>();

You should just initialize the these properties to new Lists in the constructor, then it will be done for all instances of Role:

public class Role
{
    public virtual int RoleId { get; set; }
    public virtual string Description { get; set; }

    public virtual ICollection<Right> Rights { get; set; }

public Role ()
{
this.Rights =  new List<Right>();    
}
}

answered Jul 23, 2012 at 11:09

Judo

5,2474 gold badges26 silver badges36 bronze badges

2 Comments

Madushan Over a year ago

Thanks a lot for that... That'll save me a lot of null checks... :)

Judo Over a year ago

If you need a lot of this don't after you have generated a database with the model. Just work backwards by recreating the model from the database and EF will add these automatically with some Fluent mappings too sql-server-performance.com/2012/…

Collectives™ on Stack Overflow

Entity Framework and relationships

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related