我有这种情况:
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 在内部管理并隐藏连接表。这是一个在您的模型中没有实体类的表。要使用带有附加属性的连接表,您实际上必须创建两个一对多关系。它可能看起来像这样:
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();
现在您还可以按联接表中的属性进行过滤(这在多对多关系中是不可能的),例如: 过滤属性 Something
中具有 99 的成员 1 的所有评论:
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)创建新成员并将其与现有评论相关联:
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 属性的类型是不可为空的 int
关系是必需的,最终导致级联删除设置。我认为在这个模型中是有道理的。
我将使用流畅的 API 映射发布代码来执行此操作。
public class User {
public int UserID { get; set; }
public string Username { get; set; }
public string Password { get; set; }
public ICollection<UserEmail> UserEmails { get; set; }
}
public class Email {
public int EmailID { get; set; }
public string Address { get; set; }
public ICollection<UserEmail> UserEmails { get; set; }
}
public class UserEmail {
public int UserID { get; set; }
public int EmailID { get; set; }
public bool IsPrimary { get; set; }
}
在您的 DbContext
派生类上,您可以这样做:
public class MyContext : DbContext {
protected override void OnModelCreating(DbModelBuilder builder) {
// Primary keys
builder.Entity<User>().HasKey(q => q.UserID);
builder.Entity<Email>().HasKey(q => q.EmailID);
builder.Entity<UserEmail>().HasKey(q =>
new {
q.UserID, q.EmailID
});
// Relationships
builder.Entity<UserEmail>()
.HasRequired(t => t.Email)
.WithMany(t => t.UserEmails)
.HasForeignKey(t => t.EmailID)
builder.Entity<UserEmail>()
.HasRequired(t => t.User)
.WithMany(t => t.UserEmails)
.HasForeignKey(t => t.UserID)
}
}
它与 the accepted answer 具有相同的效果,但方法不同,没有更好或更差。
In your classes you can easily describe a many to many relationship with properties that point to each other.
取自:msdn.microsoft.com/en-us/data/hh134698.aspx。朱莉勒曼不会错。
Member
中没有 Comments
属性。而且您不能通过将 HasMany
调用重命名为 MemberComments
来解决此问题,因为 MemberComment
实体没有 WithMany
的逆集合。事实上,您需要配置两个一对多关系才能获得正确的映射。
code provided by this answer 是对的,但不完整,我已经测试过了。 “UserEmail”类中缺少属性:
public UserTest UserTest { get; set; }
public EmailTest EmailTest { get; set; }
如果有人感兴趣,我会发布我测试过的代码。问候
using System.Data.Entity;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;
#region example2
public class UserTest
{
public int UserTestID { get; set; }
public string UserTestname { get; set; }
public string Password { get; set; }
public ICollection<UserTestEmailTest> UserTestEmailTests { get; set; }
public static void DoSomeTest(ApplicationDbContext context)
{
for (int i = 0; i < 5; i++)
{
var user = context.UserTest.Add(new UserTest() { UserTestname = "Test" + i });
var address = context.EmailTest.Add(new EmailTest() { Address = "address@" + i });
}
context.SaveChanges();
foreach (var user in context.UserTest.Include(t => t.UserTestEmailTests))
{
foreach (var address in context.EmailTest)
{
user.UserTestEmailTests.Add(new UserTestEmailTest() { UserTest = user, EmailTest = address, n1 = user.UserTestID, n2 = address.EmailTestID });
}
}
context.SaveChanges();
}
}
public class EmailTest
{
public int EmailTestID { get; set; }
public string Address { get; set; }
public ICollection<UserTestEmailTest> UserTestEmailTests { get; set; }
}
public class UserTestEmailTest
{
public int UserTestID { get; set; }
public UserTest UserTest { get; set; }
public int EmailTestID { get; set; }
public EmailTest EmailTest { get; set; }
public int n1 { get; set; }
public int n2 { get; set; }
//Call this code from ApplicationDbContext.ConfigureMapping
//and add this lines as well:
//public System.Data.Entity.DbSet<yournamespace.UserTest> UserTest { get; set; }
//public System.Data.Entity.DbSet<yournamespace.EmailTest> EmailTest { get; set; }
internal static void RelateFluent(System.Data.Entity.DbModelBuilder builder)
{
// Primary keys
builder.Entity<UserTest>().HasKey(q => q.UserTestID);
builder.Entity<EmailTest>().HasKey(q => q.EmailTestID);
builder.Entity<UserTestEmailTest>().HasKey(q =>
new
{
q.UserTestID,
q.EmailTestID
});
// Relationships
builder.Entity<UserTestEmailTest>()
.HasRequired(t => t.EmailTest)
.WithMany(t => t.UserTestEmailTests)
.HasForeignKey(t => t.EmailTestID);
builder.Entity<UserTestEmailTest>()
.HasRequired(t => t.UserTest)
.WithMany(t => t.UserTestEmailTests)
.HasForeignKey(t => t.UserTestID);
}
}
#endregion
我想提出一个解决方案,可以实现多对多配置的两种风格。
“捕获”是我们需要创建一个以联接表为目标的视图,因为 EF 验证架构的表在每个 EntitySet
中最多可以映射一次。
这个答案增加了之前答案中已经说过的内容,并且不会覆盖任何这些方法,它建立在它们之上。
该模型:
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 virtual ICollection<MemberCommentView> MemberComments { get; set; }
}
public class Comment
{
public int CommentID { get; set; }
public string Message { get; set; }
public virtual ICollection<Member> Members { get; set; }
public virtual ICollection<MemberCommentView> MemberComments { get; set; }
}
public class MemberCommentView
{
public int MemberID { get; set; }
public int CommentID { get; set; }
public int Something { get; set; }
public string SomethingElse { get; set; }
public virtual Member Member { get; set; }
public virtual Comment Comment { get; set; }
}
配置:
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
public class MemberConfiguration : EntityTypeConfiguration<Member>
{
public MemberConfiguration()
{
HasKey(x => x.MemberID);
Property(x => x.MemberID).HasColumnType("int").IsRequired();
Property(x => x.FirstName).HasColumnType("varchar(512)");
Property(x => x.LastName).HasColumnType("varchar(512)")
// configure many-to-many through internal EF EntitySet
HasMany(s => s.Comments)
.WithMany(c => c.Members)
.Map(cs =>
{
cs.ToTable("MemberComment");
cs.MapLeftKey("MemberID");
cs.MapRightKey("CommentID");
});
}
}
public class CommentConfiguration : EntityTypeConfiguration<Comment>
{
public CommentConfiguration()
{
HasKey(x => x.CommentID);
Property(x => x.CommentID).HasColumnType("int").IsRequired();
Property(x => x.Message).HasColumnType("varchar(max)");
}
}
public class MemberCommentViewConfiguration : EntityTypeConfiguration<MemberCommentView>
{
public MemberCommentViewConfiguration()
{
ToTable("MemberCommentView");
HasKey(x => new { x.MemberID, x.CommentID });
Property(x => x.MemberID).HasColumnType("int").IsRequired();
Property(x => x.CommentID).HasColumnType("int").IsRequired();
Property(x => x.Something).HasColumnType("int");
Property(x => x.SomethingElse).HasColumnType("varchar(max)");
// configure one-to-many targeting the Join Table view
// making all of its properties available
HasRequired(a => a.Member).WithMany(b => b.MemberComments);
HasRequired(a => a.Comment).WithMany(b => b.MemberComments);
}
}
上下文:
using System.Data.Entity;
public class MyContext : DbContext
{
public DbSet<Member> Members { get; set; }
public DbSet<Comment> Comments { get; set; }
public DbSet<MemberCommentView> MemberComments { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Configurations.Add(new MemberConfiguration());
modelBuilder.Configurations.Add(new CommentConfiguration());
modelBuilder.Configurations.Add(new MemberCommentViewConfiguration());
OnModelCreatingPartial(modelBuilder);
}
}
来自 Saluma (@Saluma) answer
例如,如果您现在想查找 LastName = "Smith" 成员的所有评论,您可以编写如下查询:
这仍然有效...
var commentsOfMembers = context.Members
.Where(m => m.LastName == "Smith")
.SelectMany(m => m.MemberComments.Select(mc => mc.Comment))
.ToList();
...但现在也可能是...
var commentsOfMembers = context.Members
.Where(m => m.LastName == "Smith")
.SelectMany(m => m.Comments)
.ToList();
或者要创建一个名为“Smith”的成员列表(我们假设有多个)以及他们的评论,您可以使用投影:
这仍然有效...
var membersWithComments = context.Members
.Where(m => m.LastName == "Smith")
.Select(m => new
{
Member = m,
Comments = m.MemberComments.Select(mc => mc.Comment)
})
.ToList();
...但现在也可能是...
var membersWithComments = context.Members
.Where(m => m.LastName == "Smith")
.Select(m => new
{
Member = m,
m.Comments
})
.ToList();
如果您想删除成员的评论
var comment = ... // assume comment from member John Smith
var member = ... // assume member John Smith
member.Comments.Remove(comment);
如果您想Include()
成员的评论
var member = context.Members
.Where(m => m.FirstName == "John", m.LastName == "Smith")
.Include(m => m.Comments);
这一切都感觉像是语法糖,但是,如果您愿意进行额外的配置,它确实会给您带来一些好处。无论哪种方式,您似乎都能够充分利用这两种方法。
EntityTypeConfiguration<EntityType>
中重新定义了实体类型的键和属性。例如,Property(x => x.MemberID).HasColumnType("int").IsRequired();
似乎与 public int MemberID { get; set; }
是多余的。你能澄清我令人困惑的理解吗?
TLDR; (与 EF6/VS2012U5 中的 EF 编辑器错误半相关)如果您从 DB 生成模型并且看不到属性 m:m 表:删除两个相关表 -> 保存 .edmx -> 从数据库生成/添加 - > 保存。
对于那些来这里想知道如何获得与属性列的多对多关系以显示在 EF .edmx 文件中的人(因为它目前不会显示并被视为一组导航属性),并且您生成了这些类从您的数据库表中(或者我相信 MS 术语中的数据库优先。)
删除 .edmx 中的 2 个有问题的表(以 OP 为例,成员和评论),然后通过“从数据库生成模型”再次添加它们。 (即不要试图让 Visual Studio 更新它们 - 删除、保存、添加、保存)
然后它将根据此处的建议创建第三张表。
这与首先添加纯多对多关系,然后在数据库中设计属性的情况相关。
这个线程/谷歌搜索并没有立即清楚这一点。因此,只需将其放在那里,因为这是 Google 上的链接 #1,正在寻找问题,但首先来自数据库方面。
解决此错误的一种方法是将 ForeignKey
属性放在您想要作为外键的属性的顶部并添加导航属性。
注意:在 ForeignKey
属性中,在括号和双引号之间,放置以这种方式引用的类的名称。
https://i.stack.imgur.com/rfXBw.png
不定期副业成功案例分享
OnModelCreating
。该示例仅依赖于映射约定和数据注释。MemberId
和CommentId
列的复合键,而不是额外的第三列Member_CommentId
(或类似的东西) - 这意味着您的键的对象之间没有完全匹配的名称