Shortening Entity Framework Index and Key Names
I recently started using ASP.Net Core for the backend API of one of my clients' systems and decided to use Entity Framework for most of the more mundane SQL operations that the API needs to handle. Overall, I've been really happy with Entity Framework, but one of the more annoying things about it is the way that it automatically generates names for indexes and foreign keys - depending on the length of the class names you might have in your Data Access Layer, the names that EF generates can be much longer than the limit allowed by your database.
It's not that big of a deal, but it's just really ugly and could even result in collisions if you have more than a few foreign keys in a table and you're using longer class names. I just got tired of looking at those comically long names when working with the database, so I thought it would be fun to figure out how to generate smaller index and key names when EF creates migrations.
The easiest way to do this turns out to be just overriding the OnModelCreating method in the application's implementation of the EF DbContext:
public class AppDatabase(DbContextOptions<AppDatabase> options) : DbContext(options)
{
// ... REDACTED ...
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
foreach (var property in entityType.GetProperties())
{
foreach (var fk in entityType.FindForeignKeys(property))
{
// We can change the foreign key name here...
}
}
}
foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
foreach (var index in entityType.GetIndexes())
{
// We can change the index name here...
}
}
}
}
There's just one minor problem with this - by the time this method is called during the creation of a migration, EF has already truncated the names, and so we don't know the original full name of the entities. Of course, EF just generated those names based on the names of the classes, so the first thing we need to do is create the names the same way that EF does it. I'm lazy, so I just went in to the EF source code and then added a few functions to my DbContext implementation that generate the names the same way that EF does it.
Here's the code for generating the foreign key names:
public class AppDatabase(DbContextOptions<AppDatabase> options) : DbContext(options)
{
// ...
// Generate foreign key name the same way EF does it:
private static string? GetDefaultName(IMutableForeignKey foreignKey)
{
var tableName = foreignKey.DeclaringEntityType.GetTableName();
var principalTableName = foreignKey.PrincipalEntityType.GetTableName();
if (tableName == null
|| principalTableName == null)
{
return null;
}
if (foreignKey.PrincipalEntityType.GetMappingStrategy() == RelationalAnnotationNames.TpcMappingStrategy
&& foreignKey.PrincipalEntityType.GetDerivedTypes().Any(et => StoreObjectIdentifier.Create(et, StoreObjectType.Table) != null))
{
return null;
}
var name = new StringBuilder()
.Append("FK_")
.Append(tableName)
.Append('_')
.Append(principalTableName)
.Append("_")
.AppendJoin( "_", foreignKey.Properties.Select(p => p.GetColumnName()))
.ToString();
return name;
}
}
And the code for generating the index names:
public class AppDatabase(DbContextOptions<AppDatabase> options) : DbContext(options)
{
// ...
private static string? GetDefaultName(IMutableIndex index)
{
var tableName = index.DeclaringEntityType.GetTableName();
if (tableName == null)
{
return null;
}
var baseName = new StringBuilder()
.Append("IX_")
.Append(tableName)
.Append('_')
.AppendJoin("_", index.Properties.Select(p => p.GetColumnName()))
.ToString();
return baseName;
}
}
Now that the full length names of the entities can be recovered, the only thing to do is make them shorter. In this case, I just decided to hash them and use the hash to replace the name that EF generated:
public class AppDatabase(DbContextOptions<AppDatabase> options) : DbContext(options)
{
// ...
static string ComputeHash(string rawData)
{
// Create a SHA256
using var md5Hash = MD5.Create();
// ComputeHash - returns byte array
var bytes = md5Hash.ComputeHash(Encoding.UTF8.GetBytes(rawData));
// Convert byte array to a string
StringBuilder builder = new StringBuilder();
for (int i = 0; i < bytes.Length; i++)
{
builder.Append(bytes[i].ToString("x2"));
}
return builder.ToString();
}
}
Now that the entity names can be generated, and we have a hash function to shorten them, we can put it all together and we're good to go:
public class AppDatabase(DbContextOptions<AppDatabase> options) : DbContext(options)
{
// ...
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
foreach (var property in entityType.GetProperties())
{
foreach (var fk in entityType.FindForeignKeys(property))
{
var fullName = GetDefaultName(fk);
if (fullName != null)
{
fk.SetConstraintName($"FK_{ComputeHash(fullName)}");
}
}
}
}
foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
foreach (var index in entityType.GetIndexes())
{
var fullName = GetDefaultName(index);
if (fullName != null)
{
index.SetDatabaseName($"IX_{ComputeHash(fullName)}");
}
}
}
}
private static string? GetDefaultName(IMutableForeignKey foreignKey)
{
var tableName = foreignKey.DeclaringEntityType.GetTableName();
var principalTableName = foreignKey.PrincipalEntityType.GetTableName();
if (tableName == null
|| principalTableName == null)
{
return null;
}
if (foreignKey.PrincipalEntityType.GetMappingStrategy() == RelationalAnnotationNames.TpcMappingStrategy
&& foreignKey.PrincipalEntityType.GetDerivedTypes().Any(et => StoreObjectIdentifier.Create(et, StoreObjectType.Table) != null))
{
return null;
}
var name = new StringBuilder()
.Append("FK_")
.Append(tableName)
.Append('_')
.Append(principalTableName)
.Append("_")
.AppendJoin( "_", foreignKey.Properties.Select(p => p.GetColumnName()))
.ToString();
return name;
}
private static string? GetDefaultName(IMutableIndex index)
{
var tableName = index.DeclaringEntityType.GetTableName();
if (tableName == null)
{
return null;
}
var baseName = new StringBuilder()
.Append("IX_")
.Append(tableName)
.Append('_')
.AppendJoin("_", index.Properties.Select(p => p.GetColumnName()))
.ToString();
return baseName;
}
static string ComputeHash(string rawData)
{
// Create a SHA256
using var md5Hash = MD5.Create();
// ComputeHash - returns byte array
var bytes = md5Hash.ComputeHash(Encoding.UTF8.GetBytes(rawData));
// Convert byte array to a string
StringBuilder builder = new StringBuilder();
for (int i = 0; i < bytes.Length; i++)
{
builder.Append(bytes[i].ToString("x2"));
}
return builder.ToString();
}
}
Now the index and foreign keys look a lot cleaner in the database, and there's no concern about ever running into any collisions.
