Two foreign keys to one Entity Framework Core table

Question

I am developing language dictionary with asp core 2.0. I wonder how to properly design my database. I came across similar question: How to design a database for translation dictionary?.

I decided to create a database like in the following picture:

database structure that i wanna realize in ef core

But i do not know how to realize this structure with entity framework core 2.0.

Word entity

    public class Word
    {
      public Word()
        {
          Translations = new HashSet<Translation>();
        }
      [Key]
      public Guid WordId { get; set; }
      [Required]
      public Guid LangCodeId { get; set; }

      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", 
        "CA2227:CollectionPropertiesShouldBeReadOnly")]
       public virtual ICollection<Translation> Translations { get; set; }


}

Translation entity

   public class Translation
{
    [Key]
    public Guid TranslationId { get; set; }
    public Guid WordId1 { get; set; }
    public Guid WordId2 { get; set; }

    //[ForeignKey("WordId1,WordId2")]
    public Word Words { get; set; }

}

fluent API

  modelBuilder.Entity<Translation>()
            .HasOne(w => w.Words)
            .WithMany(m => m.Translations)
            .HasForeignKey(fk => new { fk.WordId1, fk.WordId2 });

when i try to add a migration, i get the error:

   The relationship from 'Translation.Words' to 'Word.Translations' with 
   foreign key properties {'WordId1' : Guid, 'WordId2' : Guid} cannot target 
   the primary key {'WordId' : Guid} because it is not compatible. Configure 
   a principal key or a set of compatible foreign key properties for this 
   relationship.

score 0 · Answer 1 · answered Nov 25 '17 at 17:40

I found the solution

public partial class Translation
{
    public Guid DerivedId { get; set; }
    public Guid? Word1 { get; set; }
    public Guid? Word2 { get; set; }

    public Word Word1Navigation { get; set; }
    public Word Word2Navigation { get; set; }
}



public partial class Word
{
    public Word()
    {
        TranslationWord1Navigation = new HashSet<Translation>();
        TranslationWord2Navigation = new HashSet<Translation>();
    }

    public Guid Id { get; set; }

    public ICollection<Translation> TranslationWord1Navigation { get; set; }
    public ICollection<Translation> TranslationWord2Navigation { get; set; }
}

Fluent API

        modelBuilder.Entity<Translation>(entity =>
        {
            entity.HasKey(e => e.DerivedId);

            entity.Property(e => e.DerivedId).ValueGeneratedNever();

            entity.HasOne(d => d.Word1Navigation)
                .WithMany(p => p.TranslationWord1Navigation)
                .HasForeignKey(d => d.Word1)
             .HasConstraintName("FK__DerivedTa__Word1__5EBF139D");

            entity.HasOne(d => d.Word2Navigation)
                .WithMany(p => p.TranslationWord2Navigation)
                .HasForeignKey(d => d.Word2)
                .HasConstraintName("FK__DerivedTa__Word2__5FB337D6");
        });

        modelBuilder.Entity<Word>(entity =>
        {
            entity.Property(e => e.Id)
                .HasColumnName("id")
                .ValueGeneratedNever();
        });

Two foreign keys to one Entity Framework Core table

1 Answers1