Self referencing table Unique and distinct key pair with Ef Core Fluent API

Question

I have a many to many table for self referencing for which I'd like to have an index (or a key) made of two foreign keys. I'd also like to avoid same key repetition and reordered keys like

UserParentId xxx UserChildId

0                            1 => ok

0                           2 => ok

1                           1 => not ok

2                           0 => not ok because this link already exists (see second line)

On top of that I would like to search within this index with the two keys provided disorderly. Something like

_dbContext.UserToUsers.Find(userId1, userId2)

without knowing if userId1 represent UserParentId or UserChildId.

I've read this solution which could be a good general approach:

The model should keep UserparentId always strictly inferior to UserChildId and controllers should all be aware of that.

Nevertheless, I am still wondering if something like this have already been implemented in EF CORE. Index() or HasAlternateKey() does not seem to provide what I'd like. Any idea?

My Code:

Model:

public class User
{
    public int Id { get; set; }

    public virtual ICollection<UserToUser> ChildrenUsers { get; set; }
    public virtual ICollection<UserToUser> ParentUsers { get; set; }
}
public class UserToUser
{
    public int Id { get; set; }
    public int UserParentId { get; set; }
    public int UserChildId { get; set; }

    public virtual User ChildUser { get; set; }
    public virtual User ParentUser { get; set; }
}

Then my Fluent API code:

 modelBuilder.Entity<UserToUser>().HasOne(d => d.ChildUser)
            .WithMany(p => p.ParentUsers)
            .HasForeignKey(d => d.UserParentId); 

        modelBuilder.Entity<UserToUser>().HasOne(d => d.ParentUser)
            .WithMany(p => p.ChildrenUsers)
            .HasForeignKey(d => d.UserChildId);

        modelBuilder.Entity<UserToUser>()
            .HasAlternateKey(x => new {x.UserChildId, x.UserParentId});

Answer 1

In regard to using Find:

The Find method on DbSet uses the primary key value to attempt to find an entity tracked by the context. If the entity is not found in the context then a query will be sent to the database to find the entity there. Null is returned if the entity is not found in the context or in the database.

Find does not support cache lookup via alternate keys, although not yet implemented, in the future it will not be necessary as alternate ways to query the cache will evolve, meaning you could write an extension method called Find to do specifically what you ask. Have a look at this issue and those related to it: DbSet.Find by alternate key or predicate (you are not the first to ask this sort of request)

So you are asking EF to provide multiple PrimaryKeys on a table, but to also enforce that once a user is a child of another user that they cannot be a parent to the same user, so the values from all these combinations must be unique:

1. Id
2. UserChildId, UserParentId
3. UserParentId, UserChildId

A different way around this is recognizing that for the specified UserIds you can use Find on each User, especially if you wanted both users anyway (as there is no other information in the linking table, and both user objects can contain the link reference)

var user1 = _dbContext.Users.Find(userId1);
var user2 = _dbContext.Users.Find(userId2);
var link = user1.ChildrenUsers.FirstOrDefault(x => x.UserChildId == userId2) 
        ?? user2.ChildrenUsers.FirstOrDefault(x => x.UserChildId == userId1)
if (link != null)
{
    // process when the link exists
} 

This will bypass the local cache, but will do the job:

_dbContext.UserToUsers.Where(x => UserParentId == userId1 && UserChildId == userId2
                                  || UserParentId == userId2 && UserChildId == userId1);

If you are merely doing an existence check, then change the Where to Any and this will be just as efficient to run on the server.