Mac, DotNetCore, Postgres
July 29, 2017
I'm having fun trying to get an application that was originally developed on Windows, against SQL Server, running on a Mac against Postgres. It's written in Microsoft's dotnet core, using C# and Entity Framework Core.
The solution has multiple projects inside of it:
- Salon - contains the web site
- SalonServices - contains the services the web site makes use of, and accesses the database
- Salon.Web.Tests.Unit - unit tests for the web application controllers
- SalonServices.Tests.Integration - integration tests for the database access repositories
- SalonServices.Tests.Unit - unit tests for the Salon Services functionality.
Entity Framework Postgres Packages
In order to get Postgres support, I installed the following three packages into my project, using nuget:
<PackageReference Include="Npgsql" Version="3.2.5" /> <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="1.1.1" /> <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.Design" Version="1.1.1" />
Creating the database
I created the database on a separate Linux server, called baal
postgres=# create user salon with password 'Secret'; postgres=# create database salon with owner salon;
Database Connection
I configured the database connection in appsettings.json (in my Salon project) to have the following entry:
"ConnectionStrings": { // "Salon": "data source = .;initial catalog = Salon; integrated security = True; MultipleActiveResultSets=True;App=EntityFramework" "Salon": "User ID=salon;Password=Secret;Host=baal;Port=5432;Database=salon;Pooling=true;" },
Missing dotnet-ef
The internet told me that I needed to run dotnet ef database update in order to populate my database. This needs to be run inside the project that has a DbContext, so in my case this is the SalonServices folder. This didn't work, failing with:
$ dotnet ef database update No executable found matching command "dotnet-ef"
In order to get this to work, I had to manually edit the SalonServices.csproj file.
I added another ItemGroup containing
<ItemGroup> <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.0.0" /> </ItemGroup>
and then ran
$ dotnet restore
Missing appsettings.json
When I next ran my dotnet command to run my database migrations, I got the following error:
The configuration file 'appsettings.json' was not found and is not optional. The physical path is '/Users/drumcoder/web/Salon/SalonServices/bin/Debug/netcoreapp1.1/appsettings.json'.
I fixed this with a manual copy of the appsettings.json file out of the Salon project into the specified directory. There's probably a much better solution to this, and I'll get to it when I finally have this all working.
Could not load System.Diagnostics.DiagnosticSource
My next error was this:
$ dotnet ef database update
Build succeeded.
0 Warning(s)
0 Error(s)
Time Elapsed 00:00:01.62
System.IO.FileLoadException: Could not load file or assembly 'System.Diagnostics.DiagnosticSource, Version=4.0.1.1, Culture=neutral,
PublicKeyToken=cc7b13ffcd2ddd51'. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
File name: 'System.Diagnostics.DiagnosticSource, Version=4.0.1.1, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51'
at Microsoft.EntityFrameworkCore.Infrastructure.RelationalServiceCollectionExtensions.AddRelational(IServiceCollection services)
at Microsoft.Extensions.DependencyInjection.SqlServerServiceCollectionExtensions.AddEntityFrameworkSqlServer(IServiceCollection services)
at Microsoft.EntityFrameworkCore.Internal.ServiceProviderCache.<>c__DisplayClass4_1.<GetOrAdd>b__2(Int64 k)
at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
at Microsoft.EntityFrameworkCore.DbContext.InitializeServices()
at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
at Microsoft.EntityFrameworkCore.Infrastructure.AccessorExtensions.GetService[TService](IInfrastructure`1 accessor)
at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(Func`1 factory)
at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(String contextType)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Could not load file or assembly 'System.Diagnostics.DiagnosticSource, Version=4.0.1.1, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51'. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
In turned out that I needed to point the database update command at my startup project, so this was solved by changing the command line to being:
$ dotnet ef database update --startup-project ../Salon
This is running inside the SalonService project, but is also pointing at the Salon project.
Keyword not supported: 'host'
My next problem was that the dotnet ef database update --startup-project ../Salon command was still giving an error:
$ dotnet ef database update --startup-project ../Salon
Build succeeded.
0 Warning(s)
0 Error(s)
Time Elapsed 00:00:04.83
An error occurred while calling method 'ConfigureServices' on startup class 'Startup'. Consider using IDbContextFactory to override the
initialization of the DbContext at design-time. Error: Keyword not supported: 'host'.
System.ArgumentException: Keyword not supported: 'host'.
at System.Data.Common.DbConnectionOptions.ParseInternal(Dictionary`2 parsetable, String connectionString, Boolean buildChain, Dictionary`2 synonyms)
at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Dictionary`2 synonyms)
at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerConnection.CreateDbConnection()
at Microsoft.EntityFrameworkCore.Internal.LazyRef`1.get_Value()
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Keyword not supported: 'host'.
This was because my code was still referring to the SqlServer settings in my DbContext. I commented out the UseSqlServer line and replaced it with a UseNpgsql line.
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
// optionsBuilder.UseSqlServer(GetAppSettingsConnectionString());
optionsBuilder.UseNpgsql(GetAppSettingsConnectionString());
}
Once this was done, and I built all my projects, the command then ran successfully and ran the migrations to create my database.
Auto Increment Serials
When I ran my application and tried to insert data into my Postgres tables, I hit a problem. The database engine wasn't auto creating the primary key column, it was expecting it to be passed in from the software. In Postgres, it needed to auto populate the column from a sequence, and this wasn't happening.
After some digging, I found that I needed to change my migrations to add another annotation. I need to investigate how to do this in the source, instead of hacking around with migrations, but this gets us going for the time being.
Each of the migrations that created a primary key on a table looked like this:
Id = table.Column<int>(nullable: false)
.Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
Name = table.Column<string>(maxLength: 50, nullable: false)
I needed to add a second annotation so that it looked like this:
Id = table.Column<int>(nullable: false)
.Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)
.Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn),
Name = table.Column<string>(maxLength: 50, nullable: false)
I did this all the serial columns, deleted the tables in the database, and re-ran the migrations. The database now auto assigns Id values on INSERT.
