How to implement one-to-one relations properly while using soft delete approach in EF Core?

Question

I am using the soft delete methodology in EF Core. Every entity has a boolean "Active". I filter all Active entities in the entity configuration layer.

I encountered a problem when soft deleting one-to-one relation. The problem is, I get foreign key constraint conflict after trying to add a new relationship. Because the database does not know anything about the soft deletion. It just checks if the foreign key used before or not.

I have a Customer model which has one Campaign model.

The scenario happens in the following;

1. Create a Customer instance (Active=true)
2. Create a Campaign instance (Active=true)
3. Set Campaign to Customer's Campaign property
4. SaveChanges
5. Query to get Customer entity saved above
6. Access its Campaign navigation property and set its Active=false
7. SaveChanges
8. Query to get single Customer entity
9. Create a new Campaign instance (Active=true)
10. Set this to Customer's Campaign property
11. SaveChanges

Step 11 throws an exception like "The INSERT statement conflicted with the FOREIGN KEY constraint"

The Models:

 public class Customer{
    public long Id { get; set; }
    public string Name { get; set; }
    public bool Active { get; set; }
    public Campaign Campaign {get; set;}
    public long CampaignId { get; set; }
 }

 public class Campaign{
    public long Id { get; set; }
    public string Name { get; set; }
    public bool Active { get; set; }
    public Customer Customer {get; set;}
 }

Entity Configurations(Separated based on IEntityTypeConfiguration):

Customer;

modelBuilder.HasKey(a => a.Id);
modelBuilder.HasQueryFilter(a => a.Active);

Campaign;

modelBuilder.HasKey(a => a.Id);
modelBuilder.HasOne(c => c.Customer)
    .WithOne(c => c.Campaign)
    .HasForeignKey<Customer>(c => c.CampaignId)
    .OnDelete(DeleteBehavior.Restrict);
modelBuilder.HasQueryFilter(a => a.Active);

To illustrate it in Customers table;

| Id        | Name       | Active | CampaignId
| --------- |:----------:| ------:| ----------
| 1         | Mark       | 0      | 1         
| 2         | James      | 0      | 1         
| 3         | Henna      | 0      | 1         
| 4         | Yay        | 1      | 1          

What I really need is this kind of one-to-one relation. I don't want 2 active customers with the same CampaignId. In my business logic, customers can be soft-deleted either manually or during an insert for a new customer with the same CampaignId.

What approach should I follow for a situation like this?

Answer 1

In this context, a foreign key is not the correct solution. Although you apparently want to be able to number their CampaignId values as Campaign 1, 2, 3, etc... those should be a Campaign Name property or part of a composite primary key, not any form of an identity property.

Assuming the above, the primary key I recommend for your Campaign table is a combination of CustomerID and CampaignID. This will always have a unique value (as long as you ensure that you do not reuse a campaign ID for a given customer.)

Alternately, you can also have an Identity column on the table as a primary key, and store the campaign ID for the customer as a separate value that is simply a label for the campaign.

After re-reading your request above, I think that the second option would work best for you. The FK from Customer to Campaign would be updated with the new identity field. The biggest issue I can see is that, as configured, a customer can only have one active campaign. If you ever need to have more than one active campaign per customer, you will need to remove the CampaignID from your customer table.