Efcore: DATABASE OPTIONS not working for complex DB NAMES

[knom]

I'm using the new DATABASE CREATE OPTIONS in EFCore 3.1. #2969
With complex DB Names (containing special characters) this fails.

Steps to reproduce

Have an Azure SQL Database containing dashes..
(e.g. foo-test-1)

C# protected override void OnModelCreating(ModelBuilder builder) { builder.HasDatabaseMaxSize("1 GB"); builder.HasServiceTier("Basic"); }

Connection id "0HLRV00FJ4NQC", Request id "0HLRV00FJ4NQC:00000001": An unhandled exception was thrown by the application.
Microsoft.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '-'.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName) at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.CreateTables()
at Microsoft.EntityFrameworkCore.Storage.RelationalDatabaseCreator.EnsureCreated()
at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureCreated()
at ...

Further technical details

EF Core version: 3.1.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.0
Operating system: Windows
IDE: VSCode

[ajcvickers]

Removing from the milestone to consider for patch.

[ajcvickers]

@bricelam Some questions on this regarding patching:

• Is there a workaround?
• What if @db_name already contains [ ]? Or can this not happen?
• Is it possible to add any meaningful automated tests here? (Given that we can't create a new database each test run because of SQL Server stability.)

[ajcvickers]

Notes from triage:

• Workarounds are
  
  
  
  • Create the database outside of EF
  
  
  • Don't use special characters in the name
  
  
• @db_name is safe to add [ ] around
• Not possible to automate tests

[ajcvickers]

This change was rejected as a servicing fix for 3.1 since it is not a regression (the current behavior has existed for multiple releases) and databases should most commonly not be created directly by EF Core.

[knom]

@ajcvickers can we get this some time?

"databases should most commonly not be created directly by EF Core" isn't consistent with having EnsureCreated() methods in EFCore!

[ajcvickers]

@knom EnsureCreated is designed and documented for use in testing and prototyping. It is not recommended for production databases.

[roji]

@knom just in case it isn't clear, this was indeed fixed for 5.0 - just not backported for patching 3.1. 5.0 will be out very soon.

[knom]

@ajcvickers oh, okay.. thanks for pointing that out ;-)

De-railing the thread slightly.. what is the recommended option instead?
EF Migrations?

[ErikEJ]

@knom Migrations or reverse engineering.

[roji]

@knom in many case the idea is for the production database to be created externally (i.e. not by the application via EF Core), and then it's fine to use EF Core to create the schema in that database. This aligns well with how cloud databases work, for example - you'd use the web portal or a REST API to do the actual creation, and then you have a blank database you can start working with. But even in on-premise, many times the DBA will provision/create the database for you, provide auth details, and then you create the schema and populate data.