{"action":"created","issue":{"url":"https://api.github.com/repos/npgsql/npgsql/issues/540","labels_url":"https://api.github.com/repos/npgsql/npgsql/issues/540/labels{/name}","comments_url":"https://api.github.com/repos/npgsql/npgsql/issues/540/comments","events_url":"https://api.github.com/repos/npgsql/npgsql/issues/540/events","html_url":"https://github.com/npgsql/npgsql/issues/540","id":62561023,"number":540,"title":"Cannot find newly created temporary table","user":{"login":"andrewwwooster","id":10394379,"avatar_url":"https://avatars.githubusercontent.com/u/10394379?v=3","gravatar_id":"","url":"https://api.github.com/users/andrewwwooster","html_url":"https://github.com/andrewwwooster","followers_url":"https://api.github.com/users/andrewwwooster/followers","following_url":"https://api.github.com/users/andrewwwooster/following{/other_user}","gists_url":"https://api.github.com/users/andrewwwooster/gists{/gist_id}","starred_url":"https://api.github.com/users/andrewwwooster/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/andrewwwooster/subscriptions","organizations_url":"https://api.github.com/users/andrewwwooster/orgs","repos_url":"https://api.github.com/users/andrewwwooster/repos","events_url":"https://api.github.com/users/andrewwwooster/events{/privacy}","received_events_url":"https://api.github.com/users/andrewwwooster/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-03-18T01:47:14Z","updated_at":"2015-03-18T09:36:35Z","closed_at":null,"body":"I use Npgsql  ExecuteSqlCommand to create a temporary table named 'tmp' (ExecuteSqlCommand returns -1).  But when I immediately try to insert into the temporary table I get the following error\r\n  relation \"tmp\" does not exist\r\nThis seems to indicate that the temporary table was not created.\r\n\r\nWhen I execute the create temp table and insert using psql client all works fine.\r\n\r\n        class MyDbContext : DbContext\r\n        {\r\n            public MyDbContext() :base(\"MinervaDatabase\")\r\n            {\r\n                Database.SetInitializer<MyDbContext>(null);\r\n            }\r\n        }\r\n\r\n        [TestMethod]\r\n        public void TempTableTest ()\r\n        {\r\n            using (var dbContext = new MyDbContext())\r\n            {\r\n                dbContext.Database.ExecuteSqlCommand(\r\n                    \"create local temporary table tmp ( f1 VARCHAR(12) )\");  // This returns -1\r\n                dbContext.Database.ExecuteSqlCommand(\r\n                    \"insert into tmp values ('a')\");\r\n            }\r\n        }\r\n\r\nResult Message:\t\r\nTest method Minerva.Tests.Models.DrugIncidenceAnalysisTest.TempTableTest threw exception: \r\nNpgsql.NpgsqlException: ERROR: 42P01: relation \"tmp\" does not exist\r\nResult StackTrace:\t\r\nat Npgsql.NpgsqlState.<ProcessBackendResponses>d__0.MoveNext()\r\n   at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)\r\n   at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()\r\n   at Npgsql.ForwardsOnlyDataReader.NextResultInternal()\r\n   at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean preparedStatement, NpgsqlRowDescription rowDescription)\r\n   at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)\r\n   at Npgsql.NpgsqlCommand.ExecuteNonQuery()\r\n   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)\r\n   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)\r\n   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)\r\n   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()\r\n   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass59.<ExecuteStoreCommand>b__58()\r\n   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)\r\n   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass59.<ExecuteStoreCommand>b__57()\r\n   at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)\r\n   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreCommand(TransactionalBehavior transactionalBehavior, String commandText, Object[] parameters)\r\n   at System.Data.Entity.Internal.InternalContext.ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, String sql, Object[] parameters)\r\n   at System.Data.Entity.Database.ExecuteSqlCommand(TransactionalBehavior transactionalBehavior, String sql, Object[] parameters)\r\n   at System.Data.Entity.Database.ExecuteSqlCommand(String sql, Object[] parameters)\r\n   at Minerva.Tests.Models.DrugIncidenceAnalysisTest.TempTableTest() in c:\\Users\\Andrew\\Source\\Repos\\Minerva\\Minerva.Tests\\Models\\DrugIncidenceAnalysisTest.cs:line 53\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/npgsql/npgsql/issues/comments/82851658","html_url":"https://github.com/npgsql/npgsql/issues/540#issuecomment-82851658","issue_url":"https://api.github.com/repos/npgsql/npgsql/issues/540","id":82851658,"user":{"login":"Emill","id":1660778,"avatar_url":"https://avatars.githubusercontent.com/u/1660778?v=3","gravatar_id":"","url":"https://api.github.com/users/Emill","html_url":"https://github.com/Emill","followers_url":"https://api.github.com/users/Emill/followers","following_url":"https://api.github.com/users/Emill/following{/other_user}","gists_url":"https://api.github.com/users/Emill/gists{/gist_id}","starred_url":"https://api.github.com/users/Emill/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Emill/subscriptions","organizations_url":"https://api.github.com/users/Emill/orgs","repos_url":"https://api.github.com/users/Emill/repos","events_url":"https://api.github.com/users/Emill/events{/privacy}","received_events_url":"https://api.github.com/users/Emill/received_events","type":"User","site_admin":false},"created_at":"2015-03-18T09:36:35Z","updated_at":"2015-03-18T09:36:35Z","body":"Hi. Why are you using entity framework to run raw sql queries? Anyway,\nEntity framework opens and closes the connection for each query that you\nrun, so that is why your temporary table gets destroyed. To keep the\nconnection open, check out\nhttps://msdn.microsoft.com/en-us/library/bb738582.aspx\nDen 18 mar 2015 02:47 skrev \"andrewwwooster\" <notifications@github.com>:\n\n> I use Npgsql ExecuteSqlCommand to create a temporary table named 'tmp'\n> (ExecuteSqlCommand returns -1). But when I immediately try to insert into\n> the temporary table I get the following error\n> relation \"tmp\" does not exist\n> This seems to indicate that the temporary table was not created.\n>\n> When I execute the create temp table and insert using psql client all\n> works fine.\n>\n>     class MyDbContext : DbContext\n>     {\n>         public MyDbContext() :base(\"MinervaDatabase\")\n>         {\n>             Database.SetInitializer<MyDbContext>(null);\n>         }\n>     }\n>\n>     [TestMethod]\n>     public void TempTableTest ()\n>     {\n>         using (var dbContext = new MyDbContext())\n>         {\n>             dbContext.Database.ExecuteSqlCommand(\n>                 \"create local temporary table tmp ( f1 VARCHAR(12) )\");  // This returns -1\n>             dbContext.Database.ExecuteSqlCommand(\n>                 \"insert into tmp values ('a')\");\n>         }\n>     }\n>\n> Result Message:\n> Test method Minerva.Tests.Models.DrugIncidenceAnalysisTest.TempTableTest\n> threw exception:\n> Npgsql.NpgsqlException: ERROR: 42P01: relation \"tmp\" does not exist\n> Result StackTrace:\n>\n> at Npgsql.NpgsqlState.d__0.MoveNext()\n> at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup)\n> at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription()\n> at Npgsql.ForwardsOnlyDataReader.NextResultInternal()\n> at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable1 dataEnumeration,\n> CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock\n> threadBlock, Boolean preparedStatement, NpgsqlRowDescription rowDescription)\n> at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)\n> at Npgsql.NpgsqlCommand.ExecuteNonQuery()\n> at\n> System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand\n> t, DbCommandInterceptionContext1 c)\n> at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget\n> target, Func3 operation, TInterceptionContext interceptionContext, Action3\n> executing, Action3 executed)\n> at\n> System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand\n> command, DbCommandInterceptionContext interceptionContext)\n> at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()\n> at\n> System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass59.b__58()\n> at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransactionT\n> at\n> System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass59.b__57()\n> at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute\n> TResult\n> at\n> System.Data.Entity.Core.Objects.ObjectContext.ExecuteStoreCommand(TransactionalBehavior\n> transactionalBehavior, String commandText, Object[] parameters)\n> at\n> System.Data.Entity.Internal.InternalContext.ExecuteSqlCommand(TransactionalBehavior\n> transactionalBehavior, String sql, Object[] parameters)\n> at System.Data.Entity.Database.ExecuteSqlCommand(TransactionalBehavior\n> transactionalBehavior, String sql, Object[] parameters)\n> at System.Data.Entity.Database.ExecuteSqlCommand(String sql, Object[]\n> parameters)\n> at Minerva.Tests.Models.DrugIncidenceAnalysisTest.TempTableTest() in\n> c:\\Users\\Andrew\\Source\\Repos\\Minerva\\Minerva.Tests\\Models\\DrugIncidenceAnalysisTest.cs:line\n> 53\n>\n> —\n> Reply to this email directly or view it on GitHub\n> <https://github.com/npgsql/npgsql/issues/540>.\n>\n"}}