Click here to Skip to main content
15,792,841 members
Articles / Web Development / ASP.NET

Using PostgreSQL with Entity Framework in ASP.NET MVC

Rate me:
Please Sign up or sign in to vote.
4.89/5 (13 votes)
8 Jun 2014CPOL3 min read 148.8K   24   14
In this blog post, we are going to learn how to use PostgreSQL with Entity Framework in an ASP.NET MVC application.


I wanted to investigate using Entity Framework with PostgreSQL in an ASP.NET MVC application quickly. Instead of writing a trivial demo that simply reads and writes data from the database, I decided a better approach would be to convert an existing ASP.NET MVC application that already uses Entity Framework (with another database) to work with PostgreSQL. I decided to use MvcMusicStore. It is one of the best documented tutorials ASP.NET MVC applications. This is the description on its project site: “MVC Music Store is a tutorial application built on ASP.NET MVC. It’s a lightweight sample store which demonstrates ASP.NET MVC using Entity Framework”.

By going through the process of converting a working application, I can concentrate on the area that is specific to using Entity Framework with PostgreSQL. I can also easily perform basic testing to verify the changes I make by just running the application. The rest of this post documents the steps I have gone though to convert the MvcMusicStore.

What You Will Need

  • A working installation of PostgreSQL. I use PostgreSQL version 9.3
  • A development environment where you can compile and run ASP.NET MVC application

Step 1

Download MvcMusicStore. Unzip the folder and open the project. At this point, you should be able to compile the project. If not, there is something wrong with your development environment, resolve it now before you make more changes that could complicate it further. The project is configured to use SqServerCe. If it’s installed, you should be able to run the application.


Step 2

Prepare the PostgreSQL database.

MvcMusicStore uses EntityFramework Code First.

In the global.asax.cs file, it specifies the SampleData class as the database initializer.

protected void Application_Start()
    // specify database initializer
    System.Data.Entity.Database.SetInitializer(new MvcMusicStore.Models.SampleData());



Take a look at the SampleData class, notice that it inherits from DropCreateDatabaseIfModelChanges. This means that the application will re-create and re-seed the database when the schema changes.

// inherits from DropCreateDatabaseIfModelChanges
public class SampleData : DropCreateDatabaseIfModelChanges
    // The seed method will seed the database with initial data
    protected override void Seed(MusicStoreEntities context)
        var genres = new List
            new Genre { Name = "Rock" },
             new Genre { Name = "Jazz" },
             new Genre { Name = "Metal" },
             new Genre { Name = "Alternative" },
             new Genre { Name = "Disco" },
             new Genre { Name = "Blues" },
             new Genre { Name = "Latin" },
             new Genre { Name = "Reggae" },
             new Genre { Name = "Pop" },
             new Genre { Name = "Classical" }
// more code not shown here .......

Unfortunately, database migration and creation is not yet supported in Npgsql Entity Framework. We will have to create the database and seed the data manually.

  1. Create a database in your PostgreSQL server. Name the database MvcMusicStore.
  2. Next, we need to create the tables and seed them with data.

The MvcMusicStore download contains a \MvcMusicStore-Assets\Data\MvcMusicStore-Create.sql file that works for MSSQL. We can use it as the base and adapt it for postgreSQL. You can use the finished script here MvcMusicStore-Create-PostgreSQL. The file has documentation on what was changed from the original script.

Step 3

Install ADO.NET provider and Entity Framework provider for postgreSQL.

You will have multiple options here. We will use Npgsql.

The MvcMusicStore download uses EntityFramework 4.1, which is two versions older than the current version (Entity Framework 6). Let’s upgrade it to the latest first via Nuget.

Install-Package EntityFramework

Next, install Npgsql PostgreSQL Entity Framework provider. This will also install its dependency which includes the Npgsql ADO.NET Provider.

Install-Package Npgsql.EntityFramework

Step 4

Update web.config to tell the run time about our database connection and Entity Framework configuration.

  1. Update the connection string like below. Remember to replace the information in the connection string to the values of your environment.
    <add name="MusicStoreEntities" 
    User Id=[myusername];Password=[mypassword];" providerName="Npgsql" />

    Note: Do not change the name of the connection string name. The name MusicStoreEntities matches the project’s DbContext class name. This is how Entity Framework figures out which connection string to use.

  2. Update the entityFramework element as follows:
    type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
    <parameter value="v11.0" />
    <defaultConnectionFactory type="Npgsql.NpgsqlFactory, Npgsql" />
    <provider invariantName="System.Data.SqlClient"
    type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    <provider invariantName="Npgsql"
    type="Npgsql.NpgsqlServices, Npgsql.EntityFramework" />
  3. Add the element as follows:
    <add name="Npgsql Data Provider" invariant="Npgsql"
    support="FF" description=".Net Framework Data Provider for Postgresql"
    type="Npgsql.NpgsqlFactory, Npgsql" />

Step 5

Modify the MvcStoreEntities (DbContext) class to configure the table names the Entities mapped to.

PostgreSQL creates data tables in the public schema by default. This is different than the default Entity Framework convention. Override the OnModelCreating method to specify the new table name mapping.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    modelBuilder.Entity().ToTable("Artist", "public");
    modelBuilder.Entity().ToTable("Album", "public");
    modelBuilder.Entity().ToTable("Cart", "public");
    modelBuilder.Entity().ToTable("Order", "public");
    modelBuilder.Entity().ToTable("OrderDetail", "public");
    modelBuilder.Entity().ToTable("Genre", "public");

Step 6

Comment out the following line in the global.asax.cs file. Otherwise, you will get an error since Npgsql does not support migration and database creation.

//System.Data.Entity.Database.SetInitializer(new MvcMusicStore.Models.SampleData());


Compile and run the application again. You are now running MvcMusicStore with Entity Framework 6 against a postgreSQL database.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer (Senior)
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

QuestionWhat about Authorization Pin
chayimamaral18-Oct-15 6:24
chayimamaral18-Oct-15 6:24 
AnswerRe: What about Authorization Pin
Vince Chan30-Oct-15 7:50
professionalVince Chan30-Oct-15 7:50 
GeneralRe: What about Authorization Pin
Vince Chan3-Dec-15 5:51
professionalVince Chan3-Dec-15 5:51 
Question"Could not load Npgsql.EntityFrameworkLegacy assembly" error Pin
simon.wandry24-Jun-15 20:49
simon.wandry24-Jun-15 20:49 
AnswerRe: "Could not load Npgsql.EntityFrameworkLegacy assembly" error Pin
Vince Chan25-Jun-15 8:40
professionalVince Chan25-Jun-15 8:40 
GeneralRe: "Could not load Npgsql.EntityFrameworkLegacy assembly" error Pin
simon.wandry25-Jun-15 17:38
simon.wandry25-Jun-15 17:38 
GeneralGetting Error in ModelBuilder.Entity() Pin
Muhammad Azam21-Mar-15 21:10
Muhammad Azam21-Mar-15 21:10 
GeneralRe: Getting Error in ModelBuilder.Entity() Pin
Yves Goergen19-Aug-15 23:39
Yves Goergen19-Aug-15 23:39 
QuestionError in Connection string Pin
NitinGumgaonkar20-Aug-14 2:58
NitinGumgaonkar20-Aug-14 2:58 
AnswerRe: Error in Connection string Pin
Vince Chan20-Aug-14 8:14
professionalVince Chan20-Aug-14 8:14 
GeneralRe: Error in Connection string Pin
NitinGumgaonkar22-Aug-14 3:11
NitinGumgaonkar22-Aug-14 3:11 
GeneralRe: Error in Connection string Pin
Vince Chan22-Aug-14 6:00
professionalVince Chan22-Aug-14 6:00 
QuestionThank you very much Pin
ThanhTrungDo10-Jun-14 15:55
professionalThanhTrungDo10-Jun-14 15:55 
I'm finding this mapping.
Thank for your knowledge.
I hope you continue post other best practices
Thumbs Up | :thumbsup:
AnswerRe: Thank you very much Pin
Vince Chan10-Jun-14 21:37
professionalVince Chan10-Jun-14 21:37 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.