How create composite foreign key to table with composite primary key

code-first entity-framework-6 foreign-keys

Question

I have two tables with composite primary keys:

public class Event
{
    [Key]
    [Column(Order = 1)]
    public string ID1 { get; set; }

    [Key]
    [Column(Order = 2)]
    public int ID2 { get; set; }
    public DateTime EventDate { get; set; }
    public string DocID1 { get; set; }
    public int DocID2 { get; set; }

}

public class EventDocument
{
    [Key]
    [Column(Order = 1)]
    public string ID1 { get; set; }
    [Key]
    [Column(Order = 2)]
    public int ID2 { get; set; }

    public string Name { get; set; }
    public string SurName { get; set; }
    public string Number { get; set; }

    public virtual ICollection<Event> Events { get; set; }
}

I need to create a composite foreign key on Event table to EventDocument table

I've tried to create FK like this

class Event:

[ForeignKey("DocID1")]
[Column(Order = 0)]
public string DocID1 { get; set; }

[ForeignKey("DocID2")]
[Column(Order = 1)]
public string DocID2 { get; set; }

But I get an error:

The property 'DocID1' cannot be configured as a navigation property. The property must be a valid entity type and the property should have a non-abstract getter and setter. For collection properties the type must implement ICollection where T is a valid entity type."}

I don't understand anymore what I am doing wrong

1
3
3/2/2017 1:54:12 PM

Accepted Answer

Composite foreign key requires ForeignKey attribute to be applied on the navigation property specifying the comma separated list of the foreign key property names:

If you add the ForeignKey attribute to a foreign key property, you should specify the name of the associated navigation property. If you add the ForeignKey attribute to a navigation property, you should specify the name of the associated foreign key(s). If a navigation property has multiple foreign keys, use comma to separate the list of foreign key names.

Since you have no navigation property in the Event class, you should apply it on the corresponding navigation property in the EventDocument class:

[ForeignKey("DocID1, DocID2")]
public virtual ICollection<Event> Events { get; set; }

and the issue should be resolved.

But I personally find setting up relationships with Fluent API to be much easier to understand and less error prone. For instance, the same can be achieved by the following fluent configuration:

modelBuilder.Entity<EventDocument>()
    .HasMany(e => e.Events)
    .WithRequired() // or .WithOptional()
    .HasForeignKey(e => new { e.DocID1, e.DocID2 });

Same btw for the composite PKs (instead of all these Key / Column(Order = ...) attributes):

modelBuilder.Entity<Event>()
    .HasKey(e => new { e.ID1, e.ID2 });

modelBuilder.Entity<EventDocument>()
    .HasKey(e => new { e.ID1, e.ID2 });
5
3/3/2017 8:00:37 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow