Entity Framework Many to Many - Cannot get data

Question

I have introduced a many to many relationship between two of my existing tables. For this, I have added a third table, which contains only the Ids of the other two tables.

Since I am using EF, I have also added

public virtual List<EntityOne> EntityOnes in EntityTwo

and

public virtual List<EntityTwo> EntityTwos in EntityOne.

However, with this, when I get the EntityTwo object, it does not contain the associated EntityOne object. The list has a count of zero, even though the data is there in the tables.

Am I missing something here? Is there anything else, I need to do?

---

Not sure,if this is relevant, but I have also this in OnModelCreation

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<EntityOne>().
                HasMany(p => p.EntityTwos).
                WithMany(a => a.EntityOnes).
                Map(
                                m =>
                                {
                                    m.MapLeftKey("EntityTwoId");
                                    m.MapRightKey("EntityOneId");
                                    m.ToTable("EntityRelations");
                                });
            ////Make sure a context is not created by default.
        } 

Answer 1

Try this:

public partial class One
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public virtual int Id { get; set; }

    private ICollection<OneTwo> _oneTwos;
    public virtual ICollection<OneTwo> OneTwos
    {
        get { return _oneTwos ?? (_oneTwos = new List<OneTwo>()); }
        set { _oneTwos = value; }
    }
}

public partial class Two
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public virtual int Id { get; set; }

    private ICollection<OneTwo> _oneTwos;
    public virtual ICollection<OneTwo> OneTwos
    {
        get { return _oneTwos ?? (_oneTwos = new List<OneTwo>()); }
        set { _oneTwos = value; }
    }
}

Add navigation properties to the join class:

public partial class OneTwo
{
    public virtual int OneId { get; set; }
    public virtual int TwoId { get; set; }

    public virtual One One { get; set; }
    public virtual Two Two { get; set; }
}

Add composite key to the join class and configure relationships:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<OneTwo>() // composite primary key
                .HasKey(p => new { p.OneId, p.TwoId });
    modelBuilder.Entity<OneTwo>()
                .HasRequired(a => a.One)
                .WithMany(c => c.OneTwos)
                .HasForeignKey(fk => fk.OneId)
                .WillCascadeOnDelete(false);
    modelBuilder.Entity<OneTwo>()
                .HasRequired(a => a.Two)
                .WithMany(c => c.OneTwos)
                .HasForeignKey(fk => fk.TwoId)
                .WillCascadeOnDelete(false);
    // TODO: handle orphans when last asociation is deleted
}

An alternative strategy is to configure EF relationships via EntityTypeConfiguration<>. The following many-to-many relationship implementation demonstrates that approach:

City.cs

public partial class City
{
    public virtual int Id { get; set; }

    private ICollection<CountyCity> _countiesCities;
    public virtual ICollection<CountyCity> CountiesCities
    {
        get { return _countiesCities ?? (_countiesCities = new List<CountyCity>()); }
        set { _countiesCities = value; }
    }
}

County.cs

public partial class County
{
    public virtual int Id { get; set; }

    private ICollection<CountyCity> _countiesCities;
    public virtual ICollection<CountyCity> CountiesCities
    {
        get { return _countiesCities ?? (_countiesCities = new List<CountyCity>()); }
        set { _countiesCities = value; }
    }
}

CountyCity.cs

public partial class CountyCity
{
    public virtual int CountyId { get; set; }
    public virtual int CityId { get; set; }

    public virtual County County { get; set; }
    public virtual City City { get; set; }
}

CountyCityConfiguration.cs (EF 6 implementation)

public class CountyCityConfiguration : IEntityTypeConfiguration<CountyCity>
{
    public void Map(EntityTypeBuilder<CountyCity> builder)
    {
        // Table and Schema Name declarations are optional
        //ToTable("CountyCity", "dbo");

        // composite primary key
        builder.HasKey(p => new { p.CountyId, p.CityId });

        builder.HasOne(pt => pt.County)
               .WithMany(p => p.CountiesCities)
               .HasForeignKey(pt => pt.CountyId)
               .OnDelete(DeleteBehavior.Restrict);

        builder.HasOne(pt => pt.City)
               .WithMany(t => t.CountiesCities)
               .HasForeignKey(pt => pt.CityId)
               .OnDelete(DeleteBehavior.Restrict);

        // TODO: handle orphans when last association is deleted
    }
}

Entity Framework 6 Implementations:

You may configure the composite key and relationships using EntityTypeConfiguration<> as the previous code demonstrates.

Entity Framework Core Implementations:

EntityTypeConfiguration<> has not yet been migrated. However, it is on the roadmap for the next release.

In the meantime, you can employ the temporary pattern suggested by the EF team, or one of the patterns discussed this rather lengthy StackOverflow post discussing entity configuration in Entity Framework 7.

I implemented the pattern posted by Cocowalla in the lengthy discussion prior to reading the EF Team post. The source code for my workaround is available in this GitHub repository.

IEntityTypeConfiguration.cs

namespace Dna.NetCore.Core.DAL.EFCore.Configuration.Temporary.Cocowalla
{
    // attribute: https://stackoverflow.com/questions/26957519/ef-7-mapping-entitytypeconfiguration/35373237#35373237
    public interface IEntityTypeConfiguration<TEntityType> where TEntityType : class
    {
        void Map(EntityTypeBuilder<TEntityType> builder);
    }
}

Here is my implementation of that pattern:

namespace Dna.NetCore.Core.DAL.EFCore.Configuration.Common
{
    public class StateOrProvinceConfiguration : IEntityTypeConfiguration<StateOrProvince>
    {
        public void Map(EntityTypeBuilder<StateOrProvince> builder)
        {
            // EF Core
            builder.HasOne(p => p.Country).WithMany(p => p.StateOrProvinces).HasForeignKey(s => s.CountryId).OnDelete(DeleteBehavior.Cascade);
            builder.HasMany(d => d.Cities).WithOne().OnDelete(DeleteBehavior.Cascade);
            builder.HasMany(d => d.Counties).WithOne().OnDelete(DeleteBehavior.Cascade);
        }
    }
}