Home > Net >  Entity framework many to many, reading does not work
Entity framework many to many, reading does not work

Time:07-22

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()


  • Related