Prefixing ID columns with the table name in Entity Framework

I recently got a question about the database configuration part of my article series on N-Layer design in ASP.NET. In my model, all classes inherit DomainEntity<T> that defines a primary key called Id. This in turn means all primary key columns in the database are called Id as well. The reader wanted to know if it was possible to prefix that column with the entity / table name. So, the Id column in the People table would be called PersonId, the Id in EmailAddress would be called EmailAddressId and so on. This has always been possible (and easy to do) for separate entities, but EF 6's custom conventions makes it a lot easier to do it for the entire model.

Setting the column name for each entity

Overriding the column name can be done using the HasColumnName method. You can call this method for a specific property of an entity through the model builder, or in a custom configuration class as I have done with the Contact Manager application. Here's an example of how you could implement this in the PersonConfiguration class:

public PersonConfiguration()
{
  Property(x => x.FirstName).IsRequired().HasMaxLength(25);
  Property(x => x.LastName).IsRequired().HasMaxLength(25);

  Property(x => x.Id).HasColumnName("PersonId");

  ... Other code here
}

While this works, it's a bit cumbersome as you need to write this code for each entity. Especially in large models, that could mean a lot of code. Furthermore, changes to the entity name wouldn't be picked up in the column name automatically.

Fortunately, with EF 6, there's a better solution

Setting the column name for all entities using custom conventions

EF 6 supports custom conventions that let you configure many aspects of the model and underlying database. You can find out more about these conventions in the blog post Custom Code First Conventions (EF6 onwards).

For this particular implementation, you can use the Properties method on the model builder and then use the new ClrPropertyInfo property on each property. This property in turn has a property called ReflectedType which has a Name property that returns the name of the parent class of the property, such as Person or EmailAddress. This then allows you to change the name of all Id properties in the model at once, using something like this:

modelBuilder.Properties()
  .Where(p => p.Name == "Id")
  .Configure(p => p.IsKey().HasColumnName(p.ClrPropertyInfo.ReflectedType.Name + "Id"));

For the Contact Manager application, this would give you the following database schema:

The database tables showing the new ID column

If you want to make use of this feature in the Contact Manager application, follow these steps:

  • Right-click the solution in the Solution Explorer and choose Manage NuGet Packages for Solution
  • Click All under the Updates node, select the EntityFramework package and click Update.
  • Open the file ContactManagerContext.cs and in the OnModelCreating method add the code shown previously.
  • Save all changes and recompile the application. Depending on your setup, you either need to add a migration for this, or recreate the database.

From now on, all your Id columns will be prefixed with the entity's name.


Where to Next?

Wonder where to go next? You can post a comment on this article.

Doc ID 588
Full URL https://imar.spaanjaars.com/588/prefixing-id-columns-with-the-table-name-in-entity-framework
Short cut https://imar.spaanjaars.com/588/
Written by Imar Spaanjaars
Date Posted 01/08/2014 10:22

Comments

Talk Back! Comment on Imar.Spaanjaars.Com

I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.

When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.

If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the Doc ID of the document.

(Plain text only; no HTML or code that looks like HTML or XML. In other words, don't use < and >. Also no links allowed.