C#•6d ago

EF Core Many to Many relationship through Entity

Hello there :Wave: I'm having a weird issue with EF Core that I think I got lost in 😅 I got a mostly Scaffolded Database with couple changes I've been trying to do - I got something that looks sorta like this

public partial class DiscordMember
{
    public long Id { get; set; }
    public virtual ICollection<DiscordRole> Roles { get; set; } = new List<DiscordRole>();
}

public partial class DiscordRole
{
    public long Id { get; set; }
    public virtual ICollection<DiscordMember> Members { get; set; } = new List<DiscordMember>();
}

public partial class DiscordMemberRole
{
    public int Id { get; set; }

    public long? MemberId { get; set; }

    public long? RoleId { get; set; }

    public virtual DiscordMember Member { get; set; }

    public virtual DiscordRole Role { get; set; }
}

// OnModelCreating
// DiscordMember 
            entity.HasMany(m => m.Roles)
                .WithMany(r => r.Members)
                .UsingEntity<DiscordMemberRole>(
                    join => join
                        .HasOne(j => j.Role)
                        .WithMany()
                        .HasForeignKey(j => j.RoleId)
                        .HasConstraintName("discord_member_roles_role_id_fkey"),
                    join => join
                        .HasOne(j => j.Member)
                        .WithMany()
                        .HasForeignKey(j => j.MemberId)
                        .HasConstraintName("discord_member_roles_member_id_fkey"),
                    join =>
                    {
                        join.ToTable("discord_member_roles"); // Explicitly set the join table name
                        join.Property(j => j.RoleId).HasColumnName("role_id"); // Map to actual column
                        join.Property(j => j.MemberId).HasColumnName("member_id"); // Map to actual column
                    });
//DiscordMemberRole
        modelBuilder.Entity<DiscordMemberRole>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("discord_member_roles_pkey");

            entity.ToTable("discord_member_roles");

            entity.HasIndex(e => e.MemberId, "discord_member_roles_member_id");

            entity.HasIndex(e => e.RoleId, "discord_member_roles_role_id");

            entity.Property(e => e.Id).HasColumnName("id");
            entity.Property(e => e.MemberId).HasColumnName("member_id");
            entity.Property(e => e.RoleId).HasColumnName("role_id");
            
            entity.HasOne(d => d.Member)
                .WithMany()
                .HasForeignKey(d => d.MemberId)
                .HasConstraintName("discord_member_roles_member_id_fkey");

            entity.HasOne(d => d.Role)
                .WithMany()
                .HasForeignKey(d => d.RoleId)
                .HasConstraintName("discord_member_roles_role_id_fkey");
        });

public partial class DiscordMember
{
    public long Id { get; set; }
    public virtual ICollection<DiscordRole> Roles { get; set; } = new List<DiscordRole>();
}

public partial class DiscordRole
{
    public long Id { get; set; }
    public virtual ICollection<DiscordMember> Members { get; set; } = new List<DiscordMember>();
}

public partial class DiscordMemberRole
{
    public int Id { get; set; }

    public long? MemberId { get; set; }

    public long? RoleId { get; set; }

    public virtual DiscordMember Member { get; set; }

    public virtual DiscordRole Role { get; set; }
}

// OnModelCreating
// DiscordMember 
            entity.HasMany(m => m.Roles)
                .WithMany(r => r.Members)
                .UsingEntity<DiscordMemberRole>(
                    join => join
                        .HasOne(j => j.Role)
                        .WithMany()
                        .HasForeignKey(j => j.RoleId)
                        .HasConstraintName("discord_member_roles_role_id_fkey"),
                    join => join
                        .HasOne(j => j.Member)
                        .WithMany()
                        .HasForeignKey(j => j.MemberId)
                        .HasConstraintName("discord_member_roles_member_id_fkey"),
                    join =>
                    {
                        join.ToTable("discord_member_roles"); // Explicitly set the join table name
                        join.Property(j => j.RoleId).HasColumnName("role_id"); // Map to actual column
                        join.Property(j => j.MemberId).HasColumnName("member_id"); // Map to actual column
                    });
//DiscordMemberRole
        modelBuilder.Entity<DiscordMemberRole>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("discord_member_roles_pkey");

            entity.ToTable("discord_member_roles");

            entity.HasIndex(e => e.MemberId, "discord_member_roles_member_id");

            entity.HasIndex(e => e.RoleId, "discord_member_roles_role_id");

            entity.Property(e => e.Id).HasColumnName("id");
            entity.Property(e => e.MemberId).HasColumnName("member_id");
            entity.Property(e => e.RoleId).HasColumnName("role_id");
            
            entity.HasOne(d => d.Member)
                .WithMany()
                .HasForeignKey(d => d.MemberId)
                .HasConstraintName("discord_member_roles_member_id_fkey");

            entity.HasOne(d => d.Role)
                .WithMany()
                .HasForeignKey(d => d.RoleId)
                .HasConstraintName("discord_member_roles_role_id_fkey");
        });

But I'm in a "loop" of errors, based on what changes I do I get different error when trying to Include .Roles in discord Member query

17 Replies

Miner28_3OP•6d ago

        Exception data:
          Severity: ERROR
          SqlState: 42703
          MessageText: column d0.DiscordRoleId does not exist
          Position: 236
          File: parse_relation.c
          Line: 3665
          Routine: errorMissingColumn
      Npgsql.PostgresException (0x80004005): 42703: column d0.DiscordRoleId does not exist

        Exception data:
          Severity: ERROR
          SqlState: 42703
          MessageText: column d0.DiscordRoleId does not exist
          Position: 236
          File: parse_relation.c
          Line: 3665
          Routine: errorMissingColumn
      Npgsql.PostgresException (0x80004005): 42703: column d0.DiscordRoleId does not exist

The SQL Query then looks like this, it seem to try to guess the Column name for the Id of DiscordRole ? :Sus:

      Failed executing DbCommand (40ms) [Parameters=[@__guild_Id_0='?' (DbType = Int64)], CommandType='Text', CommandTimeout='30']
      SELECT s.id, s."DiscordRoleId", s.member_id, s.role_id, s._id, s.color, s.guild_id, s.id0, s.name, s.permissions, s.position, d._id, u.id
      FROM discord_members AS d
      LEFT JOIN users AS u ON d.id = u.id
      INNER JOIN (
          SELECT d0.id, d0."DiscordRoleId", d0.member_id, d0.role_id, d1._id, d1.color, d1.guild_id, d1.id AS id0, d1.name, d1.permissions, d1.position
          FROM discord_member_roles AS d0
          LEFT JOIN discord_roles AS d1 ON d0.role_id = d1._id
      ) AS s ON d._id = s.member_id
      WHERE d.guild_id = @__guild_Id_0 AND u.vrc_id IS NOT NULL AND u.vrc_id <> ''
      ORDER BY d._id, u.id

      Failed executing DbCommand (40ms) [Parameters=[@__guild_Id_0='?' (DbType = Int64)], CommandType='Text', CommandTimeout='30']
      SELECT s.id, s."DiscordRoleId", s.member_id, s.role_id, s._id, s.color, s.guild_id, s.id0, s.name, s.permissions, s.position, d._id, u.id
      FROM discord_members AS d
      LEFT JOIN users AS u ON d.id = u.id
      INNER JOIN (
          SELECT d0.id, d0."DiscordRoleId", d0.member_id, d0.role_id, d1._id, d1.color, d1.guild_id, d1.id AS id0, d1.name, d1.permissions, d1.position
          FROM discord_member_roles AS d0
          LEFT JOIN discord_roles AS d1 ON d0.role_id = d1._id
      ) AS s ON d._id = s.member_id
      WHERE d.guild_id = @__guild_Id_0 AND u.vrc_id IS NOT NULL AND u.vrc_id <> ''
      ORDER BY d._id, u.id

What the hell am I doing wrong 😅 I assume it's something being weird in the UsingEntity or something Just spent like another 2h figuring it out and I think I'm done :dead: and just gonna handle it through the Entity manually and not worry about having it like this cause god it's pain Something about EF Core is just fucking up or I don't know anymore 😅

glhays•6d ago

Check this and strip out all your unnecessary convention and let Ef do its stuff. Then if you need some more customizations then add it in. https://learn.microsoft.com/en-us/ef/core/modeling/relationships/many-to-many?source=recommendations

Many-to-many relationships - EF Core

How to configure many-to-many relationships between entity types when using Entity Framework Core

Miner28_3OP•6d ago

If you mean strip the database names like "member_id" "role_id" I unfortunatelly can't do that 😅 the database already exists and is used by other systems so can't do migrations 😅

glhays•6d ago

So does that DiscordRoleId column actually exist or not in the table.

Miner28_3OP•6d ago

Nope Oh wait.. I wonder.. I might have just found the issue after reading through this.. :AnimuThinku: gotta do some rewrites or not :AnimuThinku: hm Oh damn hm I think I found part of the issue Progress lol Exception data: Severity: ERROR SqlState: 42703 MessageText: column v0.Id does not exist Hint: Perhaps you meant to reference the column "v0.id". Position: 230 File: parse_relation.c Line: 3665 Routine: errorMissingColumn Well now I'm back to missing DiscordRoleId 😅 xD

glhays•6d ago

Remember seeing this in the link?

Important

Please don't attempt to fully configure everything even when it is not needed. As can be seen above, the code gets complicated quickly and its easy to make a mistake. And even in the example above there are many things in the model that are still configured by convention. It's not realistic to think that everything in an EF model can always be fully configured explicitly.

🙄

Miner28_3OP•6d ago

Yup But was kinda hoping that something like this could work being configured explicitly 😅

glhays•6d ago

I think it can work. But where is thatcolumn coming from? It's not even in postGres format.

Miner28_3OP•6d ago

But seems like my Database, atleast until I am able to do migrations and change the structure just won'T work with it like this Yea, honestly I have no idea.. there's nothing in the whole project that says "DiscordRoleId" But what i think is it's being generated by convetion as a DiscordRole (name of the class) + "Id" the name of the property that it's trying to join on

glhays•6d ago

Efcore is creating it because your missing something in you convention. I think

Miner28_3OP•6d ago

hm :AnimuThinku: But what :AnimuThinku: I believe the DiscordRoleId that it's trying to generate is meant to be the "RoleId" that's on the Join Entity Oh my goodness, I think it worked ? OMG It did Worst thing ? I don't know what I did 😅 I just changed like 10 files and lost track of what all I changed But I think it fixed by me getting rid of all references of the Join Table - "DiscordMemberRole"

glhays•6d ago

So you just proved that important message Right! Good job.

Miner28_3OP•6d ago

😅 I believe so, getting rid of all the DiscordMemberRole references seems to have fixed it which is funny 😆 Now just gotta solve the other places where I seem to have the same issue and we're golden :PartyPug: Thanks for your help @glhays 😅 🙏

glhays•6d ago

What errors do you have?

Miner28_3OP•6d ago

Same thing just different Entities So just gonna do the same I did here and should work

glhays•6d ago

Ohh. Glad to help keep you motivated.

Miner28_3OP•6d ago

🙏

Gaming

Programming

EF Core Many to Many relationship through Entity