Exception The value of 'X' is unknown when attempting to save changes

Question

There are these two entities:

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public CompanyVehicle CompanyVehicle { get; set; }
}

and

public class CompanyVehicle
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Employee Employee { get; set; }
}

Using Entity Framework Core 5.0.8 on SQL Server 2019, the configuration for CompanyVehicle is:

entityBuilder.HasOne(t => t.Employee)
    .WithOne(t => t.CompanyVehicle)
    .HasForeignKey<Employee>(t => t.Id)
    .IsRequired();

And we'll try to insert something:

public void Create(Employee employee)
{
    employee.CompanyVehicle = new CompanyVehicle();
    dbContext.Add<Employee>(employee);
    dbContext.SaveChanges();
}

The above code used to work fine in EF6. Two new records in both Employee and CompanyVehicle tables were created with the same Id. After migrating to EF Core 5.0.8, dbContext.SaveChanges() throws an exception:

System.InvalidOperationException: 'The value of 'Employee.Id' 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.'

Note that these entities are just examples and the database design should not be altered in my case.

Update
After some more investigation, I've found out my problem is:
Having X (principal) and Y (dependent) as two tables where X.Id is PK for X and Y.Id is PK for Y and also FK to X, in EF Core a record of X cannot be inserted.

Gap

For some mysterious reason, in my case the problem appears with Entity Framework when I start the primary key auto-incremented at 0. I also have a field that is both primary and foreign key. This is not the first time I have this problem and I solved it by starting my index at 1. IDENTITY(1,1) I don't know why... – Annunciata

@A.Morel Try to disable all the EF conventions at startup before the migration and see if it works with increment from 0. – Gap

I used database first but thank you for the tip. – Annunciata

Answer 1

So I finally found the problem, configuring a Property to be both PK and FK is possible and very easy. We had our old codes after migrating to EFCore from EF6 in an assembly. The project is a framework so in OnModelCreating we use modelBuilder.ApplyConfigurationsFromAssembly in our base DbContext to register configurations in the guest projects. The project will automatically find all the configurations in all of assemblies referenced by the project or DLLs in the application path.
The key point is: In EF Core explicit fluent FK configuration is in the reverse order compared to EF6. So in EF6 for Employee we used to write:

this.HasRequired(t => t.CompanyVehicle)
    .WithRequiredDependent(t => t.Employee)
    .HasForeignKey(d => d.Id);

and in EF Core we should write:

b.HasOne(t => t.CompanyVehicle)
   .WithOne(t => t.Employee)
   .HasForeignKey<Employee>(t => t.Id).IsRequired();

The parameter d used in the first part is of type CompanyVehicle. So our migrator converted the old code to:

b.HasOne(t => t.CompanyVehicle)
   .WithOne(t => t.Employee)
   .HasForeignKey<CompanyVehicle>(t => t.Id).IsRequired();

Which is incorrect. The generic parameter should be the dependent table type. We later fixed the issue in a new namespace but the ApplyConfigurationsFromAssembly method kept applying the obsolete code after our configuration too.
I used the following block of code at the end of OnModelCreating to investigate the issue:

foreach (var entity in modelBuilder.Model.GetEntityTypes()) 
    foreach(var key in entity.GetForeignKeys())
    {
        //Check what is in the key...
    }

and noticed that there are duplicated keys configured for my entities.

Gap

can you have a look this issue #72854446 – Yapok

Hi, how could we achieve the same solution with using only data annotations (without using fluentapi). Anyone knows? Thx. – Termitarium

@Termitarium Yes it is possible, but it is not as configurable as fluent api. I suggest using fluent api. You can find more information about annotations in https://mcmap.net/q/751507/-create-foreign-key-using-data-annotations – Gap

Answer 2

Entity Framework Core configures one to one relationships by being able to detect the foreign key property, and thereby identify which is the principal and which is the dependent entity in the relationship.

First look at the existing database and check what is the dependant table, assuming it is the Employee, it should have a foriegn key to CompanyVehicle table. (It could be other way around in your case.)

1. Using EF Core convestions.

If Employee is the depentant table, add that exact foriegn key property name (let's assume it's Vehicle_Id) to your Employee entity. Follow 2nd method if you don't want to add a property to the class.

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Vehicle_Id { get; set; } // <-- This right here.
    public CompanyVehicle CompanyVehicle { get; set; }
}

Without this property, as I mentioned earlier, child/dependent side could not be determined for the one-to-one relationship. (check what is yours in the db and add that property, otherwise you will get two foreign keys in the Employee table)

And using fluent API, configure the relation like this. (Notice how a and b were used to separate two navigation properties, in your implementation you have used t, for both, and when you say .HasForeignKey<Employee>(t => t.Id), you're setting the foriegn key to primary key Id of Employee table, which could be the reason behind your error).

protected override void OnModelCreating(ModelBuilder modelBuilder)
{ 
    modelBuilder.Entity<CompanyVehicle>()
        .HasOne(a => a.Employee)
        .WithOne(b => b.CompanyVehicle)
        .HasForeignKey<Employee>(b => b.Vehicle_Id);
}

2. Not using EF Core conventions.

If you do not like to add a property to the dependant table, use the exsisting foriegn key in the database (let's assume it's Vehicle_Id), fluent API config should look like this.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{ 
    modelBuilder.Entity<CompanyVehicle>()
        .HasOne(a => a.Employee)
        .WithOne(b => b.CompanyVehicle)
        .HasForeignKey<Employee>("Vehicle_Id");
}

Edit:

The Has/With pattern is used to close the loop and fully define a relationship. In this case, since the relationship to be configured is a one-to-one, the HasOne method is chained with the WithOne method. Then the dependent entity (Employee) is identified by passing it in as a type parameter to the HasForeignKey method, which takes a lambda specifying which property in the dependent type is the foreign key.

So if you want the Employee Id to act as the foriegn key to the CompanyVehicle table, ammend your Fluent API as this, again notice a and b when specifying lambdas.

modelBuilder.Entity<CompanyVehicle>()
        .HasOne(a => a.Employee)
        .WithOne(b => b.CompanyVehicle)
        .HasForeignKey<Employee>(b => b.Id);

Mccarthy

The database design should not be altered, so I go with your 2nd option, but there is no Vehicle_Id column in the db or entity. Like I said, this exact approach was implemented using EF6 and worked as expected. Assuming that Employee is the dependent table, there is a FK_dbo.Employee_dbo.Vehicle_Id key (not column) in Employee table. In fact, I need Employee.Id to be both PK and FK. – Gap

@Gap In that case change last line to .HasForeignKey<Employee>(b => b.Id);, make sure to use a,b different parameters as mentioned above. Edited the answer as well. – Mccarthy

This is misinformation. There is no difference between using a, b or just ts as they're in different contexts. (Though I was mislead and even gave it a try, surly didn't work.) I think we should investigate the exception occurring on insert. – Gap

Answer 3

I had the same issue that A. Morel had.

When manually inserting a custom join table for ManyToMany, and a foreign key was 0, I was getting this error.

Fixed by changing the seed value of the parent table to start at 2:

DBCC CHECKIDENT ('program_contact', RESEED, 1);

Because of this issue

DBCC CHECKIDENT Sets Identity to 0

Incriminate

Answer 4

For me this seems to be caused by creating "blank" entities that I don't add to the context. In EF6 these were ignored since they were not added, but in EF Core they seem to be added automatically.

I corrected the issue by reducing the scope of my "writable" context down to just the single line where a change was made, and used a separate "read only" context for everything else.

I could further correct this by not using entity types directly in my view so that I can make blank entries that are not entities.

Tody