Query never returns when DB value for column mapped to primitive collection is ''

[sindrekroknes]

Problem

When using EF to query an entity that has a primitive collection property the query never returns if the database value of the column is ''
The database query appears to execute successfully, but nothing more happens. The method never returns.
This is the last thing logged, even with LogLevel set to Trace:

info: 23.01.2024 14:47:32.803 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "s"."Id", "s"."List"
      FROM "SimpleEntities" AS "s"
      LIMIT 1

Encountered this when adding a new column to an existing table. The generated migration sets the default value to ''
Tested with different collection types and primitives, Sqlite/SqlServer, and sync/async. Same result for everything I tested with.

Update: Left the repro running over night, the FirstAsync method has still not returned.

Repro code

await using var connection = new SqliteConnection("DataSource=:memory:");
await connection.OpenAsync();

await using var ctx = new SimpleContext(connection);
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

await ctx.Database.ExecuteSqlRawAsync("INSERT INTO SimpleEntities (List) VALUES ('');");

var entity = await ctx.SimpleEntities.FirstAsync();
Console.WriteLine(entity);

public class SimpleContext(SqliteConnection connection) : DbContext
{
    public DbSet<SimpleEntity> SimpleEntities => Set<SimpleEntity>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlite(connection)
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
    }
}

public record SimpleEntity(int Id, IEnumerable<string>? List);

Include provider and version information

EF Core version: 8.0.1
Database provider: tested with Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Sqlite
Target framework: .NET 8.0)
Operating system: Windows 11
IDE: VS Code

[sindrekroknes]

@ajcvickers Just wondering, should something be done about the migrations generated when adding a new primitive collection?
It currently generates an empty string default value (for non-null columns at least). With the throw fix users have to be aware of this and fix the migration manually.

[ajcvickers]

@sindrekroknes Thanks for pointing this out. We should not be generating the empty string by default for primitive collection columns. /cc @roji @maumar

[ajcvickers]

@sindrekroknes

It currently generates an empty string default value (for non-null columns at least).

I have not been able to reproduce this. I see Stuff = table.Column<string>(type: "TEXT", nullable: false) in the migration. Can you post an entity type and config where this happens, and the provider being used?

[ajcvickers]

@sindrekroknes Got it. It happens when the table already exists and then a primitive collection column is added:

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AddColumn<string>(
                name: "Stuff",
                table: "Joins",
                type: "TEXT",
                nullable: false,
                defaultValue: "");
        }

[sindrekroknes]

@ajcvickers Oh. Yeah, should have made that more clear. It's only when adding new columns to an existing entity

[roji]

Ah yeah, this was actually raised for EFCore.PG: npgsql/efcore.pg#2965, and was fixed for 8.0.0 (the PG case is different, since the database type is actually JSON, and rejects empty strings). Should have thought about the general case in EF.

[roji]

@ajcvickers should we track the migration problem in another issue?

[ajcvickers]

@roji Yeah, I think so.