不可能使用自定义连接表创建多对多关系。在多对多关系中，EF在内部和隐藏地管理连接表。它是一个在模型中没有Entity类的表。要使用这样一个具有其他属性的连接表，实际上必须创建两个一对多的关系。它可能是这样的:
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的成员的所有注释:
var commentsOfMember = context.MemberComments
.Where(mc => mc.MemberId == 1)
.Select(mc => mc.Comment)
.ToList();
现在你还可以通过连接表中的属性进行过滤(在多对多关系中是不可能的)，例如:过滤成员1中属性为99的所有注释
var filteredCommentsOfMember = context.MemberComments
.Where(mc => mc.MemberId == 1 && mc.Something == 99)
.Select(mc => mc.Comment)
.ToList();
因为惰性加载，事情可能会变得更容易。如果你有一个加载的成员，你应该能够在没有显式查询的情况下获得注释:
var commentsOfMember = member.MemberComments.Select(mc => mc.Comment);
我猜延迟加载会在幕后自动获取注释。
Edit
只是为了好玩一些例子，更多的如何添加实体和关系，以及如何删除他们在这个模型:
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)创建新成员，并将其与现有的评论联系起来。
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();
}
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.
