JSON Columns produce NULL value for empty collections

[cschulzsuper]

I played around with the new JSON Column mapping for SQL Server and found an issue when the owned property is a collection.

If I add a new object, an empty collection will produce a NULL in the database. I expected that it would produce an empty JSON array. (see CreateJsonColumnWithNullValue from the example)

If I add a new object with at least one element in the collection, save the changes in the database, and subsequently remove the element and save the changed object again, an empty JSON array is stored in the database. (see CreateJsonColumnWithEmptyArray from the example)

using Microsoft.EntityFrameworkCore;

CreateJsonColumnWithNullValue();
CreateJsonColumnOneElement();
CreateJsonColumnWithEmptyArray();

void CreateJsonColumnWithNullValue() {
    var context = new TodoContext();

    context.Database.EnsureCreated();

    var todoList = new TodoList
    {
        Title = "List1",
    };

    context.Add(todoList);
    context.SaveChanges();

    context.Dispose();
}

void CreateJsonColumnOneElement() {

    var context = new TodoContext();

    context.Database.EnsureCreated();

    var todoList = new TodoList
    {
        Title = "List2",
        Items = new List<TodoItem>{
        new TodoItem { Text = "Item" }
    }
    };

    context.Add(todoList);
    context.SaveChanges();

    context.Dispose();
}

void CreateJsonColumnWithEmptyArray() {

    var context = new TodoContext();

    context.Database.EnsureCreated();

    var todoList = new TodoList
    {
        Title = "List3",
        Items = new List<TodoItem>{
        new TodoItem { Text = "Item" }
    }
    };

    context.Add(todoList);
    context.SaveChanges();

    todoList.Items.Clear();
    context.SaveChanges();

    context.Dispose();
}


public class TodoList
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public IList<TodoItem> Items { get; set; } = new List<TodoItem>();
}

[Owned]
public class TodoItem
{
    public required string Text { get; set; }
}

public class TodoContext : DbContext
{
    public DbSet<TodoList> Todos { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);

        optionsBuilder.UseSqlServer("SUPER_SECRET_CONNECTION_STRING");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<TodoList>()
            .OwnsMany(c => c.Items)
            .ToJson();
    }
}

Include provider and version information

EF Core version: 7.0.0-rtm.22512.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0 RC2
IDE: Visual Studio 2022 17.4.0 Preview 3.0

[cschulzsuper]

The whole issue becomes problematic, when I read from the database. The collection might have a null value which the caller might not expect.

[dradovic]

I've just bumped my head against this too. In my opinion the issue even starts one step earlier. Looking at the declaration of @cschulzsuper 's TodoList, Items seems to be a non-nullable property (assuming an enabled NRT-context). It would be great if EF would create a NVARCHAR(MAX) JSON column that is NOT NULL in the first place and always contains at least []. Trying to persist a TodoList whose Items is null should result in a relational SQL exception.

@ajcvickers what's your take on that? And is there any chance that the reported NULL-for-an-empty-collection bug will be fixed in EF8?

[ajcvickers]

@dradovic We will discuss. /cc @maumar