CODEWITHSUNDEEP
c#entity-framework-coreef-core-5.0
Hypenate
Hypenate

Reputation: 2064

Unable to fill the 'join table' on create/update

The issue:
I'm unable to let EF add entries to my join table named RoleUser, containing columns UsersId and RolesName, when creating a new user (see error 1 at the bottom).
I then tried creating the join class, but alas no salvation (see error 2 at the bottom).

My setup:
I have create a many-to-many relation in EF5 (so the join class should be obsolete) with the classes User and Role:

public class User
{
    public int Id { get; set; }
    public string Username { get; set; }
    public ICollection<Role> Roles { get; set; }
}

public class Role
{
    [Key]
    public string Name { get; set; }
    public ICollection<User> Users { get; set; }
}

The join table for Error 2:

[Table("RoleUser")]
public class RoleUserRelation
{
    [ForeignKey("UsersId")]
    public int UsersId { get; set; }

    [ForeignKey("RolesName")]
    public string RolesName { get; set; }
}

Using a very basic DbContext:

public class UserContext : DbContext
    {
        public DbSet<User> Users { get; set; }
        public DbSet<Role> Roles { get; set; }
        //public DbSet<RoleUserRelation> RoleUserRelations { get; set; } <-- Error 2

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // modelBuilder.Entity<RoleUserRelation>().HasNoKey(); <-- Error 2
        }
    }

✔ It works (shows the user with it's roles) when I try to do a GET:

[HttpGet]
public async Task<IActionResult> Get()
{
    var users = await _context.Users.Include(a => a.Roles).ToListAsync();

    return Ok(users);
}

❌ It fails when I do a create:

    [HttpPost]
    public IActionResult Create(User user)
    {
        _context.Add(user);
        _context.SaveChanges();

        return Ok();
    }

The errors:
Error 1:

SqlException: Violation of PRIMARY KEY constraint 'PK_Roles'. Cannot insert duplicate key in object 'dbo.Roles'. The duplicate key value is (Basic).

Error 2:

Cannot use table 'RoleUser' for entity type 'RoleUser (Dictionary<string, object>)' since it is being used for entity type 'RoleUserRelation' and potentially other entity types, but there is no linking relationship. Add a foreign key to 'RoleUser (Dictionary<string, object>)' on the primary key properties and pointing to the primary key on another entity typed mapped to 'RoleUser'.

The desired outcome:

Not try to insert insert the roles (it got them there in the first place) but add the user and for each role add a line to the join table.

Upvotes: 1

Views: 483

Answers (1)

Andrew Williamson
Andrew Williamson

Reputation: 8661

I'm not 100% sure about this, but I think when you attach an entity (the User) to the context, EF will also attach any child entities (the Role) in the same state as the parent. If you think the role already exists, you should be able to change the entity state of each role from 'Added' to 'Unchanged':

public IActionResult Create(User user)
{
    _context.Add(user);

    foreach (var role in user.Roles)
        _context.Entry(role).State = EntityState.Unchanged;

    _context.SaveChanges();

    return Ok();
}

Upvotes: 2

Related Questions