'Automatic join tables for one to many relationships in EF Core

I am using code first EF Core 6 with Azure SQL Server. I have several entity types that are used in transactions that span several months. There is a need to associate notes with the different entities. The notes will be added by multiple users over the transaction timeframe. For audit purposes, I need to be able to keep track of every version of every note ever entered. I use system versioned temporal tables to keep track of entity changes for the rest of the database.

I want to have a Notes list navigation property in the transaction entities. I do not want a corresponding navigation property in the Notes entity.

public class TransactionEntity
{
   ...
   public List<Note> Notes { get; set; }
}

public class Note
{
   // text, timestamp, CreatedBy, ValidFrom, ValidTo
}

I configure this as:

public void Configure(EntityTypeBuilder<TransactionEntity> entity)
{
   entity.HasMany(t => t.Notes)
         .WithOne();
}

When I create the migration for this it creates a foreign key column in the Notes table. I do not want it to do that because there will be a List of Notes in 4-5 different entities and I do not want a foreign key column for each of those entities.

I would prefer that EF Core automatically create a join table for each transaction entity type in the background so that in the code I could just reference TransactionEntity.Notes without having to deal with the join table. EF Core offers this with many to many relationships, but this is not a many to many relationship and I would prefer to avoid treating it that way just to have the automatic join table option. I think I can have automatic join table creation with owned entities too. But the problem with that is that I don't see any way to use the temporal tables functionality with owned entities and I would have to come up with audit code that only applied to this one property.

I can manually create join tables for each transaction entity that needs notes. But I was hoping for cleaner, less code solution that will let me access notes without having to manually query the join table. I also want to be able to quickly add notes to a new transaction entity type without having to create a new join table.

Is there a way to do this that I'm unaware of, or a different approach that makes more sense here?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source