Document problems/solutions for using owned collections with SQLite where partially generated composite keys are not supported

[lonix1]

File a bug

Include your code

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();      // throws here

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 an exception. See below.

Include stack traces

ERR | Microsoft.EntityFrameworkCore.Database.Command | Failed executing DbCommand (1ms) [Parameters=[@p6='?', @p7='?' (Size = 4)], CommandType='Text', CommandTimeout='30']
INSERT INTO "Comment" ("PostId", "Text")
VALUES (@p6, @p7);
SELECT "Id"
FROM "Comment"
WHERE changes() = 1 AND "rowid" = last_insert_rowid() AND "PostId" = @p6;
ERR | Microsoft.EntityFrameworkCore.Update | An exception occurred in the database while saving changes for context type 'MyContext'.
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'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
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'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

Include verbose output

No applicable info.

Include provider and version information

EF Core version: 5.0.11
Database provider: Microsoft.EntityFrameworkCore.Sqlite version 5.0.11
Target framework: .NET 5.0
Operating system: linux
IDE: vscode 1.61.0

Extras

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

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

[lonix1]

See corresponding StackOverflow issue where this problem was correctly diagnosed.

It was explained there that this behavior is surprising because it works differently on different databases, and a more sensible exception should be thrown to alert the developer to the true underlying problem. What are your thoughts?

[ajcvickers]

dotnet/efcore#11162 covers adding a warning for this to the generated migration.

Moving this to docs to better document the issue with relation to owned collections on SQLite.

[AndriySvyryd]

We can also change the convention to generate a simple PK for this case