How Can I Create a Custom Join Table with Additional Properties in a Many-to-Many Relationship using EF Code First?

Using a custom connection table in multiple relationships

When using EF Code First, it is impossible to use a custom connection table to establish a multi -pair relationship. EF internal management connection table is hidden outside the model.

Alternative method: a pair of relationships

To create a custom connection table with additional attributes, you can use two different methods of one -to -multiple relationships:

This creates two independent multi -relationships between Member and MemberComment and Comment and MemberComment. The connection table is represented by the MemberComment class.

<code class="language-csharp">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; }
}</code>

The advantages of this method

This method has the following advantages:

Allows the additional attributes on the connection table (such as Something and SomethingLSE).

Provide more flexible queries and filtering by the connection table attributes.

• Reserve the deletion of the level by agreed.
Example query

Using this model, you can perform various queries, such as:

Find all the comments with members with specific surnames:

• Find members with specific names and their comments:

<code class="language-csharp">var commentsOfMembers = context.Members
    .Where(m => m.LastName == "Smith")
    .SelectMany(m => m.MemberComments.Select(mc => mc.Comment))
    .ToList();</code>

• Filter members' comments through the attributes in the connection table:

<code class="language-csharp">var membersWithComments = context.Members
    .Where(m => m.LastName == "Smith")
    .Select(m => new
    {
        Member = m,
        Comments = m.MemberComments.Select(mc => mc.Comment)
    })
    .ToList();</code>

The above is the detailed content of How Can I Create a Custom Join Table with Additional Properties in a Many-to-Many Relationship using EF Code First?. For more information, please follow other related articles on the PHP Chinese website!