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.

Shay Rojansky Shay Rojansky · Accepted Answer · 2016-06-29T06:57:28

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)

Insert postgres row using npgsql

1 Answers