How to code C# MVC model for many to many join table for sqlite database migration { edited }

Question

I am creating a sqlite database to track users assigned to teams. This would mean many-to-many table relationship. I need help making the model for the migration.

users table: 
    public class User
    {
        public int Id { get; set; }
        public string Username { get; set; }
        public ICollection<Photo> Photos { get; set; }
        public virtual ICollection<UserTeam> UserTeams { get; set; }
    }

team table:
    public class Team
    {
        public int Id { get; set; }
        public string Name { get; set; } 
        public User User { get; set; }
        public int UserId { get; set; }
        public virtual ICollection<UserTeam> UserTeams{ get; set; }
    }

UserTeam Table:
    public class UserTeam
    {
        public User User { get; set; }
        public int UserId{ get; set; }
        public Team Team{ get; set; }  
        public int TeamId{ get; set; } //... I have this one for owner of the team
        public string Title { get; set; }

    }

DataContext:
    public class DataContext : DbContext
    {
        public DataContext(DbContextOptions<DataContext> options) : base (options) {}

        public DbSet<Value> Values { get; set; }

        public DbSet<User> Users { get; set; }

        public DbSet<Photo> Photos { get; set; }

        public DbSet<Team> Teams { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<UserTeam>()
                .HasKey(ut => new { ut.UserId, ut.TeamId });  
            modelBuilder.Entity<UserTeam>()
                .HasOne(ut => ut.User)
                .WithMany(u => u.UserTeams)
                .HasForeignKey(ut => ut.UserId);  
            modelBuilder.Entity<UserTeam>()
                .HasOne(ut => ut.Team)
                .WithMany(t => t.UserTeams)
                .HasForeignKey(ut => ut.TeamId);
        }
    }

From what I read declaring I ICollection on team class and user class would force a join table creating. However when I try to migrate, I get this message: Unable to determine the relationship represented by navigation property 'User.Teams' of type 'ICollection'. Either manually configure the relationship, or ignore this property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

Any suggestions on how to correctly make join tables in sqlite would be helpful

Answer 1

looks like you are using EF Core 2.x. It is necessary to define the entity in the model. This means you will have to define relationship by overriding it in the OnModelCreating method.

I would further normalize your structure by creating another table to hold the reference for the user and the team the user belongs to and vice-versa. I would call this table UserTeam (for the lack of better naming)

So I will have something like:

public class User
{
    //....omitted
    public virtual ICollection<UserTeam> UserTeams { get; set; }
}

public class UserTeam
{      
    public int UserId{ get; set; }
    public User User { get; set; }
    public int TeamId{ get; set; }
    public Team Team{ get; set; }
}

public class Team
{
    //.... omitted
    public virtual ICollection<UserTeam> UserTeams{ get; set; }
}

The UserTeam table will need to be configured so that EFCore can map it successfully. This is where we define the many-to-many relationship

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<UserTeam>()
        .HasKey(ut => new { ut.UserId, ut.TeamId });  
    modelBuilder.Entity<UserTeam>()
        .HasOne(ut => ut.User)
        .WithMany(u => u.UserTeams)
        .HasForeignKey(ut => ut.UserId);  
    modelBuilder.Entity<UserTeam>()
        .HasOne(ut => ut.Team)
        .WithMany(t => t.UserTeams)
        .HasForeignKey(ut => ut.TeamId);
}