How to set the right AttachDbFilename relative path in ASP.NET Core?

Question

Working in VS2015 on a web project based on ASP.NET Core (former ASP.NET 5), .NET Core CLR RC1, EF Core (former EF 7), EF Migrations enabled, LocalDb v11.0.

I manually (through SQL commands) created a database and placed MDF/LDF files in a project subdirectory, a situation similar to:

MySolution\src\MyProject\MyLocalData\
  - MyLocalDb.mdf
  - MyLocalDb_log.ldf

This is the value of "ConnectionString" key set in appsettings.json (or at least, one of the many I tried):

"Data Source=(LocalDb)\\v11.0;AttachDbFilename=.\\MyLocalData\\MyLocalDb.mdf;Integrated Security=True"

The initial migration has been created correctly, now I'm stuck at dnx ef database update command (see official tutorial), which gives this error:

Error Number:15350,State:1,Class:14 An attempt to attach an auto-named database for file .\MyLocalData\MyLocalDb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I'm pretty sure there's no other DB with that name, having checked both in my user home directory for files, and in Sql Server Management Studio for databases in LocalDb instance. As a matter of fact, if I switch to an absolute filepath in AttachDbFilename, the migration moves further (and finds other errors related to column properties set through EF fluent interface, but that's another story).

So it looks to me this is all a matter of finding the right relative path to use in AttachDbFilename. I searched here on SO for related topics, but could not find any answer. I also tried changing the relative path imagining that the current folder was wwwroot, or the artifacts folder, but with no luck.

Does any one know how to correctly set this? TA

Answer 1

I do now have this working and it was hard work. The key to this is the environment information "ContentRootPath" which in your example will return the path for MySolution\src\MyProject

My test app is the "Database First" tutorial following https://docs.efproject.net/en/latest/platforms/aspnetcore/existing-db.html

with changes including this one to suit my situation of teaching web app programming and needing self contained apps that I and students can run on each other's machines for discussion, marking etc.

In appsettings.json

    {
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;AttachDBFilename=%CONTENTROOTPATH%\\App_Data\\blogging.mdf;Trusted_Connection=true;MultipleActiveResultSets=true"
  }

With the distinctive part being:

AttachDBFilename=%CONTENTROOTPATH%\\App_Data\\blogging.mdf

OK I am using the traditional name "App_Data" but it is more securely under the ContentRootPath rather than under "wwwroot".

Then in Startup.cs

public class Startup
{
    //20160718 JPC enable portable dev database
    private string _contentRootPath = "";

    public Startup(IHostingEnvironment env)
    {
        //20160718 JPC enable portable dev database
        _contentRootPath = env.ContentRootPath;
    ...
    }

    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {
        //20160718 JPC enable portable dev database
        string conn = Configuration.GetConnectionString("DefaultConnection");
        if(conn.Contains("%CONTENTROOTPATH%"))
        {
            conn = conn.Replace("%CONTENTROOTPATH%", _contentRootPath);
        }
        ...
     }

In the above "..." represents the standard code generated by Visual Studio 2015.

NOTE that when we "Publish" an app like this, we need to manually copy and paste custom folders and files, eg my "App_Data" folder, into the published version. OR we can add the custom folder name, in this case "App_Data", to the file "project.json".

It is also good to know that for any class including controller classes, we can add a constructor method with parameter env and the hosting environment will feed us useful information including ContentRootPath. Useful for custom file storage eg providing file upload for our users.

public class HomeController : Controller
{
    //20160719 JPC access hosting environment via controller constructors
    private IHostingEnvironment _env;

    public HomeController(IHostingEnvironment env)
    {
        _env = env;
    }

    public IActionResult Index()
    {
        string contentRootPath = _env.ContentRootPath;
        return View();
    }

OK this is only to demo the principle as in I add a breakpoint on "return View()" then hover the mouse over contentRootPath to make the point.

ASP.NET Core MVC6 looks like one of the bigger learning and teaching challenges I have run into. Good luck with it for all of us. I have found one nice advance: In MVC5 we had some drama getting our custom data and the identity AspNetUser tables to live together nicely in one database. Looks like it is working out as a more neat and tidy proposition in MVC6.

Answer 2

I found an easier way around

1. run your app and create the DB in the default location

2. Open Microsoft Sql Server Management Studio (or you prefer IDE) and create a new connection to point to (localdb)\mssqllocaldb

3. Script Database as CREATE

4. Change the path in the FILENAME

5. Remove the DB you created on step 1 (choose close existing connections)

6. Run the script

Your application should work without changing anything in the config