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?

Strictly

Setting to null should not trigger the default value. How would someone actually put null in the database using this approach? I'm not hatin', just pointing it out. If it worked like you suggest there'd be another "camp" saying "if I set it to null, why isn't null being used ?"............but upvote for the question itself. – Cramfull

For future readers, I was able to get this to work for NEW rows : builder.Property(t => t.MyColumnCreateDate).HasDefaultValueSql("CURRENT_TIMESTAMP"); No ValueGeneratedOnAddOrUpdate was needed, no HasColumnType (it defaulted to 'timestamp without time zone') ("PostgreSQL 12.1, compiled by Visual C++ build 1914, 64-bit") I think you'll have to use a trigger on an UPDATE. This is similar to @GetoX 's answer, but not using proprietary NOW() – Cramfull

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.

Tinct

Note that (stored) computed column support has been added to PostgreSQL 12, and they're also supported by the Npgsql EF Core provider (see the docs). – Tinct

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.

Numerary

For future readers, if you want to use non proprietary function (NOW), you can do this: builder.Property(t => t.MyColumnCreateDate).HasDefaultValueSql("CURRENT_TIMESTAMP"); – Cramfull

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
);

Supination

Please read the question well: "The idea behind the default value for the timestamp is to have the database being the single instance creating timestamps". – Bridgman