Efcore: Clearing a nullable foreign key field throws exception

[tonnyvp]

I have a simple database model with two objects. The first (Foo) contains an optional reference to the second (Bar). An exception is thrown if I try these updates:

1. Generate a new Foo without a Bar
2. Set Foo's Bar to some Bar
3. Clear Foo's Bar again

The last step produces a foreign key violation, as EF Core tries to insert a large negative value (-2147482647) instead of null. At first guess, this is a temporary value generated when first creating a Foo without a Bar.

Steps to reproduce

Model

``` C#
public class Foo
{
public int Id { get; set; }
public Bar Bar { get; set; }
}

public class Bar
{
public int Id { get; set; }
}

#### DbContext
``` C#
public class Context : DbContext
{
    public virtual DbSet<Foo> Foos { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql(@"Server=localhost;Database=test;UserId=test;Password=test;");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Foo>(e =>
        {
            e.HasKey(x => x.Id);
            e.Property(x => x.Id).ValueGeneratedOnAdd();

            e.ToTable("Foos");
        });

        modelBuilder.Entity<Bar>(e =>
        {
            e.HasKey(x => x.Id);
            e.Property(x => x.Id).ValueGeneratedOnAdd();

            e.ToTable("Bars");
        });
    }
}

Migration generated

``` C#
migrationBuilder.CreateTable(
name: "Bars",
columns: table => new
{
Id = table.Column(nullable: false)
.Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn)
},
constraints: table =>
{
table.PrimaryKey("PK_Bars", x => x.Id);
});

migrationBuilder.CreateTable(
name: "Foos",
columns: table => new
{
Id = table.Column(nullable: false)
.Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
BarId = table.Column(nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Foos", x => x.Id);
table.ForeignKey(
name: "FK_Foos_Bars_BarId",
column: x => x.BarId,
principalTable: "Bars",
principalColumn: "Id",
onDelete: ReferentialAction.Restrict);
});

migrationBuilder.CreateIndex(
name: "IX_Foos_BarId",
table: "Foos",
column: "BarId");

#### Program logic
``` C#
var context = new Context();
var foo = new Foo();

context.Foos.Add(foo);
await context.SaveChangesAsync();   //Foo added with empty foreign key

foo.Bar = new Bar();
await context.SaveChangesAsync();   //Foreign key set

foo.Bar = null;
await context.SaveChangesAsync();   //Foreign key cleared, exception thrown

Exception thrown

Unhandled exception. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
 ---> Npgsql.PostgresException (0x80004005): 23503: insert or update on table "Foos" violates foreign key constraint "FK_Foos_Bars_BarId"
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.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.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
  Exception data:
    Severity: ERROR
    SqlState: 23503
    MessageText: insert or update on table "Foos" violates foreign key constraint "FK_Foos_Bars_BarId"
    Detail: Key (BarId)=(-2147482647) is not present in table "Bars".
    SchemaName: public
    TableName: Foos
    ConstraintName: FK_Foos_Bars_BarId
    File: d:\pginstaller_12.auto\postgres.windows-x64\src\backend\utils\adt\ri_triggers.c
    Line: 2474
    Routine: ri_ReportViolation
   --- End of inner exception stack trace ---

Further technical details

EF Core version: 3.0.1
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL
Target framework: .NET Core 3.0
Operating system: Windows 10
IDE: JetBrains Rider 2019.2.3

[ajcvickers]

Note for triage: I am able to reproduce this using 3.1.

[sludemann]

I'm running into this as well after upgrading a project from 2.2 to 3.1, any hope for a work around to keep moving while we wait on a fix?

[sludemann]

I've identified what appears to be a work around, after clearing the association property normally I've added a line like this to explicitly set the tracked Id:
context.Entry(foo).Property(x => x.BarId).CurrentValue = null

[DanielHillebrandSWMS]

@ajcvickers Will this kind of bug be fixed in 3.1 also? I would expect so because it's an LTS version.

[ajcvickers]

@DanielHillebrandSWMS The primary focus of the LTS release is stability, so we are quite conservative about taking changes. However, multiple customers reporting the same bug does raise the severity of the issue, so we will discuss this.

[BobbyCannon]

I am also having issue with 3.1, problem did not exist is 2.2. We should be able to set optional relationship to null without exception.