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

7

329

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<Comment> Comments { get; set; }
}

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

    public virtual ICollection<Member> 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?

Closefisted answered 13/8, 2011 at 12:29 Comment(0)
P
570

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.

Puett answered 13/8, 2011 at 21:28 Comment(27)
Thank you. Much appreciate the additional information you provided.Closefisted
@hgdean: I've spammed a few more examples, sorry, but it's an interesting model and questions about many-to-many with additional data in the join table occur every now and then here. Now for the next time I have something to link to... :)Puett
+1 for the examples. It'd be very useful to see the mappings inside the overriden OnModelCreating, is there any chance you can add that?Restrain
@Esteban: There is no overridden OnModelCreating. The example relies only on mapping conventions and data annotations.Puett
@Puett oh, I see, I landed here because I have a problem with many to many relationships where the cross table has an additional property, so I was kind of hoping to see the mapping, but, re-reading your post, you're right. I'm sorry, and thanks for your response.Restrain
Is this still the best way with Ef5 and even EF6 ?Bias
@Simon_Weaver: Yes, you still need to expose "customized join tables" as entities and can't use a many-to-many relationship.Puett
Note: if you use this approach without Fluent API make sure you check in your database that you only have a composite key with MemberId and CommentId columns and not an additional third column Member_CommentId (or something like that) - which means you didn't have exact matching names across objects for your keysBias
@Puett can you post the Relationship mapping for you examples up above?Hagberry
@brenjt: The example does not have an explicit mapping. EF infers the mapping from data annotations and naming conventions. You can take a look at Esteban's answer to get an idea how the mapping with Fluent API would look like.Puett
why do we need those primary keys ?Christiechristin
Great example. I like the fact you're not overriding the OnModelCreating function. Pure simple convention and attributes.Regalado
@Puett Great answer! Following it I made my own join table with payload and I get an error that I post in this question: #25929166. Any advice?Kibitka
@Bias (or anyone who may know the answer) I have a similar situation but I would like to have the "MemberCommentID" primary key for that table, is this possible or not? I'm currently getting an exception, please take a look at my question, I really need help... #26784434Quacksalver
Slauma, I'm using similar approach, but I'm having problem to update the entity. In my case the user should be able to change relationships. I'll be grateful if you could take a look at my question.Distill
I do the exact same thing and always felt like it was hacky. Not anymore! Great stuffs mate.Markos
The word "actually" in the first paragraph seems to be misplaced. Can you have a look into it?The
@Bias So bad that I cannot upvote more than once :/Cianca
Great answer man! Thank you for the insight! However I will go with the fluent api approach :)Martini
@Puett I am confused by your answer because you state that the join table is " a table without an Entity class in your model." However you do have a model for the join table: public class MemberComment. Care to elaborate?Cooker
It would be great to see an actual database relation model for devs going database firstGanymede
Are Key and Column attributes necessary in your example?Poaceous
Tried the proposed model but got this error: "The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.".Destined
This example assumes matching primary/foreign key names. With mis-matched key names add a ForeignKey attribute as described here: entityframeworktutorial.net/code-first/…Ligure
Bulk updating the link table involves (possibly) deleting and/or adding rows. I found these links helpful: entityframeworktutorial.net/EntityFramework4.3/… and thereformedprogrammer.net/…Ligure
Excellent code examples. This worked really well for me and I appreciate the effort invested to share. ThxFeuilleton
This don't work with EF COREImeldaimelida
R
106

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<UserEmail> UserEmails { get; set; }
}

public class Email {
    public int EmailID { get; set; }
    public string Address { get; set; }

    public ICollection<UserEmail> 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<User>().HasKey(q => q.UserID);
        builder.Entity<Email>().HasKey(q => q.EmailID);
        builder.Entity<UserEmail>().HasKey(q => 
            new { 
                q.UserID, q.EmailID
            });

        // Relationships
        builder.Entity<UserEmail>()
            .HasRequired(t => t.Email)
            .WithMany(t => t.UserEmails)
            .HasForeignKey(t => t.EmailID)

        builder.Entity<UserEmail>()
            .HasRequired(t => t.User)
            .WithMany(t => t.UserEmails)
            .HasForeignKey(t => t.UserID)
    }
}

It has the same effect as the accepted answer, with a different approach, which is no better nor worse.

Restrain answered 28/5, 2013 at 22:26 Comment(5)
I think this is wrong. You are creating a M:M relationship here where it needs to be 1:M for both entities.Dowell
@Dowell In your classes you can easily describe a many to many relationship with properties that point to each other. taken from: msdn.microsoft.com/en-us/data/hh134698.aspx. Julie Lerman can't be wrong.Restrain
Esteban, the relationship mapping is really incorrect. @Dowell is right about this. Julie Lerman is talking about a "true" many-to-many relationship while we have here an example for a model that can't be mapped as many-to-many. Your mapping even won't compile because you don't have a Comments property in Member. And you can't just fix this by renaming the HasMany call to MemberComments because the MemberComment entity does not have an inverse collection for WithMany. In fact you need to configure two one-to-many relationships to get the right mapping.Puett
Thank you. I followed this solution to do the mapping many-to-many.Awakening
I don't know I but this works better with MySql. Without the builder, Mysql throw me an error when I tried the migration.Belongings
B
11

The code provided by this answer is right, but incomplete, I've tested it. There are missing properties in "UserEmail" 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<UserTestEmailTest> UserTestEmailTests { get; set; }

    public static void DoSomeTest(ApplicationDbContext context)
    {

        for (int i = 0; i < 5; i++)
        {
            var user = context.UserTest.Add(new UserTest() { UserTestname = "Test" + i });
            var address = context.EmailTest.Add(new EmailTest() { Address = "address@" + i });
        }
        context.SaveChanges();

        foreach (var user in context.UserTest.Include(t => 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<UserTestEmailTest> 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<yournamespace.UserTest> UserTest { get; set; }
    //public System.Data.Entity.DbSet<yournamespace.EmailTest> EmailTest { get; set; }
    internal static void RelateFluent(System.Data.Entity.DbModelBuilder builder)
    {
        // Primary keys
        builder.Entity<UserTest>().HasKey(q => q.UserTestID);
        builder.Entity<EmailTest>().HasKey(q => q.EmailTestID);

        builder.Entity<UserTestEmailTest>().HasKey(q =>
            new
            {
                q.UserTestID,
                q.EmailTestID
            });

        // Relationships
        builder.Entity<UserTestEmailTest>()
            .HasRequired(t => t.EmailTest)
            .WithMany(t => t.UserTestEmailTests)
            .HasForeignKey(t => t.EmailTestID);

        builder.Entity<UserTestEmailTest>()
            .HasRequired(t => t.UserTest)
            .WithMany(t => t.UserTestEmailTests)
            .HasForeignKey(t => t.UserTestID);
    }
}
#endregion
Bubalo answered 3/6, 2015 at 9:35 Comment(0)
C
8

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<Comment> Comments { get; set; }
    public virtual ICollection<MemberCommentView> MemberComments { get; set; }
}

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

    public virtual ICollection<Member> Members { get; set; }
    public virtual ICollection<MemberCommentView> 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<Member>
{
    public MemberConfiguration()
    {
        HasKey(x => x.MemberID);

        Property(x => x.MemberID).HasColumnType("int").IsRequired();
        Property(x => x.FirstName).HasColumnType("varchar(512)");
        Property(x => x.LastName).HasColumnType("varchar(512)")

        // configure many-to-many through internal EF EntitySet
        HasMany(s => s.Comments)
            .WithMany(c => c.Members)
            .Map(cs =>
            {
                cs.ToTable("MemberComment");
                cs.MapLeftKey("MemberID");
                cs.MapRightKey("CommentID");
            });
    }
}

public class CommentConfiguration : EntityTypeConfiguration<Comment>
{
    public CommentConfiguration()
    {
        HasKey(x => x.CommentID);

        Property(x => x.CommentID).HasColumnType("int").IsRequired();
        Property(x => x.Message).HasColumnType("varchar(max)");
    }
}

public class MemberCommentViewConfiguration : EntityTypeConfiguration<MemberCommentView>
{
    public MemberCommentViewConfiguration()
    {
        ToTable("MemberCommentView");
        HasKey(x => new { x.MemberID, x.CommentID });
        
        Property(x => x.MemberID).HasColumnType("int").IsRequired();
        Property(x => x.CommentID).HasColumnType("int").IsRequired();
        Property(x => x.Something).HasColumnType("int");
        Property(x => x.SomethingElse).HasColumnType("varchar(max)");

        // configure one-to-many targeting the Join Table view
        // making all of its properties available
        HasRequired(a => a.Member).WithMany(b => b.MemberComments);
        HasRequired(a => a.Comment).WithMany(b => b.MemberComments);
    }
}

The context:

using System.Data.Entity;

public class MyContext : DbContext
{
    public DbSet<Member> Members { get; set; }
    public DbSet<Comment> Comments { get; set; }
    public DbSet<MemberCommentView> 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 => m.LastName == "Smith")
    .SelectMany(m => m.MemberComments.Select(mc => mc.Comment))
    .ToList();

...but could now also be...

var commentsOfMembers = context.Members
    .Where(m => m.LastName == "Smith")
    .SelectMany(m => m.Comments)
    .ToList();

Or to create a list of members with the 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 => m.LastName == "Smith")
    .Select(m => new
    {
        Member = m,
        Comments = m.MemberComments.Select(mc => mc.Comment)
    })
    .ToList();

...but could now also be...

var membersWithComments = context.Members
    .Where(m => m.LastName == "Smith")
    .Select(m => 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 => m.FirstName == "John", m.LastName == "Smith")
    .Include(m => 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.

Cubby answered 19/4, 2018 at 17:26 Comment(2)
I appreciate the increased readability when typing out the LINQ queries. I may just have to adopt this method. I have to ask, does the EF EntitySet automatically update the view in the database as well? Would you agree this seems similar to the [Sugar] as described in the EF5.0 plan? github.com/dotnet/EntityFramework.Docs/blob/master/…Artillery
I'm wondering why you seem to redefine in EntityTypeConfiguration<EntityType> the key and properties of the entity type. E.g. Property(x => x.MemberID).HasColumnType("int").IsRequired(); seems to be redundant with public int MemberID { get; set; }. Could you clear my confusing understanding please?Halfbeak
S
4

I've come back here a couple times now, but it seems that EF Core has done a few updates in the past decade, so here's where I'm at currently with setting up many-to-many with custom join entity:

public class MemberModel
{
    public int MemberId { get; set; }

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

    public ICollection<CommentModel> Comments { get; set; }
}

public class CommentModel
{
    public int CommentId { get; set; }
    public string Message { get; set; }

    public ICollection<MemberModel> Members { get; set; }
}

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

    public int MembersId { get; set; }
    [ForeignKey("MembersId")]
    public MemberModel Member { get; set; }

    public int CommentsId { get; set; }
    [ForeignKey("CommentsId")]
    public CommentModel Comment { get; set; }
}

Then in your OnModelCreating:

//Allows access directly from Comments or Members entities to the other
builder.Entity<MemberModel>()
    .HasMany(x => x.Comments)
    .WithMany(x => x.Members)
    .UsingEntity<MemberCommentModel>();

//Defines the actual relationships for the middle table
builder.Entity<MemberCommentModel>()
    .HasOne(x => x.Comment)
    .WithOne()
    .OnDelete(DeleteBehavior.NoAction);
builder.Entity<MemberCommentModel>()
    .HasOne(x => x.Member)
    .WithOne()
    .OnDelete(DeleteBehavior.NoAction);
Slowdown answered 28/9, 2022 at 12:50 Comment(1)
This answer is the most relevant today. Also if somene follows a DDD approach and doesn't want to have attributes inside entity then he should add .HasForeignKey<>(x => x.Id) to the chain (config of midde table)Haywoodhayyim
E
0

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.

Efflorescence answered 3/3, 2017 at 10:8 Comment(0)
L
-1

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.

enter image description here

Lugar answered 29/12, 2017 at 17:32 Comment(2)
Please add a minimal explanation in the answer itself, as the link provided may happen to unavailable in the future.Goldman
It should be the name of the navigation property, rather than the class.Heeley

© 2022 - 2024 — McMap. All rights reserved.