Entity Framework multiple foreign keys to the same table

Question

I have two entities DataTag and CalcDataTag:

public class CalcDataTag : BaseModel
{
    [Column("CalcDataTagId")]
    public override Guid ID { get; set; }

    public Guid DataTagId { get; set; }
    public DataTag DataTag { get; set; }

    public Guid ChildDataTagId { get; set; }
    public DataTag ChildDataTag { get; set; }
}

public class DataTag : BaseModel
{
    [Column("DataTagId")]
    public override Guid ID { get; set; }

    public ICollection<CalcDataTag> CalcDataTags { get; set; }
}

I set the context up like this:

modelBuilder.Entity<DataTag>()
            .HasMany<CalcDataTag>(x => x.CalcDataTags)
            .WithRequired(x => x.ChildDataTag)
            .HasForeignKey(x => x.ChildDataTagId);

        modelBuilder.Entity<DataTag>()
            .HasMany<CalcDataTag>(x => x.CalcDataTags)
            .WithRequired(x => x.DataTag)
            .HasForeignKey(x => x.DataTagId);

The CalcDataTags list should be a list of CalcDataTags where either the DataTagId or the ChildDataTagId is equal to the DataTag's ID but the way the context is set up, the bottom setting overrides the top setting and I only get a list of CalcDataTags where the DataTagId is equal to the DataTag's ID. If I switch the settings then I only get a list of CalcDataTags where the ChildCalcDataTagId is equal to the DataTag's ID. Basically what I am trying to get is a union of the two lists. Each entity only has one PrimaryKey, no composite keys.

Do you mean it is a composite foreign key? `.HasForeignKey(x => new { x.DataTagId, x.ChildDataTagId });`. — CodeCaster, Jan 19 '15 at 20:30
`In CalcDataTag I have two foreign keys to CalcDataTag (DataTag and ChildDataTag) and in DataTag I have a list of CalcDataTags.` It is kind of confusing. What the final result tables should look like? Or could you upload ER diagram? — Win, Jan 19 '15 at 20:44
Just clarified, does it make more sense with the entities code added? — GBreen12, Jan 19 '15 at 20:46

score 2 · Accepted Answer · answered Jan 19 '15 at 20:52

You need to have a second collection on your DataTag table. There's no other way around it. Entity Framework is getting confused since it can only map one one-to-many relationship to a specific property, not two. You need to do the following:

In DataTag:

public ICollection<CalcDataTag> CalcDataTags { get; set; }
public ICollection<CalcDataTag> ChildCalcDataTags { get; set; }

In your modelBuilder:

modelBuilder.Entity<DataTag>()
            .HasMany<CalcDataTag>(x => x.ChildCalcDataTags)
            .WithRequired(x => x.ChildDataTag)
            .HasForeignKey(x => x.ChildDataTagId);

modelBuilder.Entity<DataTag>()
            .HasMany<CalcDataTag>(x => x.CalcDataTags)
            .WithRequired(x => x.DataTag)
            .HasForeignKey(x => x.DataTagId);

If you want to be able to grab all of them at the same time (as it appears that you do), you might want to consider adding in a [NotMapped] property that will grab the union of the two collections, as so:

[NotMapped]
public ICollection<CalcDataTag> AllCalcDataTags
{
  return CalcDataTags.Union(ChildCalcDataTags);
}

Entity Framework multiple foreign keys to the same table

1 Answers1