EF Core custom complex constraint

Question

I have a situation which I will simplify to the following: Given a model TEntity with a foreign relation to TEntityType:

public class TEntity
{
    public int TEntityTypeId TypeId { get; set; }
    public string Name { get;set; }
}

Now, when I want to insert a new instance of TEntity in the database, I'd like to have a constraint, that the name is unique within the same type. In code, if I'd like to insert the instance toBeInserted, I'd check:

var conflictingEntity = await _repository.FindAsync(entity => entity.Name == toBeInserted.name && entity.TypeId == toBeInserted.TypeId );
if (conflictingEntity)
{
    // Don't insert record but, e.g., throw an Exception
}

Now, I'd also like to have that logic as a constraint on the DB itelf. How can I configure this with the model builder? How can more complex constraints concerning other properties/fields be configured?

Answer 1

Create an index on multiple columns

public class SampleContext : DbContext
{
    public DbSet<Patient> Patients { get; set; }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Patient>()
            .HasIndex(p => new { p.Ssn, p.DateOfBirth})
            .IsUnique();
    }
}
public class Patient
{
    public int PatientId { get; set; }
    public string Ssn { get; set; }
    public DateTime DateOfBirth { get; set; }
}

See here: https://www.learnentityframeworkcore.com/configuration/fluent-api/hasindex-method

One other point, don't try and do a search before an insert. It's entirely possible in a multi user system, that another user has inserted a record after your search but before your insert. Just insert your record and handle the DbUpdateException.