How to make a composite primary key bidirectional?

Question

My problem is in the title. I want to make a composite primary key as bidirectional. What I mean by bidrectional? Let me explain:

I have a friendship table. Primary keys are: SenderId and ReceiverId

In OnModelCreating:

builder.Entity<Friendship>()
                .HasKey(i => new { i.SenderId, i.ReceiverId });

So my friendship entities have primary keys like {SenderId, ReceiverId}. But not like {ReceiverId, SenderId}. I want to make keys to be both ways. In this way, my friendship requests will be unique and I will not create duplicate friendship requests as follows:

Is this possible?

NOTE: I know how to check if there is a entry with those IDs.But I want to implement the database to reject the new entry with the same IDs in both ways.

Answer 1

You need a key that conforms to a canonical form, i.e. always appear in the same order.

For example, instead of having a ReceiverID and a SenderID, you'd have two neutrally named fields (e.g. ParticipantA and ParticipantB) plus a constraint that ParticipantA must be less than ParticipantB. This way, you can set a constraint so that each pairing of IDs can only appear once, regardless of who is sending and who is receiving.

You'd then need to add a column that specifies direction of the relationship, e.g. who is the sender or if it is bidirectional.

Thus instead of

SenderID   ReceiverID
--------   ----------
1111       2222
4444       3333
1234       5678
5678       1234

You'd have

ParticipantA   ParticipantB  Sender
------------   ------------  ---------
1111           2222          A
3333           4444          B
1234           5678          BIDIRECTIONAL

Answer 2

As @John Wu points out, you need a composite key where the containing key values are always in order, so (1, 2) and (2, 1) are both mapped to (1, 2), thus treated as equal.

However the proposed implementation requires too much changes to the database model and how you work with it. Also prevents having natural User.Senders and User.Receivers collection navigation properties if needed to obtain such information for a User.

So instead of modifying the existing primary data model, what you need is unique constraint (index) on a "normalized" composite key (User1Id, User2Id), where (in preudo code) User1Id = Min(SenderId, ReceiverId) and User2Id = Max(SenderId, ReceiverId).

The implementation of this is database specific. For SqlServer it can be implemented by creating two computed columns and then create unique constraint (index) on them, e.g.

// the two shadow properties mapped to the computed columns
modelBuilder.Entity<Friendship>()
    .Property<Guid>("UserId1")
    .HasComputedColumnSql("case when SenderId < ReceiverId then SenderId else ReceiverId end");

modelBuilder.Entity<Friendship>()
    .Property<Guid>("UserId2")
    .HasComputedColumnSql("case when SenderId < ReceiverId then ReceiverId else SenderId end");

// the unique index using them
modelBuilder.Entity<Friendship>()
    .HasIndex("UserId1", "UserId2")
    .IsUnique();