Entity Framework - per distinct string property only have one possible "true" value for other property

Question

Imagine having an entity like this example:

public class Thing
{
    public int Id { get; set; }

    public string Name { get; set; }

    public bool IsActive { get; set; }
}

How can I add a contraint-ish so that only ONE of all the Things with a certain name can have true for IsActive?

In other words, we can have multiple Things with the same name, but at any given time only one can have an IsActive which is true - the others need to have false for IsActive. So if we want to add or update one, it needs to check if the new value is true for IsActive that it won't make a "conflict".

Is this somehow possible?

Answer 1

One option would be to adopt actions for changing entity state rather than public setters. For instance, in most cases my entities reside in a Domain assembly along with the DbContext and things like Repository classes. "Things" would reside under a container entity to be uniquely associated.

public class Thing
{
     public int Id { get; internal set;}
     public string Name { get; internal set; }
     public bool IsActive { get; internal set; }
}

public class Container
{
     public int Id { get; internal set; }
     public virtual ICollection<Thing> Things { get; internal set; } = new List<Thing>();

     public void AddThing(string name, bool isActive = true)
     {
        var existingActiveThing = Things.SingleOrDefault(x => x.Name == name && x.IsActive);
        if(isActive && existingActiveThing != null)
            existingActiveThing.IsActive = false;

        var newThing = new Thing { Name = name, IsActive = isActive };
        Things.Add(newThing);
    }

    public void ActivateThing(int thingId)
    {
        var thing = Things.Single(x => x.Id == thingId);
        if(thing.IsActive)
            return; // Nothing to do.
        var existingActiveThing = Things.SingleOrDefault(x => x.Name == thing.Name && x.IsAcitve);
        if (existingActiveThing != null)
            existingActiveThing.IsActive = false;

        thing.IsActive = true;
    }

    // And so forth for instance Renaming a Thing or other allowed actions.
}

Container could be a containing entity, or these actions could be encapsulated in a domain level service class initialized with the collection of Things or access to the DbContext if Things are a top-level entity. The setters are marked as Internal to require the use of a domain level method to mutate state rather than arbitrary updates via the setters. This technique can be useful where you want to enforce validation across multiple entities or multiple fields to ensure the entity state at every point in time is valid. (I.e. updating address fields as a set of values to be validated rather than 1 field at a time, where you can change Country or such leaving the entity in an invalid combination of values that cannot pass validation)