Inheritance and navigation properties to child entities

Question

I have a problem with Navigation Properties while I'm using Inheritance (TPH - the only available at the moment in EF Core).

My hierarchy models:

public class Proposal
{
    [Key]
    public int ProposalId { get; set; }

    [Required, Column(TypeName = "text")]
    public string Substantiation { get; set; }

    [Required]
    public int CreatorId { get; set; }

    [ForeignKey("CreatorId")]
    public Employee Creator { get; set; }

}

public class ProposalLeave : Proposal
{
    [Required]
    public DateTime LeaveStart { get; set; }

    [Required]
    public DateTime LeaveEnd { get; set; }
}

public class ProposalCustom : Proposal
{
    [Required, StringLength(255)]
    public string Name { get; set; }

}

And a part of DbContext:

public class AppDbContext : IdentityDbContext<User, Role, int>
{

    public DbSet<Employee> Employee { get; set; }
    public DbSet<Proposal> Proposal { get; set; }

    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Proposal>()
            .HasDiscriminator<string>("proposal_type")
            .HasValue<Proposal>("proposal_base")
            .HasValue<ProposalCustom>("proposal_custom")
            .HasValue<ProposalLeave>("proposal_leave");

    }
}

Ok, let's get to the point. As you can see inside parent Proposal model I have property CreatorId - reference to Employee entity. Inside Employee model I want to have two navigation properties to load created Proposals of child types as below:

public class Employee
{
    public ICollection<ProposalCustom> CreatedProposalCustoms { get; set; } 
    public ICollection<ProposalLeave> CreatedProposalLeaves { get; set; } 

}

But it causes migration errors. After I applied migration I have two references to Employee entity (CreatorId, EmployeeUserId) inside Proposal table instead of one (CreatorId). When I changed navigation properties to:

public class Employee
{      
    public ICollection<Proposal> CreatedProposals { get; set; } 
}

model was correct (there was only one reference to Employee inside Proposal table), but I still can't Include() to Employee model CreatedProposalCustoms and CreatedProposalLeaves separately.

The problem is probably inside my DbContext configuration, but I have no idea how to setup it correctly :/

Add `public DbSet ProposalLeaves { get; set; }` and `public DbSet ProposalCustoms { get; set; }` to your `AppDbContext` class. Then you can create your navigation properties in `Employee`. And make them `virtual`. — Kos, Dec 23 '16 at 10:03
Agree on the adding, but I wouldn't make them virtual unless you're actually aiming for lazy loading. — Zephire, Dec 23 '16 at 10:43
@Kos I applied your changes and it still don't work. `.Include(e=>e.CreatedLeaves)` gives always empty list, while `_context.ProposalLeave.Where(pl => pl.CreatorId == emp.UserId).ToList();` and `_context.Proposal.OfType().Where(pl => pl.CreatorId == emp.UserId).ToList()` works fine :/ Oh, and I still have unnecessary EmployeeUserId inside Proposal table. — Kuba, Dec 23 '16 at 10:45

Tseng · Accepted Answer · 2016-12-23T11:06:22.497

The problem is that when you have to navigation properties, EF Core will also create two foreign keys as you already found out.

One workaround could be to have non-mapped navigation properties, which just wrap the casting of your collection with the base class.

public class Employee
{
    public IDbSet<Proposal> Proposals { get; set; } 
    [NotMapped]
    public IQueryable<ProposalCustom> CreatedProposalCustoms { get; } => Proposals.OfType<ProposalCustom>();
    [NotMapped]
    public IQueryable<ProposalLeave> CreatedProposalLeaves { get; } => Proposals.OfType<ProposalLeave>();
}

Where the two non-mapped properties would just act as shorthand for Proposals.OfType<T>()

Or if you want it more generic:

public class Employee
{
    public IDbSet<Proposal> Proposals { get; set; } 
    public IQueryable<T> AllProposals<T>() where T :Proposal => Proposals.OfType<T>();
}

then use it as employee.AllProposals<ProposalLeave>().Where(p => p.LeaveStart >= DateTime.Now).ToListAsync().

Thanks. It didn't solve my problem at all, but it was really helpfull hint ;) — Kuba, Dec 23 '16 at 15:55

Inheritance and navigation properties to child entities

1 Answers1