CODEWITHSUNDEEP
.netentity-framework-4.1code-first
Andrew Theken
Andrew Theken

Reputation: 3480

How is the many-to-many junction table name determined with Entity Framework Code First?

I know that Code-First uses conventions for model binding, and that this is the list of conventions that are available (and active?) by default.

It seems that the selection of the junction table name for Many-to-Many relationships is a little bit random..

Which of the referenced conventions is used to determine the junction table name? What is the algorithm used to determine that name?

Upvotes: 4

Views: 1023

Answers (1)

Slauma
Slauma

Reputation: 177133

I think, the general rule for the many-to-many join table name is

ClassNameOfLeftEntity + PluralizedClassNameOfRightEntity

So, if the first entity is User and the second is Role the join table name is UserRoles.

It is possible that this can be influenced by removing the PluralizingTableNameConvention but i am not sure.

The bigger problem is to determine what is the "left" and what the "right" entity. I think it depends on almost random factors, like the order which EF builds the model in which in turn depends on navigation properties between entities and the order in which you have written the DbSets in your derived context. For this reason it is strongly recommended to define the join table name explicitely with Fluent API. A small change in your model or changing the order of the sets in your context can EF let think the name must be RoleUsers instead of the former UserRoles.

Here is a reference to a similar answer.

Edit

Another reason to define the many-to-many mapping always explicitely with Fluent API (not that much related to the table name question but more to the question what should be the left and what the right entity) is performance.

The join table has a compound primary key and a clustered index on that key (at least in SQL Server). Now assume the table name would be RoleUsers and the left entity is Role and the right entity is User because perhaps some developer decided to sort the sets alphabetically in the context:

public DbSet<Role> Roles { get; set; }
public DbSet<User> Users { get; set; }

The join table entries would look like this:

RoleId    UserId
----------------
1         1
1         2
2         1
2         2
3         1
3         2

Now, possibly the majority of queries in your application are interested in getting the roles for a given user. But you are not often or never interested to get all users for a given role. The roles of a given user could be queried by an Include:

var user = context.Users.Include(u => u.Roles).Single(u => u.UserId == 1);

This would create a JOIN in SQL on the UserId in the join table: ON Users.UserId = RoleUsers.UserId. This join cannot use the index in the join table but instead results in a table scan to retrieve row 1, 3 and 5 in the table for the user's RoleIds.

Better for performance of queries like this is to have the User as the left entity and the Role as the right entity, resulting in a clustered index for the pair (UserId, RoleId) which is better for performance of such queries. (You could of course create a second index in the example above to improve the performance.) To achieve this you must specify the mapping with Fluent API.

So, it makes sense to choose the left and right entity wisely and to not leave this decision to EF.

Upvotes: 5

Related Questions