Entity Framework Core always returns an empty list related tables

Question

I'm using .Net Core 5.0 and Entity Framework and I'm having issues with .Include(), it's returning empty lists.

Here are the classes :

public class Contact
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string FullName { get; set; }
    public string Address { get; set; }
    public string Email { get; set; }
    public string MobilePhoneNumber { get; set; }

    public ICollection<ContactSkill> ContactSkills { get; set; }
}

public class Skill
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int ExpertiseLevel { get; set; }

    public ICollection<ContactSkill> ContactSkills { get; set; }
}

public class ContactSkill
{
    public int Id { get; set; }
    public int ContactId { get; set; }
    public int SkillId { get; set; }

    public Contact Contact { get; set; }
    public Skill Skill { get; set; }
}

And the DbContext :

public class APIContext : DbContext
{
    public APIContext(DbContextOptions<APIContext> options) : base(options)
    {
    }

    public DbSet<Contact> Contacts { get; set; }
    public DbSet<Skill> Skills { get; set; }
    public DbSet<ContactSkill> ContactSkills { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Contact>().ToTable("Contacts");
        modelBuilder.Entity<Skill>().ToTable("Skills");
        modelBuilder.Entity<ContactSkill>().ToTable("ContactSkills");
    }
}

Both Contacts and Skills are filled properly but when I add the include, it's empty :

        var contact = await _context.Contacts
            .Include(c => c.ContactSkills)
                .ThenInclude(cs => cs.Skill)
            .AsNoTracking()
            .FirstOrDefaultAsync(m => m.Id == id);

DbInitializer.cs :

    public static void Initialize(APIContext context)
    {
        context.Database.EnsureCreated();

        // Look for any contacts.
        if (context.Contacts.Any())
        {
            return;   // DB has been seeded
        }

        var contacts = new Contact[]
        {,
        new Contact{ FirstName = "Sam", LastName = "Gamegi", FullName = "Sam Gamegi", Address = "The Shire", Email = "[email protected]", MobilePhoneNumber = "+11 0 98 76 54 32"  },
            new Contact{ FirstName = "Samus", LastName = "Samus Aran", FullName = "Samus Aran", Address = "Space", Email = "[email protected]", MobilePhoneNumber = "+66 1 23 45 76 90"  },
        new Contact{ FirstName = "Frodon", LastName = "Baggins", FullName = "Frodon Baggins", Address = "The Shire", Email = "[email protected]", MobilePhoneNumber = "+11 0 98 76 54 32"  },
        };

        // Look for any skills.
        if (context.Skills.Any())
        {
            return;   // DB has been seeded
        }

        var skills = new Skill[]
        {
            new Skill{ Name = ".Net Framework", ExpertiseLevel = 75},
            new Skill{ Name = "SQL", ExpertiseLevel = 80},
            new Skill{ Name = "Bounty Hunter", ExpertiseLevel = 100},
            new Skill{ Name = "Ring Bearer", ExpertiseLevel = 100}
        };

        foreach (var s in skills)
        {
            context.Skills.Add(s);
        }
        context.SaveChanges();

        // Look for any contact's skills.
        if (context.ContactSkills.Any())
        {
            return;   // DB has been seeded
        }

        var contactSkills = new ContactSkill[]
        {
            new ContactSkill{ ContactId = 1, SkillId = 1},
            new ContactSkill{ ContactId = 1, SkillId = 2},
            new ContactSkill{ ContactId = 2, SkillId = 3},
            new ContactSkill{ ContactId = 3, SkillId = 4},
        };

        foreach (var cs in contactSkills)
        {
            context.ContactSkills.Add(cs);
        }
        context.SaveChanges();
    }

I suppose my db is created because I see the data but I don't see my db in SQL Explorer.

Answer 1

Try to use this code. It was tested in Visual Studio.

public class Contact

{
    public Contact()
    {
        ContactSkills = new HashSet<ContactSkill>();
    }
    [Key]
    public int Id { get; set; }
    [Required]
    public string LastName { get; set; }
    [Required]
    public string FirstName { get; set; }
  
    public string FullName { get; set; }
    public string Address { get; set; }
    public string Email { get; set; }
    public string MobilePhoneNumber { get; set; }
    [InverseProperty(nameof(ContactSkill.Contact))]
    public virtual ICollection<ContactSkill> ContactSkills { get; set; }
}


public class Skill
{
    public Skill()
    {
        ContactSkills = new HashSet<ContactSkill>();
    }
    [Required]
    public int Id { get; set; }
    [Required]
    public string Name { get; set; }
    public int ExpertiseLevel { get; set; }
    [InverseProperty(nameof(ContactSkill.Skill))]
    public virtual ICollection<ContactSkill> ContactSkills { get; set; }
}

public class ContactSkill
{
    [Key]
    public int Id { get; set; }

    public int ContactId { get; set; }

    [ForeignKey(nameof(ContactId))]
    [InverseProperty(nameof(ContactSkill.Contact.ContactSkills))]
    public virtual Contact Contact { get; set; }

    public int SkillId { get; set; }

    [ForeignKey(nameof(SkillId))]
    [InverseProperty(nameof(ContactSkill.Contact.ContactSkills))]
    public virtual Skill Skill { get; set; }
}


public class ApiContext : DbContext
{
    public ApiContext()
    {
    }

    public ApiContext(DbContextOptions<ApiContext> options)
        : base(options)
    {
    }
    public DbSet<Contact> Contacts { get; set; }
    public DbSet<Skill> Skills { get; set; }
    public DbSet<ContactSkill> ContactSkills { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
            if (!optionsBuilder.IsConfigured)
            {
            }
//Use your connection string if it is not configured in Startup file. 
//optionsBuilder.UseSqlServer(@"Server=localhost;Database=Contacts;Trusted_Connec//tion=True;");
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<ContactSkill>(entity =>
        {
            entity.HasOne(d => d.Contact)
                .WithMany(p => p.ContactSkills)
                .HasForeignKey(d => d.ContactId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_ContactSkill_Contact");

            entity.HasOne(d => d.Skill)
                .WithMany(p => p.ContactSkills)
                .HasForeignKey(d => d.SkillId)
                .HasConstraintName("FK_ContactSkill_Skill");
        });

    }

}