I have created many to many relationships between Users and their roles. whenever I add the role to the user it works fine if the user and the role were not previously inserted (same key), but I can't read the middle table. If I search for the connection whit a user or with a role result is always 0.
how can I fix this?
public User()
{
Roles = new HashSet<Role>();
}
public virtual ICollection<Role> Roles { get; set; }
public Role()
{
Users = new HashSet<User>();
}
public virtual ICollection<User> Users { get; set; }
SomeDbContext :
public virtual DbSet<User> Users { get; set; } = null!;
public virtual DbSet<Role> Roles { get; set; } = null!;
modelBuilder.Entity<User>(entity =>
{
entity.ToTable("users");
entity.HasIndex(e => e.id, "users$UNIQ_34634663476347")
.IsUnique();
entity.HasIndex(e => new { e.CrmId, e.CrmResident }, "users$crm_unique")
.IsUnique();
entity.Property(e => e.Id).HasColumnName("id");
entity.Property(e => e.CrmId)
.HasColumnType("numeric(20, 0)")
.HasColumnName("crm_id");
entity.HasMany(d => d.Roles)
.WithMany(p => p.Users)
.UsingEntity<Dictionary<string, object>>("UserRole",l => l.HasOne<Role>()
.WithMany().HasForeignKey("RoleId").OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("user_role$FK_2DE8C6A3D60322AC"),r => r.HasOne<User>()
.WithMany().HasForeignKey("UserId").HasConstraintName("user_role$FK_2DE8C6A3A76ED395"),
j =>
{
j.HasKey("UserId", "RoleId").HasName("PK_user_role_user_id");
j.ToTable("user_role");
j.HasIndex(new[] { "UserId" }, "IDX_2DE8C6A3A76ED395");
j.HasIndex(new[] { "RoleId" }, "IDX_2DE8C6A3D60322AC");
j.IndexerProperty<int>("UserId").HasColumnName("user_id");
j.IndexerProperty<int>("RoleId").HasColumnName("role_id");
});
}
I am trying to access data with :
user = SomeDbContext.Users.FirstOrDefault(a => a.CrmId == userData.CrmId);
user.Roles.Count(); // always 0
var MatchingRole = SomeDbContext.Roles.FirstOrDefault(role => role.Role1 == "someRole1");
MatchingRole.Users.Count() // always 0
// Adding
MatchingRole.Users.Add(user); // or
//user.Roles.Add(MatchingRole);
SomeDbContext.SaveChanges();
CodePudding user response:
Something like that:
var user = SomeDbContext.Users.Include(x => x.Roles).FirstOrDefault(x => x.CrmId == userData.CrmId);
var rolesCount = user.Roles.Count();
var matchingRole = SomeDbContext.Roles.Include(x => x.Users).FirstOrDefault(role => role.Role1 == "someRole1");
var usersCount = matchingRole.Users.Count()