首先创建代码，多对多，关联表中的附加字段 [英] Create code first, many to many, with additional fields in association table

问题描述

我有这种情况:

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; }
}

如何配置我与 fluent API 的关联?或者有没有更好的方法来创建关联表?

How do I configure my association with fluent API? Or is there a better way to create the association table?

推荐答案

无法使用自定义连接表创建多对多关系.在多对多关系中，EF 在内部管理并隐藏连接表.这是一个在您的模型中没有实体类的表.要使用带有附加属性的连接表，您实际上必须创建两个一对多关系.它可能看起来像这样:

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; }
}

例如，如果您现在想查找具有 LastName = "Smith" 的成员的所有评论，您可以编写如下查询:

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();

...或...

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

或者要创建一个名为Smith"的成员列表(我们假设有多个)以及他们的评论，您可以使用投影:

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();

如果要查找MemberId = 1的成员的所有评论:

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();

现在您还可以按联接表中的属性进行过滤(这在多对多关系中是不可能的)，例如:过滤成员 1 的所有评论，其属性为 99 Something:

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();

由于延迟加载，事情可能会变得更容易.如果你有一个加载的 Member 你应该能够在没有明确查询的情况下获得评论:

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.

编辑

为了好玩，再举几个例子，如何在这个模型中添加实体和关系以及如何删除它们:

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

1) 创建一个成员和该成员的两条评论:

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) 添加member1的第三条评论:

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) 创建新成员并将其与现有评论相关联:

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) 在现有 member2 和 comment3 之间创建关系:

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) 再次删除此关系:

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) 删除 member1 及其与评论的所有关系:

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();
}

这也删除了 MemberComments 中的关系，因为 Member 和 MemberComments 之间以及 Comment<之间是一对多的关系/code> 和 MemberComments 按照惯例设置为级联删除.之所以如此，是因为 MemberComment 中的 MemberId 和 CommentId 被检测为 Member 和 Comment 导航属性，并且由于 FK 属性的类型是不可为空的 int，因此需要最终导致级联删除设置的关系.我认为在这个模型中是有意义的.

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.

这篇关于首先创建代码，多对多，关联表中的附加字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！