Many to many relationship returns 0 element collection using entity framework 6

Question

I have a probably simple question, I am trying to create many to many relationships using entity framework and fluent api, and my problem is that when i try to do any query or view a object in debug it has always 0 items.

I am using junction table that looks like:

So relations exists, to be sure ive checked:

select candidate.firstname, skillset.name from candidate join candidate_skillset on candidate.id = candidate_skillset.candidate_id join skillset on candidate_skillset.skillset_id = skillset.id

and joined results are displayed.

Now my context looks like:

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");
    }
}

My left side model candidates:

[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; }
}

And my rightside model skillset:

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

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

and that model has a parent class:

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

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

So all should work but when I do for example:

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

Result is null, also when I view object on debug collection of property skillset allays has 0 elements, any idea what could be wrong with it?

Answer 1

I tried this with same structure mentioned here in you question and tried locally . And I am able to get the data with this code . Please try this and let me know if this helps . I just omitted commendation table for simplicity .

        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 )  );
            }
        }

Below is my DbContext and Other Entity Classes

 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; }
    }


}

The output of the query you mentioned and the result of my code both matched I hope this is that you wanted .