Home ExecuteSqlCommand fails in Entity Framework when the command text has a DDL and DML instruction
Reply: 2

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

MedinaMAN
1#
MedinaMAN Published in 2018-02-13 19:02:20Z

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.

Ivan Mogila
2#
Ivan Mogila Reply to 2018-02-13 19:21:46Z

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.
MedinaMAN
3#
MedinaMAN Reply to 2018-02-14 16:22:19Z

As Shay Rojansky mentioned, upgrading the NpgSQL to version 3.2.x solved the problem

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.298116 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO