Commented by: @cincuranet

set autoddl on is not a DDL statement. It's something that ISQL recognizes and handles. So another nail to the coffin. Injecting transaction handling is not a great idea either, because it's not going to work for generated script.

Executing in autonomous transaction is probably the only way to go. It is something I'm handling in EF6 (https://github.com/cincuranet/FirebirdSql.Data.FirebirdClient/blob/master/Provider/src/EntityFramework.Firebird/FbMigrationSqlGenerator.cs#L293), but that also means that the TIL has to be lowered from i.e. Serializable to i.e. Read Committed. And that's not nice and smooth either.

Commented by: Giacomo Policicchio (pgiacomo)

Yes it would be an unnecessary mess, sorry for your wasted time, and thank you for explanation. I gave also a glance at efcore sources, and I don't see any good solution to overcome FB behavior. It's really better to split data seeding from structure migration.

Thank You

Commented by: @cincuranet

It's not wasted time. An open discussion about a problem is always good.

The problem is really two pieces. 1) overcoming (or not) Firebird limitations and 2) keeping expected behavior across EF Core providers. Obviously for 1) you can say that we want EF Core to just work and don't bother with "internal" details. But also with 2) the behavior (i.e. doing the changes in two TXs - no matter what way) might be confusing. I don't have clear answer which path is better.

Commented by: Giacomo Policicchio (pgiacomo)

I think EF core is tailored around MSSQL, so, it is not easy to keep a behavior decently consistent across providers. For doing so, EF should have been designed intersecting features of major providers, so loosing a lot of specific features. At the end, it is on developer shoulder to mantain a consistent behavior of an application if interchangeable provider is required, and perhaps it is better this way..
However, I'm using Pomelo.EntityFrameworkCore.MySql on a project and I don't like what when a migration fails, because all action performed until the exception aren't rolled back, I don't worry since I don't use it so many times, but I prefer the all-or-nothing approach. So, maybe EfCore for Firebird shoud remain as it is, regarding this feature.

Commented by: @cincuranet

> At the end, it is on developer shoulder to mantain a consistent behavior of an application if interchangeable provider is required, and perhaps it is better this way..

I agree. But also on the other hand sometimes hiding some implementation/behavior details is handy.

I'm using google translator I was reading the comments, we have the same problems, but we got around it with a migration to structure and another to data, as there are operations that even by isql are not allowed in the same transaction, honestly, I prefer the Firebird peovedor's approach, the all or nothing, because if one part fails, the database does not contain just one part of the migration