Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Entity framework automatically renaming many-to-many tables without any changes

I've been working in the same project for almost 1½ year and today when I wanted to add a change to the db (code first). All of the sudden EF migration wants to rename a lot of the many-to-many tables that exist in the database today. These table names have been unchanged for over 2 years and I can't figure out why EF wants to rename them.

I've checked the entity files which the migration is pointing to and there is no change to the files that could explain this. In this project we work with data annotations, so we've never given any names to the many-to-many tables, we just let EF set the name of the tables.

RenameTable(name: "dbo.FilterLocation", newName: "LocationFilter");
RenameTable(name: "dbo.FilterCluster", newName: "ClusterFilter");
RenameTable(name: "dbo.ProfileFilter", newName: "FilterProfile");
RenameTable(name: "dbo.HtmlTemplateFilterProfile", newName: "ProfileHtmlTemplateFilter");
RenameTable(name: "dbo.HtmlTemplateProfile", newName: "ProfileHtmlTemplate");
RenameTable(name: "dbo.CategoryHtmlTemplateFilter", newName: "HtmlTemplateFilterCategory");
RenameTable(name: "dbo.HtmlTemplateCategory", newName: "CategoryHtmlTemplate");
DropPrimaryKey("dbo.LocationFilter");
DropPrimaryKey("dbo.ClusterFilter");
DropPrimaryKey("dbo.FilterProfile");
DropPrimaryKey("dbo.ProfileHtmlTemplateFilter");
DropPrimaryKey("dbo.ProfileHtmlTemplate");
DropPrimaryKey("dbo.HtmlTemplateFilterCategory");
DropPrimaryKey("dbo.CategoryHtmlTemplate");
AddPrimaryKey("dbo.LocationFilter", new[] { "Location_LocationId", "Filter_FilterId" });
AddPrimaryKey("dbo.ClusterFilter", new[] { "Cluster_ClusterId", "Filter_FilterId" });
AddPrimaryKey("dbo.FilterProfile", new[] { "Filter_FilterId", "Profile_ProfileId" });
AddPrimaryKey("dbo.ProfileHtmlTemplateFilter", new[] { "Profile_ProfileId", "HtmlTemplateFilter_HtmlTemplateFilterId" });
AddPrimaryKey("dbo.ProfileHtmlTemplate", new[] { "Profile_ProfileId", "HtmlTemplate_HtmlTemplateId" });
AddPrimaryKey("dbo.HtmlTemplateFilterCategory", new[] { "HtmlTemplateFilter_HtmlTemplateFilterId", "Category_CategoryId" });
AddPrimaryKey("dbo.CategoryHtmlTemplate", new[] { "Category_CategoryId", "HtmlTemplate_HtmlTemplateId" });

Edit:

The changes that I actually did is as following:

AddColumn("dbo.HtmlAdField", "MediaFactConnection", c => c.Int(nullable: false));
AddColumn("dbo.HtmlAd", "FactId", c => c.Int());
CreateIndex("dbo.HtmlAd", "FactId");
AddForeignKey("dbo.HtmlAd", "FactId", "dbo.Fact", "FactId");
DropColumn("dbo.HtmlAdField", "ConnectionState");
like image 298
grimsan55 Avatar asked Dec 01 '16 09:12

grimsan55


1 Answers

For anyone stumbling across this post...

Contrary to my initial comment, my problem was not solved by letting the migration run as generated. I found out why this behavior happens, how to fix it, and learned a lesson about configuring my entities more explicitly from the beginning.

Why?

Entity Framework will name and configure junction tables based on the order in which its algorithm configures the entities. The order in which the DbSet<> and modelBuilder 'definitions' appear changes which table is left/right for junction tables. I can't confirm exactly which/how this impacts the generated code but in my case, it all went pear-shaped after an all-to-easy-to-hit Resharper 'Code Cleanup'.

How to fix

By configuring a many-to-many mapping explicitly, you can imitate the behavior of EF generation when it first created the tables.

This post got me on the right track: Creating Many To Many Relationships using Fluent API in Entity Framework

In my case, EF was trying to rename the RoleCategory table to CategoryRole (and failing). This was the addition I made:

modelBuilder.Entity<Role>()
     .HasMany(e => e.Categories)
     .WithMany(e => e.Roles)
         .Map(m =>
         {
             m.ToTable("RoleCategory");
         });

Notes

Configure 'from' the entity that is named first in your table name. e.g. for my table RoleCategory I did this:

Entity<Role>.HasMany(e => e.Categories)

not

Entity<Category>.HasMany(e => e.Roles)

Include an explicit .WithMany(e => e.Roles) that names the navigation property on the other side.

Use .Map() to specify the table name. In my case, following the linked post failed when I specified the column names with .MapLeftKey() and .MapRightKey().

Essentially I just had to keep fiddling with the definition and running add-migration each time until all manipulations to the table disappeared from the generated DbMigration file.

My Lesson

Clearly best practice is to configure all many-to-many relationships explicitly from the start. In my case, I had made no changes to either of the entities involved in the junction table. However, a change to a distant 'cousin' entity and some reordering of DbContext had me flummoxed for some time.

Lesson learned. Hope someone else finds this helpful too.

like image 97
Nick Pearce Avatar answered Oct 19 '22 11:10

Nick Pearce