C# Entity Framework - Multiple Foreign Keys

Question

I have a problem with Entity Framework 6 code first creating multiple foreign keys from my classes when it should only create one. Consider I have 2 classes -

public class Work
{
    public Work()
    {
        Document1 = new Collection<Document>();
        Document2 = new Collection<Document>();
        Document3 = new Collection<Document>();
    }
    [Key]
    public int WorkId { get; set; }
    public virtual ICollection<Document> Document1 { get; set; }
    public virtual ICollection<Document> Document2 { get; set; }
    public virtual ICollection<Document> Document3 { get; set; }
}

public class Document
{
    public int? WorkId { get; set; }
    [ForeignKey("WorkId")]
    public Work Work { get; set; }
}

Now when I run the code first update-database command, in my documents table it creates 3 different foreign keys for the Work table WorkId, Work_Id1, Work_Id2, this should just be one foreign key instead of 3. I know there should be a simple annotation to fix this or a fluent mapping I need to add?

Work is connected to Document by three properties (collection) Document1, Document2 and Document3. Thats why it is creating three foreign key. If you wanted to specify only 1 foreign key how other two will be connected to Document entity. — Jenish Rabadiya
– Jenish Rabadiya, Commented Jun 7, 2017 at 9:52

anaximander · Accepted Answer · 2017-06-07 09:55:52Z

1

Your Work class has three collections of Document, so the Document class requires three keys to denote where it attaches. If I have an instance of Document whose WorkId is 4, where does that exist on the instance of Work with that ID? Does it belong in Document1, Document2, or Document3? With only one key, there is no way to determine this. Entity Framework knows this, and has created three keys to solve the problem.

answered Jun 7, 2017 at 9:55

anaximander

7,1613 gold badges48 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harald Coppoolse · Accepted Answer · 2017-06-07 10:25:24Z

Apparently a Work may have zero or more Document1, zero or more Document2 and zero or more Document3. All these documents are of type Document.

Each Document belongs to exactly one Work, in which it is either a Document1, a Document2 or a Document3. The problem is when using your classes it does not know which Document type it is.

The most database friendly and flexible solution is to give each document a property, telling which type of Document it is, and put all Documents into one table:

public enum DocumentType
{
    Document1,
    Document2,
    Document3,
};

public class Work
{
    public int WorkId { get; set; }
    public virtual ICollection<Document> Document { get; set; }
}

public class Document
{
    public int DocumentId {get; set;} // primary key
    public DocumentType DocumentType {get; set;}

    public int WorkId { get; set; }
    public Work Work { get; set; }
}
public MyDbContext : DbContext
{
    public DbSet<Work> Works {get; set;}
    public DbSet<Document> Documents {get; set;}
}

The result is one Table with Documents. column WorkId will tell you to which Work the document belongs. Column DocumentType will tell you whether it is a Document1, a Document2 or a Document3.

This way it will be easy to add new DocumentTypes without having to change the database. If you want fast searches for all documents of a certain type consider creating an extra index with keys (Id, DocumentType)

If you really want three different tables, for instance because it could be that in future a Document1 is not similar to a Document2 anymore, you'll have to create three classes derived from Document, and three DbSet for these classes

public class Document1 : Document
{
   public int WorkId {get; set;}
   public virtual Work Work {get; set;}
}
public MyDbContext : DbContext
{
    public DbSet<Work> Works {get; set;}
    public DbSet<Document1> Document1s {get; set;}
    public DbSet<Document2> Document2s {get; set;}
    public DbSet<Document3> Document3s {get; set;}
}

Consider using Table-per-concrete-class for this. This way you'll get a table with Document1, a table with Document2 and one for Document3

Collectives™ on Stack Overflow

C# Entity Framework - Multiple Foreign Keys

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related