Return ID of newly inserted row on a PostgreSQL database using C# and Npgsql?

Question

I'm building a WinForms project in C# using a PostgreSQL database and the Npgsql framework.

For inserting a record, I need to return the ID of the new record. This SO question says to add SELECT SCOPE_IDENTITY() to the query string passed to cmd. So my query string looks like this:

string insertString = "INSERT INTO sometable (company_name, category, old_value, old_desc, new_value, new_desc, reference1, reference2) VALUES (@comp, @cat, @oldValue, @oldDesc, @newValue, @newDesc, @ref1, @ref2); SELECT SCOPE_IDENTITY();";

and then get the ID with something like

int modified = cmd.ExecuteNonQuery();

But that's likely SQL Server-specific. If I use that method, I get an exception at the above line saying, "fuction scope_identity() does not exist".

I wasn't able to find anything that seemed to address this on the Npgsql documentation.

Per the linked SO question and Denis' suggestions I've tried adding both

RETURNING id;

and

CURRVAL(pg_get_serial_sequence('my_tbl_name','id_col_name'))

to the query string, replacing SELECT SCOPE_IDENTITY(); with those statements in the code above. In both cases they work as intended in DBeaver on an insert, but in my C# code in my WinForm project, modified was set to "1".

NOTE: I re-titled the question and added more information about what I've done.

Answer 1

Add "returning idcolumn" to the end of the sql query, then run the command with the ExecuteScalar() method instead of ExecuteNonQuery(). It should return with an int.

string insert = "insert into table1 (col1) values (something) returning idcol";
int id = cmd.ExecuteScalar();

Answer 2

All the comments above were almost nearly spot on and got me to a solution but didn't exactly wrap it in a bow -- so I thought i'd post my implementation that works (with silly fake example tables of course).

        private int? InsertNameIntoNamesTable(string name)
        {
            int? id = null;
            
            using (var dbcon = new NpgsqlConnection(_connectionString))
            {
                dbcon.Open();

                StringBuilder sb = new StringBuilder();

                var sql = $@"                    
                    insert into names_table
                        (name)
                    values
                        ({name})
                        returning id;
                ";

                sb.Append(sql);

                using (var cmd = new NpgsqlCommand(sql, dbcon))
                {
                    id = (int)cmd.ExecuteScalar();
                }

                dbcon.Close();
            }

            return id;          
        }