CODEWITHSUNDEEP
entity-frameworkdatabase-designentity-relationship
Phill
Phill

Reputation: 17

C# EF Core Models independent FK

I have a table for a spot. This spot can have different categories. For example: restaurant, hotel, park. Each individual category has additional and different details. My questions now:

Is it possible to create a column (FK) in the spot table that is independent of the category? I mean when I create a new spot with the category Resteraunt and the next one is with the category park. If I now select a spot that I get the correct data from the respective spot category

And if possible, how can I write that in C# EF Core Models?

Or is this not possible and i have to take another way?

Thanks in advance

Database Design for Example: enter image description here

Upvotes: 0

Views: 108

Answers (1)

Moho
Moho

Reputation: 16498

No, you can't have an FK column that is independent of the table it's referencing (the manifestations of your category). What you're looking for is inheritance. EF Core 5 currently supports two types of inheritance - Table Per Hierarcy (TPH) and Table Per Type. Either can work for your purposes.

First, you implement your entity Classe in an inheritance hierarchy to be mapped to the database with the derived classes representing the "category" of Spot:

public abstract class Spot
{
    public Guid Id { get; set; }
    // common spot properties here 
    // (i.e. props shared by different types of spots like address)
}

public class Restaurant : Spot
{
    // restaurant specific properties here
}

public class Park : Spot
{
    // park specific properties here
}

Then you map the entities in one of two ways - either to a single table (TPH) which will use a discriminator column to type each record in the DB (would be considered your category) and each property for the derived types would also be included but only populated for the specific type of record (i.e. Park properties will be null when the record in the DB represents a Restaurant and vice versa). Faster query performance with this method but all type-specific columns must be nullable and this implementation violates 3NF.

TPH is the default inheritance implementation but you can configure how it handles the discriminator like any other property (name, datatype) and specify the values to use for each derived type:

modelBuilder.Entity<Spot>()
    .HasDescriminator("Category") // name it what you want
    .HasValue<Restaurant>("R") // value for restaurants
    .HasValue<Park>("P") // value for parks
    ;

In TPT each type in the inheritance hierarchy is mapped to its own table which contains their specific properties. The tables for the derived types use shared primary keys to reference their corresponding Spot record. Query performance can be slower and while it doesn't violate 3NF, its possible for manual data manipulation operations to mess things up (e.g. a Park and a Restaurant can reference the same Spot record).

For this configuration, merely map each type in the entity hierarchy to its own table:

modelBulider.Entity<Restaurant>().ToTable("Restaurant");
modelBuilder.Entity<Park>().ToTable("Park");

For both implementations, you can implement the DbSet properties normally:

public DbSet<Spot> Spots { get; set; }
public DbSet<Restaurant> Restaurants { get; set; }
public DbSet<Park> Parks { get; set; }

You can get specific types of Spots from Spots by using .OfType<T>()

var parks = dbContext.Spots.OfType<Park>();

So you do not need the Restaurants or Parks DbSet<T>s if you include the Spots DbSet<T>. Alternatively, Spots is optional if you include DbSet<T>s for the derived types.

I encourage you to model your entities both ways to see how EF models the DB and choose which you prefer.

https://learn.microsoft.com/en-us/ef/core/modeling/inheritance

Upvotes: 1

Related Questions