EF Core, nullable primary key results into inability to delete not-yet-added entity

Question

I'm using EF Core 2.2.4.

This is my entity:

public class Person
{
    public int? Id { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
}

Notice that Person has a nullable-type primary key. This seems to be handled gracefully by EF Core and at the end entities get their Id; I used the term 'nullable-type' PK instead of 'nullable PK' because the intention is NOT to insert record with null PK, that is generated by the database, but to handle correctly the situation of entities for which the key has not yet been generated.
And as I said, generation seems to work fine. Not everything works nicely though.

AppDbContext dbc = new AppDbContext();
Person p1 = new Person() { Name = "Isaac", Surname = "Newton" };
dbc.Add(p1);
dbc.Remove(p1);
dbc.SaveChanges();

If I run this code, the line dbc.Remove(p1) doesn't work properly in fact I get this error:

System.ArgumentNullException: 'Value cannot be null. Parameter name: key'

If I change the Id from int? to int, the above code works.

It seems that in case of nullable-type keys the temporary (negative) Id doesn't get assigned to the Id.

Nullable-type keys have some advantages, mainly not showing the temporary Id that seems to be generated lazily, in the Save phase.

I couldn't find in the documentation if this is a supported feature and what I'm seeing is a bug or rather this is simply an unsupported feature and I should avoid nullable-type primary keys tout-court.

Answer 1

You should not use an int? as your Id as it will be null when you want to remove it and EFCore just won't be able to find the right value and throw this exception as the real Id is generated by the database after dbc.SaveChanges();. I suggest using a normal int as EFCore gives your Entry a temporary Id which will be overwritten by your database.

There are also databases where EFCore generates the key and not the database itself where I guess EFCore thinks that a nullable value doesn't need to be generated or is generated after dbc.SaveChanges().

Now why does the remove scream in agony because of null in the primary key?! Simple answer: In SQL NULL = NULL equals false and EFCore simply throws an error at you because if it asks for Is Null it might delete Entities you don't want deleted.

You can find more Information there...

Answer 2

Person in your example is DTO. Apparently, Id is used as Primary Key (PK) in DB. By definition, PK in DB is not null-able. So you have a mismatch between DTO and DB constraint on PK (not null). Unless your db allows otherwise, which is due to error not the case.

It works when you fetching data, code direction (null-able can hold value, yes). Alternatively delete command don't get past PK value constraint set in database (when value is null). Thus the error being propagated.

Highlighting that DTO does not match it's data origin.

You can, however, have a model, mapped to DTO (e.g. using AutoMapper). And that model can have null-able id, should you need that in your business logic you're trying to implement. Null handling advised.

That way DB operation are to be performed using DTO (where Id is set to non-null, i.e. int in your case). And Business logic operations are to be performed using Model (where Id can be null-able, i.e. int?).

That gives you advantage of null-able id, but plays nice with db at the same time.