EF Core Code First - one to one conditional relationship-CodePudding

I'm not sure if this relationship has a name, so maybe I'm just missing it. I didn't find anything in the docs that looked like this.

I have a many-to-one relationship, sort of, but only one relationship matters, so it's kind of a one-to-one, but on a condition at the many side.

Equipment has many Contracts, but at most one active Contract.

Contract always has one Equipment, and has a reference to the Equipment. That side works just fine. I have business logic to prevent more than one Contract for an Equipment being active.

public class EquipmentModel{
    public int Id {get; set;}
    //...other properties
}

public class ContractModel{
    public int Id {get;set;}
    public EquipmentModel Equipment {get;set;}
    public bool Active {get;set;}
    //...other properties
}

This means I can do a _contracts.Include(x => x.Equipment) and get the equipment entity for the contract.

But I can't wrap my head around the relationship the other direction. I don't want to just put all the Contacts on the Equipment entity, I don't have a need for them and it seems like overkill.

I suppose I could put the Contract entity on the Equipment model and manually manage the relationship in business logic, but I'm hoping Entity Framework has a better way to designate this.

I could also pull back all contracts and use some business logic to filter down to the active one, but that's also less than ideal.

public IEnumerable<ContractModel> Contracts {get;set;}

Ideally, I'd want to be able to do something like _equipment.Include(x => x.ActiveContract) and have the one Contract with an Active == true automatically. Is this a pattern that exists within EF Core?

CodePudding user response：

The easiest way with that design is to use a Filtered Include,

_equipment.Include(x => x.Contracts.Where(c => c.Active))

You can reuse this expression by creating a method on your DbContext, that returns the base query with the Include, eg

 public IQueryable<Equipment> GetEquipment() => this.Set<Equipment>().Include(x => x.Contracts.Where(c => c.Active));

Which can be further filtered by the calling code before running the query, eg

var equips = db.GetEquipment().Where(e => e.Name == equipName).ToList();

If you feel strongly about it you can even omit the DbSet<Equipment> property on the DbContext (registering Equipment as an Entity in OnModelCreating), and replace it with

public IQueryable<Equipment> Equipment => this.Set<Equipment>().Include(x => x.Contracts.Where(c => c.Active));