Mapping composite foreign key to composite primary key where the foreign key is also a primary key

12,558

The main difference between your question and the one I suggested as duplicate is that your ForeignKey attributes don't refer -

  • from a primitive property to a navigation property
  • from a navigation property to a primitive property

In your case, the reference is from a primitive property to another primitive property, in another type. Also, little detail, VirtualMachine.Datetime should be a property, not a member. But I have to admit that the "duplicate" didn't cover your case.

So let's try to make this into a comprehensive answer how to handle this situation in Entity Framework 6. I'll use an abstracted model to explain the various options:

public class Parent
{
    public int Id1 { get; set; } // Key
    public int Id2 { get; set; } // Key   
    public string Name { get; set; }   
    public virtual List<Child> Children { get; set; }
}

public class Child
{
    public int Id1 { get; set; } // Key
    public int Id2 { get; set; } // Key
    public int Id3 { get; set; } // Key
    public string Name { get; set; }
    public virtual Parent Parent { get; set; }
} 

There are three options to setup the mappings.

Option 1

Data annotations, ForeignKey attribute:

public class Parent
{
    [Key]
    [Column(Order = 1)]
    public int Id1 { get; set; }
    [Key]
    [Column(Order = 2)]
    public int Id2 { get; set; }
    
    public string Name { get; set; }

    public virtual List<Child> Children { get; set; }
}

public class Child
{
    [Key]
    [Column(Order = 0)]
    public int Id1 { get; set; }
    [Key]
    [Column(Order = 1)]
    public int Id2 { get; set; }
    [Key]
    [Column(Order = 2)]
    public int Id3 { get; set; }

    public string Name { get; set; }

    [ForeignKey("Id1,Id2")]
    public virtual Parent Parent { get; set; }
}

As you see, here the ForeignKey attribute refers from a navigation property to primitive properties. Also, the absolute numbers in the column order don't matter, only their sequence.

Option 2

Data annotations, InverseProperty attribute:

public class Parent
{
    [Key]
    [Column(Order = 1)]
    public int Id1 { get; set; }
    [Key]
    [Column(Order = 2)]
    public int Id2 { get; set; }
    
    public string Name { get; set; }

    public virtual List<Child> Children { get; set; }
}

public class Child
{
    [Key]
    [Column(Order = 0)]
    [InverseProperty("Children")]
    public int Id1 { get; set; }
    [Key]
    [Column(Order = 1)]
    [InverseProperty("Children")]
    public int Id2 { get; set; }
    [Key]
    [Column(Order = 2)]
    public int Id3 { get; set; }

    public string Name { get; set; }

    public virtual Parent Parent { get; set; }
}

InverseProperty points from one or more properties in a type at one end of a relationship to a navigation property in the type on the other end of the relationship. Another way to achieve the same mapping is to apply [InverseProperty("Parent")] on both key properties of Parent.

Option 3

Fluent mapping:

modelBuilder.Entity<Parent>().HasKey(p => new { p.Id1, p.Id2 });
modelBuilder.Entity<Child>().HasKey(p => new { p.Id1, p.Id2, p.Id3 });
modelBuilder.Entity<Parent>()
    .HasMany(p => p.Children)
    .WithRequired(c => c.Parent)
    .HasForeignKey(c => new { c.Id1, c.Id2 });

As said in the comments, fluent mapping is less error-prone than data annotations. Data annotations offer too many options to configure mappings and it's not always easy to see which parts are connected. That's why fluent mapping is my favorite.

Entity Framework Core

In EF-core (current version 3.1.6) composite primary keys can't be modeled by data annotations. It throws a run-time exception:

Entity type 'Parent' has composite primary key defined with data annotations. To set composite primary key, use fluent API.

So for EF-core only option 3 is feasible. The mapping is almost identical:

modelBuilder.Entity<Parent>().HasKey(p => new { p.Id1, p.Id2 });
modelBuilder.Entity<Child>().HasKey(p => new { p.Id1, p.Id2, p.Id3 });
modelBuilder.Entity<Parent>()
    .HasMany(p => p.Children)
    .WithOne(c => c.Parent) // Different here
    .HasForeignKey(c => new { c.Id1, c.Id2 });
Share:
12,558
NeuralLynx
Author by

NeuralLynx

Updated on July 06, 2022

Comments

  • NeuralLynx
    NeuralLynx over 1 year

    I want to make VM_hostname,datetime and name properties as a composite Key for Disk class . At the same time VM_hostname and datetime of Disk class should refer to VM_hostname and datetime of VirtualMachine class (ie Foreign keys) .

    I did this but it gives me this exception : The ForeignKeyAttribute on property 'datetime' on type 'WebJob1.Historical.Disk' is not valid. The navigation property 'Datetime' was not found on the dependent type 'WebJob1.Historical.Disk'. The Name value should be a valid navigation property name

    Anyone have a clue ? Also, please note that im using Data Annotation.

    public class VirtualMachine
    {
    
        [Key]
        [Column(Order = 0)]
        public string VM_Hostname { get; set; }
        [Key]
        [Column(Order = 1)]
        public DateTime Datetime;
        public virtual List<Disk> disks { get; set; }
    }
    
     public class Disk
    {
        [Key,ForeignKey("VirtualMachine"),Column(Order = 0)]
        public string VM_hostname { get; set; }
        [Key,ForeignKey("Datetime"), Column(Order = 1)]
        public DateTime datetime { get; set; }
        [Key, Column(Order = 2)]
        public string name { get; set; }
    
        public virtual VirtualMachine VirtualMachine{ get; set; }
    
    
    }
    
  • NeuralLynx
    NeuralLynx over 6 years
    Thanks, I used the 2nd method and it works perfectly. Also, I could use this too right ? [Key,ForeignKey("Parent"),Column(Order = 1) to define the relationship
  • Gert Arnold
    Gert Arnold over 6 years
    I tried (i.e. these ForeignKey annotations on Child's first two key properties), but it fails with "Multiplicity is not valid in Role 'Child_Parent_Source' ...". It looks like the ForeignKey attribute can't be split like the InverseProperty attribute.
  • zolty13
    zolty13 over 3 years
    Is it possible to convert non-primitive value object containing two fields? : public class MyId { public int CompanyId; public int UserId }
  • Gert Arnold
    Gert Arnold over 3 years
    @zolty13 I have no idea what you mean. You better post a new question if you have something to ask. This doesn't work in comments.
  • zolty13
    zolty13 over 3 years
    I mean if it is possible to use object of MyId as primary Key with EF core?