How to create a third table for Many to Many relationship in Entity Framework Core?

Question

I have two classes: One is User

 public class User
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public List<Subscription> Subscriptions { get; set; }
    }

Other is Subscription:

public class Subscription
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
    }

As you can see that User has a list of Subscriptions. Now when using the entity framework code first approach I am getting a table for User which doesn't contain Subscriptions but a new column for User Id is being added to Subscription table. I was expecting to have a third table which contains two columns one with User ID and the other with subscription ID. How can I achieve this?

That's a many-to-many association. Depends on the EF version which options you have. But it shouldn't be hard to find examples. — Gert Arnold, Mar 31 '18 at 21:32
@GertArnold, I think you are right that its a many to many relationship, as one subscription can be associated with multiple users, but given that Subscription class doesn't contain a list of users, how would you approach this in EF? — Sameed, Mar 31 '18 at 21:38

Alexan · Accepted Answer · 2018-03-31T23:24:53.763

From documentation:

Many-to-many relationships without an entity class to represent the join table are not yet supported. However, you can represent a many-to-many relationship by including an entity class for the join table and mapping two separate one-to-many relationships.

So this answer is correct.

I just corrected code a little bit:

class MyContext : DbContext
{
    public DbSet<Use> Users { get; set; }
    public DbSet<Subscription> Subscriptions { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<UserSubscription>()
            .HasKey(t => new { t.UserId, t.SubscriptionId });

        modelBuilder.Entity<UserSubscription>()
            .HasOne(pt => pt.User)
            .WithMany(p => p.UserSubscription)
            .HasForeignKey(pt => pt.UserId);

        modelBuilder.Entity<UserSubscription>()
            .HasOne(pt => pt.Subscription)
            .WithMany(t => t.UserSubscription)
            .HasForeignKey(pt => pt.SubscriptionId);
    }
}

public class User
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public List<UserSubscription> UserSubscriptions{ get; set; }
    }

public class Subscription
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
        public List<UserSubscription> UserSubscriptions{ get; set; }
    }

public class UserSubscription
{
    public int UserId { get; set; }
    public User User { get; set; }

    public int SubscriptionId { get; set; }
    public Subscription Subscription { get; set; }
}

PS. You don't need use virtual in navigation property, because lazy loading still not available in EF Core.

Ramy M. Mousa · Answer 2 · 2018-03-31T21:26:25.940

Create a third middle table named: UserSubscriptions for example.

public class User
    {
        public int ID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public virtual ICollection<UserSubscription> Subscriptions { get; set; }
    }


public class Subscription
{
    public int ID { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

  public class UserSubscription
{
    public int ID { get; set; }
    public int SubscriptionID { get; set; }
    public decimal Price { get; set; }
    public int UserID { get; set; }
    public virtual User { get; set; }
    public DateTime BeginDate { get; set; }
    public DateTime EndDate { get; set; }
}

Second Solution:

Add reference for Subscription to User and name it CurrentSubscription for example.

public class User
    {
        public int ID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public int CurrentSubscriptionID { get; set; }
        public virtual Subscription Subscription { get; set; }
    }


public class Subscription
{
    public int ID { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

what will happen when I add a new subscription to a current user? In which table will that entry go? — Sameed, Mar 31 '18 at 21:25
@Sameed I edited my solution because the first one wasn't a very good reliable one. check the new two solutions — Ramy M. Mousa, Mar 31 '18 at 21:26
In case of the first solution, isn't there a way EF can create a third table on its own without me specifying another class? For the second solution, it looks like if a new subscription is added for a user it will create a new entry in User table which I don't think is a good thing as all other columns data will be repeated? — Sameed, Mar 31 '18 at 21:32

How to create a third table for Many to Many relationship in Entity Framework Core?

2 Answers2