Many-to-many relationship defined in EF Core Fluent API

Question

This might be a duplicate but sincerely I have spent sleepless nights trying to figure out how to implement this relationship. Basically, a bank customer can have many accounts that they are primary on but also can be a joint ownership on any other account.

Here is my customer model:

public class Customer
{
    public int CustomerNumber { get; set;}
    public string Name { get; set;}
    public ICollection<Account> Accounts { get; set;}
    public virtual ICollection<JointAccount>? JointAccounts { get; set;}  
}

And my Account model looks like this:

public class Account
{
    public string AccountNumber {get; set;}
    public string Name {get; set;}

    public Customer Customer {get; set;}

    public virtual ICollection<JointAccount>? JointOnwers {get; set;} = new List<JointAccount>();
}

Then a model for JointAccount like so:

public class JointAccount
{
    public int CustomerNumber { get; set; }
    public Customer Customers { get; set; }

    public int AccountNumber { get; set; }
    public Account Accounts { get; set; }
}

EF Core config is this:

public class MemberConfig : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> builder)
    {
        builder.HasKey(x => x.CustomerNumber);
        builder.HasMany( a => a.Accounts)
               .WithMany(a => a.AccountNumber)
               .IsRequired();
    }
}

public class AccountConfig : IEntityTypeConfiguration<Account>
{
    public void Configure(EntityTypeBuilder<Account> builder)
    {
        builder.HasKey(x => x.AccountNumber);
        builder.HasMany<JointAccount>(jo => jo.JointOnwers)
               .WithMany(jo => jo.MemberNumber);
    }
}

public class JointAccountConfig : IEntityTypeConfiguration<JointAccount>
{
    public void Configure(EntityTypeBuilder<JointAccount> builder)
    {
         builder.HasKey(x => new {x.CustomerNumber, x.AccountNumber});
    }
}

I have tried different variations based on my understanding so far but it doesn't seem correct to me. I'll appreciate if anyone with a kind heart could explain:

• How to implement the usecase above
• Explain to me like a two year old:

1. What are navigational properties and the role they play in EF Core config files
2. For relationships like this what would be the best design for the join model / join table.

Also any resource out there that you think would help me better understand this concept would be highly appreciated.

A big thank you in advance

Answer 1

If I understand correctly:
A customer can have many "primary accounts" : one-to-many
A customer can also have many "Joint accounts", and multiple customer may share own same "joint account" : many-to-many

So the entity could be like:

    public class Customer
    {
        [Key]
        public int CustomerNumber { get; set; }
        public string Name { get; set; }


        public IEnumerable<Account> PrimaryAccounts { get; set; }
        public IEnumerable<Account> JointAccounts { get; set; }
    }


    public class Account
    {
        [Key]
        public string AccountNumber { get; set; }
        public string Name { get; set; }
        public Customer PrimaryOwner{ get; set; }
        public IEnumerable<Customer> JointOwners { get; set; }
    }

The DbContext:

    public class MyContext :DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseMySql("server=10.96.5.5;database=WA37;user=root;password=xxxxxxx", new MySqlServerVersion(new Version()));
        }

        public DbSet<Account> Accounts { get; set; }
        public DbSet<Customer> Customers { get; set; }


        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

            modelBuilder.Entity<Customer>()
             .HasMany(x => x.PrimaryAccounts)
             .WithOne(x => x.PrimaryOwner);

            modelBuilder.Entity<Customer>()
             .HasMany(x => x.JointAccounts)
             .WithMany(x => x.JointOwners);
        }
    }

3 Tables will be generated

These navigation properties let you explict configure relationshitp using modelbuilder. And generally you need them to do some queries. Such as when you want to query the JointOwners of an accountnumber:

IEnumerable<Customer> jointowners=_mycontext.Accounts.FirstOrDefault(x=x.AccountNumber="xxx").jointOwners;

Reference:
https://www.entityframeworktutorial.net/efcore/configure-one-to-many-relationship-using-fluent-api-in-ef-core.aspx
https://www.entityframeworktutorial.net/efcore/configure-many-to-many-relationship-in-ef-core.aspx