'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 |
|---|
