Error when specifying AttachDbFileName in connection string

[XiaoweiJia]

Edit: look at #2810 (comment) for details on the bug.

When specifying AttachDbFileName in connection string, the following error appear: Unable to Attach database file as database xxxxxxx.
Please advise.

Thanks

[natemcmaster]

Please provide more details about your issue.

[XiaoweiJia]

I am trying to use migration commands to generate database: "dnx . ef migration apply". I get an error saying the database file cannot be attached as database xxxx but if I remove the "AttachDbFileName" section, it works fine and the database file is created in default location.
Please see my code in ConfigureServices.

public void ConfigureServices(IServiceCollection services)
        {
            var connection = @"Data Source=(LocalDB)\mssqllocaldb;Integrated Security=True;Initial Catalog=SampleDb;AttachDbFileName=D:\SampleDb.mdf";
            services.AddEntityFramework().AddSqlServer().AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(connection));
        }

[natemcmaster]

Thanks for updating. This is not a bug in EF 7. We pass-through the connection string to SqlClient. There may be a problem with your localdb setup.

Feel free to re-open if you identify a problem with how EF 7 handles your connection string.

[bricelam]

Cross-reference: Cannot attach database file when using Entity Framework 7 Migration commands (on StackOverflow)

[axelheer]

I still think this is a bug, or a missing feature compared to EF6. It's quite annoying anyway.

• EnsureDeleted changes the database name to master but keeps any AttachDbFileName value, which leads to an error since we cannot attach the master database to another file.
• EnsureCreated opens a connection using the provided AttachDbFileName value, which leads to an error since the file of the database we want to create does not yet exist.

EF6 seems to have some logic to handle this stuff, see SqlProviderServices.DbCreateDatabase. And working with Local DB requires using AttachDbFileName if we don't want to bloat our user profile (as far as I know). Maybe updating SqlServerConnection.CreateMasterConnection to remove AttachDbFileName and SqlServerDatabaseCreator.IsDoesNotExist to accept the other error would be the first step to resolve this?

I posted a hacky workaround for this at the stackoverflow question linked by @bricelam...

[bricelam]

@axelheer That sounds like a bug. Could you submit a new issue?

[divega]

It seems the original report might have just missed a few details, but it could have been the same issue that @axelheer is describing from the beginning. I will just reactivate for now.

[bricelam]

Beside not stripping AttachDBFileName, we also don't support creating new attached databases. We'll need to produce DDL like the following.

CREATE DATABASE [MyDatabase]
ON (NAME = 'MyDatabase.mdf', FILENAME = 'C:\Path\To\MyDatabase.mdf')
LOG ON (NAME = 'MyDatabase_log.ldf', FILENAME = 'C:\Path\To\MyDatabase_log.ldf');

This also means we'll need to manually resolve the |DataDirectory| substitution string if present in the connection. 😱

[axelheer]

Yeah, makes sense. Thus, we need already three things:

• Remove AttachDBFilename when connecting to master (SQL Server: Remove AttachDBFilename when connecting to master #6435)
• Accept error 1832 within SqlServerDatabaseCreator.Exists or check via dbo.sysdatabases or via db_id(.)
• Add file name to SqlServerCreateDatabaseOperation or the like

I've already looked into your tests, but I've no idea how to write some kind of integration test to play through this whole scenario.

[bricelam]

FYI, all the logic for resolving paths was in SqlProviderServices.GetOrGenerateDatabaseNameAndGetFileNames for EF6.

[axelheer]

I may be able to solve that. Should I dive into it and prepare a PR?

[bricelam]

That would be awesome if you're willing!

[axelheer]

Okay, I'll give it a try then.

[divega]

Triage: if we don't take a PR (cc @axelheer) to have proper support in 1.1, the scope for the EF team would be to try to throw a better error.

[efidiles]

Hey guys,

We just ran into this ourselves; looks like relative paths aren't supported with AttachDbFileName.

System.Data.SqlClient.SqlException (0x80131904): Cannot attach the file '.\OurDbName.mdf' as database 'OurDbName'.

Just wanted to express that here for other people who might be bumping into this. The "fix" is use absolute paths.

[axelheer]

@efidiles You can use the mighty |DataDirectory| placeholder too (see here).