Context

I'm following Microsoft's tutorial on MVC in ASP.NET Core 5.0, using VS Code and the terminal, and I got stuck on the Initial Migration step.

There are numerous similar questions on SO, but none of them contain an answer I can understand, as this is my first dive into ASP.NET.

Problem Description

The tutorial asks that I run the following commands:

dotnet ef migrations add InitialCreate
dotnet ef database update

The first one ran without a hitch. The second results in the following error:

Build started...
Build succeeded.
System.ArgumentException: Connection string keyword 'server' is not supported. For a possible alternative, see https://go.microsoft.com/fwlink/?linkid=2142181.
   at Microsoft.Data.Sqlite.SqliteConnectionStringBuilder.GetIndex(String keyword)
   at Microsoft.Data.Sqlite.SqliteConnectionStringBuilder.set_Item(String keyword, Object value)
   at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
   at Microsoft.Data.Sqlite.SqliteConnectionStringBuilder..ctor(String connectionString)
   at Microsoft.EntityFrameworkCore.Sqlite.Storage.Internal.SqliteDatabaseCreator.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Connection string keyword 'server' is not supported. For a possible alternative, see https://go.microsoft.com/fwlink/?linkid=2142181.

Setup

Here's my appsettings.json file, the one with the connection string:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "MvcMovieContext": "Server=(localdb)\\mssqllocaldb;Database=MvcMovieContext-9dffe5a0-829d-4c64-9129-54ea0791196d;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

And here's appsettings.Development.json, which lacks any connection string:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  }
}

I tried adding the "AllowedHosts" and "ConnectionString" entries to this file, since I'd think I should be in development mode right now, but it didn't change anything.

If I understand this right, my project is using SQLite in development and SQLServer in production:

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();

    services.AddDbContext<MvcMovieContext>(options =>
    {
        var connectionString = Configuration.GetConnectionString("MvcMovieContext");

        if (Environment.IsDevelopment())
            options.UseSqlite(connectionString);
        else
            options.UseSqlServer(connectionString);
    });
}

Finally, here's my .csproj file:

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

  <PropertyGroup>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore" Version="5.0.0-*" />
    <PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="5.0.0-*" />
    <PackageReference Include="Microsoft.AspNetCore.Identity.UI" Version="5.0.0-*" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.11">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SQLite" Version="5.0.11" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.11" />
    <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="5.0.2" />
  </ItemGroup>

</Project>

If you'd like to see other parts of the code, I've hosted the project on GitHub. Alternatively, you can let me know and I'll put it up.

CodePudding user response：

Sqlite is not a server based database; the provider you're using for it doesn't understand the Server keyword you've used in the connection string. Try specifying Data Source=some_path.

For a list of valid keywords, see https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/connection-strings

Side note: SQLServer's provider understands Server as a synonym of Data Source

If you remove/fix one keyword it doesn't like you'll see the error has shifted to complaining about the next one (Database); in essence it's to be expected if you're trying to use a SQLServer flavored connection string (which would reasonably have Server and Database keywords) with SQLite.

You can also take a look at connectionstrings.com for some examples of connstrs for each DB and form the relevant strings approriately - ultimately, these two DBs have very different needs when it comes to what keywords you use in the conenction strings.

It's probably going to be simplest to just have two strings:

  "ConnectionStrings": {
    "MvcMovieContextSQLS": "Server=(localdb)\\mssqllocaldb;Database=MvcMovieContext-9dffe5a0-829d-4c64-9129-54ea0791196d;Trusted_Connection=True;MultipleActiveResultSets=true",
    "MvcMovieContextSQLite": "Data Source=c:\path\to\my.db"
  }

And your code pulls them appropriately:

    if (Environment.IsDevelopment())
        options.UseSqlite(Configuration.GetConnectionString("MvcMovieContextSQLite"));
    else
        options.UseSqlServer(Configuration.GetConnectionString("MvcMovieContextSQLS"));

Or if you're deciding that "dev is SQLite and live is SQLS" you can have your development config override your main config by specifying the same group (ConnectionStrings) and attribute (MvcMovieContext) in appsettings.Development.json:

  "ConnectionStrings": {
    "MvcMovieContext": "Data Source=c:\path\to\my.db"
  }

I tend not to go this route, for various reasons, but it's perfectly valid if you remember that the appsettings.Development.json replaces any matching elements in appsettings.json.