Mvc – Using “Id” as Primary Key in green field Dapper Contrib MVC 5 site

asp.net-mvcmvcorm

I am creating a new site and will base it on ASP.NET MVC 5 & Dapper Contrib. I want to go all in with all these technologies – and get all the time saving / convention over configuration / terse code advantages available.

I am designing the database in SQL Server 2014. Historically I have always named primary keys TableId rather than "Id".

CREATE TABLE User (
    UserId int IDENTITY(1,1) NOT NULL,
    Username VARCHAR(50),
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
)

Rather than…

CREATE TABLE User (
    Id int IDENTITY(1,1) NOT NULL,
    Username VARCHAR(50),
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
)

This avoids ambiguity in some SQL – and avoids joining primary key fields to foreign keys with a different name. Good for clarity.

However – and I think Ruby on Rails Active Record started this – ORMs such as Dapper Contrib require less configuration if the Id of a table / model is called "Id". To me this is an indication of best practice and is to be encouraged.

This is Dapper Contrib having an opinion and recommending a best practice.

Having said that very few example sites or code I have found (beyond dapper documentation) – using Dapper or not – use "Id" – most use "TableID".

Are there advantages beyond not having to add the [Key] attribute to the "TableID" property of a model to get it to map – and what is "best practice" when doing green field development?

Yes this question has been asked many times – but not with direct relation to ORMs – and not specifically about ASP.NET MVC 5 & Dapper Contrib. These are new(ish) factors.

Or am I just flogging a dead horse here – is it purely personal preference and not that important – even when ORMs are taken into account?

Best Answer

Does your DBMS care about which naming convention you use?

  • No

Do you care?

  • You probably do, just like any other human would. Everyone has a preference.

Do your colleagues care?

  • Yes? Consult with them.

Does your ORM care?

  • You should be able to answer this one yourself. I'm not familiar with Dapper but I have experience with Entity Framework, NHibernate and Eloquent and I find it ridiculous for any ORM to force a naming convention.

Does it make ever sense to name a primary key TableNameId instead of Id?

  • Not in my experience. If you do raw SQL queries and are worried about issues when doing joins you should use aliases. In fact, I find it more proper to name primary keys Id since that way I'm forced into the good practice of using aliases!

For me, User.UserId does not make sense. I always name my primary keys Id.

The only factors in this decision should be yours and your colleagues' preferences.

If there's something I missed out you can probably find it in this popular holy debate.

Related Topic