ExecuteSqlCommand fails in Entity Framework when the command text has a DDL and DML instruction

Question

In C#, using NpgSQL to connect on a Postgres database, I'm trying to execute an unique command that have a DDL and DML instructions, however that command not work.

Sample:

var m_comandoSQL = @"DROP TABLE IF EXISTS public.test_55;
CREATE TABLE public.test_55 ( ds_nome VARCHAR(50) );
SELECT * FROM public.test_55;";

var m_modelo = new Modelo(); // Modelo is a dbContext
m_modelo.Database.ExecuteSqlCommand(m_comandoSQL);

When the ExecuteSqlCommand is called, the following exception is raised

Npgsql.NpgsqlException (0x80004005): 42P01: relation "public.test_55" does not exist

em Npgsql.NpgsqlConnector.DoReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage, Boolean isPrependedMessage)

em Npgsql.NpgsqlConnector.ReadSingleMessage(DataRowLoadingMode dataRowLoadingMode, Boolean returnNullForAsyncMessage)

em Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior)

em Npgsql.NpgsqlCommand.ExecuteNonQueryInternal()

em System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func3 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed)

em System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)

em System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)

em System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass59.b__57()

em rei_cadastros.InitializeTeste.Main(String[] args)

If I execute the commands separately, no errors happen.

Answer 1

You can try this:

var m_comandoSQL = @"SET client_min_messages = error;
DROP TABLE IF EXISTS public.test_55;
CREATE TABLE public.test_55 ( ds_nome VARCHAR(50) );
SELECT * FROM public.test_55;";

var m_modelo = new Modelo(); // Modelo is a dbContext
m_modelo.Database.ExecuteSqlCommand(m_comandoSQL);

The reason of error probably is a postgres notice generated by drop table statement when table does not exists.

IF EXISTS

Do not throw an error if the table does not exist. A notice is issued in this case.