Insert postgres row using npgsql

Question

In my postgres database, the keys for my tables are serial data types that the postgres database autoincrements. Using C#, Npgsql, Entity Framework 6, WPF, linq and EF dbsets (any extension methods?), is there a way to implement a repository method such as:

 public virtual void Add(TEntity entity, string addrow)
 {NpgsqlCommand cmd = new NpgsqlCommand(addrow, DbConnect);
 cmd.ExecuteNonQuery();}

The viewmodel would pass in the string argument something like:

 _addRow = string.Format("insert into mytable ('descrip') values ( " + DescripProperty + ") returning 'mytable_id' into mytableID");

Is this the right way to insert a postgres row / record that has an autoincrement key? Or do I need to utilize a PL/pgSQL - SQL Procedural Language function to first lock the table and do the insert from that server side? Thank you in advance.

EDIT --------------------------- For clarification, I pass the EF DbContext into the genericrepository constructor. This code is in genericrepository class:

    private readonly DbSet<TEntity> _aquery;
 public DbSet<TEntity> AQuery;

 // CTOR - inject dbcontext/entities 
 public GenericRepository(MyContextClass context) 
 {
     if (context == null)
         throw new ArgumentNullException("context");
     _theDbContext = context;
  _aquery = _theDbContext.Set<TEntity>();
    AQuery = _aquery;
 }

So I can do AQuery.AsQueryable, etc.

score 0 · Accepted Answer · answered Jun 29 '16 at 06:57

0

Your SQL query is correct in the sense that the serial key will get autogenerated by PostgreSQL and then return the new ID - there's no need for pl/pgsql or any explicit locking - PostgreSQL's serial mechanism is atomic and will work. However, several comments on your code:

You're passing Entity Framework entirely and doing things with raw SQL. The whole point of an ORM is to generate this kind of statement for you so you don't have to, and EF6 is perfectly capable of doing so.
Concatenating a parameter into your SQL query is a bad idea and opens the door to SQL injection. You should use a parameter instead.
You don't seem to be actually using the returned ID, so there's little use for the returning clause (although you may have omitted the relevant code)

answered Jun 29 '16 at 06:57

Shay Rojansky

15,357
2
40
69

Shay, are you saying I can just use EF's dbset.add(TEntity) method and in the TEntity object I just leave the mytable_id column / field value null & the PostgreSQL will provide the mytable_id value with its serial autoincrement mechanism? Thank you. – harpagornis Jun 29 '16 at 16:07
OK, I tried doing dbset.add and passed in an object that I created by the statement: MyTableModel newrec = new(MyTableModel). In MyTableModel, I had defined mytable_id as type int. When newrec was created, the new() method by default assigned a value of 0. After that, I executed dbset.submit. Then I ran a psql query & the id for the new record was now 7. So I guess PostgreSql changed the mytable_id from 0 to 7 with its autoincrement mechanism. Thank you. – harpagornis Jun 29 '16 at 18:03
Correct typo: It was dbset.SaveChanges(), not Submit(). – harpagornis Jun 29 '16 at 22:31
Yes, that's right. On the PostgreSQL side, auto-increment columns (serial) are simply regular columns with a default value from a sequence; if you insert a row without specifying a value, the default is used. On the EF side, if you leave the property as default (sometimes called a sentinel value), EF will automatically understand that the property is "unset", will not send it to the database and thereby allow PostgreSQL to generate it. – Shay Rojansky Jun 29 '16 at 23:36

Insert postgres row using npgsql

1 Answers1