Entity Framework Core 2 - save empty string as null in database

Question

I'm using Entity Framework Core 2 in my application. I have a lot of nullable string columns in my database.

The problem is that I want to save empty strings as NULL in the database.

In old versions of EF, I used a IDbCommandInterceptor for implementing an interceptor, but in EF Core 2, I don't know how to write one ?

Answer 1

There is a new IDbCommandInterceptor interface that should be able to handle this, but it looks complicated.

A simple approach is be to write a function to remove empty strings, and then call it before data is saved within your DbContext class.

public void RemoveEmptyStrings()
{
    // Look for changes
    this.ChangeTracker.DetectChanges();

    // Loop through each entity
    foreach (var entity in this.ChangeTracker.Entries())
    {
        // Use reflection to find editable string properties
        var properties = from p in entity.Entity.GetType().GetProperties()
            where p.PropertyType == typeof(string)
                  && p.CanRead
                  && p.CanWrite
            select p;

        // Loop through each property and replace empty strings with null
        foreach (var property in properties)
        {
            if (string.IsNullOrWhiteSpace(property.GetValue(entity.Entity, null) as string))
                property.SetValue(entity.Entity, null, null);
       }
    }
}

Remember to override each version of SaveChanges(), SaveChanges(bool), SaveChangesAsync() in your DbContext class.

public override int SaveChanges()
{
    // Replace empty strings with null
    this.RemoveEmptyStrings();

    // Continue with base functionality
    return base.SaveChanges();
}