Recently I've been building out a .Net Core WebAPI microservice that talks to a PostgreSQL database. We're diving into the world of HATEOS so we want to have a Url parameter that correlates to the Id of the record once it is created. Additionally, we're using Docker to better manage our deployments, so we want to be able to have this URL change when building the database in each environment as part of our Entity Framework migrations process.
It's a bit tricky, but doable. Here's how you get your "ducks in a row" in order to make this happen.
The first step is to create your model that has all of your needed properties. While our url field will be a required field, we will enforce it by setting a default value. Otherwise we won't be able to have the field autogenerated properly. Our model looks something like this:
public class Duck<br>{ <br> [Key]<br> [Column("id")] <br> public int Id { get; set; }<br><br> [Required]<br> [Column("name")]<br> public string Name { get; set; }<br><br> [Column("url")]<br> public string Url { get; set; }<br><br> [Column("breed")]<br> public string Breed { get; set; } <br>}
Next, run dotnet ef migrations add InitialSchema to create the initial migration script that will create this table. Make sure you build your ApplicationDbContext object to add the DBSet for and run dotnet ef database update to run the migration script and create your table.
The next step is to verify that everything was created. I've become a fan of DBeaver as my tool of choice to inspect my PostgreSQL database instances. Once you login and navigate down to your table, make a note of the Default value that was created as part of your sequence when the Key was generated for the ID. In my case, it was named Ducks_id_seq.
With info handy, we need to create another migration. We couldn't do this all in one step because simply referencing the id column as a default value would in turn reference the sequence, which hadn't been created yet and would cause the migration to fail.
In your ApplicationDbContext, modify (or create) your OnModelCreating override method to include the following code:
// Autogenerate URL based on ID that has been generated. modelBuilder.Entity<Duck>()<br> .Property(b => b.Url)<br> .HasDefaultValueSql("'" + urlBase + "' || currval('\"Ducks_id_seq\"')");
What this will do it grab the current value of the sequence and apply it to the Url field. Why do we use currval? Calling nextval would force the sequence to increment, and since the sequence is incremented and assigned to the Id before we update this value, everything synchronizes up.
We need to set the urlBase parameter really quick for our script. Since we want this configuration driven, it is easy enough to add a parameter called ApplicationBaseUrl into our appsettings.json file and set it's value to http://api.epicducks.test/v0/ducks/ (and it's subsequent Development/Staging/Production files), update our ApplicationDbContext object to have our app's configuration injected and go from there.
... {namespace and class declaration code} ...<br>private readonly IConfiguration _configuration;<br><br>public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options, IConfiguration configuration) : base(options)<br>{<br> _configuration = configuration;<br>}<br><br>protected override void OnModelCreating(ModelBuilder modelBuilder)<br>{ <br> var urlBase = _configuration["ApplicationUrlService"];<br><br> ... {potential other code} ...<br><br> // Autogenerate URL based on ID that has been generated.<br> modelBuilder.Entity<Duck>()<br> .Property(b => b.Url)<br> .HasDefaultValueSql("'" + urlBase + "' || currval('\"Ducks_id_seq\"')");
Now we can run dotnet ef migrations add AddUrlDefault and we should be all set...
...except that we aren't! If you open up the generated migration files for your project and look at our actions. You'll find something like this:
migrationBuilder.AlterColumn<string>(<br> name: "url",<br> table: "Ducks",<br> nullable: true,<br> defaultValueSql: "'http://api.epicducks.test/v0/ducks/' || currval('\"Ducks_id_seq\"')",<br> oldClrType: typeof(string),<br> oldNullable: true);
Our migration code turned this into static text instead of a variable. This work fine locally, but the migration code isn't going to be updated in our other environments, so we need to do something to make this more dynamic.
Fortunately we can simply overwrite our generated script code to make it more dynamic. Unfortunately, it is much harder to get the application configuration injected into these low level migration scripts, so we will use environment variables instead. Update your AddUrlDefault.cs, AddUrlDefault.Designer.cs, and ApplicationDbContextModelSnapshot.cs files to include code similar to this:
var urlBase = Environment.GetEnvironmentVariable("APPLICATION_URL_SERVICE"); <br><br>b.Property<string>("Url")<br> .ValueGeneratedOnAdd()<br> .HasColumnName("url")<br> .HasDefaultValueSql("'" + urlBase + "' || currval('\"Ducks_id_seq\"')");<br>
Note: The code above will be slightly different for each file, but the key concept (getting the urlBase and updating the default value) will be the same.
Rebuild your project, run dotnet ef database update, and check checkout the results. You should see that the `DefaultValue` for the url column has been updated and if you insert a new record, the url will be fully formatted for your needs!
This nice thing about this is that you can expand this pattern to other fields as needed in the future. It is structured enough that you aren't doing too much custom coding of your architecture, and updating environment variables is much easier in .Net Core these days.
Hope this helps!