Adding DateTime field to many-to-many EF Code First relationship

Question

I am using EF 6 Code-First, table per type, and I have two concrete classes Group and User. Group has a navigation property Members which contains a collection of User. I have mapped this many-to-many relationship in EF using Fluent syntax:

modelBuilder.Entity<Group>
    .HasMany<User>(g => g.Members)
    .WithMany(u => u.Groups);

I would like to be able to say when a member has joined a group so that I can query for, say, the newest member(s). I am not sure of how this is best accomplished within the framework.

I see the following options:

1. Create and use an audit table (ie GroupMembershipAudit consisting of Group, User, join/unjoin, and DateTime
2. Add a column to the autogenerated many-to-many table between User and Group

Is there anything within EF to facilitate this sort of storage of many-to-many historical info like this / append columns to the many-to-many relationship?

Answer 1

Add a column to the autogenerated many-to-many table between User and Group

That is not possible - auto-generated junction tables can contain only keys (that is called Pure Join Table). According to Working with Many-to-Many Data Relationships article: If the join table contains fields that are not keys, the table is not a PJT and therefore Entity Framework cannot create a direct-navigation (many-to-many) association between the tables. (Join tables with non-key fields are also known as join tables with payload.)

Create and use an audit table (ie GroupMembershipAudit consisting of Group, User, join/unjoin, and DateTime

Actually you should create GroupMembershipAudit entity. With Code First table will be generated, you don't need to create it manually.