Updating and managing many-to-many relation in Entity Framework core

asp.net-core asp.net-mvc c# entity-framework-core

Question

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
0
2/7/2018 1:27:47 PM

Popular Answer

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));
2
2/7/2018 1:48:02 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow