EF One to Many Relationship on Composite Key

Question

I'm having issues using Entity Framework (6.3) to retrieve a child collection of entities where the relationship uses a composite key. In the example below I'm trying to get the Sprints associated with a Plan, but the Sprints child collection keeps coming back empty.

// Returns no sprints
var queryUsingSelect = await _dbContext
    .Plans
    .Select(p => new
    {
        p,
        p.Sprints
    })
    .ToListAsync();

// Returns a plan without sprints
var queryUsingInclude = await _dbContext
    .Plans
    .Include(p => p.Sprints)
    .ToListAsync();

// Returns me all sprints
var allSprints = await _dbContext
    .Plans
    .SelectMany(p => p.Sprints)
    .ToListAsync();

In the last query I've tested it using SelectMany which does return Sprints, but really I need to be able to do it using Include. I'm having the same issue with another collection in the same project so it seems to be an issue with my approach in general. Note that I have lazy loading turned off to prevent accidental n+1 queries.

Here's a stripped down version of my code:

public class User
{
    public int UserId { get; set; }
    public string Name { get; set; }
}

public class Aspiration
{
    public int AspirationId { get; set; }
    public string Title { get; set; }
}

public class Plan
{
    public Plan()
    {
        Sprints = new List<Sprint>();
    }

    public int UserId { get; set; }
    public int AspirationId { get; set; }
    public virtual User User { get; set; }
    public virtual Aspiration Aspiration { get; set; }
    public virtual ICollection<Sprint> Sprints { get; set; }
}

public class Sprint
{
    public int SprintId { get; set; }
    public int UserId { get; set; }
    public int AspirationId { get; set; }
    public virtual Plan Plan { get; set; }
    public virtual User User { get; set; }
    public virtual Aspiration Aspiration { get; set; }
}

public class UserMap : EntityTypeConfiguration<User>
{
    public UserMap()
    {
        Property(t => t.Name)
            .HasMaxLength(100)
            .IsRequired();
    }
}

public class AspirationMap : EntityTypeConfiguration<Aspiration>
{
    public AspirationMap()
    {
        Property(t => t.Title)
            .HasMaxLength(100)
            .IsRequired();
    }
}

public class PlanMap : EntityTypeConfiguration<Plan>
{
    public PlanMap()
    {
        HasKey(s => new { s.UserId, s.AspirationId });

        HasRequired(s => s.User)
            .WithMany()
            .HasForeignKey(s => s.UserId);

        HasRequired(s => s.Aspiration)
            .WithMany()
            .HasForeignKey(s => s.AspirationId);
    }
}

public class SprintMap : EntityTypeConfiguration<Sprint>
{
    public SprintMap()
    {
        HasRequired(s => s.User)
            .WithMany()
            .HasForeignKey(s => s.UserId);

        HasRequired(s => s.Aspiration)
            .WithMany()
            .HasForeignKey(s => s.AspirationId);

        HasRequired(s => s.Plan)
            .WithMany(d => d.Sprints)
            .HasForeignKey(s => new { s.AspirationId, s.UserId });
    }
}

public class MyDbContext : DbContext
{
    static MyDbContext()
    {
        Database.SetInitializer<MyDbContext>(null);
    }

    public MyDbContext()
        : base(DbConstants.ConnectionStringName)
    {
        Configuration.LazyLoadingEnabled = false;
    }

    public DbSet<User> Users { get; set; }
    public DbSet<Aspiration> Aspirations { get; set; }
    public DbSet<Plan> Plans { get; set; }
    public DbSet<Sprint> Sprints { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        modelBuilder
            .Map(new UserMap())
            .Map(new AspirationMap())
            .Map(new PlanMap())
            .Map(new SprintMap())
            ;
    }
}

Answer 1

Well, I see some errors in your mapping.

The FK of Plan in Sprint must have the same order of the PK of Plan. So replace this:

HasRequired(s => s.Plan)
    .WithMany(d => d.Sprints)
    .HasForeignKey(s => new { s.AspirationId,s.UserId });

for this:

HasRequired(s => s.Plan)
    .WithMany(d => d.Sprints)
    .HasForeignKey(s => new { s.UserId, s.AspirationId });

After making those changes, I tried to run your code and everything worked fine.