我有这样一个场景:

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 MemberModel
{
    public int MemberId { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }

    public ICollection<CommentModel> Comments { get; set; }
}

public class CommentModel
{
    public int CommentId { get; set; }
    public string Message { get; set; }

    public ICollection<MemberModel> Members { get; set; }
}

public class MemberCommentModel
{
    public int Something { get; set; }
    public string SomethingElse { get; set; }

    public int MembersId { get; set; }
    [ForeignKey("MembersId")]
    public MemberModel Member { get; set; }

    public int CommentsId { get; set; }
    [ForeignKey("CommentsId")]
    public CommentModel Comment { get; set; }
}

然后在OnModelCreating中:

//Allows access directly from Comments or Members entities to the other
builder.Entity<MemberModel>()
    .HasMany(x => x.Comments)
    .WithMany(x => x.Members)
    .UsingEntity<MemberCommentModel>();

//Defines the actual relationships for the middle table
builder.Entity<MemberCommentModel>()
    .HasOne(x => x.Comment)
    .WithOne()
    .OnDelete(DeleteBehavior.NoAction);
builder.Entity<MemberCommentModel>()
    .HasOne(x => x.Member)
    .WithOne()
    .OnDelete(DeleteBehavior.NoAction);

其他回答

我将发布使用fluent 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)
    }
}

它和公认的答案有同样的效果,只是方法不同,没有更好也没有更坏。

我想提出一种可以同时实现多对多配置的解决方案。

“问题”是我们需要创建一个以连接表为目标的视图,因为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)的回答

如果您现在想要找到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);

这一切都感觉像是语法糖,但是,如果您愿意进行额外的配置,它确实会给您带来一些好处。无论哪种方式,您似乎都能够获得两种方法的最佳效果。

解决这个错误的一种方法是将ForeignKey属性放在你想要作为外键的属性之上,并添加navigation属性。

注意:在ForeignKey属性中,在括号和双引号之间,放置以这种方式引用的类的名称。

我现在已经回到这里几次了,但似乎EF核心已经在过去的十年里做了一些更新,所以这是我目前在哪里与自定义连接实体设置多对多:

public class MemberModel
{
    public int MemberId { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }

    public ICollection<CommentModel> Comments { get; set; }
}

public class CommentModel
{
    public int CommentId { get; set; }
    public string Message { get; set; }

    public ICollection<MemberModel> Members { get; set; }
}

public class MemberCommentModel
{
    public int Something { get; set; }
    public string SomethingElse { get; set; }

    public int MembersId { get; set; }
    [ForeignKey("MembersId")]
    public MemberModel Member { get; set; }

    public int CommentsId { get; set; }
    [ForeignKey("CommentsId")]
    public CommentModel Comment { get; set; }
}

然后在OnModelCreating中:

//Allows access directly from Comments or Members entities to the other
builder.Entity<MemberModel>()
    .HasMany(x => x.Comments)
    .WithMany(x => x.Members)
    .UsingEntity<MemberCommentModel>();

//Defines the actual relationships for the middle table
builder.Entity<MemberCommentModel>()
    .HasOne(x => x.Comment)
    .WithOne()
    .OnDelete(DeleteBehavior.NoAction);
builder.Entity<MemberCommentModel>()
    .HasOne(x => x.Member)
    .WithOne()
    .OnDelete(DeleteBehavior.NoAction);

这个答案提供的代码是正确的,但不完整,我已经测试过了。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