1

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.

Liam
  • 27,717
  • 28
  • 128
  • 190
AgostinoX
  • 7,477
  • 20
  • 77
  • 137
  • 2
    why are you adding then removing? – Daniel A. White Jul 30 '20 at 13:50
  • What's wrong with comparing against `0` instead of `null`? – CodeCaster Jul 30 '20 at 13:50
  • 1
    If you don't want to persist changes, just don't call `SaveChanges`. Make sure you use the DbContext in a `using` block too - it's meant to be short-lived, used only for a unit-of-work. If you don't call `SaveChanges()` all modifications will be discarded when the DbContext gets disposed – Panagiotis Kanavos Jul 30 '20 at 13:52
  • All of this is by design, and you don't want an int? Id in code unless your database also allow null (and as you write, it doesn't) – Thomas Koelle Jul 30 '20 at 13:55
  • 1
    BTW Id *shouldn't* be nullable. The PK of a table simply can't be null. If you want to use an auto-generated ID, just leave its default value, 0. EF Core (and all ORMs for that matter) will generate an INSERT when the ID is 0, UPDATE if it's non-zero, unless the entity is marked as new instead of modified – Panagiotis Kanavos Jul 30 '20 at 13:56
  • Probably depends on the **database engine** used - for one, **SQL Server** does **NOT** allow nullable columns to be the (or part of the) primary key – marc_s Jul 30 '20 at 13:59
  • In general null seems to represent better the case of a not-yet-inserted value. In my WPF data entry, 0 is not as nice as null and I have to handle it; not a big problem but if null was handled i would go with it. – AgostinoX Jul 30 '20 at 14:02
  • @marc_s, As I said, i don't wont a nullable primary key. But the entity is NOT the database table, and it holds entities *before* theyr associated record is added to the database table. So null represents those cases. – AgostinoX Jul 30 '20 at 14:04

2 Answers2

3

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...

Farizio
  • 51
  • 7
1

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.

d.stack
  • 429
  • 3
  • 5