I have an ASP.NET Core web application written in C# that utilizes the Table-Per-Concrete-Type (TPC) strategy in its database structure.
For simplicity, let's say that my application solely has 3 tables: A, B, and C.
Regarding Table A, it has two columns (C1 and C2), plus a unique identifier column (Id). C1 is a nullable DateOnly column; C2 is a non-nullable DateOnly column.
Tables B and C have columns not relevant to this question; thus are omitted from the discussion.
It is worth mentioning that table A is mapped to a base reference type named "Class_A". Tables B and C are mapped to classes "Class_B" and "Class_C", respectively. Finally, Class_B and Class_C are direct descendants of Class_A.
Constraint
One of my business cases implies a database constraint over Table A. Specifically, column C1 must be greater than C2 when C1 is not null.
Question
Given the above context, how could one implement a constraint over Table_A using fluent API during modelBuilding? This constraint should be directly applicable and inheritable by its descendant tables.
It is worth mentioning that implementations like the example below cause database errors due to constraint duplicity (as already shown elsewhere). Meanwhile, Microsoft Documentation does not cite any potential constraint conflict (see here) due to TPC strategy (see here, here, and here).
Constraint Implementation (code example)
namespace MyApplication.DataPersistance
public class Class_A
{
public DateOnly? C1 { get; set; }
public DateOnly C2 { get; set; }
}
public class Class_B : Class_A
{
// ... other properties
}
public class Class_C : Class_A
{
// ... other properties
}
public class ApplicationDbContext : IdentityDbContext
{
public DbSet<Class_A> A { get; set; }
public DbSet<Class_B> B { get; set; }
public DbSet<Class_C> C { get; set; }
// ... other DbSets
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
: base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Class_A>(entity => {
entity.ToTable(b => b.HasCheckConstraint("CK_Class_A_C1_gt_C2", "[C1] > [C2]"));
entity.UseTpcMappingStrategy();
});
modelBuilder.Entity<Class_B>(entity => { });
modelBuilder.Entity<Class_C>(entity => { });
// ... configuration of other tables ...
}
}
Conclusion
So far, the TPC strategy seems to have some limitations regarding database constraint implementations. Nevertheless, I am eager to know Stackoverflow's community opinion and experience in the subject.
Sincerely,