How to correctly configure a composite primary key with HasDefault() for Sqlite?

[Metritutus]

I am attempting to configure a composite primary key for a table, where one of key fields has a default value specified using HasDefault(). This key field with a default value is never intended to be manually set by code, so I wanted to create it as a shadow property.

The configuration code I have written successfully generates migration scripts which run without error. If run SELECT * FROM pragma_table_info("SomethingOfCategoryA"), the columns appear to be configured as I would expect. SomethingOfCategoryB also looks as I would expect (ie identical to SomethingOfCategoryA aside from the default value).

In addition, if I manually run SQL to insert records into either of the two tables, it all works as expected, making use of the default value on the column, etc.

However, if I try to insert a record using EF Core for SomethingOfCategoryA or SomethingOfCategoryB, an InvalidOperationException like the following is thrown:

The value of 'SomethingOfCategoryB.CategoryId' is unknown when attempting to save changes. This is because the property is also part of a foreign key for which the principal entity in the relationship is not known.

I was initially attempting to achieve what I wanted using shadow properties (as seen for the configuration of SomethingOfCategoryA), and was unsure if this error was the result of those efforts. However attempting this using properties in the model itself (as seen for the configuration of SomethingOfCategoryB) has the same result.

I am unsure what I am doing wrong.

In researching this I happened upon this issue, although I am unsure if this is the same problem, and in any case that issue is marked as fixed and the advice there is to open a new issue with a small project that reproduces the issue for investigative purposes.

Stack trace

   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalEntityEntry.PrepareToSave()
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.GetEntriesToSave(Boolean cascadeChanges)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(DbContext _, Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.Storage.NonRetryingExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
   at CompositeKeyDefaultValueTest001.Main.StartAsync() in C:\Users\<REDACTED>\Documents\Visual Studio 2022\Projects\CompositeKeyDefaultValueTest001\CompositeKeyDefaultValueTest001\Main.cs:line 38
   at CompositeKeyDefaultValueTest001.AppHostedService.StartAsync(CancellationToken cancellationToken) in C:\Users\<REDACTED>\Documents\Visual Studio 2022\Projects\CompositeKeyDefaultValueTest001\CompositeKeyDefaultValueTest001\AppHostedService.cs:line 18
   at Microsoft.Extensions.Hosting.Internal.Host.<StartAsync>d__12.MoveNext()

The code

The code for the test project that demonstrates this issue can be found here.

Simply run the project observe the thrown exception.

Provider and version information

EF Core version: 5.0.13
Database provider: Microsoft.EntityFrameworkCore.Sqlite
Target framework: .NET Core 3.1
Operating system: Windows 10 Version 21H2 (OS Build 19044.1415)
IDE: Visual Studio 2022 17.0.2

[ajcvickers]

Note for triage: this appears to be related to the FK property being used in two relationships. It is both part of a composite FK, and also an FK on it's own. This means we don't detect properly that it is marked as generated and hence doesn't need to have an explicit value set.

@Metritutus As a workaround, can you explicitly set the value, rather than relying on it being set by the default constraint?

[Metritutus]

Note for triage: this appears to be related to the FK property being used in two relationships. It is both part of a composite FK, and also an FK on it's own. This means we don't detect properly that it is marked as generated and hence doesn't need to have an explicit value set.

@Metritutus As a workaround, can you explicitly set the value, rather than relying on it being set by the default constraint?

If I explicitly set the value when creating the model instance, eg:

var somethingOfCategoryB = new SomethingOfCategoryB() { CategoryId = 2, SomethingId = newSomething.Id, Name = "Whatever" };

Or if I set it in the model class itself like this:

public int CategoryId { get; set; } = 2;

Then yes, that does work, although I had hoped to avoid this and be able to do this with shadow properties, as my goal in this was to avoid having the property existing in the model, so that it cannot be manually set, read, etc.

Am I correct then that this isn't going to be possible until this issue is resolved? It looks like I may have to make do with a getter-only model property for the time being if that is the case.

I have also tried using HasValueGenerator(), but it look the failure occurs before the value generator is used.

EDIT: The Next() method of the ValueGenerator is called, but the original exception still gets thrown regardless.

EDIT2: Looks like I can achieve what I want by overriding SaveChanges()! It's a little awkward (I'd be interested to know if there's an alternative way of doing this that is specific for an entity), and I'm aware I'll need to override SaveChangesAsync() as well, but it should hopefully do for now until this is fixed, assuming it's something that will be fixed(?)

The workaround:

        public override int SaveChanges()
        {
            foreach (EntityEntry entityEntry in ChangeTracker.Entries().Where(ee => ee.State == EntityState.Added))
            {
                if (entityEntry.Entity is SomethingOfCategoryA)
                {
                    entityEntry.CurrentValues["CategoryId"] = Models.Categories.A;
                }
            }

            return base.SaveChanges();
        }

[ajcvickers]

@Metritutus That workaround looks good to me.