ASP.NET Core 1.0 with PostgreSQL and Entity Framework Core

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

Code: https://github.com/damienbod/AspNet5MultipleProject

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 project.json file.

{
    "dependencies": {
        "DomainModel": "*",
        "Microsoft.AspNetCore.Diagnostics": "1.1.0",
        "Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore": "1.1.0",
        "Microsoft.AspNetCore.Mvc": "1.1.0",
        "Microsoft.AspNetCore.StaticFiles": "1.1.0",
        "Microsoft.EntityFrameworkCore": "1.1.0",
        "Microsoft.EntityFrameworkCore.Tools": "1.1.0-preview4-final",
        "Microsoft.Extensions.Configuration.EnvironmentVariables": "1.1.0",
        "Microsoft.Extensions.Configuration.Json": "1.1.0",
        "Microsoft.Extensions.Logging": "1.1.0",
        "Microsoft.Extensions.Logging.Console": "1.1.0",
        "Microsoft.Extensions.Logging.Debug": "1.1.0",
        "Microsoft.Extensions.Options.ConfigurationExtensions": "1.1.0",
        "Microsoft.NETCore.App": {
            "version": "1.1.0",
            "type": "platform"
        },
        "Npgsql.EntityFrameworkCore.PostgreSQL": "1.1.0",
        "Npgsql.EntityFrameworkCore.PostgreSQL.Design": "1.1.0"
    },

    "tools": {
    },

    "frameworks": {
        "netcoreapp1.1": {
            "imports": [
                "dotnet5.6",
                "portable-net45+win8"
            ]
        }
    }
}

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

Experiments with Entity Framework Core and ASP.NET Core 1.0 MVC

14 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 – […]

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: