Get ID for new record from existing sequence in EF Core and npgsql

I scaffolded an existing database into the project and adjusted the entity properties to the best of my knowledge.

Now when I try to save a new record I get the error that the ID column is not allowed to be null, because it's the primary key.

I use a PostgreSQL Database my model builder looks like this:

modelBuilder.ForNpgsqlUseIdentityColumns();
modelBuilder.HasSequence<int>("contact_id_seq");
modelBuilder.Entity<Contact>(entity =>

entity.HasKey(e => e.Id);

entity.ToTable("contact");

entity.Property(e => e.Id)
.HasColumnName("id")
.HasDefaultValueSql("nextval('contact_id_seq')")
.ValueGeneratedOnAdd();

[...]




The model:

public partial class Contact
[DisplayName("DBID")]
public int Id get; set;

[...]



The Controller call:

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Create([Bind("<model member>")] Contact contact)
(ModelState.IsValid)
_context.Add(contact);
await _context.SaveChangesAsync();
return RedirectToAction(nameof(Index));

return View(contact);



The sequence inside the postgreSQL database looks like this:

-- Sequence: contact_id_seq

-- DROP SEQUENCE contact_id_seq;

CREATE SEQUENCE contact_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 12758
CACHE 1;
ALTER TABLE contact_id_seq
OWNER TO postgres;


The error:

Npgsql.PostgresException (0x80004005): 23502: null value in column "id" violates not-null constraint
at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---


Update:
I also tried select nextval('contact_id_seq') and additional model annotations like [Key] or [DatabaseGenerated(DatabaseGeneratedOption.Identity)] to no avail. I am running out of solutions, maybe I should just query the nextval with a sqlcommand...

select nextval('contact_id_seq')

[Key]

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]

Update2:
As a workaround I am now doing a raw sql query for the sequence value:

using (DbCommand cmd = _context.Database.GetDbConnection().CreateCommand())

cmd.CommandText = "select nextval('contact_id_seq')";
_context.Database.OpenConnection();
DbDataReader reader = cmd.ExecuteReader();
reader.Read();
newID = (int)reader.GetValue(0);
_context.Database.CloseConnection();



I used the information from here: https://www.npgsql.org/efcore/value-generation.html

I tried to google the problem with appropriate keywords, but aside from the linked one I couldn't find one that used my constellation.

Microsoft.EntityFrameworkCore.Tools 2.1.0

Npgsql.EntityFrameworkCore.PostgreSQL 2.1.0

PostgreSQL 9.6.10

Contact.Id

0

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.