Multiple relationships between two entities

December 1st 2023 EF Core

Conventions for relationships between entities in Entity Framework Core do a great job for simple scenarios. Unfortunately, they fail as soon as you want to have more than one relationship between the same two entities. At that point, you have to configure both relationships manually.

Let's say you are developing a blogging engine. You might want to track authors of posts:

public class Post(string title)
{
    public int PostId { get; set; }
    public string Title { get; set; } = title;
    public User? Author { get; set; }
}

public class User(string name)
{
    public int UserId { get; set; }
    public string Name { get; set; } = name;
    public List<Post>? AuthoredPosts { get; set; }
}

Entity Framework Core will correctly recognize this as a one-to-many relationship between posts and users.

Alternatively, you might want to be able to specify which users are allowed to comment on a post:

public class Post(string title)
{
    public int PostId { get; set; }
    public string Title { get; set; } = title;
    public List<User>? AllowedToComment { get; set; }
}

public class User(string name)
{
    public int UserId { get; set; }
    public string Name { get; set; } = name;
    public List<Post>? AllowedToComment { get; set; }
}

In this case, Entity Framework Core will correctly recognize a many-to-many relationship and create a join entity for it.

Of course, you might want to have relationships for both the author and the comment permissions:

public class Post(string title)
{
    public int PostId { get; set; }
    public string Title { get; set; } = title;
    public User? Author { get; set; }
    public List<User>? AllowedToComment { get; set; }
}

public class User(string name)
{
    public int UserId { get; set; }
    public string Name { get; set; } = name;
    public List<Post>? AuthoredPosts { get; set; }
    public List<Post>? AllowedToComment { get; set; }
}

Unfortunately, this exceeds the capabilities of built-in conventions. If you try to generate a migration for this model, the command will fail:

Unable to create a DbContext of type ''. The exception 'Unable to determine the relationship represented by navigation Post.AllowedToComment of type List<User>. Either manually configure the relationship, or ignore this property using the [NotMapped] attribute or by using EntityTypeBuilder.Ignore in OnModelCreating.' was thrown while attempting to create an instance. For the different patterns supported at design time, see https://go.microsoft.com/fwlink/?linkid=851728

If we want to have both relationships, manually configuring the relationship is the only suitable option of the ones listed in the error message. We do that by overriding the OnModelCreating method in our context class. The process is well documented.

The one-to-many relationship is pretty straightforward. We only need to specify the navigation properties in each entity:

modelBuilder.Entity<Post>()  
    .HasOne(post => post.Author)  
    .WithMany(user => user.AuthoredPosts);

The many-to-many relationship requires more configuration. In addition to the navigation properties, we must also define the join entity:

modelBuilder.Entity<Post>()
    .HasMany(post => post.AllowedToComment)
    .WithMany(user => user.AllowedToComment)
    .UsingEntity(
        "PostUser",
        entity => entity
            .HasOne(typeof(User))
            .WithMany()
            .HasForeignKey("AllowedToCommentUserId"),
        entity => entity
            .HasOne(typeof(Post))
            .WithMany()
            .HasForeignKey("AllowedToCommentPostId"),
        entity => entity
            .HasKey("AllowedToCommentUserId", "AllowedToCommentPostId"));

With both relationships defined in the OnModelCreating method, the migration will now be successfully created.

You can find full source code for this example in my GitHub repository. Individual commits contain code for each of the steps:

  • working code for single one-to-many relationship,
  • working code for single many-to-many relationship,
  • broken code for both relationships depending on conventions, and
  • working code for both relationships with manual configuration.

Entity Framework Core conventions are a powerful tool which can save a lot of time because we don't need to manually define each and every relationship in our model. However, it's also important to know that when we exceed the capabilities of these conventions, there still is a well documented way to define the relationships manually. And we only need to do that for the problematic relationships, not for all relationships in the model.

Get notified when a new blog post is published (usually every Friday):

Copyright
Creative Commons License