c# - 首先创建代码，多对多，关联表中有附加字段

Question

我有这种情况:

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 的关联？或者有没有更好的方法来创建关联表？

Answer 1

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

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"的成员的所有评论，您可以编写如下查询:

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”的成员列表(我们假设有多个成员)以及他们的评论，您可以使用投影:

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

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

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

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

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

因为延迟加载，事情可能会变得更容易。如果你有一个加载的Member，你应该能够在没有显式查询的情况下获得评论:

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

我猜延迟加载会在幕后自动获取评论。

编辑

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

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

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的第三条评论:

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

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 之间创建关系:

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

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

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

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