EF 6 code-first generating extra table for many-many table?

Question

Referencing from the @Ogglas answer of this post, I would like to ask if it is normal for EF to generate another table?

If the additional table should not be there, then what am I doing wrong here? Please enlighten me. TIA!

Sample code:

public class Aggregate
{
    public Aggregate()
    {
        Episodes = new HashSet<Episode>();
    }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public Guid Id { get; set; }
    ...
    public virtual ICollection<Episode> Episodes { get; set; }
}

public class Episode
{
    public Episode()
    {
        Aggregates = new HashSet<Aggregate>();
    }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public Guid Id { get; set; }
    ...
    public virtual ICollection<Aggregate> Aggregates { get; set; }

}

public class EpisodeAggregate
{
    [Key]
    [Column(Order = 1)]
    [ForeignKey("Episode")]
    public Guid EpisodeId { get; set; }

    [Key]
    [Column(Order = 2)]
    [ForeignKey("Aggregate")]
    public Guid AggregateId { get; set; }

    
    public virtual Episode Episode { get; set; }
    public virtual Aggregate Aggregate { get; set; }

    public DateTime Timestamp { get; set; }
}

In my DbContext.cs:

public DbSet<EpisodeAggregate> EpisodeAggregates { get; set; }

Yes, using a "join" or "association" table between the two many-to-many entities is the way to do it - that is by design and absolutely intentional — marc_s, Nov 11 '20 at 05:02
@marc_s then is the extra table that was generated intentional by EF? — honsho, Nov 11 '20 at 07:12
I consider [this](https://stackoverflow.com/q/19342908/861716) a duplicate because it shows how to do the configuration for many-to-many with an explicit junction class. In short: both entities should have `ICollection`. — Gert Arnold, Nov 11 '20 at 07:34
Yes - to enable a m:n relationship in a relational database, you must use an association table - and EF6 will *automagically* create that for you so you don't have to worry about it - how nice is that? :-) — marc_s, Nov 11 '20 at 09:31
@marc_s Except that OP wants a timestamp in the junction table. — Gert Arnold, Nov 11 '20 at 15:50
@honsho The current collection properties, `ICollection Episodes` and `ICollection Aggregates` trigger EF to create a junction table automatically (`EpisodeAggregate1`), while the class `EpisodeAggregate` triggers the creation of the `EpisodeAggregate` table. — Gert Arnold, Nov 11 '20 at 18:59

Harald Coppoolse · Accepted Answer · 2020-11-16T07:32:11.680

You are right. In a relational database, a many-to-many relation is solved using a junction table.

For a standard many-to-many relationship, you don't need to mention this junction table; entity framework recognizes the many-to-many by your use of the virtual ICollection<...> on both sides of the many-to-many relation, and will automatically create the tables for you.

To test my database theories and entity framework, I quite often use a simple database with Schools, Students and Teachers. One-to-many for School-Student and School-Teacher and many-to-many for Teacher-Student. I always see that the Teacher-Student junction table is created automatically, without ever having to mention it.

However!

Your junction table is not standard. A standard junction table has only two columns: the EpisodeId and the AggregateId. It doesn't even have an extra primary key. The combination [EpisodeId, AggregateId] is already unique and can be used as a primary key.

You have in table EpisodeAggregate an extra column: TimeStamp. Apparently you want to know when an Episode and an Aggregate got related.

"Give me the TimeStamp when Episode[4] got related with Aggregate[7]"

This makes that this table is not a standard junction table. There is no many-to-many relation between Episodes and Aggregates. You made a one-to-many relation between Episode and its Relations with the Aggregates, and similarly a one-to-many relation between Aggregates and its Relations with the Episodes.

This makes that you have to change your many-to-many into one-to-many:

class Episode
{
    public Guid Id { get; set; }

    // every Episode has zero or more Relations with Aggregates (one-to-many)
    public virtual ICollection<EpisodeAggregateRelation> EpisodeAggregateRelations { get; set; }

    ...
}

class Aggregate
{
    public Guid Id { get; set; }

    // every Episode has zero or more Relations with Episodes(one-to-many)
     public virtual ICollection<EpisodeAggregateRelation> EpisodeAggregateRelations { get; set; }

    ...
}

class EpisodeAggregateRelation
{
    // Every Relation is the Relation between one Episode and one Aggregate
    // using foreign key:
    public Guid EpisodeId { get; set; }
    public Guid AggregateId { get; set; }

    public virtual Episode Episode { get; set; }
    public virtual Aggregate Aggregate { get; set; }

    public DateTime Timestamp { get; set; }
}

If you are certain the there will always be at utmost one relation between an Episode and an Aggregate, you can use the combination [EpisodeId, AggregateId] as a primary key. If you think these two might have several relations, you need to add a separate primary key.

I often use my classes in different databases, hence I don't like attributes, I solve it in fluent API in OnModelCreating:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Episode>()
        .HasKey(episode => episode.Id)

        // define the one-to-many with EpisodeAggregateRelations:
        .HasMany(episode => episode.EpisodeAggregateRelations)
        .WithRequired(relation => relation.Episode)
        .HasForeignKey(relation => relation.EpisodeId);

    modelBuilder.Entity<Aggregate>()
        .HasKey(aggregate => aggregate.Id)

        // define the one-to-many with EpisodeAggregateRelations:
        .HasMany(aggregate => aggregate .EpisodeAggregateRelations)
        .WithRequired(relation => relation.Aggregate)
        .HasForeignKey(relation => relation.aggregateId);

The above is not needed!

Because you followed the entity framework code first conventions, you can omit these two statements. Entity framework will recognize the primary key and the one-to-many relation. Only if you want to deviate from the conventions, like a non-standard table name, or if you want to define the column order:

modelBuilder.Entity<Episode>()
    .ToTable("MySpecialTableName")
    .Property(episode => episode.Date)
    .HasColumnName("FirstBroadcastDate")
    .HasColumnOrder(3)
    .HasColumnType("datetime2");

But again: you followed the conventions, all those attributes like Key, ForeignKey, DatabaseGenerated are not needed. And the column order: who cares? Let your database management system decide about the most optimum column order.

My advice would be: try to experiment: leave out this fluent API and check whether your unit tests still pass. Checked in five minutes.

The EpisodeAggregateRelation has something non-standard: it has a composite primary key. Hence you need to define this. See Configuring a composite primary key

modelBuilder.Entity<EpisodeAggregateRelation>()
.HasKey(relation => new
{
   relation.EpisodId,
   relation.AggregateId
});

If you already defined the one-to-many in Episodes and Aggregates, or if that was not needed because of the conventions, you don't have to mention this relation here again.

If you want, you can put the one-to-many in the fluent API part of EpisodeAggregateRelation, instead of in the fluent API part of Episode / Aggregate:

// every EpisodeAggregateRelation has one Episode, using foreign key
modelBuilder.Entity<EpisodeAggregateRelation>()
    .HasRequired(relation => relation.Episode(
    .WithMany(episode => episode.EpisodeAggregateRelations)
    .HasForeignKey(relation => relation.EpisodeId);

// similar for Aggregate

One final tip

Don't create a HashSet in the constructor. It is a waste of processing power if you fetch data: you create the HashSet, and it is immediately replaced by the ICollection<...> that entity framework creates.

If you don't believe me: just try it out, and see that your unit tests pass, with the possible exception of the unit test that checks for an existing ICollection<...>

EF 6 code-first generating extra table for many-many table?

1 Answers1