Reuse Entity in Entity Framework Core

Question

I wish to create the following database using Entity Framework Core. I am using Entity Framework Core with SQLite and WinUi 3 with the MVVM toolkit.

I am having trouble with getting the Address part of my database to work. I wish to have it so that any Addresses created can be shared/reused. I am not sure how to go about this though as I am getting various errors about foreign keys and the like.

For example a Person's address could also be used as part of a Start/End Location in a Worker Shift table and as part of a Route.

I am using the Table Per Type setup for my database currently.

This is what my database currently looks like:

public partial class Address: BaseModel
{

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int AddressId { get; protected set; }

    [ObservableProperty]
    [NotifyDataErrorInfo]
    [Required(ErrorMessage = "Name is Required")]
    private string _name;

    [ObservableProperty]
    private string? _unitNum;

    [ObservableProperty]
    [NotifyDataErrorInfo]
    [Required(ErrorMessage = "Street Number is Required")]
    private string _streetNum;

    [ObservableProperty]
    [NotifyDataErrorInfo]
    [Required(ErrorMessage = "Street Name is Required")] 
    private string _streetName;

    [ObservableProperty]
    [NotifyDataErrorInfo]
    [Required(ErrorMessage = "Street Type is Required")]
    private string _streetType;

    public int SuburbId {  get; set; }

    [ObservableProperty]
    [NotifyDataErrorInfo]
    [Required(ErrorMessage = "Suburb is Required")]
    public Suburb _suburb;

    [ObservableProperty]
    [NotifyDataErrorInfo]
    [Required(ErrorMessage = "City is Required")]
    private string _city;

    [ObservableProperty]
    [NotifyDataErrorInfo]
    [Required(ErrorMessage = "State is Required")]
    private string _state;

    [ObservableProperty]
    [NotifyDataErrorInfo]
    [Required(ErrorMessage = "Post Code is Required")]
    private string _postCode;

    public int? ClientId { get; set; }
    public Client? Client { get; set; }
    public Address() { }

    public Address(string name, string? unitNum, string streetNum, string streetName, string streetType, Suburb suburb, string city, string state, string postCode)
    {
        Name = name;
        UnitNum = unitNum;
        StreetNum = streetNum;
        StreetName = streetName;
        StreetType = streetType;
        Suburb = suburb;
        City = city;
        State = state;
        PostCode = postCode;
    }
}

public partial class Shift: BaseModel
{
    public int ShiftId { get; protected set; }                             

    [ObservableProperty]
    private string _day =string.Empty;

    [ObservableProperty]
    private string _startTime =string.Empty;

    [ObservableProperty]
    private string _endTime = string.Empty;               
    
    [ObservableProperty]
    private Address _startLocation;

    [ObservableProperty]        
    private Address _endLocation;

                  

    [ObservableProperty]
    private ObservableCollection<ShiftWorker> _shiftWorkers;

    [ObservableProperty]
    private ObservableCollection<Route> _routes;
    
    public int ClientId { get; set; } 
    public Client Client { get; set; } = null;  
    
    public Shift() { }

    
    public Shift(string day, string startTime, string endTime, ShiftAddress startLocation, ShiftAddress endLocation, Client client)
    {   
        Day = day;                     
        StartTime = startTime;
        EndTime = endTime;        
        StartLocation = startLocation;
        EndLocation = endLocation; 
        Client = client;                                  
    }
}

public partial class Route: BaseModel
{
    public int RouteId { get; protected set; }                             
    
    [ObservableProperty]
    private string _name =string.Empty;                  
    
    [ObservableProperty]
    private Address _startAddress;

    [ObservableProperty]        
    private Address _endAddress;

    [ObservableProperty]        
    private int _distance;                         
}

public partial class Person: BaseModel
{
    public int PersonId { get; protected set; }                             
    
    [ObservableProperty]
    private string _firstName =string.Empty;  

    [ObservableProperty]
    private string _lastName =string.Empty;                
    
    [ObservableProperty]
    private Address _address;                               
}

Answer 1

Based on your schema, that should be fine, but the Address is on the Person table, not the Client table, and the FK (AddressId) is on Person, there would be no ClientId on Address. Similarly, the Shifts and Routes can reference the same Addresses by having the AddressIds for start/end on those tables respectively

Everything you need to know about relationships between entities in a relational database is covered here: https://learn.microsoft.com/en-us/ef/core/modeling/relationships

There are many flavors to cover any scenario you might need. There may be times when you might think of something that doesn't really fit (like "sharing" a table between multiple "owners" with an OwnerId and OwnerType for instance to avoid creating multiple similar tables with the same columns) but this is strongly inadvisable as it breaks referential integrity rules aimed at having performant and reliable data schemas.

The address entity can have a reference back to the Person (Client or Worker) but would not contain a PersonId/ClientId.

So in your Person base entity you should have:

[ForeignKey("AddressId")] // for a shadow property to the Address ID FK
public virtual Address Address { get; set; }

or

public int AddressId { get; set; }
[ForeignKey(nameof(AddressId))]
public virtual Address Address { get; set; }

If the Address wants a reference back to the Client or Worker, add the following attribute to the Person.Address:

[InverseProperty("Person")]

... and add the Person property to Address:

public virtual Person Person { get; set; }

Alternatively if you are configuring this in OnModelCreating or using Entity Type Config classes with the fluent configuration:

modelBuilder.Entity<Person>()
    .HasOne(p => p.Address)
    .WithOne(a => a.Person)
    .HasForeignKey<Person>("AddressId"); // Person has the AddressID, Address does not have a PersonId

Similarly your classes that have start and end address references would be set up with FKs on those entities pointing to the Address record, but no inverse property. These are not .HasOne().WithOne(), but rather set up with .HasOne().WithMany() indicating that many Routes or Shifts etc. will reference the same Address row.

modelBuilder.Entity<Route>()
    .HasOne(r => r.StartAddress)
    .WithMany() // No inverse property on address
    .HasForeignKey("StartAddressId"); // or (r => r.StartAddressId) if you have a FK property on Route

By default if you set up a navigation property on an entity, EF will treat it as a reference type ("many" on the other side) So the alternative to the above with attributes in the entity would be in the Route entity:

 [ForeignKey("StartAddressId")] // Shadow FK
 public virtual Address StartAddress { get; set; }
 [ForeignKey("EndAddressId")] // Shadow FK
 public virtual Address EndAddress { get; set; }

The important detail when a class contains multiple references to the same entity type is to specify the FK explicitly. A common pitfall is a bad assumption about how EF resolves FKs. For example looking at the Person class if you have:

public int AddressId { get; set; }
public virtual Address Address { get; set; }

... without any attributes or fluent configuration, EF would work this out and wire it up. However, this similar code in Route would not work:

public int StartAddressId { get; set; }
public virtual Address StartAddress { get; set; }

EF would complain about a missing AddressId FK, or try and create one. The reason is that EF's convention for resolving a FK is based on the entity type, not the property name. So as a general rule for navigation properties, always explicitly nominate the FK, whether you have a FK property or use a shadow property for the FK. (Recommended)