How to get the Inserted ID in Dotnet Core using Postgresql (Using RAW SQL)

Question

Issue: I can't get the new ID of the inserted entry into my POSTGRES Database on DotNet Core. I am so depressed, i can't find this answer!

Here is the command that I am trying that DOES work when I use a postgres client. I am using RAW SQL commands to run the QUERY.

The SQL command that should work

context.Database.ExecuteSqlCommandAsync($"INSERT INTO pulserelations (pulseid, create_time, modified_time) VALUES ({pulse_id}, now(),now()) RETURNING pulserelationid;");

I have verified the insert was successful. The Return value of this function is always 1;

Additional Info

    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder.UseNpgsql("Host=localhost;Database=localdb;Username=yyyy;Password=xxxx");

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
        }

  public DbQuery<IDInsertQuery> get_id_of_insert_query {get;set;}

I have tried to even RE-QUERY the database after an insert using and that didn't work either

public class IDInsertQuery
{
public int currval;

}

    List<IDInsertQuery> list_id =  await _context.get_id_of_insert_query.FromSql($"select currval from currval(pg_get_serial_sequence('pulserelations', 'pulserelationid'));").ToListAsync();

Maybe you want [`SqlQuery`](https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.database.sqlquery?view=entity-framework-6.2.0#System_Data_Entity_Database_SqlQuery__1_System_String_System_Object___) ? — clcto, Sep 12 '19 at 19:03
Maybe this helps: https://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row — C.Schubert, Sep 12 '19 at 20:05
What library and version are you using? EntityFramework v6.2 has `System.Data.Entity.Database` which has both `ExecuteSqlCommandAsync()` and `SqlQuery()` but EntityFrameworkCore's (v2.2.6) `Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade` does not have either. Maybe I am just looking at the wrong library? — clcto, Sep 12 '19 at 21:06
I am using EntityFrameworkCore 2.2.4 and you are correct. DatabaseFascade does not have either. (This is the problem) — FlyingV, Sep 14 '19 at 04:06
Please Help! I can't code without solving this fairly simple problem — FlyingV, Sep 14 '19 at 04:58

score 0 · Accepted Answer · answered Sep 14 '19 at 05:57

TL&DR: Create an Output Variable to house the 'returning' ID query.

Rationale: The INSERT statement returns an ID which can only be obtained using an OUTPUT paramater. See Below.

The goal is to get an OUTPUT from a 'returning'

  NpgsqlParameter idOut = new NpgsqlParameter
        {
            Direction = System.Data.ParameterDirection.Output
        };
  await _context.Database.ExecuteSqlCommandAsync($"INSERT INTO pulserelations (pulseid, create_time, modified_time) VALUES ({pulse_id}, now(),now()) RETURNING pulserelationid;", idOut);

How to get the Inserted ID in Dotnet Core using Postgresql (Using RAW SQL)

1 Answers1