EF Core Table Per Hierarchy - Is it possible to keep track of collections for specific Entity?

Question

Lets say I would implement a Table Per Hierarchy for a class where I would store subclasses of this type distinguished by a discriminator (~ 5 types). Some subclasses will have their own ICollections and some wont, so this will not be specified in the superclass. Im currenly only able to fetch the data that is directly stored in the table but unable to fetch the collection of this subclass (length of collection will be 0) Any thoughts on how I would be able to fill in this list when I fetch this specific subclass (with specific discriminator) object from the database?

Answer 1

Here is a fully working sample console project, that demonstrates this approach:

using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate
{
    public class IceCream
    {
        public int IceCreamId { get; set; }
        public string Name { get; set; }
    }

    public class IceCreamAsDrink : IceCream
    {
        public string DrinkName { get; set; }
    }

    public class IceCreamWithToppings : IceCream
    {
        public ICollection<Topping> Toppings { get; set; } = new HashSet<Topping>();
    }

    public class Topping
    {
        public int ToppingId { get; set; }
        public string Name { get; set; }
        public int IceCreamWithToppingsIceCreamId { get; set; } // <-- use this exact
                                                                //     name or use the
                                                                //     Fluent API

        public IceCreamWithToppings IceCreamWithToppings { get; set; }
    }

    public class Context : DbContext
    {
        public DbSet<IceCream> IceCreams { get; set; }
        public DbSet<IceCreamAsDrink> IceCreamsAsDrink { get; set; }
        public DbSet<IceCreamWithToppings> IceCreamsWithToppings { get; set; }
        public DbSet<Topping> Toppings { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlServer(
                    @"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=So63087805")
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<IceCreamWithToppings>()
                .HasMany(e => e.Toppings)
                .WithOne(e => e.IceCreamWithToppings)
                .HasForeignKey(e => e.IceCreamWithToppingsIceCreamId);

            modelBuilder.Entity<IceCream>().HasData(
                new IceCream
                {
                    IceCreamId = 1,
                    Name = "Basic Vanilla"
                });

            modelBuilder.Entity<IceCreamAsDrink>().HasData(
                new IceCreamAsDrink
                {
                    IceCreamId = 2,
                    Name = "Vanilla Ice Coffee",
                    DrinkName = "Coffee"
                });

            modelBuilder.Entity<IceCreamWithToppings>().HasData(
                new IceCreamWithToppings
                {
                    IceCreamId = 3,
                    Name = "Vanilla With Sprinkles"
                });

            modelBuilder.Entity<Topping>().HasData(
                new Topping
                {
                    ToppingId = 1,
                    Name = "Chocolate Sprinkles",
                    IceCreamWithToppingsIceCreamId = 3
                },
                new Topping
                {
                    ToppingId = 2,
                    Name = "Whipped Cream",
                    IceCreamWithToppingsIceCreamId = 3
                });
        }
    }

    internal static class Program
    {
        private static void Main()
        {
            using var context = new Context();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var allIceCreams = context.IceCreams
                .OrderBy(i => i.IceCreamId)
                .ToList();

            var iceCreamsAsDrink = context.IceCreamsAsDrink
                .ToList(); 

            var iceCreamsWithToppings = context.IceCreamsWithToppings
                .Include(i => i.Toppings)
                .ToList();

            Debug.Assert(allIceCreams.Count == 3);
            Debug.Assert(iceCreamsAsDrink.Count == 1);
            Debug.Assert(iceCreamsWithToppings.Count == 1);
            Debug.Assert(iceCreamsWithToppings[0].Toppings.Count == 2);
        }
    }
}

If you want to use conventions for the foreign key between Topping and IceCreamWithToppings, then the foreign key needs to be named <DerivedType><PrimaryKeyOnBaseType>, so IceCreamWithToppingsIceCreamId in this case.

Alternatively, just define the relationship using the Fluent API:

public class Topping
{
    public int ToppingId { get; set; }
    public string Name { get; set; }
    public int MyForeignKeyToIceCream { get; set; } // <-- non-convention name
    
    public IceCreamWithToppings IceCreamWithToppings { get; set; }
}

public class Context : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<IceCreamWithToppings>()
            .HasMany(e => e.Toppings)
            .WithOne(e => e.IceCreamWithToppings)
            .HasForeignKey(e => e.MyForeignKeyToIceCream);
    }
}