Entity Framework Core code first default values for PostgreSQL

Question

So far I read the docs, tutorials google brought up and other SO questions but it seems I miss something and I don't get it (to work).

I try to implement a really tiny PostgreSQL database for a .NET Core 2.1 web api (microservice). I am used to the database first approach but for this service I decided to give the code first approach a try.

I also decided to use the fluent api of the ModelBuilder to keep the classes clean from attributes and define most of the structure in the DbContext.OnModelCreating method. Andy maybe one time find a solution how to keep the DbContext clean from Postgres specific elements and move them to the migrations...

My problem is that the requirements define a lot of default values and I can't get them working as they should behave.

For example I have the table Entity1 which just has the 3 columns:

• int Id (auto incrementing id)
• bool? IsEnabled (which should get the default value true)
• DateTime? LastStatusChange (timestamp without timezonewhich should get set on create or update to default value CURRENT_TIMESTAMP)

The idea behind the default value for the timestamp is to have the database being the single instance creating timestamps and using the db default values as configuration for all running instances. Eg. when requirements change to "the default value should now be false" we just change the db default values in the existing databases and update the migration for newer installments.

My modelBuilder code currently is:

  modelBuilder.Entity<Entity1>(entity =>
  {
    entity.HasKey(e => e.Id);

    entity.Property(e => e.Id)
      .ValueGeneratedOnAdd();

    entity.Property(e => e.IsEnabled)
      .HasDefaultValue(true)
      .ValueGeneratedOnAddOrUpdate();

    entity.Property(e => e.LastStatusChange)
      .HasColumnType("timestamp without time zone")
      .HasDefaultValueSql("CURRENT_TIMESTAMP")
      .ValueGeneratedOnAddOrUpdate();
  });

Which works for new created values.

When toggling or resetting the fields i use

entity.LastStatusChange = null;

and or

entity.IsEnabled = null;

I assumed setting them to null would trigger the creation of a default value but this does not affect the LastStatusChange field (the value stays the same) and sets IsEnabled to null.

Anyway to get db default values on update via entity framework core?

Answer 1

As the EF Core docs mention, HasDefaultValue() and HasDefaultValueSql() only specify the value that gets set when a new row is inserted. Setting a column to null is a completely different thing (after all, null is a valid value for those columns). You may be looking for computed columns, which is a different feature.

Unfortunately, as the Npgsql docs mention, computed columns aren't supported by PostgreSQL at this time. It's possible to set this up using PostgreSQL database triggers, but this isn't managed by EF Core so you'd have to use SQL in your migrations to set this up.

Answer 2

Solution for CreateDate in PostgreSQL:

builder.Property(e => e.CreationDate)
  .HasColumnType("timestamp without time zone")
  .HasDefaultValueSql("NOW()")
  .ValueGeneratedOnAdd();

Unfortunately there is not solution for update event.

Answer 3

I was able to solve the DateUpdated problem using nuget Triggered as neither [DatabaseGenerated(DatabaseGeneratedOption.Computed)] nor HasComputedColumnSql("timezone('utc', now()) nor ValueGeneratedOnAddOrUpdate() were working for postgres.

The cool thing is that it works even for base class properties, all relevant entities are deriving from it

public abstract class Auditable
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public DateTime DateCreated { get; private set; }

    public DateTime DateUpdated { get; set; }
}

public class User : Auditable
{
    [Key]
    public int Id { get; set; }
    ...
}

Here is how I did it:

public class SetDateUpdated : IBeforeSaveTrigger<Auditable>
{
    public Task BeforeSave(ITriggerContext<Auditable> context, CancellationToken cancellationToken)
    {
        if (context.ChangeType == ChangeType.Modified)
        {
            context.Entity.DateUpdated = DateTime.UtcNow;
        }

        return Task.CompletedTask;
    }
}

In Program.cs register for DI:

builder.Services.AddTransient(typeof(SetDateUpdated));

And finally:

builder.Services.AddDbContext<DatabaseContext>
(
    options =>
    {
        options.UseNpgsql(builder.Configuration.GetConnectionString("DBConnection"));
        options.UseTriggers(triggerOptions =>
        {
            triggerOptions.AddTrigger<SetDateUpdated>();
        });

    }, ServiceLifetime.Transient, ServiceLifetime.Transient
);