EF Core owned entity shadow PK causes null constraint violation with SQLite

Question

I have a Comment owned entity type:

public class Comment {    // owned entity type
  public Comment(string text) { Text = text; }
  public string Text { get; private set; }
}

public class Post {
  public Post(string content) { Content = content; }
  public long Id { get; private set; }
  public string Content { get; private set; }
  public ICollection<Comment> Comments { get; private set; } = new HashSet<Comment>();
}

And Post's configuration includes:

builder.OwnsMany(x => x.Comments, x => {
  x.Property(y => y.Text).IsRequired();
});

The seeding code includes this:

var post = new Post("content");
post.Comments.Add(new Comment("comment1"));
post.Comments.Add(new Comment("comment2"));
await _context.AddAsync(post);
await _context.SaveChangesAsync();

When I use the postgres provider, I can successfully create, seed and edit the database.

When I use the sqlite provider, I can successfully create the database, but when I try to seed it I get this error:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
---> Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'NOT NULL constraint failed: Comment.Id'.

The docs say that the owned table has an implicit key, which explains the complaint about Comment.Id.

But why does this happen only for sqlite, and how do I fix it?

Answer 1

It's caused by a combination of (1) improper (IMHO) EF Core default and (2) unsupported SQLite feature.

1. As explained in Collections of owned types EF Core documentation

Owned types need a primary key. If there are no good candidates properties on the .NET type, EF Core can try to create one. However, when owned types are defined through a collection, it isn't enough to just create a shadow property to act as both the foreign key into the owner and the primary key of the owned instance, as we do for OwnsOne: there can be multiple owned type instances for each owner, and hence the key of the owner isn't enough to provide a unique identity for each owned instance.

The problem is that in case you don't define explicit PK, then EF Core generates shadow property (column) called Id, type int, autoincrement (they think, however see (2)) and defines composite PK on (OwnerId, Id)

2. However, SQLite supports autoincrement column only if it is the single PK column. Thus, it generates regular INT column Id, which then requires explicit value on INSERT, but EF Core does not send it since it still thinks the property is auto-generated on server.

With that being said, you'd better always define the PK of owned collection entity. Since the autoincrement is unique by itself, the absolute minimum would be to just mark the auto generated shadow Id property as PK, e.g.

builder.Entity<Post>.OwnsMany(e => e.Comments, cb => {
    cb.HasKey("Id"); // <-- add this
    // The rest...
    cb.Property(e => e.Text).IsRequired();
});

The generated migration should have "Sqlite:Autoincrement" annotation for Id column:

Id = table.Column<long>(type: "INTEGER", nullable: false)
    .Annotation("Sqlite:Autoincrement", true),

which was missing and causing the problem in the OP design.

I would personally prefer if EF Core throws the regular no key defined error instead of defining PK construct not supported by all databases. Also SQLite provider to throw exception instead of silently ignoring the auto-increment model request, thus introducing difference between model metadata (which is used by EF Core infrastructure to control all runtime behaviors). So both could technically be considered bugs. But they are what they are. Prefer convention over configuration in general, but be explicit for things with arbitrary defaults.