.netentity-framework-coreone-to-oneef-fluent-api

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

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."

Solution

  • 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.

    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:

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