多对多关系使用实体框架6返回0元素集合

Question

我有一个可能很简单的问题，我正在尝试使用实体框架和fluent api创建多对多关系，我的问题是，当我试图在debug中执行任何查询或查看对象时，它总是0项。

我使用的连接表如下所示：

​

​

所以关系是存在的，为了确保我检查过：

select candidate.firstname，skillset.name from candidate join candidate_skillset on candidate.id = candidate_skillset.candidate_id join技能集on candidate_skillset.skillset_id = skillset.id

并显示连接的结果。

现在我的上下文看起来像这样：

public class CatalogContexct : DbContext
{
    public DbSet<Candidate> Candidates { get; set; }
    public DbSet<SkillSet> SkillSets { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Candidate>().HasMany(t => t.SkillSets).WithMany(t => t.Candidates)
        .Map(m =>
        {
            m.ToTable("candidate_skillset");
            m.MapLeftKey("candidate_id");
            m.MapRightKey("skillset_id");
        });

        modelBuilder.Entity<SkillSet>().ToTable("skillset");
        modelBuilder.Entity<Candidate>().ToTable("candidate");
    }
}

我的左侧模型候选人：

[Table("candidate")]
public class Candidate
{
    public Candidate()
    {
        this.SkillSets = new HashSet<SkillSet>();
    }

    [Key]
    public int id { get; set; }

    [Column("firstname")]
    public string Firstname { get; set; }

    public int? commendation_id { get; set; }
    [ForeignKey("commendation_id")]
    public Commendation commendation { get; set; }

    public ICollection<SkillSet> SkillSets { get; set; }
}

和我右边的模型技能集：

[Table("skillset")]
public class SkillSet : SimpleDictionary
{
    public SkillSet()
    {
        this.Candidates = new HashSet<Candidate>();
    }

    public virtual ICollection<Candidate> Candidates { get; set; }
}

该模型有一个父类：

public class SimpleDictionary
{
    [Key]
    public int id { get; set; }

    [Column("name")]
    public string Name { get; set; }
}

因此，一切都应该正常工作，但当我这样做时，例如：

        var ca = this._catalog.Candidates
            .Include("SkillSets").Include("commendation").
            FirstOrDefault(x => x.SkillSets.Any());

结果为空，而且当我在属性skillset的debug集合上查看对象时发现有0个元素，你知道它有什么问题吗？

Answer 1

我用你的问题中提到的相同结构尝试了这个，并在本地尝试了。我可以用这段代码来获取数据。请尝试一下，让我知道这是否有帮助。为了简单起见，我只是省略了表扬表。

        var context = new SampleDbContext();

        var candidates = context.Candidates
            .Include("SkillSets").ToList();


        foreach (var candidate in candidates)
        {
            foreach (var sk in candidate.SkillSets.Where(  s1 => s1.Candidates.Count(c=>c.id == candidate.id)>0 ))
            {
                Console.WriteLine( string.Format(@" Name : {0} Skill :{1}",candidate.Firstname ,sk.Name )  );
            }
        }

下面是我的DbContext和其他实体类

 using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
    public class SampleDbContext : DbContext
    {
        public SampleDbContext()
            : base("name=SampleDBConnection")
        {
            this.Configuration.LazyLoadingEnabled = false;
        }

        public DbSet<Candidate> Candidates { get; set; }
        public DbSet<SkillSet> SkillSets { get; set; }


        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Candidate>().HasMany(t => t.SkillSets).WithMany(t => t.Candidates)
        .Map(m =>
        {
            m.ToTable("candidate_skillset");
            m.MapLeftKey("candidate_id");
            m.MapRightKey("skillset_id");
        });

            modelBuilder.Entity<SkillSet>().ToTable("skillset");
            modelBuilder.Entity<Candidate>().ToTable("candidate");
        }
    }

    [Table("candidate")]
    public class Candidate
    {
        public Candidate()
        {
            this.SkillSets = new HashSet<SkillSet>();
        }

        [Key]
        public int id { get; set; }

        [Column("firstname")]
        public string Firstname { get; set; }

        public int? commendation_id { get; set; }

        //[ForeignKey("commendation_id")]
        //public Commendation commendation { get; set; }

        public ICollection<SkillSet> SkillSets { get; set; }
    }

    public class SimpleDictionary
    {
        [Key]
        public int id { get; set; }

        [Column("name")]
        public string Name { get; set; }
    }

    [Table("skillset")]
    public class SkillSet : SimpleDictionary
    {
        public SkillSet()
        {
            this.Candidates = new HashSet<Candidate>();
        }

        public virtual ICollection<Candidate> Candidates { get; set; }
    }


}

您提到的查询的输出与我的代码的结果都匹配，我希望这就是您想要的。

​

​