Here’s a quick one, I’m using EF6 code first migrations and I want to add a created date/time column to every table in the database. I want it to have a default value of GETUTCDATE() and ultimately look like:
I’m using UTC dates so I don’t have to worry about the time zone of the server where this ultimately lives and calling the column CreatedUtc because it’s concise but clear.
So let’s get started, I’ll create a new class library in Visual Studio called CreatedUtcColumnDemo and immediately add Entity Framework via Nuget.
PM> Install-Package EntityFramework
Now we’ll add our first entity and data context, notice the CreatedUtc property with data annotations:
Because we’ll ultimately want our CreatedUtc property on all entities let’s extract an abstract base class (EntityBase) and have Product inherit from it:
Now we’ll enable migrations and create a single migration to create our Products table:
PM> Add-Migration AddProduct
At this point our solution looks like:
We now want to ensure that when we update our database via code first migrations our CreatedUtc property gets a default value, in this case GETUTCDATE(). One option would be to adjust the migration class manually, for each entity we could update CreateTable(), notice the addition of defaultValueSql: “GETUTCDATE()”.
This would become tedious and error prone as we start to add new entities though. A more efficient approach would be to create a custom SqlServerMigrationSqlGenerator and update our migration configuration to use this. Jumping right to the code:
Notice how CustomSqlServerMigrationSqlGenerator inherits from SqlServerMigrationSqlGenerator and overrides two Generate(…) methods. We then set our custom generator to the default for our project via SetSqlGenerator(“System.Data.SqlClient”, new CustomSqlServerMigrationSqlGenerator()).
Lastly, we’ll update our local database and we’re done.
Now, every time we create a new entity we inherit from EntityBase, when we update our database with migrations it will always add a CreatedUtc column with a default value of GETUTCDATE().
You can find the demo source code at https://github.com/andy-mehalick/CreatedUtcColumnDemo.