EF自身之间的核心关系映射 [英] EF Core relationship mapping between itself

问题描述

背景

假设数据库中有两个表

Member

ID	名称
1	Tony
2	Steve
3	布鲁斯
4	Scott

MemberRecruit

MemberID	RecruitmentMemberID
1	2
1	3
2	4

其中RecruitmentMemberId仅为MemberId

有两个类

public class Member
{
   public int Id { get; set; }
   public string Name { get; set; }
   public IList<MemberRecruit> Recruits { get; set; }
}

public class MemberRecruit
{
   public int MemberId { get; set; }
   public int RecruitmentMemberId { get; set; }
}

使用EF Core 3.1，很容易在DbContext中为member.Recuits创建OwnsMany关系，如

builder.Entity<Member>(m =>
{
    m.ToTable("Member", "dbo");

    m.HasKey(x => x.MemberId);

    m.OwnsMany(x => x.Recruits, nav =>
    {
        nav.ToTable("MemberRecruit", "dbo");
        nav.HasKey(x => new
        {
            x.MemberId, 
            x.RecruitmentMemberId
        });
        nav.WithOwner().HasForeignKey(x => x.MemberId);
    });
});

问题

如果我想要将类结构更改为类似

public class Member
{
   public int Id { get; set; }
   public string Name { get; set; }
   public IList<MemberRecruit> Recruits { get; set; }
}

public class MemberRecruit
{
   public int MemberId { get; set; }
   public int RecruitmentMemberId { get; set; }
   public Member Recruitment { get; set; } // This is the recruited member
}

在不更改表的情况下，使用Owns或.HasOne.WithMany + .HasMany.WithOne关系在DbContext和FluentAPI中映射它，这样当我有DbSet<Member> Members时，我可以进行如下查询，这是一个好做法吗？

var recruitments = (await _db.Members.AsNoTrack()
                             .Include(x => x.Recruits)
                             .FirstOrDefaultAsync(x => x.Id == request.Id, token))
                          .Recruits
                          .Select(x => x.Recruitment, token);
return recruitments.Select(x => (x.Id, x.Name));
// if request.Id is 1, return will be [(2,"Steve"),(3,"Bruce")]
// if request.Id is 2, return will be [(4,"Scott")]
// if request.Id is 3, return will be []

---

或使用相同的两个表，映射以下类

public class Member
{
   public int Id { get; set; }
   public string Name { get; set; }
   public IList<Member> Recruitments { get; set; }
}

以便将查询简化为

var member = await _db.Members.AsNoTrack()
                               .Include(x => x.Recruitments)
                               .FirstOrDefaultAsync(x => x.Id == request.Id, token);
return member.Recruitments.Select(x => (x.Id, x.Name));

我尝试的内容

测试1

builder.Entity<Member>(m =>
{
    m.ToTable("Member", "dbo");

    m.HasKey(x => x.MemberId);

    m.OwnsMany(x => x.Recruits, nav =>
    {
        nav.ToTable("MemberRecruit", "dbo");
        nav.HasKey(x => new
        {
            x.MemberId, 
            x.RecruitmentMemberId
        });
        nav.WithOwner().HasForeignKey(x => x.MemberId);

        nav.HasOne(x => x.Member) // Added HasOne.WithMany
           .WithMany(x => x.Recruits)
           .HasForeignKey(x => x.RecruitmentMemberId);
    });
});

此退货

var member = await _db.Members.AsNoTrack()
                      .Include(x => x.Recruits)
                      .FirstOrDefaultAsync(x => x.Id == 1, token);
// member.Recurits.Count is 1 instead of 2
// member.Recurits[0].Recruitment.Id is 1 which is incorrect

测试2

builder.Entity<Member>(m =>
{
    m.ToTable("Member", "dbo");

    m.HasKey(x => x.MemberId);

    m.OwnsMany(x => x.Recruits, nav =>
    {
        nav.ToTable("MemberRecruit", "dbo");
        nav.HasKey(x => new
        {
            x.MemberId, 
            x.RecruitmentMemberId
        });
        nav.WithOwner().HasForeignKey(x => x.MemberId);

        nav.HasOne(x => x.Member)
           .WithMany(x => x.Recruits)
           .HasForeignKey(x => x.MemberId); // Change the foreign key
    });
});

此退货

var member = await _db.Members.AsNoTrack()
                      .Include(x => x.Recruits)
                      .FirstOrDefaultAsync(x => x.Id == 1, token);
// member.Recurits.Count is 2 which is correct
// member.Recurits.Select(x => x.Recruitment.Id) are all 1 which is incorrect

推荐答案

将关系添加到您的类

    public partial class Member
    {
        public Member()
        {
            MemberMembers = new HashSet<MemberRecruit>();
            MemberRecruits = new HashSet<MemberRecruit>();
        }

        [Key]
        public int Id { get; set; }
        public string Name { get; set; }

       

        [InverseProperty(nameof(MemberRecruit.Member))]
        public virtual ICollection<MemberRecruit> MemberRecruits { get; set; }

        [InverseProperty(nameof(MemberRecruit.Recruit))]
        public virtual ICollection<MemberRecruit> MemberMembers { get; set; }
    }
        public partial class MemberRecruit
        {
            [Key]
            public int Id { get; set; }
            public int RecruitId { get; set; }
            public int MemberId { get; set; }

            [ForeignKey(nameof(MemberId))]
            [InverseProperty("MemberRecruits")]
            public virtual Member Member { get; set; }

            [ForeignKey(nameof(RecruitId))]
            [InverseProperty("MemberMembers")]
            public virtual Member Recruit { get; set; }
        }

和数据库上下文

        public virtual DbSet<Member> Members{ get; set; }
        public virtual DbSet<MemberRecruit> MemberRecruits { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
          modelBuilder.Entity<MemberRecruit>(entity =>
            {
                entity.HasOne(d => d.Member)
                    .WithMany(p => p.MemberRecruits)
                    .HasForeignKey(d => d.MemberId)
                    .OnDelete(DeleteBehavior.ClientSetNull);


                entity.HasOne(d => d.Recruit)
                    .WithMany(p => p.MemberMembers)
                    .HasForeignKey(d => d.RecruitId);

            });

由于任何成员可能是其他成员的新兵和另一个新兵的成员，因此它有2个虚拟集合-1显示他是另一个新兵的成员的记录(在MemberID部分)，第二个显示他是另一个成员的新兵的记录(在Recruit ID部分)。因此，如果您想要查看Members的所有招聘人员，您可以通过两种方式

var recruiters=context.MemberRecruits
.Where(i=> i.MemberId==memberId)
.Select(i=>i.Recruit)
.ToList();
//or 
var recruits = _context.Members
.Where(i => i.Id == memberId)
.Select(i => i.MemberRecruits.Select(j => j.Recruit)).FirstOrDefault();

第二个查询看起来有点奇怪，但您应该理解，Members是指当Members是成员时，Recruits-where Members就是招聘人员。也许更好的做法是将集合重命名为&Quot；AsMember&Quot；和&Quot；AsRecruiter&Quot；。由你决定。 由于查询MemberRecruit要容易得多，我建议使用它进行查询。 再举一个例子。成员ID为招聘人员的成员

var members=context.MemberRecruits
.Where(i=> i.RecruiteId==memberId)
.Select(i=>i.Member)
.ToList();

这篇关于EF自身之间的核心关系映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！