Constrain Data Based on Implicit Relationships in Entity Framework Core

Question

I have generalized the problem as outlined by the Data Model and Data. My generalization only keeps the relationships in tact.

The Problem

We have a base model Owner, which captures the Pet for the owner, in this way we can look at all owners and determine if they are a Cat or Dog owner.

We have separated Owners based on their Pet into two tables: CatOwner and DogOwner. These tables are where unique details are captured for each owner type.

In the specific tables, CatOwner and DogOwner, we collect the information PetType so we can determine if their pet is: Grumpy, Friendly, Cute, etc...

The business has defined what Type each Pet can be in the PetType table.

The Question

Is there a mechanism in Entity Framework Core that allows us to constrain the data in a manner which won't allow a CatOwner to have a PetType where the PetType.Pet = Dog?

I understand that an option for this is to add CatType and DogType tables to the data model, then a CatOwner will have a CatType and a DogOwner will have a DogType. This solution will require adding a generic type to the data model architecture which I don't want to do, since this is such a small piece of the overall data model. We will enforce this relationship in the API, but there won't be any Referential Integrity enforced by the database engine.

We don't leverage the Fluent API. We rely heavily on Data Annotations which is our preferred technology for defining relationships.

Data Model (Pseudo-Code Overview)

Pet {
    Id
}

Type {
    Id
}

PetType {
    Id,
    PetId,
    TypeId
}

Owner {
    Id,
    PetId
}

CatOwner {
    Id,
    OwnerId,
    PetTypeId
}

DogOwner {
    Id,
    OwnerId,
    PetTypeId
}

Data

Pet

Id
Cat
Dog

Type

Id
Grumpy
Selfish
Friendly
Helpful
Cute

PetType (These relationships are defined by the business)

Id	Pet	Type
1	Cat	Cute
2	Cat	Grumpy
3	Cat	Selfish
4	Dog	Cute
5	Dog	Friendly
6	Dog	Helpful

Data Model (Details)

[Index(nameof(PetId), nameof(TypeId), IsUnique = true)]
class PetType
{
    int Id { get; set; }

    Pet? PetNavigation { get; set; }

    [ForeignKey(nameof(PetNavigation)]
    int PetId { get; set; }

    Type? TypeNavigation { get; set; }

    [ForeignKey(nameof(TypeNavigation)]
    int TypeId { get; set; }
}

class Owner
{
    int Id { get; set; }
    
    Pet? PetNavigation { get; set; }
    
    [ForeignKey(nameof(PetNavigation)]
    int PetId { get; set; }
}

class CatOwner
{
    int Id { get; set; }

    Owner? OwnerNavigation { get; set; }

    [ForeignKey(nameof(OwnerNavigation)]
    int OwnerId { get; set; }

    PetType? PetTypeNavigation { get; set; }

    [ForeignKey(nameof(PetTypeNavigation)]
    int PetTypeId { get; set; }
}

I have come up with other viable solutions which will be implemented in the case that this path is a dead end. Right now I am just exploring options and looking for new ways to approach the problem.

Answer 1

Entity Framework Core does not directly support the kind of constraint you are describing using Data Annotations alone.it might help you with some basic checking. you could try using the database trigger based approach. Use SQL database trigger to enforce this constraint. Any insertion or update that violates your constraints would be rejected by the trigger.

Answer 2

Why even have tables such as CatOwner and DogOwner? You'll need a table for each type of pet. Just use a junction table: