[EF 6.4.0] update-database is not generating the correct filepath

[Tzadiko]

Running the update-database command with the -verbose flag, Entity Framework does not properly construct the path to write the .mdf file to.

As a result it throws an SQL exception: System.Data.SqlClient.SqlException (0x80131904): CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Users\TomerNinjaDB.mdf'.

It should be under C:\Users\Tomer\NinjaDB.mdf, or preferably within another nested folder (not sure how to configure this), as such C:\Users\Tomer\DB\NinjaDB.mdf

Here is an example, CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Users\TomerNinjaDB.mdf'.

Steps to reproduce

What steps can we follow to reproduce the issue?

1. Run enable-migrations
2. Run add-migration
3. Run update-database -verbose

NinjaDomain.Classes.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.1</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="EntityFramework" Version="6.4.0" />
  </ItemGroup>

</Project>

Classes.cs

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace NinjaDomain.Classes
{
    public enum EquipmentType 
    {
        Tool = 1,
        Weapon = 2,
        Outwear = 3
    }

    public enum NinjaType 
    {
        Shinobi = 1,
        Kunoichi = 2
    }

    public class Ninja
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public bool ServedInOniwaban { get; set; }
        public Clan Clan { get; set; }
        public int ClanId { get; set; }
        public List<NinjaEquipment> EquipmentOwned { get; set; }
        public System.DateTime DateOfBirth { get; set; }
    }

    public class Clan
    {
        public int Id { get; set; }
        public string ClanName { get; set; }
        public List<Ninja> Ninjas { get; set; }
    }

    public class NinjaEquipment
    {
        public int Id { get; set; }       
        public string Name { get; set; }
        public EquipmentType Type { get; set; } 
        [Required]
        public Ninja Ninja { get; set; }
    }
}

NinjaContext.cs

using NinjaDomain.Classes;
using System.Data.Entity;

namespace NinjaDomain.DataModel
{
    public class NinjaContext : DbContext
    {
        public NinjaContext() : base("NinjaDB")
        {
        }
        public DbSet<Ninja> Ninjas { get; set; }
        public DbSet<Clan> Clans { get; set; }
        public DbSet<NinjaEquipment> Equipment { get; set; }
    }
}

Stack Trace

PM> update-database -verbose
C:\Program Files\dotnet\dotnet.exe exec --depsfile C:\Users\Tomer\Documents\Coding\C#\Entity\Project\src\NinjaDomain.Classes\bin\Debug\netcoreapp3.1\NinjaDomain.Classes.deps.json --additionalprobingpath C:\Users\Tomer\.nuget\packages --runtimeconfig C:\Users\Tomer\Documents\Coding\C#\Entity\Project\src\NinjaDomain.Classes\bin\Debug\netcoreapp3.1\NinjaDomain.Classes.runtimeconfig.json C:\Users\Tomer\.nuget\packages\entityframework\6.4.0\tools\netcoreapp3.0\any\ef6.dll database update --verbose --no-color --prefix-output --assembly C:\Users\Tomer\Documents\Coding\C#\Entity\Project\src\NinjaDomain.Classes\bin\Debug\netcoreapp3.1\NinjaDomain.Classes.dll --project-dir C:\Users\Tomer\Documents\Coding\C#\Entity\Project\src\NinjaDomain.Classes\ --language C# --root-namespace NinjaDomain.Classes
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: 'NinjaDB' (DataSource: (localdb)\mssqllocaldb, Provider: System.Data.SqlClient, Origin: Convention).
System.Data.SqlClient.SqlException (0x80131904): CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Users\TomerNinjaDB.mdf'.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<>c.<NonQuery>b__4_0(DbCommand t, DbCommandInterceptionContext`1 c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.SqlServer.SqlProviderServices.<>c__DisplayClass52_0.<CreateDatabaseFromScript>b__0(DbConnection conn)
   at System.Data.Entity.SqlServer.SqlProviderServices.<>c__DisplayClass60_0.<UsingConnection>b__0()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c__DisplayClass2_0.<Execute>b__0()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation)
   at System.Data.Entity.SqlServer.SqlProviderServices.UsingConnection(DbConnection sqlConnection, Action`1 act)
   at System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action`1 act)
   at System.Data.Entity.SqlServer.SqlProviderServices.CreateDatabaseFromScript(Nullable`1 commandTimeout, DbConnection sqlConnection, String createDatabaseScript)
   at System.Data.Entity.SqlServer.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)
   at System.Data.Entity.Core.Common.DbProviderServices.CreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)
   at System.Data.Entity.Core.Objects.ObjectContext.CreateDatabase()
   at System.Data.Entity.Migrations.Utilities.DatabaseCreator.Create(DbConnection connection)
   at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
   at System.Data.Entity.Infrastructure.Design.Executor.UpdateInternal(String targetMigration, Boolean force, DbConnectionInfo connectionInfo, String migrationsConfigurationName)
   at System.Data.Entity.Infrastructure.Design.Executor.Update.<>c__DisplayClass0_0.<.ctor>b__0()
   at System.Data.Entity.Infrastructure.Design.Executor.OperationBase.Execute(Action action)
ClientConnectionId:96230f70-5c9f-4d9b-abbd-517b34bd72ac
Error Number:5123,State:1,Class:16
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Users\TomerNinjaDB.mdf'.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Further technical details

EF Core version: 6.4.0 Database provider: SQLExpress (System.Data.SqlClient) Target framework: 3.1 (e.g. .NET Core 3.0) Operating system: Windows 10 IDE: Visual Studio 2019

[ErikEJ]

This is most likely due to a LocalDB 2017 bug, update your instance with the latest CU.

[Tzadiko]

This is most likely due to a LocalDB 2017 bug, update your instance with the latest CU.

Brilliant, I updated the CU via the link here: https://support.microsoft.com/en-ie/help/4527377/cumulative-update-18-for-sql-server-2017

(Download link: https://www.microsoft.com/en-us/download/confirmation.aspx?id=56128)

It works now.

After a lot more digging, more on the bug: https://support.microsoft.com/en-ie/help/4096875/fix-access-is-denied-error-when-you-try-to-create-a-database-in-sql-se

Thank you for your reply!