Ef6: DbMigrator should avoid connecting to master and should use existing connection if possible

[divega]

The current implementation causes issues, e.g. when using ADD authentication with Azure SQL DB.

From @ajcvickers's email:

... it seems that there is an issue with the database existence check that is done by the DbMigrator—it is attempting to connect to the master database, which fails. We had updated other existence checks to not require this, but it looks like DbMigrator was either missed or there is some reason it couldn’t be done here...

Also, there is apparently a code path there that may leave a connection open.

[GraemeSMiller]

Is there any work around for this? Everything is marked private or internal etc. I hoped to just override update in a certain part of my code and avoid call to EnsureDatabaseExists but without taking huge amount of EF code this doesn't seem possible

[gvasko]

Hi,
we have been experimenting with a workaround which seems working so far, but not heavily tested.

1. we created an AzureSqlClientFactory class that extends DbProviderFactory
2. it just wraps the SqlClientFactory, everything is delegated to it

3. except the CreateConnection, in which we add the token

4. created an AzureDbResolver class that implements IDbProviderFactoryResolver

5. returns AzureSqlClientFactory instance if the connection is SqlConnection

6. returns EntityProviderFactory.Instance if the connection is EntityConnection

7. in the ctor of the DbConfiguration subclass

8. SetProviderFactoryResolver(new AzureDbResolver());

9. SetMigrationSqlGenerator("My.AzureSqlClient", () => new SqlServerMigrationSqlGenerator());

10. app config

    <system.data>
        <DbProviderFactories>
            <add name="My Azure Provider Wrapper" invariant="My.AzureSqlClient" support="FF" description="Wrapper for Azure"
                type="My.AzureSqlClientFactory, MyAssembly"/>
        </DbProviderFactories>
    </system.data>
    <entityFramework>
        <defaultConnectionFactory type="My.AzureSqlClientFactory, MyAssembly" />
        <providers>
            <provider invariantName="My.AzureSqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>
        </providers>
    </entityFramework>

(We use a separated executable for database migration with its own app config, so this workaround doesn't affect the web app.)

Do you think this can help?

[ajcvickers]

Additional note: this is problematic when an existing connection has an access token associated. We cant then clone the connection.