ASP.NET Core with PostgreSQL and Entity Framework Core

This article shows how to use PostgreSQL with ASP.NET Core 1.0 using Entity Framework Core.

Code: VS2017 msbuild | VS2015 project.json

Posts in this series:

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 7 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>netcoreapp1.1</TargetFramework>
    <AssemblyName>DataAccessPostgreSqlProvider</AssemblyName>
    <PackageId>DataAccessPostgreSqlProvider</PackageId>
    <RuntimeFrameworkVersion>1.1.0</RuntimeFrameworkVersion>
    <PackageTargetFallback>$(PackageTargetFallback);dotnet5.6;portable-net45+win8</PackageTargetFallback>
    <GenerateAssemblyTitleAttribute>false</GenerateAssemblyTitleAttribute>
    <GenerateAssemblyDescriptionAttribute>false</GenerateAssemblyDescriptionAttribute>
    <GenerateAssemblyConfigurationAttribute>false</GenerateAssemblyConfigurationAttribute>
    <GenerateAssemblyCompanyAttribute>false</GenerateAssemblyCompanyAttribute>
    <GenerateAssemblyProductAttribute>false</GenerateAssemblyProductAttribute>
    <GenerateAssemblyCopyrightAttribute>false</GenerateAssemblyCopyrightAttribute>
  </PropertyGroup>

  <ItemGroup>
    <ProjectReference Include="..\DomainModel\DomainModel.csproj" />
  </ItemGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.Diagnostics" Version="1.1.0" />
    <PackageReference Include="Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore" Version="1.1.0" />
    <PackageReference Include="Microsoft.AspNetCore.Mvc" Version="1.1.0" />
    <PackageReference Include="Microsoft.AspNetCore.StaticFiles" Version="1.1.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="1.1.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="1.0.0-msbuild3-final" />
    <PackageReference Include="Microsoft.Extensions.Configuration.EnvironmentVariables" Version="1.1.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="1.1.0" />
    <PackageReference Include="Microsoft.Extensions.Logging" Version="1.1.0" />
    <PackageReference Include="Microsoft.Extensions.Logging.Console" Version="1.1.0" />
    <PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="1.1.0" />
    <PackageReference Include="Microsoft.Extensions.Options.ConfigurationExtensions" Version="1.1.0" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="1.1.0" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.Design" Version="1.1.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.

EF7_PostgreSQL_01

Right click your user and click propeties to set the password

EF7_PostgreSQL_02

Set up the migration scripts now. Open the command line in the same folder where the context is added in the Startup.

>
> dotnet ef migrations add testPG
>
> dotnet ef database update
>

The database should now exist. Add Entity Framework 7 to the ASP.NET Core RC2 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 6 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.

EF7_PostgreSQL_03

PostgreSQL is a great choice for a rational database if you what to support multiple runtime environments.

Links:

Announcing Entity Framework Core 1.1

Announcing Entity Framework Core RC2

http://www.postgresql.org

http://www.pgadmin.org/

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/

Advertisements

26 comments

  1. 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

    1. 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

      1. 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

  2. sumatan · · Reply

    migration doesn’t work. It only creates the migration table. I’m I doing something wrong?

    1. 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

  3. 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. 🙂

    1. I’m hitting, {“42P01: relation \”DataEventRecord\” does not exist”}

  4. 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.

    1. Hi, thanks, you need to define the context as well in the command dotnet ef migrations dddd –context YourContext

      Greetings Damien

  5. 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?

    1. Hi Vlad

      Yes, EFCore is used as the ORM

      greetings Damien

  6. 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.

    1. Hi Vlad

      cool, thanks for the link

      Greetings Damien

  7. […] ASP.NET Core 1.0 with PostgreSQL and Entity Framework Core | Software Engineering – […]

  8. 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.

    1. 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

  9. mziyabo · · Reply

    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

    1. Thanks, no should work, I just did a dotnet restore. I will check it for PostgreSQL

  10. 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)))

      1. 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

      2. in the Context class see the EF Core docs from Microsoft these are really good

  11. Rafael Brugnollo · · Reply

    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

  12. Hello There. Do you have a working example to share ? Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: