c# - DataAdapter throws error while filling DataTable -

i trying fill datatable using npgsqldataadapter. have prepared command

string commandstring=@" drop  table if exists tempdata;   create temp table tempdata  select x x x x (_query_); select x+x, xx, x-y newtemptable tempdata; 

and using below function fill data in datatable

public datatable searchpg(string commandstring, npgsqlparameter[] param) {     datatable resulttable = new datatable();     try     {          openconnection();          dbcommandpg.commandtext = commandstring;         dbcommandpg.connection = databaseconnectionpg;         dbcommandpg.parameters.clear();         if (param != null)         {              dbcommandpg.parameters.addrange(param);         }         adappg.selectcommand = dbcommandpg;         resulttable.clear();         adappg.fill(resulttable);     }     catch (exception ex)     {         file.writeexception(ex.message, null);         throw ex;     }         {         databaseconnectionpg.close();     }     return resulttable; } 

error occurs @ adappg.fill(resulttable);

and error message {"42p01: relation \"tempdata\" not exist"}

i using npgsql version 3.0.2.0 , vs 2013 , postgres 9.3

but when run same query in sql editor in pgadmin, runs finely , return result per desired.

update: query works smoothly npgsql 2.0.1.0 not 3.x

this known issue starting npgsql 3.x, described here: https://github.com/npgsql/npgsql/issues/641.

in nutshell, cannot create entity (e.g. table) , use entity inside same npgsqlcommand - send create table , select in separate commands.

see issue above explanation, has pretty low-level details on how npgsql communicates postgresql. unfortunately it's unlikely we'll fix soon.