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

1

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

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

c#
postgresql
npgsql
ef-core-2.1
asked on Stack Overflow Sep 10, 2018 by Splitframe • edited Sep 12, 2018 by Splitframe

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0