TIL: Configure Default Values for Nullable Columns With Default Constraints in EntityFramework Core

#csharp #tutorial #todayilearned #dotnet

I originally posted this post on my blog a long time ago in a galaxy far, far away.

TL;DR: For nullable columns with default constraints, you have to tell EntityFramework the default value of the mapping property via C#. Otherwise, when you create a new record, it will have NULL instead of the default value in the database. You're welcome! Bye!

Let's create a dummy table with one nullable column but with a default constraint

Let's create a new Movies database with one table called Movies, with an Id, a name, and a director name as optional, but with a default value. Like this,

CREATE DATABASE Movies;
GO
USE Movies;
GO
CREATE TABLE Movies (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    DirectorName NVARCHAR(100) DEFAULT 'ThisIsADefaultValue'
);
GO

Nothing fancy!

Let's create a new record (without the nullable column) and read it back

Now to prove a point, let's use EntityFramework Core to insert a new movie without passing a director name and read it back.

What will be the value of DirectorName once we read it back? Null? The value inside the default constraint? Make your bets!

Here we go,

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace TestProject1;

[TestClass]
public class EntityFrameworkAndDefaults
{
    [TestMethod]
    public void TestInsertAndReadMovie()
    {
        const string connectionString = $"Server=(localdb)\\MSSQLLocalDB;Database=Movies;Trusted_Connection=True;";

        var dbContextOptions = new DbContextOptionsBuilder<MoviesContext>()
                .UseSqlServer(connectionString)
                .Options;

        using (var context = new MoviesContext(dbContextOptions))
        {
            var inception = new Movie
            {
                Name = "Inception"
                // No director name here...
                // 👆👆👆
            };
            context.Movies.Add(inception);
            context.SaveChanges();
        }

        using (var context = new MoviesContext(dbContextOptions))
        {
            var movie = context.Movies.FirstOrDefault(m => m.Name == "Inception");

            Assert.IsNotNull(movie);
            Assert.AreEqual("ThisIsADefaultValue", movie.DirectorName);
            //     👆👆👆
            //     Assert.AreEqual failed. Expected:<ThisIsADefaultValue>. Actual:<(null)>. 
            //
            // Whaaaaat!
        }
    }
}

public class MoviesContext : DbContext
{
    public DbSet<Movie> Movies { get; set; }

    public MoviesContext(DbContextOptions<MoviesContext> options)
        : base(options)
    {
    }
}

public class Movie
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public string? DirectorName { get; set; }
}

Ok, to my surprise that test fails. movie.DirectorName isn't "ThisIsADefaultValue". It's null.

I was expecting to see the value from the default constraint in the database. But, no. Wah, wah, wah, Wahhhhhhh...

Here you have it EntityFramework. Can I get my default value now?

We have to tell EntityFramework Core the default value of our column, like this,

public class MoviesContext : DbContext
{
    public DbSet<Movie> Movies { get; set; }

    public MoviesContext(DbContextOptions<MoviesContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // For simplicity, let keep it here...
        // If you're a purist, yes, this should be in a separate class.
        // But, hey this is just a tutorial, and I'm lazy
        modelBuilder.Entity<Movie>(entity =>
        {
            entity
                .Property(e => e.DirectorName)
                .HasDefaultValueSql("ThisIsADefaultValue");
                // 👆👆👆
                // Here you have it EntityFramework Core
                // Can I get my default value now?
        });
    }
}

This behavior only adds up to my love and hate relationship with EntityFramework Core.

Et voilà!

Join my email list and get a short, 2-minute email with 4 curated links about programming and software engineering delivered to your inbox every Friday.