This article shows how to use PostgreSQL with ASP.NET Core 3.1 using Entity Framework Core.
Code: https://github.com/damienbod/AspNetCoreMultipleProject
Posts in this series:
- Experiments with Entity Framework Core and an ASP.NET Core API
- ASP.NET Core with PostgreSQL and Entity Framework Core
- ASP.NET Core with MySQL and Entity Framework Core
2020-01-10: Updated to ASP.NET Core 3.1
2018-12-05: Updated to ASP.NET Core 2.2 and EF Core 2.2
2017-08-16: Updated to ASP.NET Core 2.1 and EF Core 2.1
2017-08-17: Updated to ASP.NET Core 2.0 and EF Core 2.0
2017-02-10: Updated to VS2017 and msbuild
2016-12-01: Updated to ASP.NET Core 1.1
2016-07-02: Updated to ASP.NET Core RTM
2016-05-17: Updated to ASP.NET Core RC2 dotnet
The PostgreSQL Entity Framework Core provider can be downloaded as a NuGet package. Add the NuGet package Npgsql.EntityFrameworkCore.PostgreSQL to your dependencies in the csproj file file.
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<TargetFramework>netcoreapp3.1</TargetFramework>
<AssemblyName>DataAccessPostgreSqlProvider</AssemblyName>
<PackageId>DataAccessPostgreSqlProvider</PackageId>
</PropertyGroup>
<ItemGroup>
<ProjectReference Include="..\DomainModel\DomainModel.csproj" />
</ItemGroup>
<ItemGroup>
<FrameworkReference Include="Microsoft.AspNetCore.App" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="3.1.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.0" PrivateAssets="All" />
</ItemGroup>
<ItemGroup>
<Folder Include="Properties\" />
</ItemGroup>
</Project>
The web project which loads the project with EF Core needs to support migrations if you wish to create a database this way.
<Project Sdk="Microsoft.NET.Sdk.Web">
<PropertyGroup>
<TargetFramework>netcoreapp3.1</TargetFramework>
<AssemblyName>AspNetCoreMultipleProject</AssemblyName>
<PackageId>AspNet5MultipleProject</PackageId>
<StartupObject>AspNetCoreMultipleProject.Program</StartupObject>
</PropertyGroup>
<ItemGroup>
<ProjectReference Include="..\DataAccessMsSqlServerProvider\DataAccessMsSqlServerProvider.csproj" />
<ProjectReference Include="..\DataAccessMySqlProvider\DataAccessMySqlProvider.csproj" />
<ProjectReference Include="..\DataAccessPostgreSqlProvider\DataAccessPostgreSqlProvider.csproj" />
<ProjectReference Include="..\DataAccessSqliteProvider\DataAccessSqliteProvider.csproj" />
<ProjectReference Include="..\DomainModel\DomainModel.csproj" />
</ItemGroup>
<ItemGroup>
<PackageReference Include="Microsoft.AspNetCore.App" />
<PackageReference Include="Microsoft.AspNetCore.Mvc.NewtonsoftJson" Version="3.1.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.0" PrivateAssets="All" />
<PackageReference Include="Newtonsoft.Json" Version="12.0.3" />
<PackageReference Include="Swashbuckle.AspNetCore" Version="5.0.0-rc5" />
</ItemGroup>
<ItemGroup>
<DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.0" />
</ItemGroup>
</Project>
Create the context for Entity Framework Core which is used for the PostgreSQL database.
using System;
using System.Linq;
using DomainModel.Model;
using Microsoft.EntityFrameworkCore;
namespace DataAccessPostgreSqlProvider
{
// >dotnet ef migration add testMigration in AspNet5MultipleProject
public class DomainModelPostgreSqlContext : DbContext
{
public DomainModelPostgreSqlContext(DbContextOptions<DomainModelPostgreSqlContext> options) :base(options)
{
}
public DbSet<DataEventRecord> DataEventRecords { get; set; }
public DbSet<SourceInfo> SourceInfos { get; set; }
protected override void OnModelCreating(ModelBuilder builder)
{
builder.Entity<DataEventRecord>().HasKey(m => m.DataEventRecordId);
builder.Entity<SourceInfo>().HasKey(m => m.SourceInfoId);
// shadow properties
builder.Entity<DataEventRecord>().Property<DateTime>("UpdatedTimestamp");
builder.Entity<SourceInfo>().Property<DateTime>("UpdatedTimestamp");
base.OnModelCreating(builder);
}
public override int SaveChanges()
{
ChangeTracker.DetectChanges();
updateUpdatedProperty<SourceInfo>();
updateUpdatedProperty<DataEventRecord>();
return base.SaveChanges();
}
private void updateUpdatedProperty<T>() where T : class
{
var modifiedSourceInfo =
ChangeTracker.Entries<T>()
.Where(e => e.State == EntityState.Added || e.State == EntityState.Modified);
foreach (var entry in modifiedSourceInfo)
{
entry.Property("UpdatedTimestamp").CurrentValue = DateTime.UtcNow;
}
}
}
}
Add the POCO classes to use as entities.
public class DataEventRecord
{
[Key]
public long DataEventRecordId { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public DateTime Timestamp { get; set; }
[ForeignKey("SourceInfoId")]
public SourceInfo SourceInfo { get; set; }
public long SourceInfoId { get; set; }
}
}
public class SourceInfo
{
[Key]
public long SourceInfoId { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public DateTime Timestamp { get; set; }
public List<DataEventRecord> DataEventRecords { get; set; }
}
The connection string can be added in the OnConfiguring method in the class which implements the DbContext, or via dependency injection in the constructor using the options. The connection string property User ID needs to exist in the database and should have the create database rights.
{
"ConnectionStrings": {
"DataAccessPostgreSqlProvider": "User ID=damienbod;Password=1234;Host=localhost;Port=5432;Database=damienbod;Pooling=true;"
}
}
}
Open pgAdmin to configure the user in PostgreSQL.
Right click your user and click propeties to set the password
Set up the migration scripts now. Open the command line in the same folder where the context is added in the Startup.
> > dotnet restore > > dotnet ef migrations add postgresqlMigration --context DomainModelPostgreSqlContext > > dotnet ef database update --context DomainModelPostgreSqlContext >
The database should now exist. Add Entity Framework Core to the ASP.NET Core application in the Startup class. A DataAccessPostgreSqlProvider class with an interface is used to access the context from anywhere else in the application.
public void ConfigureServices(IServiceCollection services)
{
// Use a PostgreSQL database
var sqlConnectionString = Configuration.GetConnectionString("DataAccessPostgreSqlProvider");
services.AddDbContext<DomainModelPostgreSqlContext>(options =>
options.UseNpgsql(
sqlConnectionString,
b => b.MigrationsAssembly("AspNet5MultipleProject")
)
);
services.AddScoped<IDataAccessProvider, DataAccessPostgreSqlProvider.DataAccessPostgreSqlProvider>();
JsonOutputFormatter jsonOutputFormatter = new JsonOutputFormatter
{
SerializerSettings = new JsonSerializerSettings
{
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
}
};
services.AddMvc(
options =>
{
options.OutputFormatters.Clear();
options.OutputFormatters.Insert(0, jsonOutputFormatter);
}
);
}
Now the PostgreSQL provider can be used in a MVC controller using construction injection.
using System.Collections.Generic;
using DomainModel;
using DomainModel.Model;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
namespace AspNet5MultipleProject.Controllers
{
[Route("api/[controller]")]
public class DataEventRecordsController : Controller
{
private readonly IDataAccessProvider _dataAccessProvider;
public DataEventRecordsController(IDataAccessProvider dataAccessProvider)
{
_dataAccessProvider = dataAccessProvider;
}
[HttpGet]
public IEnumerable<DataEventRecord> Get()
{
return _dataAccessProvider.GetDataEventRecords();
}
[HttpGet]
[Route("SourceInfos")]
public IEnumerable<SourceInfo> GetSourceInfos(bool withChildren)
{
return _dataAccessProvider.GetSourceInfos(withChildren);
}
[HttpGet("{id}")]
public DataEventRecord Get(long id)
{
return _dataAccessProvider.GetDataEventRecord(id);
}
[HttpPost]
public void Post([FromBody]DataEventRecord value)
{
_dataAccessProvider.AddDataEventRecord(value);
}
[HttpPut("{id}")]
public void Put(long id, [FromBody]DataEventRecord value)
{
_dataAccessProvider.UpdateDataEventRecord(id, value);
}
[HttpDelete("{id}")]
public void Delete(long id)
{
_dataAccessProvider.DeleteDataEventRecord(id);
}
}
}
The controller api can be called using Fiddler:
POST http://localhost:5000/api/dataeventrecords HTTP/1.1
User-Agent: Fiddler
Host: localhost:5000
Content-Length: 135
Content-Type: application/json;
{
"DataEventRecordId":3,
"Name":"Funny data",
"Description":"yes",
"Timestamp":"2015-12-27T08:31:35Z",
"SourceInfo":
{
"SourceInfoId":0,
"Name":"Beauty",
"Description":"second Source",
"Timestamp":"2015-12-23T08:31:35+01:00",
"DataEventRecords":[]
},
"SourceInfoId":0
}
The data can be viewed now in PostgreSQL.
PostgreSQL is a great choice for a rational database if you what to support multiple runtime environments.
Links:
https://docs.microsoft.com/en-us/ef/core/
https://github.com/npgsql/npgsql
http://www.npgsql.org/doc/ef7.html
https://damienbod.com/2016/01/07/experiments-with-entity-framework-7-and-asp-net-5-mvc-6/
Software Engineering 
Hi,
Your job seems very cool but I got an error running “dnu restore” on searching for DomainModel package. What is this package!? How can I reference it?
Thank you
Hi
The DomainModel package is the project in the src. This should build per default. It is referenced in the project.json file.
Does the DomainModel package build. What happens in the dnu restore in the cmd line? rc1 is required.
Hope this helps
Greetings Damien
Hi Bobbe Maresca
Thanks for your feedback. This was a problem, I did a fix and pushed it, it should work now. I had an incorrect project name.
Greetings Damien
migration doesn’t work. It only creates the migration table. I’m I doing something wrong?
Hi sumatan
It should work, have you setup the database and it’s running?, ASP.NET Core RC1? The committed migrations for the Postgres are for ‘damienbod’. If you setup a different db, you need to delete these and recreate the migrations.
greetings Damien
Nice post. However, I am getting stuck on the migration step. It only creates the _EFMMigrationsHistory table. I’m I missing something here. I’m using github source. 🙂
I’m hitting, {“42P01: relation \”DataEventRecord\” does not exist”}
Hi!
I have a problem… When I run dotnet ef migrations add testPG return the message “More than one DbContext was found. Specify which one to use. Use the ‘-Context’ parameter for PowerShell commands and the ‘–context’ parameter for dotnet commands”.
I follow all the steps with the options configurated of the demo.
Hi, thanks, you need to define the context as well in the command dotnet ef migrations dddd –context YourContext
Greetings Damien
Hi, I am kind of a noob. If I use Visual Studio to generate a project using ASp.NET Core MVC, would I be able to switch the database to PostgreSQL using this tutorial?
Hi Vlad
Yes, EFCore is used as the ORM
greetings Damien
Thanks! I’ve managed to make an ASP.NET Core MVC project generator for yeoman with PostgreSQL support, you can find it here if you are interested: https://github.com/DeathArrow01/generator-aspnetpostgresql
It’s a quick hack of another project generator buy it works.
Hi Vlad
cool, thanks for the link
Greetings Damien
[…] ASP.NET Core 1.0 with PostgreSQL and Entity Framework Core | Software Engineering – […]
Thank you Damienbod for this post, it’s very useful!
Only one question, I’m getting this error messsage: An attempt was made to use the context while it is being configured. A DbContext instance cannot be used inside OnConfiguring since it is still being configured at this point.
This happens when I try to do 2 request in a row and both connect to the database to get some rows. I’m getting this error running locally, I imagine when I’ll have concurrency this could be worst.
Do you know a fix for this?
Thank in advance.
Hi Mateus
I don’t fully get what your setup is, could you maybe post some code, maybe a gitHub issue and I’ll have a look. Each HTTP request should have its own context, or if 2 requests to the database are sent for a single HTTP request, the same context should be used.
Greetings Damien
Hey man. Thanks for updating to the .csproj file. Is there anything else you did to get it working after dotnet migrate? I must’ve broken something after the migration. Getting No executable found matching dotnet-ef
Thanks, no should work, I just did a dotnet restore. I will check it for PostgreSQL
Hi, thx for this post, I am a noob in VS and MVC, but i need it(( so can you share your example. Because i don’t understand where i should put “Create the context for Entity Framework Core which is used for the PostgreSQL database.” code and etc. Please)))
https://github.com/damienbod/AspNet5MultipleProject
big thanks
sorry for my another stupid question, but how can i check for a connection and get data, where should i write sql code? I changed startup.cs for postgre connection. Also i changed connection string in config.json. And what next? when i run the app i see “Server running” and that’s all cause it’s in index page. Sorry once again
in the Context class see the EF Core docs from Microsoft these are really good
Getting this error when trying to generate the migrations. Any ideas?
dotnet : No executable found matching command “dotnet-ef”
At line:1 char:1
+ dotnet ef migration add testMigration
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (No executable f…and “dotnet-ef”:String) [], RemoteException
+ FullyQualifiedErrorId : NativeCommandError
Hello There. Do you have a working example to share ? Thanks!
Hi
https://github.com/damienbod/AspNet5MultipleProject/tree/master/src/DataAccessPostgreSqlProvider
Greetings Damien
[…] If you want a quick setup guide from someone that probably knows what he’s doing. check this link. […]
“Hi!
I have a problem… When I run dotnet ef migrations add testPG return the message “More than one DbContext was found. Specify which one to use. Use the ‘-Context’ parameter for PowerShell commands and the ‘–context’ parameter for dotnet commands”.
I follow all the steps with the options configurated of the demo.”
Correct answer is:
1) simply delete content under src\Migrations tab, which included into source tree (MySQL EF adapter)
2) comment and select correct lines in main flie Startup.css which corresponds to the database of your chose
3) restart commands:
dotnet ef migrations add testPG
dotnet ef database update
4) continue
You missed a line 63 (which includes a SourceInfo to the result set):
public List GetDataEventRecords()
{
// Using the shadow property EF.Property(dataEventRecord)
//_context.SourceInfos.Include(s => s.DataEventRecords).OrderByDescending(srcInfo => EF.Property(srcInfo, “UpdatedTimestamp”)).ToList();
_context.DataEventRecords.Include(s => s.SourceInfo).OrderByDescending(dataEventRecord => EF.Property(dataEventRecord, “UpdatedTimestamp”)).ToList();
return _context.DataEventRecords.OrderByDescending(dataEventRecord => EF.Property(dataEventRecord, “UpdatedTimestamp”)).ToList();
}
Result will be like:
[
{
“dataEventRecordId”: 1,
“name”: “text”,
“description”: “description “,
“timestamp”: “2015-12-27T08:31:35”,
“sourceInfo”: {
“sourceInfoId”: 1,
“name”: “source info name”,
“description”: “source info test”,
“timestamp”: “2015-12-27T08:31:35”,
“dataEventRecords”: []
},
“sourceInfoId”: 1
}
]
Otherwize:
[
{
“dataEventRecordId”: 1,
“name”: “text”,
“description”: “description “,
“timestamp”: “2015-12-27T08:31:35”,
“sourceInfo”: null,
“sourceInfoId”: 1
}
]
Great Article! Thanks for sharing your knowledge. I’ve a question. What’s the best way to use EF Core in a psql db replication? Can you show a full example of how to do get queries using a readonly context and inserts/updates using a read-write context? I’m not able to understand when to select the connection string and how to assign it to the context at runtime for an api project
Yes, i understand you question. In my opinion, the best way is to use a EF – avoid using it for replication, because usually the most efficient way to replicate databases is native replication, without materialization – dematerialization (or serialization – deserialization DTO objects within in-memory replication process), so EF is of no use at all in this scenario, or at least you can share generated by EF Core classes (due to the usage of database-first scenario, where DB classes are generated by dotnet core commands). The most simple scenario as I see it is to use DB-first approach on one side for replication, wilst use a code-first approach on the other end of replication sequence, so if you match this to approaches with generated EF database Context classes and sequences in one project, theoretically by using DTO objects and passing them by any means from one context to another context, connected to other db instance, you will be able to replicate db structure of the source database as well as some data you will provide for it, but in most cases this approach will not work. There are some reasons that EF replication will fail – one is a coherence, and other is integrity. Also, there are always be a scenarios where there are more than one working thread are needed or DB connections used to complete some tasks, so it can all ruin in any time. It will definetely work if your database is small and you are going to use a full replication strategy, limit access to database by one instance, try use one writer-multiple reader or use other syncronization mechanizms to deliver a corerent and sync-ed database witout some of the errors. Of cause you can programmatically fill all the cases, and of cause provide some write blocking mechanizm for a time of synchronization or implement some non-blocking approach which can garantee that replication will not end prematurely or end up with unexpected error and will always finish replication if at some point of time you leave a source database “intact” (i mean without any queued writes), for a time frame big enouth so you can finish non-blocking replication at this time
Hi! Thanks for your blog and it is amazingly helpful. One more question, do you have any resources or docs about how to set up Postgres in this case on Linux with shell portal instead of GUI?
Thanks?
Unable to create an object of type ‘DomainModelPostgreSqlContext’. For the different patterns supported at design time, see https://go.microsoft.com/fwlink/?linkid=851728
should work now
Greetings Damien
Unable to call PostgreySQl stored procedure from DataAccessLayer class..
And run insert and select query simultaneously in Stored Procedure. Please help.
Hi Keshav
I’ll have a look but maybe you have already solved this
Greetings Damien
If you are going to produce more articles on this topic, please identify OS in Title.
The most important aspect of .NET core is it’s cross-platform capability.
If you ignore that by using ASP.NET core on Windows, I would ask: what’s the point?
[…] taken from here: https://damienbod.com/2016/01/11/asp-net-5-with-postgresql-and-entity-framework-7/ […]
[…] Currently I am doing database migration using MVC6 on .netcore 2.0. here is the link that I am following, to migrate POSTGRESQL : https://damienbod.com/2016/01/11/asp-net-5-with-postgresql-and-entity-framework-7/ […]