Configure relationships when inheriting in Entity Framework Core

Question

I am working with Entity Framework Core and have the following entity structure.

There is an abstract class Transaction. The two entities IncomeTransaction and ExpenseTransaction inherit from Transaction. For optimization, IncomeTransaction and ExpenseTransaction are different tables.

public abstract class Transaction
{
    public long Id { get; set; }
    public decimal Amount { get; set; }
}

public class ExpenseTransaction : Transaction
{
}

public class IncomeTransaction : Transaction
{
}

// EF context
public DbSet<IncomeEntry> IncomeEntries { get; set; }

public DbSet<ExpenseEntry> ExpenseEntries { get; set; }

I also have the entity of the Comment. It has the following logical relation:

Transaction 1 -> 0..1 Comment

So at the code level, I would like to abstract from the transaction type and work with the transaction type.

public class Comment
{
    public long Id { get; set; }

    public required string Text { get; set; }
    
    // Currently, this prop not working
    public required Transaction Transaction { get; set; }
}

// EF Context
public DbSet<Comment> Comments { get; set; }

So, is there any way to configure the foreign key so that at the database level it would be the keys between

Comment -> IncomeTransaction
Comment -> ExpenseTransaction

and at the code level I would get an abstract object of type Comment.Transaction?

I tried this, but it doesn't work

modelBuilder.Entity<IncomeTransaction>()
            .HasOne(e => e.Comment)
            .WithOne()
            .HasForeignKey<Comment>(c => new { c.TransactionId,TransactionType.Income })
            .HasPrincipalKey<IncomeTransaction>(e => new { e.Id, TransactionType.Income });

modelBuilder.Entity<ExpenseTransaction>()
            .HasOne(e => e.Comment)
            .WithOne()
            .HasForeignKey<Comment>(c => new { c.TransactionId, TransactionType.Expense })
            .HasPrincipalKey<ExpenseTransaction>(e => new { e.Id, TransactionType.Expense });

A discriminator is also unlikely to work.

Do you have any thoughts? I can say for sure that I will not be ability to merge IncomeTransaction and ExpenseTransaction into one table and need another solution

Answer 1

You are using two simple foreign keys on the same attribute.

I believe the only way would be to create a composite foreign key in the comment table, which will have one more field to decide which relationship to create. In this case, you can create an enum to do this work of saying which Transaction it belongs to.

To create a multiple foreign key, you can check this other answer:

Composite Key as foreign key