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
?
UsersPermissionsController
specifically to that linking tabel UsersPermissions
in order to add, delete, update records ?Permission.UsersPermissions
in
Permission
entity in order to add, delete, update records ?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));