I have an issue with seeding the many to many tables, when I add the Fluent API.
First I have created the two entities, and the many to many relationship between them like this:
[Table("meta.DataCategory")]
public partial class DataCategory : ResolvableEntityBase
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
[Column("DataCategoryId")]
public int Id { get; set; }
[Required]
[StringLength(50)]
[Column("DataCategory")]
[DisplayName("DataCategory")]
public string Name { get; set; }
public virtual ICollection<DestinationTable> DestinationTables { get; set; }
}
[Table("meta.DestinationTable")]
public partial class DestinationTable
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
[Column("DestinationTableId")]
public int Id { get; set; }
[Required]
[StringLength(200)]
[Column("TableName")]
public string Name { get; set; }
[Required]
[StringLength(200)]
[Column("SchemaName")]
public string Schema { get; set; }
public virtual ICollection<DataCategory> DataCategories { get; set; }
}
Adding the migration, and updating the database create the 3 tables, including the many to many table between the DataCategory and DestinationTable. The seed code showen here also worked as it should, and I was able to populate test data to all 3 tables:
context.DataCategories.AddOrUpdate(s => s.Id,
new DAL.DataCategory() { Id = 0, Name = "Unknown", RegexPattern = @"" },
new DAL.DataCategory() { Id = 1, Name = "ProductsMaster", RegexPattern = @"(?:\b|[_]{1})(product|products|produkter|articles)(?:\b|[_]{1})" },
new DAL.DataCategory() { Id = 2, Name = "CustomersTraffic", RegexPattern = @"(?:\b|[_]{1})(trafik|antal)(?:\b|[_]{1})" },
new DAL.DataCategory() { Id = 3, Name = "ProductSales", RegexPattern = @"(?:\b|[_]{1})(salg|sales|ugedata|uge data|uge_data)(?:\b|[_]{1})" },
new DAL.DataCategory() { Id = 4, Name = "CategorySales", RegexPattern = @"(?:\b|[_]{1})(kategory|kategori|category|kat)(?:\b|[_]{1})" },
new DAL.DataCategory() { Id = 5, Name = "StoresMaster", RegexPattern = @"(?:\b|[_]{1})(site\bmaster|store|stores|butik)(?:\b|[_]{1})" },
new DAL.DataCategory() { Id = 6, Name = "MultipleCategories", RegexPattern = @"" },
new DAL.DataCategory() { Id = 7, Name = "ConsultantsMaster", RegexPattern = @"" }
);
DAL.DestinationTable dt = new DAL.DestinationTable();
dt.Id = 1;
dt.Name = "Product";
dt.Schema = "DW";
dt.Type = "Reference";
dt.DataCategories = new List<DAL.DataCategory>();
dt.DataCategories.Add (context.DataCategories.Where(x => x.Name == "ProductsMaster").First());
context.DestinationTables.AddOrUpdate(x => x.Name, dt);
dt = new DAL.DestinationTable();
dt.Id = 2;
dt.Name = "Store";
dt.Schema = "DW";
dt.Type = "Reference";
dt.DataCategories = new List<DAL.DataCategory>();
dt.DataCategories.Add (context.DataCategories.Where(x => x.Name == "StoresMaster").First());
context.DestinationTables.AddOrUpdate(x => x.Name, dt);
dt = new DAL.DestinationTable();
dt.Id = 3;
dt.Name = "ProductSales";
dt.Schema = "DW";
dt.Type = "Transactions";
dt.DataCategories = new List<DAL.DataCategory>();
dt.DataCategories.Add (context.DataCategories.Where(x => x.Name == "ProductSales").First());
dt.DataCategories.Add (context.DataCategories.Where(x => x.Name == "ProductsMaster").First());
dt.DataCategories.Add (context.DataCategories.Where(x => x.Name == "StoresMaster").First());
context.DestinationTables.AddOrUpdate(x => x.Name, dt);
So far so good, however, the many to many table was created under the dbo schema, and the column names used the _Id suffix, which I do not use in the rest of the model, so I had to change that, so I used the following Fluent API in the OnModelCreating() method:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<DAL.DestinationTable>()
.HasMany<DAL.DataCategory>(dest => dest.DataCategories)
.WithMany(dcat => dcat.DestinationTables)
.Map(m2m =>
{
m2m.MapLeftKey("DestinationTableId");
m2m.MapRightKey("DataCategoryId");
m2m.ToTable("DestinationTableToDataCategory", "meta");
});
}
This made the expected result in terms of naming the table, and naming the columns the way I want it to be, but now the seeding does not work as before.
The seed code populate the DataCatetory records, and the DestinationTable records, but the many to many table is empty. I tried to reveres the direction, so that the DataCategory was on the left side, but the result was the same, that is the many to many table was not populated.
Note that the seed method run completely without errors.
Why does this happens? What is the way to seed many to many relationship, when using the Fluent API to bypass EF defaults?
Is there something wrong/missing in the Fluent API part?
