Seeding many-to-many databases in EFCore5 with ModelBuilder?

Question

There are many questions about seeding many-to-many relationships in Entity Framework. However, most of them are extremely old, and many-to-many behavior has changed significantly in EFCore5. The official docs recommend overriding OnModelCreating to implement ModelBuilder.Entity<>.HasData().

However, with the new many-to-many behavior (without explicit mappings), I can find no clear path to seed the intermediate tables. To use the example of this tutorial, the BookCategories class is now implicit. Therefore, there is no path to explicitly declare the intermediate table values while seeding.

I've also tried simply assigning the arrays, e.g.,:

public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    public ICollection Categories { get; set; }
}  
public class Category
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; }
    public ICollection Books { get; set; }
}

And then at seed time:

Book book = new Book() { BookId = 1, Title = "Brave New World" }

Category category = new Category() { CategoryId = 1, CategoryName = "Dystopian" }

category.Books = new List() { book };
book.Categories = new List() { category };

modelBuilder.Entity().HasData(book);
modelBuilder.Entity().HasData(category);

... but there are no entries created for BookCategories in the resulting migration. This was somewhat expected, as this article suggests that one must explicitly seed the intermediate table. What I want is something like this:

modelBuilder.Entity().HasData(
  new BookCategory() { BookId = 1, CategoryId = 1 }
);

However, again, since there is no concrete class to describe BookCategories in EFCore5, the only way I can think of to seed the table is to manually edit the migration with additional MigrationBuilder.InsertData commands, which rather defeats the purpose of seeding data via application code.

user_5202563 · Answer

However, again, since there is no concrete class to describe BookCategories in EFCore5

Actually, as explained in the What's new link, EF Core 5 allows you to have explicit join entity

public class BookCategory
{
    public int BookId { get; set; }
    public EBook Book { get; set; }
    public int CategoryId { get; set; }
    public Category Category { get; set; }
}

and configure the many-to-many relationship to use it

modelBuilder.Entity()
    .HasMany(left => left.Categories)
    .WithMany(right => right.Books)
    .UsingEntity(
        right => right.HasOne(e => e.Category).WithMany(),
        left => left.HasOne(e => e.Book).WithMany().HasForeignKey(e => e.BookId),
        join => join.ToTable("BookCategories")
    );

This way you can use all normal entity operations (query, change tracking, data model seeding etc.) with it

modelBuilder.Entity().HasData(
  new BookCategory() { BookId = 1, CategoryId = 1 }
);

still having the new many-to-many skip navigations mapping.

This is probably the simplest as well as the type-safe approach.

In case you thing it's too much, using the conventional join entity is also possible, but you need to know the shared dictionary entity type name, as well as the two shadow property names. Which as you will see by convention might not be what you expect.

So, by convention the join entity (and table) name is

{LeftEntityName}{RightEntityName}

and the shadow property (and column) names are

{LeftEntityNavigationPropertyName}{RightEntityKeyName}
{RightEntityNavigationPropertyName}{LeftEntityKeyName}

The first question would be - which is the left/right entity? The answer is (not documented yet) - by convention the left entity is the one which name is less in alphabetical order. So with your example Book is left, Category is right, so the join entity and table name would be BookCategory.

It can be changed adding explicit

modelBuilder.Entity()
    .HasMany(left => left.Books)
    .WithMany(right => right.Categories);

and now it would be CategoryBook.

In both cases the shadow property (and column) names would be

CategoriesCategoryId
BooksBookId

So neither the table name, nor the property/column names are what you'd normally do.

And apart from the database table/column names, the entity and property names are important because you'd need them for entity operations, including the data seeding in question.

With that being said, even if you don't create explicit join entity, it's better to configure fluently the one created automatically by EF Core convention:

modelBuilder.Entity()
    .HasMany(left => left.Categories)
    .WithMany(right => right.Books)
    .UsingEntity("BookCategory", typeof(Dictionary),
        right => right.HasOne(typeof(Category)).WithMany().HasForeignKey("CategoryId"),
        left => left.HasOne(typeof(Book)).WithMany().HasForeignKey("BookId"),
        join => join.ToTable("BookCategories")
    );

Now you can use the entity name to access the EntityTypeBuilder

modelBuilder.Entity("BookCategories")

and you can seed it similar to normal entities with shadow FK properties with anonymous type

modelBuilder.Entity("BookCategory").HasData(
  new { BookId = 1, CategoryId = 1 }
);

or for this specific property bag type entity, also with Dictionary instances

modelBuilder.Entity("BookCategory").HasData(
  new Dictionary { ["BookId"] = 1, ["CategoryId"] = 1 }
);

Update:

People seem to misinterpret the aforementioned "extra" steps and find them redundant and "too much", not needed.

I never said they are mandatory. If you know the conventional join entity and property names, go ahead directly to the last step and use anonymous type or Dictionary.

I already explained the drawbacks of taking that route - loosing the C# type safety and using "magic" strings out of your control. You have to be smart enough to know the exact EF Core naming conventions and to realize that if you rename class Book to EBook the new join entity/table name will change from "BookCategory" to "CategoryEBook" as well as the order of the PK properties/columns, associated indexes etc.

Regarding the concrete problem with data seeding. If you really want to generalize it (OP attempt in their own answer), at least make it correctly by using the EF Core metadata system rather than reflection and assumptions. For instance, the following will extract these names from the EF Core metadata:

public static void HasJoinData(
    this ModelBuilder modelBuilder,
    params (TFirst First, TSecond Second)[] data)
    where TFirst : class where TSecond : class
    => modelBuilder.HasJoinData(data.AsEnumerable());

public static void HasJoinData(
    this ModelBuilder modelBuilder,
    IEnumerable<(TFirst First, TSecond Second)> data)
    where TFirst : class where TSecond : class
{
    var firstEntityType = modelBuilder.Model.FindEntityType(typeof(TFirst));
    var secondEntityType = modelBuilder.Model.FindEntityType(typeof(TSecond));
    var firstToSecond = firstEntityType.GetSkipNavigations()
        .Single(n => n.TargetEntityType == secondEntityType);
    var joinEntityType = firstToSecond.JoinEntityType;
    var firstProperty = firstToSecond.ForeignKey.Properties.Single();
    var secondProperty = firstToSecond.Inverse.ForeignKey.Properties.Single();
    var firstValueGetter = firstToSecond.ForeignKey.PrincipalKey.Properties.Single().GetGetter();
    var secondValueGetter = firstToSecond.Inverse.ForeignKey.PrincipalKey.Properties.Single().GetGetter();
    var seedData = data.Select(e => (object)new Dictionary
    {
        [firstProperty.Name] = firstValueGetter.GetClrValue(e.First),
        [secondProperty.Name] = secondValueGetter.GetClrValue(e.Second),
    });
    modelBuilder.Entity(joinEntityType.Name).HasData(seedData);
}

Also here you don't need to know which type is "left" and which is "right", neither requires special base class or interface. Just pass sequence of entity pairs and it will properly seed the conventional join entity, e.g. with OP example, both

modelBuilder.HasJoinData((book, category));

and

modelBuilder.HasJoinData((category, book));

would do.

user_5219344 · Answer

Update (EF Core 5.0.2)

It's working well using the name of the associative table:

    builder.Entity("ContractDeclarationType").HasData(
        new { ContractsId = 1L, DeclarationTypesId = 1L },
        new { ContractsId = 1L, DeclarationTypesId = 2L },
        new { ContractsId = 1L, DeclarationTypesId = 3L });

user_559301 · Answer

I ended up whipping up a generic solution to this problem based upon the answer from Ivan (thanks!). I'm now able to seed all my M2M tables with this syntax:

// Add book1 and book2 to category1:
modelBuilder.HasM2MData(new [] { book1, book2 }, new [] { category1 });

This may not be fully robust, but it should work with conventional M2M mappings.

It makes some assumptions:

T1 & T2 Inherit from some ModelBase that provides an Id property.
T1 & T2 Have exactly one ICollection property.
You know the correct order (which model is T1 and which is T2) — this can be discovered by running the migration for the tables first and inspecting the migration.
You're running EFCore5 RC2 or later (see this issue).

public static void HasM2MData
  (this ModelBuilder mb, T1[] t1s, T2[] t2s)
  where T1 : ModelBase where T2 : ModelBase
{
  string table = $"{typeof(T1).Name}{typeof(T2).Name}";
  PropertyInfo t1Prop = GetM2MProperty();
  PropertyInfo t2Prop = GetM2MProperty();
  string t1Key = $"{t1Prop.Name}Id";
  string t2Key = $"{t2Prop.Name}Id";
  foreach (T1 t1 in t1s) {
    foreach (T2 t2 in t2s) {
      mb.Entity(table).HasData(new Dictionary() { [t2Key] = t1.Id, [t1Key] = t2.Id });
    }
  }
}

// Get a property on T1 which is assignable to type ICollection, representing the m2m relationship
private static PropertyInfo GetM2MProperty() {
  Type assignableType = typeof(ICollection);
  List props = typeof(T1).GetProperties()
                                       .Where(pi => pi.PropertyType.IsAssignableTo(assignableType))
                                       .ToList();
  if (props.Count() != 1) {
    throw new SystemException(
      $"Expected {typeof(T1)} to have exactly one column of type {assignableType}; got: {props.Count()}");
  }
  return props.First();
}

In the migration, we see something like:

migrationBuilder.InsertData(
table: "BookCategory",
columns: new[] { "BooksId", "CategoriesId" },
values: new object[,]
{
    { "book1", "category1" },
    { "book2", "category1" }
});

Seeding many-to-many databases in EFCore5 with ModelBuilder?

3 Answers

Update (EF Core 5.0.2)