Entity Framework Model builder with conditioanl foreign key

c# entity-framework entity-framework-6


Currently I am working on legacy sytem db which is quite messy In hotel booking system table we have Reservation table which has one to many relation with different reservation system(e.g hotel reservation, train reservation) based on systemid
Table - Reservation
Column Reservation_id (int) | System_id(int) | Customer_id|
45432 | can be 1,2,3 etc | 343 |

e.g. system id 1 can be hotel, 2 is train etc.

Table - [System]_Reservation system can be hotel or train or flight
Column Reservation_id (int) | create_date(datetime) |other columns 45432 |

Currently to load reservation details, we query the reservation table than based on system id we query respective reservation system table. each reservation can have 0..1* to many relationship with reservation system. How can i create EF mdel builder around this. I tried the following

    public class HotelReservation
    public int Reservation_id { get; set; }
    public Virtual Reservation ReservationInfo { get; set; }
    //other properties etc
    public class Reservation
    public Reservation()
        HotelReservations  = new HashSet<HotelReservation>();
        TrainReservations  = new HashSet<TrainReservation>();
    public int Reservation_id { get; set; }
    public int SystemID { get; set; }
    public virtual ICollection<HotelReservation> HotelReservations { get; set; }
    public virtual ICollection<TrainReservation> TrainReservations { get; set; }
    //other properties etc
//On model builder class contains
            .HasOptional(x => x.ReservationInfo)
            .WithMany(x => x.TrainReservations )
            .HasForeignKey(x => x.Reservation_id);

but on initialization thros following error Multiplicity conflicts with the referential constraint in Role .....Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principal Role must be '1'.

8/29/2018 1:09:04 AM

Popular Answer

This looks roughly like an inheritance model using Table-per-Type, however that System_Id looks more like an attempt at Table-per-Hierarchy. If the sub-class tables(Hotel_Reservation etc) hold a Reservation ID then you should be able to map it Table-per-Type and ignore the SystemId all-together in the relationship mapping. Essentially you want to treat Reservation as a base class with the specific reservation types extending it. From there you should be able to query specific reservation types, or the base reservation.

Have a read through: https://weblogs.asp.net/manavi/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-2-table-per-type-tpt

This outlines how the relationships are set up in EF, along with other inheritance data structure mappings including Table-per-Hierarchy.

8/29/2018 2:44:40 AM

Related Questions


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