创建code首先，多对多，与关联表的附加字段 [英] 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; }
}
如何配置与流利的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; }
}
如果您现在想找到姓 =史密斯成员的所有意见，例如，你可以写这样的查询：
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();
......或者......
...or...
var commentsOfMembers = context.MemberComments
.Where(mc => mc.Member.LastName == "Smith")
.Select(mc => mc.Comment)
.ToList();
或者要创建一个名为史密斯的成员名单（我们假设有一个以上的）与他们的意见以及您可以使用投影：
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();
现在，你还可以通过你的连接表的属性（这不是在一个多一对多的关系是可能的），例如过滤：过滤器，在财产99件1的所有评论的东西：
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();
由于延迟加载的事情可能变得更加容易。如果你有一个加载成员你应该能够得到的意见没有explicite查询：
Because of lazy loading things might become easier. If you have a loaded Member you should be able to get the comments without an explicite 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）创建新的成员，它与现有的注释2：
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及其所有relationsships的评论：
6) Delete member1 and all its relationsships to the comments:
var member1 = context.Members.Where(m => m.FirstName == "Pete")
.SingleOrDefault();
if (member1 != null)
{
context.Members.Remove(member1);
context.SaveChanges();
}
这将删除 MemberComments 的关系也因为成员和<$ C之间的一到多的关系$ C> MemberComments 之间注释和 MemberComments 的设置与级联按约定删除。而这种情况下，因为 MEMBERID 和 CommentId 在 MemberComment 被检测为为会员外键的属性和注释导航性能，因为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.
这篇关于创建code首先，多对多，与关联表的附加字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
