How to add a second foreign key to an EF property which is part of a composite foreign key already

Question

I am trying to create an audit trail for a table by creating a duplicate of that table which includes an extra date as part of the primary key.

Here is a simplified example of what I want to do.

My code for the entities is currently as follows:

public class Person
{
    [Key]
    public int ID { get; set; }
    public string Name { get; set; }
    public DateTime DateOfBirth { get; set; }
}

public class GiftIdea
{
    [Key, Column(Order:0), ForeignKey("Person")]
    public int PersonID { get; set; }
    [Key, Column(Order:1)]
    public DateTime RecordDate { get; set; }
    public string Description { get; set; }
    public string Occasion { get; set; }

    //Linked Entities
    public virtual Person Person { get; set; }
}

public class AuditGiftIdea
{
    [Key, Column(Order:0), ForeignKey("GiftIdea")]
    public int PersonID { get; set; }
    [Key, Column(Order:1), ForeignKey("GiftIdea")]
    public DateTime RecordDate { get; set; }
    [Key, Column(Order:2)]
    public DateTime AuditRecordDate { get; set; }
    public string Description { get; set; }
    public string Occasion { get; set; }

    //Linked Entities
    public virtual GiftIdea GiftIdea { get; set; }
}

Ultimately I want to be able to reference a Person from an AuditGiftIdea object without having to go through the attached GiftIdea.

So I tried this:

public class AuditGiftIdeas
{
    [Key, Column(Order:0), ForeignKey("GiftIdea"), ForeignKey("Person")] //Extra Foreign Key
    public int PersonID { get; set; }
    [Key, Column(Order:1), ForeignKey("GiftIdea")]
    public DateTime RecordDate { get; set; }
    [Key, Column(Order:2)]
    public DateTime AuditRecordDate { get; set; }
    public string Description { get; set; }
    public string Occasion { get; set; }

    //Linked Entities
    public virtual GiftIdea GiftIdea { get; set; }
    public virtual Person Person { get; set; } //Access to desired object
}

But I get a compile time error of "Duplicate 'ForeignKey' attribute".

How can I accomplish this setup?

Why not just keep the FK("GiftIdea") and trust that the Gift Idea table will enforce the FK to Person? — Vlad274, Mar 30 '16 at 16:16
@Vlad274 I was primarily interested in being able to access the person directly via the AuditGiftIdea (e.g. AuditGiftIdea.Person rather than AuditGiftIdea.GiftIdea.Person) — hobwell, Mar 30 '16 at 16:45

score 1 · Accepted Answer · answered Mar 30 '16 at 16:23

You cannot do that with data annotations because ForeignKeyAttribute is marked with AllowMultiple = false.

But you can configure that with Fluent API:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<AuditGiftIdea>()
        .HasRequired(t => t.Person).WithMany()
        .HasForeignKey(t => t.PersonID)
        .WillCascadeOnDelete(false);

    base.OnModelCreating(modelBuilder);
}

Note that you need to turn cascade delete off, because the new FK is creating a circular cascade path and EF will throw an error if you don't do that. Which in turn means you may have a problems with deleting your Person records - you'll have to manually delete the related records from the AuditGiftIdea table. So think carefully before introducing that FK.

That worked perfectly. Your note about cascade delete raised a point I hadn't considered. It would probably be best for me to NOT tie the audit record to the records being audited since if the original records are deleted I still want to retain the audit trail. Thanks for the insight as I expect this to be a useful technique in other cases! — hobwell, Mar 30 '16 at 17:03

How to add a second foreign key to an EF property which is part of a composite foreign key already

1 Answers1