EF core 2.0 one to many share the same table

Question

EF core 2.0 one to many share the same table

Question

How to resolve "Navigation properties can only participate in a single relationship." error on below case?

1 company has many Milestone and MissionValueStory, where Milestone and MissionValueStory share same table with different typeId, and each of those has many translation where link up with companyInfoId only

Or BETTER break the relationship between companyInfo and company, and just another query to fetch companyInfo is much easy?

    public class Company
    {
        [key]
        public long Id { get; set; }

        public string Name { get; set; }

        public virtual ICollection<CompanyInfo> Milestone { get; set; } //multi

        public virtual ICollection<CompanyInfo> MissionValueStory { get; set; } //multi
    }

    public class CompanyInfo
    {
        [key]
        public long Id { get; set; }

        public long typeId { get; set; }
        
        [Required]
        public long CompanyId { get; set; }
            
        public string Title { get; set; }
        
        public string Text { get; set; }
      
        [ForeignKey("CompanyId")]
        public virtual Company Company { get; set; } 
        
        public ICollection<Translation> Translation { get; set; }
    }

    public class Translation
    {
        [key]
        public long Id { get; set; }
        
        public string Title { get; set; }
        
        [Required]
        public long CompanyInfoId { get; set; }
        
        public string Language { get; set; }
      
        [ForeignKey("CompanyInfoId")]
        public virtual CompanyInfo CompanyInfo { get; set; } 
    }

           modelBuilder.Entity<Company>()
              .HasMany(e => e.Milestone)
              .WithOne(t => t.Company)
              .HasForeignKey(m => m.CompanyId).IsRequired()
                .OnDelete(DeleteBehavior.Cascade);


            modelBuilder.Entity<Company>()
              .HasMany(e => e.MissionValueStory)
              .WithOne(t => t.Company)
              .HasForeignKey(m => m.CompanyId).IsRequired()
                .OnDelete(DeleteBehavior.Cascade);

            
            modelBuilder.Entity<CompanyInfo>()
              .HasMany(e => e.Translation)
              .WithOne(t => t.CompanyInfo).IsRequired();

user192344

View more on Stack Overflow

Charles · Accepted Answer · 11/22/2017 2:04:04 PM

What you're trying to do is legitimately not supported. At least in the way you're going about this. Fortunately there's a fairly painless solution for you. Use Table Per Hierarchy.

Change the class CompanyInfo to be an abstract class called CompanyInfoBase, and let it be an abstract type. Make typeId abstract on CompanyInfoBase.

Create two new classes that implement CompanyInfoBase:

public class MilestoneCompanyInfo : CompanyInfoBase
{
    public override long typeId { get; set; } = MILESTONE_TYPE_ID;
}

public class MissionValueStoryCompanyInfo : CompanyInfoBase
{
    public override long typeId { get; set; } = MISSION_VALUE_STORY_TYPE_ID;
}

where MILESTONE_TYPE_ID and MISSION_VALUE_STORY_TYPE_ID are some sort of predefined constants.

Then, in your DbContext's OnModelCreating, use typeId as your discriminator.

It'll look something like this:

modelBuilder.Entity<CompanyInfoBase>()
    .HasDiscriminator<long>(nameof(CompanyInfoBase.typeId))
    .HasValue<MilestoneCompanyInfo>(MILESTONE_TYPE_ID)
    .HasValue<MissionValueStoryCompanyInfo>(MISSION_VALUE_STORY_TYPE_ID);

Since you're changing the name of the entity, it's worth setting the table name to accommodate your existing db. Something like:

modelBuilder.Entity<CompanyInfoBase>().ToTable("CompanyInfos");

Note to other readers: It's only required to define the discriminator like this due to his decision to use a long. If he had just left it undefined then EF Core automagically handles this (by creating a column named discriminator that contains the concrete class names).

Here's a link to the inheritance reference page: https://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/inheritance

EF core 2.0 one to many share the same table

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related