Entity Framework Core Two Objects as Primary Key

Question

I have a model which is used for managing friend relationships. It looks as follows:

public class Relationship
{   
   [Required]
   public User User { get; set; }

   [Required]
   public User Friend { get; set; }

   [Required]
   public DateTimeOffset RelationshipInitializationDate { get; set; }
}    

Users will have multiple records for their ID and there will be multiple records with the same FriendID so defining either of these as a key is a no-go. I would like the key to be a composite between User and Friend but when I define it like this:

modelBuilder.Entity<Relationship>().HasKey(r => new { r.User, r.Friend });

I get an error that states:

The property 'Relationship.User' is of type 'User' which is not supported by current database provider. Either change the property CLR type or ignore the property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

How should I go about this to create the primary key that will link with a user and friend object. I didn't have any issues with my other objects having typed properties and I don't have an issue if I add an arbitrary key to the Relationship model. Thanks in advance

Answer 1

The basic idea here is that your adding properties to the model that EF can use to make a relationship. Right you're trying to create a relationship of type User and that is creating an error. To assign a composite key each key needs to be a type compatible with a Key field, not a navigation property. So we add UserId and FriendId of type int, string or GUID etc. and create a relationship off those properties.

public class Relationship
{
    public User Friend { get; set; }

    public User User { get; set; }

    public int FriendId { get; set; }

    public int UserId { get; set; }

    public DateTimeOffset RelationshipInitializationDate { get; set; }
}

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

You can now define a composite key across UserId and FriendId. Something like this should do:

public class NorthwindContext : DbContext
{
     public NorthwindContext(DbContextOptions<NorthwindContext> options):base(options) { }

     public NorthwindContext() { }

     protected override void OnModelCreating(ModelBuilder builder)
     {
         builder.Entity<Relationship>().HasKey(table => new {
         table.FriendId, table.UserId
         });
     }
     public DbSet<Relationship> Relationships { get; set; }
     public DbSet<User> Users { get; set; }
}

Source: Medium - How To: Entity Framework Core relationships, composite keys, foreign keys, data annotations, Code First and Fluent API