How do I create a One-to-One mapping without using a foreign key constraint?

Question

Problem - I need to create some type of mapping between 2 entities with property "SourceTransactionId", where either entity can be added to the db first, before the other, but still be able to query like below.

What I want: Display transfers to sender or receiver (depending on who's requesting to see their transfers) and its associated StripePayment data:

var transfers = _dbContext.StripeTransfers.Select(p => new {
   TransferAmount = p.StripePayment.Amount,
   TransferDate = p.DateCreated,
   Sender = p.StripePayment.Sender.UserName,
   Receiver = p.StripePayment.Receiver.UserName
}).Where(p => p.StripePayment.Sender.Id == userId || p.StripePayment.Receiver.Id == userId)
.ToListAsync();

Requirement - I don't know which entity will be created first as StripeTransfer is being created from a webhook that might be received before I can create the StripePayment entity, so either row should be capable of being added before the other one.

Here is my code:

public class StripePayment
{
    // primary key
    public int Id { get; set; }

    public string SourceTransactionId { get; set; }
    public StripeTransfer StripeTransfer { get; set; }

    public int Amount { get; set; }
    public int SenderId { get; set; }
    public User Sender { get; set; }
    public int ReceiverId { get; set; }
    public User Receiver { get; set; }
}

public class StripeTransfer
{
    // primary key
    public int Id { get; set; }

    public string SourceTransactionId { get; set; }
    public StripePayment StripePayment { get; set; }

    public DateTime DateCreated { get; set; }
}

What I tried - I tried adding a foreign key constraint, but this won't allow me to add a StripeTransfer before there is a StripePayment created.

modelBuilder.Entity<StripePayment>()
            .HasOne<StripeTransfer>(t => t.StripeTransfer)
            .WithOne(t => t.StripePayment)
            .HasPrincipalKey<StripePayment>(p => p.SourceTransactionId)
            .HasForeignKey<StripeTransfer>(t => t.SourceTransactionId)
            .IsRequired(false);

Error received when trying to add a StripeTransfer before a StripePayment:

"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_StripeTransfers_StripePayments_SourceTransactionId". The conflict occurred in database "yogabandy-database-dev", table "dbo.StripePayments", column 'LatestChargeId'.\nThe statement has been terminated."

Answer 1

This is a special 0..1 - 0..1 relationship I never encountered so far. Let me summarize its properties so we can check that we're on the same page.

• In the database there are two independent entities (tables).
• They're connected by key (i.e. unique) fields that are alternate (non-primary) key fields.
• The connection is defined semantically by these fields having the same values in both entities.
• The connection is established logically as soon as two entities with the same key values have entered the database, in any order of arrival.

Without any relationship modelling it would be possible to query the entities by a custom join statement over the two connecting properties. That may be a viable solution.

But let's explore what EF can do here.

In the database, because both entities can exist without their counterparts, none can have a foreign key to the other. The relationship can only be enforced by a junction table that refers to both tables.

All that said, the good news is that EF's configuration API is expressive enough to model this.

The classes (omitting user fields):

public class StripePayment
{
    public int Id { get; set; }
    public int Amount { get; set; }

    public string SourceTransactionId { get; set; }
    public PaymentTransfer? PaymentTransfer { get; set; }
}

public class StripeTransfer
{
    public int Id { get; set; }
    public DateTime DateCreated { get; set; }
    
    public string SourceTransactionId { get; set; }
    public PaymentTransfer? PaymentTransfer { get; set; }
}

The junction class:

public class PaymentTransfer
{
    public string SourceTransactionId { get; set; }
    public StripePayment StripePayment { get; set; }
    public StripeTransfer StripeTransfer { get; set; }
}

Yes, it's only one string property.

The mapping configuration:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var pt = modelBuilder.Entity<PaymentTransfer>();
    pt.HasKey(e => e.SourceTransactionId);
    pt.HasOne(e => e.StripePayment).WithOne(e => e.PaymentTransfer)
        .HasPrincipalKey<StripePayment>(e => e.SourceTransactionId)
        .HasForeignKey<PaymentTransfer>(e => e.SourceTransactionId);
    pt.HasOne(e => e.StripeTransfer).WithOne(e => e.PaymentTransfer)
        .HasPrincipalKey<StripeTransfer>(e => e.SourceTransactionId)
        .HasForeignKey<PaymentTransfer>(e => e.SourceTransactionId);
}

Some highlights:

• EF core allows relationships to alternate keys (SourceTransactionId in both entities). It recognizes these keys by the HasForeignKey statements.
• The relationships to the junction class are 1:1, making the entire relationship 0..1-0..1.
• PaymentTransfer.SourceTransactionId is primary key and foreign key to both independent (principal) entities. (To me, so much function in one field is the fun part of this question).
• If EF creates a database schema from this model, both SourceTransactionId fields in the entity tables get unique indexes. When working database-first, make sure you create these indexes.

Now, stepping back, let's evaluate the options.

• A simple join statement would suffice, but manually coded joins are tedious and error-prone. Still, when tucked away in some reusable method, it may be the best option.
• A junction class, while requiring complex configuration, isn't hard to understand and is very light-weight. It -
  • Enforces the relationship by database constraints.
  • It allows querying by navigation properties (p.PaymentTransfer.StripePayment.Amount, etc. in your query).
  • It is a visible, self-explanatory expression of the relationship
  • But it's logically redundant: if entities are logically connected by identical key values, w/o a junction record they're not physically connected.