I am trying to achieve the following relationship in my model (simplified). In other words, each Employee can have multiple bank Accounts (private account, corporate account, etc...). Each employee can bring any company related Receipt (e.g. gas bill, police fine, etc). Now, depending on the total value of this receipt company can decide whether they will charge this value immediately to user, or, in case this is large amount of money, they need to deduct it monthly (thus each receipt can have multiple Transactions which are in this context understood as deductions). If Receipt is charged immediately (see ReceiptType property on a model), then only one Transaction exists for this entity. Finally (especially in case of long term deductions), Transaction needs to have access to Account as we need to specify, to which employee's account this value will be charged.
Here is how I specified entities:
public class EmployeeDB
{
public long Id { get; set; }
public string Name { get; set; }
public string Surname { get; set; }
public virtual IEnumerable<EmployeeAccountDB> Accounts { get; set; }
public virtual IEnumerable<ReceiptDB> Receipts { get; set; }
}
public class EmployeeAccountDB
{
public long Id { get; set; }
public long EmployeeId { get; set; }
public virtual EmployeeDB Employee { get; set; }
public virtual IEnumerable<TransactionDB> Transactions { get; set; }
}
public class ReceiptDB
{
public long Id { get; set; }
public long EmployeeDBId { get; set; }
public ReceiptTypeEnum ReceiptType { get; set; }
public double Value { get; set; }
public double VAT { get; set; }
public double TotalValue { get; set; }
public virtual EmployeeDB Employee { get; set; }
public virtual IEnumerable<TransactionDB> Transactions { get; set; }
}
public class TransactionDB
{
public long Id { get; set; }
public long EmployeeAccountId { get; set; }
public long ReceiptDBId { get; set; }
public virtual ReceiptDB Receipt { get; set; }
public virtual EmployeeAccountDB Account { get; set; }
}
Now via Fluent API I specified the following Entity Configurations:
public class EmployeeDBConfiguration : IEntityTypeConfiguration<EmployeeDB>
{
public void Configure(EntityTypeBuilder<EmployeeDB> builder)
{
builder.ToTable("Employees", ApplicationDbContext.DefaultSchema);
builder.HasKey(t => t.Id);
builder.Property(t => t.HireDate).IsRequired();
builder.HasMany(x => x.Accounts).WithOne(x => x.Employee).OnDelete(DeleteBehavior.Cascade);
builder.HasMany(x => x.Receipts).WithOne(x => x.Employee).OnDelete(DeleteBehavior.Cascade);
}
}
public class EmployeeAccountDBConfiguration : IEntityTypeConfiguration<EmployeeAccountDB>
{
public void Configure(EntityTypeBuilder<EmployeeAccountDB> builder)
{
builder.ToTable("EmployeeAccounts", ApplicationDbContext.DefaultSchema);
builder.HasKey(t => t.Id);
builder.Property(t => t.Id).ValueGeneratedOnAdd();
builder.HasOne(x => x.Employee).WithMany(o => o.Accounts).HasForeignKey(nameof(EmployeeAccountDB.EmployeeId));
builder.HasMany(x => x.Transactions).WithOne(x => x.Account).OnDelete(DeleteBehavior.Cascade);
}
}
public class ReceiptDBConfiguration : IEntityTypeConfiguration<ReceiptDB>
{
public void Configure(EntityTypeBuilder<ReceiptDB> builder)
{
builder.ToTable("Receipts", ApplicationDbContext.DefaultSchema);
builder.HasKey(t => t.Id);
builder.Property(t => t.Id).ValueGeneratedOnAdd();
builder.Property(t => t.TotalValue).IsRequired();
builder.HasOne(x => x.Employee).WithMany(x=>x.Receipts).HasForeignKey(x => x.EmployeeDBId);
builder.HasMany(x => x.Transactions).WithOne(x => x.Receipt).OnDelete(DeleteBehavior.Cascade);
}
}
class TransactionDBConfiguration : IEntityTypeConfiguration<TransactionDB>
{
public void Configure(EntityTypeBuilder<TransactionDB> builder)
{
builder.ToTable("Transactions", ApplicationDbContext.DefaultSchema);
builder.HasKey(t => t.Id);
builder.Property(t => t.Id).ValueGeneratedOnAdd();
}
}
Migrations are created just fine. The problem however occurs, when I try to create database via dotnet ef database update. I get the following problem
Introducing FOREIGN KEY constraint 'FK_Transactions_Receipts_ReceiptDBId' on table 'Transactions' may cause cycles or multiple cascade path
I am not really sure what the actual problem is. My goal is (what I tried to set up) when Employee is removed, then all subsequent entities Account, Receipt, Transaction that are related to this entity will be removed as well (as specified in configuration > Cascade delete).
Any help in respect to this matter would be highly appreciated. If my model structure is incorrect, I am more than happy to refactor it in order to keep it simple and clean.
P.S. I am connecting to SQL database.
