Representing aggregation and inheritance in a database

Representing aggregation and inheritance in a database#

Aggregated classes create multiple tables#

If a class contains a list of another class, then tables are created for both classes. We only need to define a DbSet of the class that contains other class/es. In the example below this class is Inventory:

using Microsoft.EntityFrameworkCore;

var db = new InventorySystemDbContext();
db.Database.EnsureDeleted();
db.Database.EnsureCreated();

public class InventorySystemDbContext : DbContext
{
    public DbSet<Inventory> Inventories { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data Source=../../../inventory.sqlite");
    }
}

public class Item
{
    public string Id { get; set; }
    public decimal PricePerUnit{ get; set; }
    public decimal Quantity { get; set; }
    public uint InventoryLocation { get; set; }
}

public class Inventory
{
    public string Id { get; set; }
    public List<Item> Stock { get; set; }
}

Above code uses EnsureDeleted() method to delete the database first to create the database from scratch. The database view on the Database column is not updated automatically after the database has been deleted and remade. Use icon to refresh the view.

Now you should have two tables:

  1. Inventory

    • Id

  2. Item

    • Id

    • PricePerUnit

    • Quantity

    • InventoryLocation

    • InventoryId

We see that each Item is automatically associated with an inventory using InventoryId.

Inherited classes must be created manually#

Remember that we have also UnitItem and BulkItem. We have to add them manually.

using Microsoft.EntityFrameworkCore;

var db = new InventorySystemDbContext();
db.Database.EnsureDeleted();
db.Database.EnsureCreated();

public class InventorySystemDbContext : DbContext
{
    public DbSet<Inventory> Inventories { get; set; }
    public DbSet<UnitItem> UnitItems { get; set; }
    public DbSet<BulkItem> BulkItems { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data Source=../../../inventory.sqlite");
    }
}

public class Item
{
    public string Id { get; set; }
    public decimal PricePerUnit { get; set; }
    public decimal Quantity { get; set; }
    public uint InventoryLocation { get; set; }
}

public class BulkItem : Item
{
    public string MeasurementUnit { get; set; }
}

public class UnitItem : Item
{
    public decimal Weight { get; set; }
}
public class Inventory
{
    public string Id { get; set; }
    public List<Item> Stock { get; set; }
}

Additional to the previous example, we see these additional columns in Item that are related to BulkItem and UnitItem:

  • MeasurementUnit

  • Weight

  • Discriminator

Discriminator is used to differentiate between different Item classes.

Appendix#

Contents