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

Question

I have this scenario   public class Member       public int MemberID   get  set         public string FirstName   get  set        public string LastName   get  set         public virtual ICollection lt Comment gt  Comments   get  set       public class Comment       public int CommentID   get  set        public string Message   get  set         public virtual ICollection lt Member gt  Members   get  set       public class MemberComment       public int MemberID   get  set        public int CommentID   get  set        public int Something   get  set        public string SomethingElse   get  set        How do I configure my association with fluent API  Or is there a better way to create the association table

User · Answer

TLDR   semi-related to an EF editor bug in EF6 VS2012U5  if you generate the model from DB and you cannot see the attributed m m table  Delete the two related tables -  Save  edmx -  Generate add from database -  Save   For those who came here wondering how to get a many-to-many relationship with attribute columns to show in the EF  edmx file  as it would currently not show and be treated as a set of navigational properties   AND you generated these classes from your database table  or database-first in MS lingo  I believe    Delete the 2 tables in question  to take the OP example  Member and Comment  in your  edmx and add them again through  Generate model from database    i e  do not attempt to let Visual Studio update them - delete  save  add  save   It will then create a 3rd table in line with what is suggested here   This is relevant in cases where a pure many-to-many relationship is added at first  and the attributes are designed in the DB later   This was not immediately clear from this thread Googling  So just putting it out there as this is link  1 on Google looking for the issue but coming from the DB side first

User · Answer

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 lt MemberComment gt  MemberComments   get  set       public class Comment       public int CommentID   get  set        public string Message   get  set         public virtual ICollection lt MemberComment gt  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   gt  m LastName     Smith        SelectMany m   gt  m MemberComments Select mc   gt  mc Comment        ToList          or      var commentsOfMembers   context MemberComments      Where mc   gt  mc Member LastName     Smith        Select mc   gt  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   gt  m LastName     Smith        Select m   gt  new               Member   m          Comments   m MemberComments Select mc   gt  mc Comment              ToList      If you want to find all comments of a member with MemberId   1   var commentsOfMember   context MemberComments      Where mc   gt  mc MemberId    1       Select mc   gt  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   gt  mc MemberId    1  amp  amp  mc Something    99       Select mc   gt  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   gt  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   gt  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   gt  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   gt  m FirstName     Paul        SingleOrDefault    var comment3   context Comments Where c   gt  c Message     Good night         SingleOrDefault    if  member2    null  amp  amp  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   gt  mc Member FirstName     Paul           amp  amp  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   gt  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

User · Answer

One way to solve this error is to put the ForeignKey attribute on top of the property you want as a foreign key and add the navigation property   Note  In the ForeignKey attribute  between parentheses and double quotes  place the name of the class referred to in this way

User · Answer

The code provided by this answer is right  but incomplete  I ve tested it  There are missing properties in  quot UserEmail quot  class      public UserTest UserTest   get  set        public EmailTest EmailTest   get  set     I post the code I ve tested if someone is interested  Regards using System Data Entity  using System  using System Collections Generic  using System ComponentModel DataAnnotations  using System ComponentModel DataAnnotations Schema  using System Linq  using System Web    region example2 public class UserTest       public int UserTestID   get  set        public string UserTestname   get  set        public string Password   get  set         public ICollection lt UserTestEmailTest gt  UserTestEmailTests   get  set         public static void DoSomeTest ApplicationDbContext context                 for  int i   0  i  lt  5  i                          var user   context UserTest Add new UserTest     UserTestname    quot Test quot    i                 var address   context EmailTest Add new EmailTest     Address    quot address  quot    i                       context SaveChanges             foreach  var user in context UserTest Include t   gt  t UserTestEmailTests                         foreach  var address in context EmailTest                                user UserTestEmailTests Add new UserTestEmailTest     UserTest   user  EmailTest   address  n1   user UserTestID  n2   address EmailTestID                                     context SaveChanges             public class EmailTest       public int EmailTestID   get  set        public string Address   get  set         public ICollection lt UserTestEmailTest gt  UserTestEmailTests   get  set       public class UserTestEmailTest       public int UserTestID   get  set        public UserTest UserTest   get  set        public int EmailTestID   get  set        public EmailTest EmailTest   get  set        public int n1   get  set        public int n2   get  set            Call this code from ApplicationDbContext ConfigureMapping       and add this lines as well        public System Data Entity DbSet lt yournamespace UserTest gt  UserTest   get  set          public System Data Entity DbSet lt yournamespace EmailTest gt  EmailTest   get  set        internal static void RelateFluent System Data Entity DbModelBuilder builder                   Primary keys         builder Entity lt UserTest gt    HasKey q   gt  q UserTestID           builder Entity lt EmailTest gt    HasKey q   gt  q EmailTestID            builder Entity lt UserTestEmailTest gt    HasKey q   gt              new                               q UserTestID                  q EmailTestID                             Relationships         builder Entity lt UserTestEmailTest gt                 HasRequired t   gt  t EmailTest               WithMany t   gt  t UserTestEmailTests               HasForeignKey t   gt  t EmailTestID            builder Entity lt UserTestEmailTest gt                 HasRequired t   gt  t UserTest               WithMany t   gt  t UserTestEmailTests               HasForeignKey t   gt  t UserTestID            endregion

User · Answer

I ll just post the code to do this using the fluent API mapping  public class User       public int UserID   get  set        public string Username   get  set        public string Password   get  set         public ICollection lt UserEmail gt  UserEmails   get  set       public class Email       public int EmailID   get  set        public string Address   get  set         public ICollection lt UserEmail gt  UserEmails   get  set       public class UserEmail       public int UserID   get  set        public int EmailID   get  set        public bool IsPrimary   get  set       On your DbContext derived class you could do this  public class MyContext   DbContext       protected override void OnModelCreating DbModelBuilder builder               Primary keys         builder Entity lt User gt    HasKey q   gt  q UserID           builder Entity lt Email gt    HasKey q   gt  q EmailID           builder Entity lt UserEmail gt    HasKey q   gt               new                    q UserID  q EmailID                             Relationships         builder Entity lt UserEmail gt                 HasRequired t   gt  t Email               WithMany t   gt  t UserEmails               HasForeignKey t   gt  t EmailID           builder Entity lt UserEmail gt                 HasRequired t   gt  t User               WithMany t   gt  t UserEmails               HasForeignKey t   gt  t UserID           It has the same effect as the accepted answer  with a different approach  which is no better nor worse

User · Answer

I want to propose a solution where both flavors of a many-to-many configuration can be achieved    The  catch  is we need to create a view that targets the Join Table  since EF validates that a schema s table may be mapped at most once per EntitySet   This answer adds to what s already been said in previous answers and doesn t override any of those approaches  it builds upon them    The model   public class Member       public int MemberID   get  set         public string FirstName   get  set        public string LastName   get  set         public virtual ICollection lt Comment gt  Comments   get  set        public virtual ICollection lt MemberCommentView gt  MemberComments   get  set       public class Comment       public int CommentID   get  set        public string Message   get  set         public virtual ICollection lt Member gt  Members   get  set        public virtual ICollection lt MemberCommentView gt  MemberComments   get  set       public class MemberCommentView       public int MemberID   get  set        public int CommentID   get  set        public int Something   get  set        public string SomethingElse   get  set         public virtual Member Member   get  set        public virtual Comment Comment   get  set        The configuration   using System ComponentModel DataAnnotations Schema  using System Data Entity ModelConfiguration   public class MemberConfiguration   EntityTypeConfiguration lt Member gt        public MemberConfiguration                 HasKey x   gt  x MemberID            Property x   gt  x MemberID  HasColumnType  int   IsRequired            Property x   gt  x FirstName  HasColumnType  varchar 512             Property x   gt  x LastName  HasColumnType  varchar 512                configure many-to-many through internal EF EntitySet         HasMany s   gt  s Comments               WithMany c   gt  c Members               Map cs   gt                                cs ToTable  MemberComment                    cs MapLeftKey  MemberID                    cs MapRightKey  CommentID                             public class CommentConfiguration   EntityTypeConfiguration lt Comment gt        public CommentConfiguration                 HasKey x   gt  x CommentID            Property x   gt  x CommentID  HasColumnType  int   IsRequired            Property x   gt  x Message  HasColumnType  varchar max              public class MemberCommentViewConfiguration   EntityTypeConfiguration lt MemberCommentView gt        public MemberCommentViewConfiguration                 ToTable  MemberCommentView            HasKey x   gt  new   x MemberID  x CommentID              Property x   gt  x MemberID  HasColumnType  int   IsRequired            Property x   gt  x CommentID  HasColumnType  int   IsRequired            Property x   gt  x Something  HasColumnType  int            Property x   gt  x SomethingElse  HasColumnType  varchar max                 configure one-to-many targeting the Join Table view            making all of its properties available         HasRequired a   gt  a Member  WithMany b   gt  b MemberComments           HasRequired a   gt  a Comment  WithMany b   gt  b MemberComments             The context   using System Data Entity   public class MyContext   DbContext       public DbSet lt Member gt  Members   get  set        public DbSet lt Comment gt  Comments   get  set        public DbSet lt MemberCommentView gt  MemberComments   get  set         protected override void OnModelCreating DbModelBuilder modelBuilder                base OnModelCreating modelBuilder            modelBuilder Configurations Add new MemberConfiguration             modelBuilder Configurations Add new CommentConfiguration             modelBuilder Configurations Add new MemberCommentViewConfiguration              OnModelCreatingPartial modelBuilder              From Saluma s   Saluma  answer     If you now want to find all comments of members with LastName      Smith  for example you can write a query like this    This still works     var commentsOfMembers   context Members      Where m   gt  m LastName     Smith        SelectMany m   gt  m MemberComments Select mc   gt  mc Comment        ToList         but could now also be     var commentsOfMembers   context Members      Where m   gt  m LastName     Smith        SelectMany m   gt  m Comments       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    This still works     var membersWithComments   context Members      Where m   gt  m LastName     Smith        Select m   gt  new               Member   m          Comments   m MemberComments Select mc   gt  mc Comment              ToList         but could now also be     var membersWithComments   context Members      Where m   gt  m LastName     Smith        Select m   gt  new               Member   m          m Comments                 ToList      If you want to remove a comment from a member  var comment          assume comment from member John Smith var member          assume member John Smith  member Comments Remove comment     If you want to Include   a member s comments  var member   context Members      Where m   gt  m FirstName     John   m LastName     Smith        Include m   gt  m Comments     This all feels like syntactic sugar  however it does get you a few perks if you re willing to go through the additional configuration  Either way you seem to be able to get the best of both approaches

[c#] Create code first, many to many, with additional fields in association table

Examples related to c#

Examples related to entity-framework

Examples related to ef-code-first

Examples related to many-to-many