How can I "split" a table using EF Core 6.0 to participate in two different relationships?

Question

I need to add 'notes' to multiple entities in my EF Core 6 data model. Notes have exactly the same schema - a date/time, the name of the person who created the note, and a comment - so I'd like to use a single database table, with a discriminator column on each Note record which indicates which relationship/entity it belongs to.

My entity model looks like this:

public class Note {
  public int Id { get; set; }
  public DateTime CreatedAt { get; set;}
  pulic string CreatedBy { get; set; }
  public string Comments { get; set; }
}

public class Customer {
  public int Id { get; set; }
  public string Name { get; set; }
  public List<Note> Notes { get; set; }
}

public class Supplier {
  public int Id { get; set; }
  public string Name { get; set; }
  public List<Note> Notes { get; set; }
}

and I'm trying to map the appropriate properties on my DbContext to produce this set of tables:

Notes
+----+------------+----------+------------------+------------+----------------------------------+
| Id | EntityType | EntityId | CreatedAt        | CreatedBy  | Comments                         |
+----+------------+----------+------------------+------------+----------------------------------+
| 1  | Customer   | 17       | 2022-01-22T18:22 | alice      | Cancelled erroneous invoice      |
| 2  | Supplier   | 19       | 2022-01-23T12:52 | bob        | Amended booking date to April    |
| 3  | Customer   | 24       | 2022-01-24T19:07 | bob        | Refunded duplicated order        |
| 4  | Customer   | 17       | 2022-01-27T18:22 | alice      | Added Bryan Smith as contact     |
| 5  | Supplier   | 22       | 2022-01-27T20:17 | carol      | Override booking terms for 2022  |
+----+------------+----------+------------------+------------+----------------------------------+


Customers
+----+------------------+
| Id | Name             |
+----+------------------+
| 17 | Fisher Price Plc |
| 24 | Bob's Bikes Ltd  |
+----+------------------+


Suppliers
+----+-------------------------+
| Id | Name                    |
+----+-------------------------+
| 19 | Wigs'r'Us International |
| 22 | Monkeys Unlimited       |
+----+-------------------------+

Anybody know what combination of properties, entities and discriminators I can use to get this working?

I've tried creating a SupplierNote and CustomerNote class, which inherit from Note, and then mapping them like this:

modelBuilder.Entity<Note>()
  .HasDiscriminator<string>(note => note.EntityType)
  .HasValue<CustomerNote>(nameof(Customer))
  .HasValue<SupplierNote>(nameof(Supplier));

but this creates a Note table with CustomerId and SupplierId columns, which isn't what I'm after.

Answer 1

Anybody know what combination of properties, entities and discriminators I can use to get this working?

I know for sure that no such combination exists currently (up to the latest at this time EFC 6.0 inclusive).

What you are seeking for is called polymorphic association, and is not supported by any EF (classic or Core) version. Because EF supports only associations which can be represented by physical enforced FK constraint in relational database.

Shortly, it's not possible. The simplest you could do though is to revisit the "I'd like to use a single database table" and just use separate tables. Multiple tables maintained by EF Core migrations are basically no cost. You can map your common class as owned entity and use it as collection of owned entities mapped to different tables in the entities which need it. Or you can try to map it as the EFC 5.0 introduced shared entity type. Or you can use it as a base class (not entity) and create and use concrete entity classes derived from it with no additional members. In all the cases, they would be mapped to separate tables, but their content will be defined/maintained in one place and also could be queried/manipulated polymorphically against the base class (of course except instance creation).

If you really want to stay with that db model, then you can't use navigation properties and must query/maintain them manually. And wait if someday (if any) EF adds support for logical relationships/navigations.

Answer 2

You will need to create two derivative classes CustomerNote : Note and SupplierNote: Note each which has their own binding to the type. You can even make a generic derivative type, although that might be over engineering. Both will be combined in one Note table, as by default EF used a table-per-hierarchy (link). This will add a discriminator to the table.

namespace EFExample
{
    public abstract class NoteBase
    {
        public int Id { get; set; }
        public DateTime CreatedAt { get; set; } = DateTime.Now;
        public string CreatedBy { get; set; }
        public string Comments { get; set; }
    }

    public class Note<T> : NoteBase
    {
        public virtual T Owner { get; set; }
    }

    public class Customer
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Note<Customer>> Notes { get; set; } = new();
    }

    public class Supplier
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Note<Supplier>> Notes { get; set; } = new();
    }
}

The context will just have three enities

        public DbSet<NoteBase> Notes { get; set; }
        public DbSet<Customer> Customers { get; set; }
        public DbSet<Supplier> Suppliers { get; set; }

adding is a bit verbose this way, so maybe SupplierNote is not a bad idea

    context.Add(new Supplier
    {
        Notes = new List<Note<Supplier>> {
            new Note<Supplier>
            {
                CreatedBy = "Me",
                Comments = "Hello",
            }
        }

    });
    context.SaveChanges();