Create Code First, Many to Many, With Additional Fields in Association Table

  • Home
  • Languages
  • Csharp
  • Create Code First, Many to Many, With Additional Fields in Association Table

Create code first, many to many, with additional fields in association table

It's not possible to create a many-to-many relationship with a customized join table. In a many-to-many relationship EF manages the join table internally and hidden. It's a table without an Entity class in your model. To work with such a join table with additional properties you will have to create actually two one-to-many relationships. It could look like this:

public class Member
{
    public int MemberID { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }

    public virtual ICollection<MemberComment> MemberComments { get; set; }
}

public class Comment
{
    public int CommentID { get; set; }
    public string Message { get; set; }

    public virtual ICollection<MemberComment> MemberComments { get; set; }
}

public class MemberComment
{
    [Key, Column(Order = 0)]
    public int MemberID { get; set; }
    [Key, Column(Order = 1)]
    public int CommentID { get; set; }

    public virtual Member Member { get; set; }
    public virtual Comment Comment { get; set; }

    public int Something { get; set; }
    public string SomethingElse { get; set; }
}

If you now want to find all comments of members with LastName = "Smith" for example you can write a query like this:

var commentsOfMembers = context.Members
    .Where(m => m.LastName == "Smith")
    .SelectMany(m => m.MemberComments.Select(mc => mc.Comment))
    .ToList();

... or ...

var commentsOfMembers = context.MemberComments
    .Where(mc => mc.Member.LastName == "Smith")
    .Select(mc => mc.Comment)
    .ToList();

Or to create a list of members with name "Smith" (we assume there is more than one) along with their comments you can use a projection:

var membersWithComments = context.Members
    .Where(m => m.LastName == "Smith")
    .Select(m => new
    {
        Member = m,
        Comments = m.MemberComments.Select(mc => mc.Comment)
    })
    .ToList();

If you want to find all comments of a member with MemberId = 1:

var commentsOfMember = context.MemberComments
    .Where(mc => mc.MemberId == 1)
    .Select(mc => mc.Comment)
    .ToList();

Now you can also filter by the properties in your join table (which would not be possible in a many-to-many relationship), for example: Filter all comments of member 1 which have a 99 in property Something:

var filteredCommentsOfMember = context.MemberComments
    .Where(mc => mc.MemberId == 1 && mc.Something == 99)
    .Select(mc => mc.Comment)
    .ToList();

Because of lazy loading things might become easier. If you have a loaded Member you should be able to get the comments without an explicit query:

var commentsOfMember = member.MemberComments.Select(mc => mc.Comment);

I guess that lazy loading will fetch the comments automatically behind the scenes.

Edit

Just for fun a few examples more how to add entities and relationships and how to delete them in this model:

1) Create one member and two comments of this member:

var member1 = new Member { FirstName = "Pete" };
var comment1 = new Comment { Message = "Good morning!" };
var comment2 = new Comment { Message = "Good evening!" };
var memberComment1 = new MemberComment { Member = member1, Comment = comment1,
                                         Something = 101 };
var memberComment2 = new MemberComment { Member = member1, Comment = comment2,
                                         Something = 102 };

context.MemberComments.Add(memberComment1); // will also add member1 and comment1
context.MemberComments.Add(memberComment2); // will also add comment2

context.SaveChanges();

2) Add a third comment of member1:

var member1 = context.Members.Where(m => m.FirstName == "Pete")
    .SingleOrDefault();
if (member1 != null)
{
    var comment3 = new Comment { Message = "Good night!" };
    var memberComment3 = new MemberComment { Member = member1,
                                             Comment = comment3,
                                             Something = 103 };

    context.MemberComments.Add(memberComment3); // will also add comment3
    context.SaveChanges();
}

3) Create new member and relate it to the existing comment2:

var comment2 = context.Comments.Where(c => c.Message == "Good evening!")
    .SingleOrDefault();
if (comment2 != null)
{
    var member2 = new Member { FirstName = "Paul" };
    var memberComment4 = new MemberComment { Member = member2,
                                             Comment = comment2,
                                             Something = 201 };

    context.MemberComments.Add(memberComment4);
    context.SaveChanges();
}

4) Create relationship between existing member2 and comment3:

var member2 = context.Members.Where(m => m.FirstName == "Paul")
    .SingleOrDefault();
var comment3 = context.Comments.Where(c => c.Message == "Good night!")
    .SingleOrDefault();
if (member2 != null && comment3 != null)
{
    var memberComment5 = new MemberComment { Member = member2,
                                             Comment = comment3,
                                             Something = 202 };

    context.MemberComments.Add(memberComment5);
    context.SaveChanges();
}

5) Delete this relationship again:

var memberComment5 = context.MemberComments
    .Where(mc => mc.Member.FirstName == "Paul"
        && mc.Comment.Message == "Good night!")
    .SingleOrDefault();
if (memberComment5 != null)
{
    context.MemberComments.Remove(memberComment5);
    context.SaveChanges();
}

6) Delete member1 and all its relationships to the comments:

var member1 = context.Members.Where(m => m.FirstName == "Pete")
    .SingleOrDefault();
if (member1 != null)
{
    context.Members.Remove(member1);
    context.SaveChanges();
}

This deletes the relationships in MemberComments too because the one-to-many relationships between Member and MemberComments and between Comment and MemberComments are setup with cascading delete by convention. And this is the case because MemberId and CommentId in MemberComment are detected as foreign key properties for the Member and Comment navigation properties and since the FK properties are of type non-nullable int the relationship is required which finally causes the cascading-delete-setup. Makes sense in this model, I think.

Adding column in Entity Framework 6.1 - Many-To-Many - Code First

To add a column to the junction table, you need to map it as an entity explicitly and create two one-to-many relationships:

public class PersonCourse
{
   [Key, Column(Order = 0),ForeignKey("Person")]
   public int PersonId { get; set; }
   [Key, Column(Order = 1),ForeignKey("Course")]
   public int CourseId { get; set; }

   public Person Person { get; set; }
   public Course Course { get; set; }

   public int Quantity{ get; set; }
}

public class Person
{
  public int PersonId { get; set; }
  //...

  public ICollection<PersonCourse> CoursesAttending { get; set; }

  public Person()
  {
    CoursesAttending = new HashSet<PersonCourse>();
  }
}

public class Course
{
  public int CourseId { get; set; }
  //...

  public ICollection<PersonCourse> Students { get; set; }

  public Course()
  {
    Students = new HashSet<PersonCourse>();
  }
}

If you want to use Fluent Api instead Data Annotations, you can use these configurations:

modelBuilder.Entity<PersonCourse>().HasKey(pc => new { pc.PersonId, pc.CourseId});

modelBuilder.Entity<PersonCourse>().HasRequired(pc=>pc.Person).WithMany(p=>p.CoursesAttending).HasForeignKey(pc=>pc.PersonId);

modelBuilder.Entity<PersonCourse>().HasRequired(pc=>pc.Course).WithMany(c=>c.Students).HasForeignKey(pc=>pc.CourseId);

EF Code first - many to many relation mapping table with extra columns

I think it'll work if you do the following:

  1. Remove the configuration you showed in the code snippet above

  2. Add a mapping table and configure its table name to match the original table name.

    // name this whatever you want
    class UserUserGroupMapping
    {
        public UserUserGroupMappingId { get; set; }
        public int UserId { get; set; }
        public virtual User User { get; set; } 
        public int UserGroupId { get; set; }
        public virtual UserGroup UserGroup { get; set; } 
        // other properties
    }

    modelBuilder.Entity<UserUserGroupMapping>()
        .HasKey(um => um.UserUserGroupMappingId)
        .ToTable("UserUserGroupMapping");

  3. Replace the many-to-many collection properties from User and UserGroup and replace it with one-to-many associations

    class User
    {
        // other properties
        // remove this:
        // public virtual ICollection<UserGroup> UserGroup { get; set; }
        public virtual ICollection<UserUserGroupMapping> UserGroupMappings { get; set; }
    }

    class UserGroup
    {
        // other properties
        // remove this:
        // public virtual ICollection<User> Users { get; set; }
        public virtual ICollection<UserUserGroupMapping> UserMappings { get; set; }
    }

    modelBuilder.Entity<UserUserGroupMapping>()
        .HasRequired(um => um.UserGroup).WithMany(g => g.UserMappings)
        .HasForeignKey(um => um.UserGroupId);

    modelBuilder.Entity<UserUserGroupMapping>()
        .HasRequired(um => um.User).WithMany(g => g.UserGroupMappings)
        .HasForeignKey(um => um.UserId);

  4. Use the package manager to Add-Migration and remove anything from the scaffolded migration that might attempt to drop the old table and create a new table. The migration will need to at least (I might be missing some here):

    • DropPrimaryKey for the original key columns
    • AddColumn for the new columns (with Int(identity:true, nullable: false) for the new primary key column)
    • AddPrimaryKey for the new key column

Then you can use the methods outlined in this answer to retrieve entities.

Creating a one to many with an association table in Entity Framework

You're configuring the relashionship between SportTeam and Matches incorrectly.
So you say that SportTeam entity can have many Matches then it logic that Matches entity to have a foreign key that reference the SportTeam entity.

But if your look at your SportTeam entity configuration, you say it has a composite keys as primary key (SportId, TeamId).

You get this error:

The number of properties in the Dependent and Principal Roles in a
relationship constraint must be identical.

Because if you have composite key as primary key then the foreign key that refers to the primary key of SportTeam should also have both the properties implied in the composite key.

So to solve this, your Matches entity should look like this:

public class Matches
{
    public int Id  { get; set; }

    // these two properties below represent the foreign key that refers to SportTeam entity.
    public int SportId { get; set; }
    public int TeamId { get; set; }

    public SportTeam SportTeam { get; set; };
}

In the OnModelCreating method you should have this line:

modelBuilder.Entity<SportTeam>()
            .HasMany(st => st.Matches)
            .WithRequired(matches => matches.SportTeam)
            .HasForeignKey(m => new { m.SportId, m.TeamId }) // <-- the composite foreign keys.
            .WillCascadeOnDelete(false);

Instead of:

modelBuilder.Entity<SportTeam>()
            .HasMany(st => st.Matches)
            .WithRequired(matches => matches.SportTeam)
            .HasForeignKey(m => m.SportTeamId)
            .WillCascadeOnDelete(false);

Side Note 1:

I always avoid using composite foreign key. If I end up with things like you have in your sample I just put a primary key property Id in SportTeam entity and make the couple of properties SportId and TeamId with unique constraint in database.

Side Note 2:

I don't know if you code like this in your real project but please use properties and make them public if necessary.

Many to Many Relationship with extra columns in EF 6 Code?

Given you are code first I would do something like the following.

public class Student
{
    [Key]
    public int StudentId { get; set; }
    public string Name { get; set; }

    public List<StudentCourse> Courses { get; set; } // So you can access Courses for a student
}

public class Course
{
    [Key]
    public int CourseId { get; set; }
    public string Name { get; set; }

    public List<StudentCourse> Students { get; set; }
}

public class StudentCourse
{
    [Key]
    public int StudentCourseId { get; set; }

    public int StudentId { get; set; }
    [ForeignKey("StudentId")]
    public Student Student { get; set; } // Include this so you can access it later

    public int CourseId { get; set; }        
    [ForeignKey("CourseId")]
    public Course Course { get; set; } 
}

EDIT: Wanted to note relationships are established with Data Attributes, you could also use EF Fluent API to establish your relationships. The properties will look the same, but without the [ForeignKey("")]

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

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<...>

How to create a many-to-many mapping in Entity Framework?

If you want to create many to many relationship with additional data in association table, you have to make the association table as entity. The pure many to many relationship is only in pure table with entity id's.

In you case it will be:

public class Media // One entity table
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool Enabled { get; set; }

    public virtual ICollection<ContractMedia> ContractMedias { get; set; }
}

public class Contract // Second entity table
{
    public int Id { get; set; }
    public string Code { get; set }

    public virtual ICollection<ContractMedia> ContractMedias { get; set; }
}

public class ContractMedia // Association table implemented as entity
{
    public int MediaId { get; set; }
    public int ContractId { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
    public double Price { get; set; }

    public virtual Media Media { get; set; }
    public virtual Contract Contract { get; set; }
}

And after you created models/entities, you need to define relationships in context:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   modelBuilder.Entity<ContractMedia>()
       .HasKey(c => new { c.MediaId, c.ContractId });

   modelBuilder.Entity<Contract>()
       .HasMany(c => c.ContractMedias)
       .WithRequired()
       .HasForeignKey(c => c.ContractId);

   modelBuilder.Entity<Media>()
       .HasMany(c => c.ContractMedias)
       .WithRequired()
       .HasForeignKey(c => c.MediaId);  
}

Also you can refer to these links:

Many to many mapping with extra fields in Fluent API

Entity Framework CodeFirst many to many relationship with additional information

Create code first, many to many, with additional fields in association table

EF Core 2.0 migration - Many-to-Many with additional fields

Anytime you see EF (Core) generating additional FK columns, it's clear indication of unmapped reference or collection navigation property caused by the usage of a wrong fluent API overload. All related fluent API (HasOne, HasMany, WithOne, WithMany) have overloads with and w/o navigation property. You must use the first when the corresponding entity has no navigation And you should use the exact one representing the presence/absence of a navigation property in the corresponding entity. Failing to do so leaves unmapped navigation properties and EF creates additional relationships.

In your case, the two discrepancies (marked as A, B) are between:

public class Team_Member
{
    public int TeamId { get; set; }
    public Team Team { get; set; } // <-- A

    public int MemberId { get; set; }
    public Member Member { get; set; } // <-- B

    public MemberTypeEnum MemberType { get; set; }
}

and

builder.HasOne<Team>() // <-- A
    .WithMany(t => t.Team_Member)
    .HasForeignKey(tm => tm.TeamId)
    .OnDelete(DeleteBehavior.Restrict);
builder.HasOne<Member>() // <-- B
    .WithMany(m => m.Team_Member)
    .HasForeignKey(tm => tm.MemberId)
    .OnDelete(DeleteBehavior.Restrict);

As mentioned earlier, simply make them match:

builder.HasOne(tm => tm.Team) // <-- A
    .WithMany(t => t.Team_Member)
    .HasForeignKey(tm => tm.TeamId)
    .OnDelete(DeleteBehavior.Restrict);
builder.HasOne(tm => tm.Member) // <-- B
    .WithMany(m => m.Team_Member)
    .HasForeignKey(tm => tm.MemberId)
    .OnDelete(DeleteBehavior.Restrict);

and the issue is gone.


Related Topics

How to Find the Text Within a Div in the Source of a Web Page Using C#

Communicate Between Two Windows Forms in C#

Is There a Constraint That Restricts My Generic Method to Numeric Types

Combining Two Expressions (Expression≪Func≪T, Bool≫≫)

The Request Was Aborted: Could Not Create Ssl/Tls Secure Channel

Direct Casting VS 'As' Operator

How to Convert Utf-8 Byte[] to String

What Is an "Index Out of Range" Exception, and How to Fix It

How to Add Extension Methods to an Existing Static Class

Volatile Vs. Interlocked Vs. Lock

How to Convert HTML to Text in C#

How to Handle Both a Single Item and an Array For the Same Property Using Json.Net

C# Variance Problem: Assigning List≪Derived≫ as List≪Base≫

Typenamehandling Caution in Newtonsoft Json

How to Remove All Event Handlers from an Event

Convert Integer to Hexadecimal and Back Again

Observablecollection Not Noticing When Item in It Changes (Even With Inotifypropertychanged)

What Are Your Favorite Extension Methods For C# (Codeplex.Com/Extensionoverflow)


Leave a reply



Submit