Two primarykeys on same table and foreignkey

Question

I have two tables that one of them relate to other. The first table

 public class Text
  {
    [Key]
    [Column(Order = 1)]
    [Required]
    [MinLength(7)]
    [MaxLength(7)]
    public string Fieldname { get; set; }

    [Key]
    [Column(Order = 2)]
    [Required]
    public virtual Language Language { get; set; }

    [MaxLength(50)]
    [MinLength(1)]
    [Required]
    public string Description { get; set; }
  }

and the second table:

public class Language
  {
    [Key]
    [Required]
    [MaxLength(2), MinLength(2)]
    public string Code { get; set; }
    [Required]
    public string Country { get; set; }
  }

The seed data looks as follow:

context.Language.AddOrUpdate(
        new Language() {Code = "DE", Country = "German"},
        new Language() {Code = "EN", Country = "English"});

context.Text.AddOrUpdate(
        new Text { Fieldname = "TEXT001", Description = "Server", Language = context.Language.First(e => e.Code == "EN") },
        new Text { Fieldname = "TEXT001", Description = "Server", Language = context.Language.First(e => e.Code == "DE") }
      );

I update the db and got following error message :

System.Data.Entity.Core.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_dbo.Texts'. Cannot insert duplicate key in object 'dbo.Texts'. The duplicate key value is (TEXT001).

What is wrong?

Yes but in combination with fields `Fieldname` and `Language`. Please look at the seed data, then you should what I mean. — softshipper, Aug 02 '16 at 10:02
@zero_coding - Not sure but I think you should specify that the `Language` `key` is a foreign key - `[Key, ForeignKey("Language"), Column(Order=2)]` — Gilad Green, Aug 02 '16 at 10:04

score 1 · Answer 1 · answered Aug 02 '16 at 10:05

I don't think you can define part of the PK with navigation property, so EF simply ignores your Column and Key attributes on Language property, ending up with PK being just Fieldname.

You need to include the FK field explicitly like this:

public class Text
{
    [Key]
    [Column(Order = 1)]
    [Required]
    [MinLength(7)]
    [MaxLength(7)]
    public string Fieldname { get; set; }

    [Key]
    [Column(Order = 2)]
    [Required]
    [MaxLength(2), MinLength(2)]
    public string LanguageCode { get; set; }

    [ForeignKey("LanguageCode")]
    public virtual Language Language { get; set; }

    [MaxLength(50)]
    [MinLength(1)]
    [Required]
    public string Description { get; set; }
}

score 1 · Accepted Answer · edited Sep 28 '17 at 05:04

@IvanStoev answer is correct. But I want to show another option. I suggest mapping your classes using Fluent API. Beleive me, this way, your models will remain much more cleaner and mappings are easy to understand.

Text model and mapping for it:

public class Text
{
    public string FieldName { get; set; }  
    public string LanguageCode { getl set; } // Add this foriegn key property
    public string Description { get; set; }

    // Navigation properties
    public virtual Language Language { get; set; }
}

internal class TextMap
    : EntityTypeConfiguration<Text>
{
    public TextMap()
    {
        // Primary key
        this.HasKey(m => new { m.FieldName, m.LanguageCode });

        this.Property(m => m.FieldName)
                .HasMaxLength(7)
                .IsFixedLength();

        this.Property(m => m.LanguageCode)
                .HasMaxLength(2)
                .IsFixedLength();

        // Properties
        this.Property(m => m.Description)
            .IsRequired()
            .HasMaxLength(50);                      

        // Relationship mappings
        this.HasRequired(m => m.Language)
            .WithMany()
            .HasForeignKey(m => m.LanguageCode)
            .WillCascadeOnDelete(false);
    }
}

Language model and mapping for it:

public class Language
{
    public string Code { get; set; }
    public string Country { get; set; }
}

internal class LanguageMap
    : EntityTypeConfiguration<Language>
{
    public LanguageMap()
    {
        // Primary key
        this.HasKey(m => m.Code);

        this.Property(m => m.Code)
                .HasMaxLength(2)
                .IsFixedLength();

        // Proeprties
        this.Property(m => m.Country)
            .IsRequired();
    }
}

Then you should override OnModelCreating method of DbContext and add your mappings as:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Configurations.Add(new TextMap());
    modelBuilder.Configurations.Add(new LanguageMap());
}

So at the navigation property, I do not have to set `[Key]` anymore? — softshipper, Aug 02 '16 at 11:01
You have primary key at Navigation proeprty, but you do not use any of attributes if you use Fluent API @zero_coding — Adil Mammadov, Aug 02 '16 at 11:04
I did as you describe above, for example delete all attributes etc. When I do the `Update-Database` I've got following error `The object 'DF__Texts__Fieldname__21B6055D' is dependent on column 'Fieldname'`. — softshipper, Aug 02 '16 at 11:52
@zero_coding I renamed `Fieldname` to `FieldName`. May it be the reason of error? — Adil Mammadov, Aug 02 '16 at 11:59
@zero_coding You do not have to change it, I just renamed it for myself :). I am sorry but I haven't used UpdateDatabase too much. `DF__Texts__Fieldname__21B6055D` looks like auto-generated name of PK. But I do nor know exact reason of the error. — Adil Mammadov, Aug 02 '16 at 12:06
This help me out http://stackoverflow.com/questions/19460912/the-object-df-is-dependent-on-column-changing-int-to-double. Thanks — softshipper, Aug 02 '16 at 12:49

Two primarykeys on same table and foreignkey

2 Answers2