EF Core, one to many relationship, one table referencing multiple tables

Question

In attempt to reduce the number of tables in a database, I'd like to use a single table to store the same type of data for multiple reference tables: a one to many relationship several times over with a single foreign table.

public class Note
{
    public int Id { get; set; }
    public int ParentId { get; set; }
}

public class Article
{
    public int Id { get; set; }

    public ICollection<Note> Notes { get; set; }
}

public class Order
{
    public int Id { get; set; }

    public ICollection<Note> Notes { get; set; }
}

Using Code First, I've attempted OnModelCreating for Article and Order entities:

        builder.HasMany(h => h.Notes)
            .WithOne()
            .HasForeignKey(f => f.ParentId)
            .OnDelete(DeleteBehavior.Cascade);

and I get an exception when saving changes:

Microsoft.EntityFrameworkCore.DbUpdateException : An error occurred while saving the entity changes. See the inner exception for details. ----> Microsoft.Data.Sqlite.SqliteException : SQLite Error 19: 'FOREIGN KEY constraint failed'.

Is what I am trying to accomplish possible with EF Core 8?

I would like to avoid creating multiple foreign key columns (parent1id, parent2id, etc..) each for separate parent table.. and intermediate/linking table also seems redundant..

Answer 1

You will run into problems with the suggested structure.

Note references an Order or Article by ID. Imagine you have an Article with ID 1 and an Order with ID 1 and a Note with ParentId = 1 Which object (Article or Order) does the Note belong to? It cannot be said.