Entity framework one foreign key toward two tables - code first

entity-framework entity-framework-6

Question

All, Is it possible to use the same FK for two tables. Probably it is not a good practice, but I have a two different classes that can be both booked:

public class Course {

    public Course() {
        BookingRefs = new HashSet<BookingRef>();
    }

    public long Id { get; set; }
    public string Title { get; set; }
    // other props ...

    [InverseProperty(nameof(BookingRef.Course))]
    public virtual ICollection<BookingRef> BookingRefs { get; set; }
}

public class GiftCard {

    public GiftCard() {
        BookingRefs = new HashSet<BookingRef>();
    }

    public long Id { get; set; }
    public string Prop1 { get; set; }
    public int Prop2 { get; set; }
    // other props ...

    [InverseProperty(nameof(BookingRef.Course))]
    public virtual ICollection<BookingRef> BookingRefs { get; set; }
}

// this is the bookin reference for a Course or an GiftCard
public class BookingRef {

    public BookingRef() {
    }

    public long Id { get; set; }
    // other props ...

    /// <summary>The item (usually the course but theoretically anything with a long id)</summary>
    public long? ItemId { get; set; }

    // maybe a generic Object?
    [ForeignKey(nameof(ItemId))]
    public Object GiftCard { get; set; }

    // maybe 2 items possibly null?
    [ForeignKey(nameof(ItemId))]
    public Course Course { get; set; }

    // maybe 2 items possibly null?
    [ForeignKey(nameof(ItemId))]
    public GiftCard GiftCard { get; set; }
}
1
0
1/30/2019 2:23:33 PM

Popular Answer

Think of it from the relational data perspective. How would the database know what table an "Item ID" pointed at? How would it index it?

This would be a case for using a null-able FK to each related table on the booking. These FKs do not need to reside in the entity, just the navigation properties. You can leverage .Map(x => x.MapKey) in EF6 or .HasForeignKey("") in EF Core to leverage a shadow property.

This does not enforce if you want a booking to only be associated to a course or a gift card but not both. That would need to be catered for at the application level, and I would recommend using a scheduled maintenance task to evaluate the data for violations to that rule. (Look for bookings holding both a course ID and a gift card ID for example)

You can alternatively keep the joins "loose" and evaluated by the application based on a discriminator similar to an inheritance model. (ItemId + ItemType) However you have to resolve the relationship load separately in your application based on the ItemType and lose out on any FK, indexing, and data integrity checks in the database. This could be a significant performance & maintenance cost to save adding a couple FKs.

0
1/30/2019 11:19:37 PM


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