EF Core requires id-property to be explicitly set although it is database generated in "table splitting" scenario

Question

Using EF Core in .NET Core 3.1 I am trying to use table splitting where contents from two objects are stored in same SQL database table:

public class Store {
    [Column(Order = 0), Key]
    public long StoreId { get; set; }
    public bool IsActive { get; set; }
    public string StreetName { get; set; }
    public int StreetNumber { get; set; }

    public List<BusinessHours> BusinessHours { get; set; }
}

public class BusinessHours {
    public long StoreId { get; set; }
    public Store Store { get; set; }

    public DayOfWeek Day { get; set; }
    public DateTime StartTime { get; set; }
    public DateTime EndTime { get; set; }
}

OnModelCreating is defined as following:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Store>()
        .ToTable("Stores")
        .HasMany<BusinessHours>()
        .WithOne(o => o.Store).HasForeignKey(a => a.StoreId);

    modelBuilder.Entity<BusinessHours>()
        .ToTable("Stores").HasKey(k => k.StoreId);
}

Unfortunately, when I try to seed sample data to database using:

var teststore = new Store {
    Name = "Chipotle Southwest",
    IsActive = true,
    StreetName = "Random Street NE",
    StreetNumber = 20,
    BusinessHours = new List<BusinessHours>()
    {
        new OpenHours { Day = DayOfWeek.Monday, StartTime = "08", EndTime = "17" },
        new OpenHours { Day = DayOfWeek.Tuesday, StartTime = "08", EndTime = "17" },
        new OpenHours { Day = DayOfWeek.Wednesday, StartTime = "08", EndTime = "17" },
        new OpenHours { Day = DayOfWeek.Thursday, StartTime = "08", EndTime = "17" },
        new OpenHours { Day = DayOfWeek.Friday, StartTime = "08", EndTime = "17" },
        new OpenHours { Day = DayOfWeek.Saturday, StartTime = "10", EndTime = "15" },
        new OpenHours { Day = DayOfWeek.Sunday, StartTime = "11", EndTime = "13" }
    }
};

_context.Stores.Add(teststore);
_context.SaveChanges();

I get following error: System.InvalidOperationException: The property 'StoreId' on entity type 'BusinessHours' has a temporary value while attempting to change the entity's state to 'Unchanged'. Either set a permanent value explicitly or ensure that the database is configured to generate values for this property.

I understand the reason for it but unfortunately this is (and should) be a database generated value. What would be the proper way of resolving this issue?

akurasu akurasu · Accepted Answer · 2020-06-30T12:43:52

From EF Core documentation:

Depending on the database provider being used, values may be generated client side by EF or in the database. If the value is generated by the database, then EF may assign a temporary value when you add the entity to the context. This temporary value will then be replaced by the database generated value during SaveChanges.

You could also try with this Fluent Api configuration:

modelBuilder.Entity<BusinessHours>()
            .Property(f => f.Id)
            .ValueGeneratedOnAdd();

EF Core requires id-property to be explicitly set although it is database generated in "table splitting" scenario

1 Answers