我有这种情况:
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配置关联?还是有更好的方法来创建关联表?
用自定义的联接表创建多对多关系是不可能的。在多对多关系中,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” 的成员的所有注释,则可以编写如下查询:
LastName
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的所有注释:
MemberId
var commentsOfMember = context.MemberComments .Where(mc => mc.MemberId == 1) .Select(mc => mc.Comment) .ToList();
现在,您还可以按联接表中的属性进行过滤(在多对多关系中这是不可能的),例如:过滤成员1中属性为99的所有注释Something:
Something
var filteredCommentsOfMember = context.MemberComments .Where(mc => mc.MemberId == 1 && mc.Something == 99) .Select(mc => mc.Comment) .ToList();
由于延迟加载,事情可能会变得更容易。如果您已加载Member,则无需显式查询即可获得注释:
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是建立与惯例级联删除。之所以如此,是因为将MemberId和CommentIdin MemberComment检测为Member和Comment导航属性的外键属性,并且由于FK属性的类型为不可为空int,因此需要这种关系,最终导致级联删除设置。我认为在这种模型中是有道理的。
MemberComments
Comment
CommentId
MemberComment
int