Entity Framework SQLite Treats 0 As NULL For Non-Nullable fields

[s8moahme]

I am facing a problem with Entity Framework SqLite where 0 is interpreted as NULL. Here is the code to reproduce the issue:

var TestContext = new CoreTestContext();
TestContext.MasterDbDataContext.Database.EnsureCreated();
TestContext.GenerateData();
Assert.Single(TestContext.MasterDbDataContext.Addresses.Where(a => a.Status == Status.Error));

public class CoreTestContext : IDisposable
{
    private readonly DataTestContext _dataTestContext;
    public CoreTestContext()
    {
        _dataTestContext = new DataTestContext();
    }

    public MasterDbDataContext MasterDbDataContext => _dataTestContext.MasterDbDataContext;

    public void Dispose()
    {
        _dataTestContext.Dispose();
    }

    public void GenerateData()
    {
        var address = new Address
        {
            City = "City",
            Street = "Street",
            Status = Status.Error,
            //HouseNumber = 1
        };
        MasterDbDataContext.Addresses.Add(address);
        MasterDbDataContext.SaveChanges();
    }
}

public class DataTestContext : IDisposable
{
    public MasterDbDataContext MasterDbDataContext { get; private set; }

    public DataTestContext()
    {
        MasterDbDataContext = CreateDbContext();
    }

    public void Dispose()
    {
        // Nothing to dispose yet...
    }

    private static MasterDbDataContext CreateDbContext()
    {
        SQLitePCL.Batteries.Init();
        var connectionString = $"DataSource=:memory:";
        var keepAliveConnection = new SqliteConnection(connectionString);
        keepAliveConnection.Open();
        var contextOptions = new DbContextOptionsBuilder<MasterDbDataContext>()
            .UseSqlite(keepAliveConnection)
            .Options;

        return new MasterDbDataContext(contextOptions);
    }
}

public class MasterDbDataContext : DbContext
{

    public const string SchemaName = "schema";

    public DbSet<Address>? Addresses { get; set; }

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

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new AddressConfiguration());
    }

    public override int SaveChanges(bool acceptAllChangesOnSuccess)
    {
        return base.SaveChanges(acceptAllChangesOnSuccess);
    }
}

public class AddressConfiguration : IEntityTypeConfiguration<Address>
{
    public void Configure(EntityTypeBuilder<Address> builder)
    {
        builder.ToTable("Addresses", MasterDbDataContext.SchemaName);
        builder.Property(m => m.Status)
            .HasDefaultValue(Status.None);
    }
}

public class Address
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }
    [Required]
    [MaxLength(150)]
    public string Street { get; set; } = string.Empty;
    [Required]
    [MaxLength(15)]
    public int PostalCode { get; set; }
    [Required]
    [MaxLength(150)]
    public string City { get; set; } = string.Empty;
    [Required]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int HouseNumber { get; set; }
    public Status Status { get; set; }
}

public enum Status
{
    [EnumMember(Value = "Error")]
    Error = 0,
    [EnumMember(Value = "None")]
    None = 1,
    [EnumMember(Value = "Registered")]
    Registered = 2,
}

There are 2 problems with this code. First, HouseNumber is not generated by the database. CoreTestContext.GenerateData method throws the following exception:

SqliteException: SQLite Error 19: 'NOT NULL constraint failed: Addresses.HouseNumber'.

If HouseNumber is set to a non-zero value as in the commented out line, the SqliteException does not get thrown and Assert.Single fails with the following exception:

Xunit.Sdk.SingleException: 'The collection was expected to contain a single element, but it was empty.'

Here is what I found after some investigation:

When HouseNumber is set to 0, the same SqliteException is thrown

As mentioned above, when HouseNumber is set to a non-zero value, the SqliteException does not get thrown

When Status.Error value is set to a non-zero value, the assertion succeeds

When Status.Error value is set to 0, address.Status value is changed to Status.None once MasterDbDataContext.SaveChanges is executed in CoreTestContext.GenerateData

When Address.Status is changed to nullable, it does not change after MasterDbDataContext.SaveChanges is executed

This tells me that entity framework treats 0 as NULL (or unassigned value) when the field is non-nullable.

For example, address.Status value is set to 0 (Status.Error), which is treated as if the value was not assigned and therefore, the value is overwritten to Status.None as expected from the default value specified in the Address class.

Also, Address.HouseNumber is seen as NULL when it is assigned a 0 value as seen in the SqliteException

Can someone please explain to me why entity framework is behaving that way with SQLite? My understanding is that SQLite differentiates between 0 and NULL, but this is clearly not happening here.

[roji]

Can you please submit a minimal, runnable repro? The above is notably missing your model (how is HouseNumber configured) and the data.

[s8moahme]

@roji I am not sure what you mean. HouseNumber is configured in Address class using attributes

[ajcvickers]

@s8moahme HouseNumber is configured has a generated property with [DatabaseGenerated(DatabaseGeneratedOption.Identity)]. For generated properties configured in this way, EF will leave it up to the database to generate a value unless a non-default value is set. However there is no way to have two generated "Identity" columns in the same table when using SQLite, and hence SQLite doesn't generate a value, and instead throws.

@bricelam I know we have discussed this before, but shouldn't we warn for this kind of schema?

[bricelam]

Related issues: #14532 & #20475

[ajcvickers]

Note from triage: attempt to warn in this case that there is no mechanism in the database to generate the default value.

[andrerav]

I came across this problem today on a context scaffolded from a SQLite database using dotnet ef scaffold. The SQL definition for the columns is as follows:

    [...]
    direction_vector_x REAL NOT NULL,
    direction_vector_y REAL NOT NULL,
    direction_vector_z REAL NOT NULL,
    [...]

To my surprise, dotnet ef scaffold appears to have looked at the data and assigned default values from the single row of data that existed in this table (which had the values 0, 0 and -1 respectively for X, Y and Z). Here's an excerpt from the generated data context:

entity.Property(e => e.DirectionVectorX)
    .HasDefaultValueSql("0.0")
    .HasColumnName("direction_vector_x");

entity.Property(e => e.DirectionVectorY)
    .HasDefaultValueSql("0.0")
    .HasColumnName("direction_vector_y");

entity.Property(e => e.DirectionVectorZ)
    .HasDefaultValueSql("-1.0")
    .HasColumnName("direction_vector_z");

Now, if in my application I try to create a new entity instance, assign 0 to e.DirectionVectorX, and attempt to insert this, EF appears to either set this column to null or omit it (I haven't checked the actual SQL). Regardless, the result is a NOT NULL constraint failed error. If I set the value to a different value from whatever is configured in HasDefaultValueSql(), the error message goes away.

Removing the HasDefaultValueSql() statements from the context fixes the problem and I can assign values freely.