1

I'm building an ASP.NET core REST server. I need to know how to properly update many-to-many relations between my entities - I want to add, delete and update some records in a linking table via Entity Framework core.

My code :

User model

public class User
{
    [Key]
    public int IDUser { get; set; }
    [Required]
    public string Forename { get; set; }
    [Required]
    public string Name { get; set; }
    public string AvatarPath { get; set; }
    public string Email { get; set; }
    public string PhoneNumber { get; set; }
    public string Password { get; set; }
    public User CreatedBy { get; set; }
    public DateTime CreatedAt { get; set; }

    public List<UserPermission> UsersPermissions { get; set; }
}

Permission model

public class Permission
{
    [Key]
    public int IDPermission { get; set; }
    public string Name { get; set; }

    [Required]
    public int IDMachine { get; set; }
    public Machine Machine { get; set; }

    public List<UserPermission> UsersPermissions { get; set; }
}

UserPermission model - for a linking tabel

public class UserPermission
{
    [Key]
    public int IDUserPermission { get; set; }

    public int IDUser { get; set; }
    public int IDPermission { get; set; }
    public User User { get; set; }
    public Permission Permission { get; set; }
}

DbContext

public class DBContext : DbContext
{
    public DBContext(DbContextOptions<DBContext> options)
        : base(options)
    {

    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
           .HasIndex(u => u.Email)
           .IsUnique();

        modelBuilder.Entity<UserPermission>()
            .HasIndex(bp => new { bp.IDPermission, bp.IDUser })
            .IsUnique();

        modelBuilder.Entity<UserPermission>()
            .HasOne(up => up.User)
            .WithMany(u => u.UsersPermissions)
            .HasForeignKey(up => up.IDUser);

        modelBuilder.Entity<UserPermission>()
            .HasOne(up => up.Permission)
            .WithMany(p => p.UsersPermissions)
            .HasForeignKey(up => up.IDPermission);

        base.OnModelCreating(modelBuilder);
    }

    public DbSet<Machine> Machines { get; set; }
    public DbSet<Permission> Permissions { get; set; }
    public DbSet<User> Users { get; set; }
    public DbSet<UserPermission> UsersPermissions { get; set; }
}

PermissionsController - HttpGet

[HttpGet("{id}", Name = "GetPermissionByID")]
public async Task<IActionResult> GetPermissionByID(Int32 id)
{
    try
    {
        var permission = await _context.Permissions.Include(p => p.UsersPermissions).FirstOrDefaultAsync(s => s.IDPermission == id);

        if (permission == null)
        {
            return NotFound();
        }
        else
        {
            return new ObjectResult(permission);
        }
    }
    catch (Exception ex)
    {
        Helpers.ExceptionLogger.LogException(ex);
        return StatusCode(500);
    }

}

PermissionController - HttpPut

[HttpPut("{id}")]
public async Task<IActionResult> UpdatePermission(int id, [FromBody]Permission permission)
{
    if (permission == null || permission.IDPermission != id)
    {
        return BadRequest();
    }

    try
    {
        var permissionToupdate = await _context.Permissions.Include(p => p.UsersPermissions).AsNoTracking().FirstOrDefaultAsync(u => u.IDPermission == id);
        if (permissionToupdate == null)
        {
            return NotFound();
        }

        permissionToupdate.IDMachine = permission.IDMachine;
        permissionToupdate.IDPermission = permission.IDPermission;
        permissionToupdate.Machine = permission.Machine;
        permissionToupdate.Name = permission.Name;
        permissionToupdate.UsersPermissions = permission.UsersPermissions; // it doesn't actually work

        _context.Permissions.Update(permissionToupdate);
        await _context.SaveChangesAsync();
        return new NoContentResult();
    }
    catch (Exception ex)
    {
        Helpers.ExceptionLogger.LogException(ex);
        return StatusCode(500);
    }
}

When I call HttpPut action and try to update UsersPermissions tabel in that line permissionToupdate.UsersPermissions = permission.UsersPermissions; EF don't generate correct query to SQL Server. I would like to be able to add, delete and update some records in that linking tabel by modifying UsersPermissions navigation property. It seems I do something wrong or don't understand how to achieve this. Can someone guide me what are my options ? How to add, delete and update linking tabel in my case ?

I don't know what good practices say but I just wonder : how would you manage that linking tabel UsersPermissions between entities User and Permissions ?

  • 1) by creating separate controller UsersPermissionsController specifically to that linking tabel UsersPermissions in order to add, delete, update records ?
  • 2) by updateing navigation property Permission.UsersPermissions in Permission entity in order to add, delete, update records ?
1
  • Why are you using .AsNoTracking() on an update? Commented Feb 7, 2018 at 22:24

1 Answer 1

4

You can't just replace one list with another. Due to the change tracking EF Core uses, this looks like you're issuing a delete on everything in the initially list, and then and add on everything the new list. Then, since some of the ones that are being "added" are actually existing, EF ends up with conflicted entity states.

Long and short, you need to selectively remove items that have been removed and add items that are new, leaving the existing relationships alone.

permissionToUpdate.UserPermissions
    .Except(permission.UserPermissions)
    .ToList()
    .ForEach(x => permissionToUpdate.UserPermissions.Remove(x));

permission.UserPermissions
    .Except(permissionToUpdate.UserPermissions)
    .ToList()
    .ForEach(x => permissionToUpdate.UserPermissions.Add(x));
Sign up to request clarification or add additional context in comments.

1 Comment

Since he is fetching the existing entity with AsNoTracking(), should change tracking be an issue?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.