Setting Table Relationships in Entity Framework 6

Question

I need a structure in EF6 where the Item Color can have multiple translations. Would this structure support that functionality and is it correct?

I am attempting to apply the following relationships:

Each PIMItem has 1 PIMColor
Each PIMColor can have multiple PIMColorTranslations
Only 1 PIMColorTranslation per Color per LanguageCode is allowed.

    [Table("PIMItem")]
    public class PIMItem  
    {
        [Key]
        public string Id {get;set;}//RowKey no.
        //there are additional columns in this item not related to color
        [ForeignKey("PIMColor")]
        [Column(Order=3)]
        public string ColorId {get;set;}
        public PIMColor PIMColor {get;set;}
    }

    [Table("PIMColor")]
    public class PIMColor
    {
        [Key]
        public string ColorId {get;set;}
        public ICollection<PIMItem> PIMItems {get;set;}
        public ICollection<PIMColorTranslation> PIMColorTranslation {get;set;}
    }

    [Table("PIMColorTranslation")]
    public class PIMColorTranslation{
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Column(Order=1)]
        public int Id {get;set;}
        [ForeignKey("PIMLanguage")]
        [Column(Order=2)]
        public string LanguageCode {get;set;}
        [ForeignKey("PIMColor")]
        [Column(Order=3)]
        public string ColorId {get;set;}
        public string Translation {get;set;}
    }

    [Table("PIMLanguage")]
    public class PIMLanguage{
        [Key] 
        public string LanguageCode {get;set;}
        public string Language {get;set;}
        public ICollection<PIMColorTranslation> PIMColorTranslation {get;set;}
    }

    public class SharedCtxt : DbContext 
    {
        public SharedCtxt() : base(sharedData.conn_string)
        {
        }
        public DbSet<PIMLanguage> PIMLanguages {get;set;}
        public DbSet<PIMColor> PIMColors {get;set;}
        public DbSet<PIMColorTranslation> PIMColorTranslations {get;set;}
        public DbSet<PIMItem> PIMItems {get;set;}

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        }

    }

score 1 · Accepted Answer · answered Dec 16 '21 at 16:07

You need a unique constraint in PIMColorTranslation for both LanguageCode and ColorId. I don't know if there's an attribute for that, but you can use the Fluent API:

protected override void OnModelCreating(ModelBuilder modelBuilder) {  
    ...
    modelBuilder.Entity<PIMColorTranslation>(entity =>
    {
        ...
        entity.HasIndex(e => new { e.LanguageCode, e.ColorId }).IsUnique();
        ...
    });  
    ...  
}

Another note:
Add virtual to the navigation properties. See why-use-virtual-for-class-properties-in-entity-framework-model-definitions

Just a suggestion:
Use Fluent API to configure the schema. You can't do much with annotations AND it literally bloats the code.

Thank you for the feedback! – WouldBeNerd Dec 16 '21 at 23:35 — WouldBeNerd, Dec 16 '21 at 23:35

Setting Table Relationships in Entity Framework 6

1 Answers1