Link

Using PostgreSQL with Entity Framework in ASP.NET MVC

In this blog post we are going to learn how to use PostgreSQL with Entity Framework in an ASP.NET MVC application.

Introduction:

I wanted to investigate using Entity Framework with PostgreSQL in an ASP.NET MVC application quickly. Instead of writing a trivial demo that simply read and write 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 best documented tutorial 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 are 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 that 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.

Instructions:

Step 1: Setup MvcMusicStore
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 SqlServerSe If it’s installed, you should be able to run the application.
MVC-Music-Store-Thumb

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());

    AreaRegistration.RegisterAllAreas();

    RegisterGlobalFilters(GlobalFilters.Filters);
    RegisterRoutes(RouteTable.Routes);
}

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. a) Create a database in your PostgreSQL server. Name the database MvcMusicStore. b) 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.

a) Update the connection string like below. Remember to replace the information in the connection string to the values in your environment.

<connectionStrings>
<add name="MusicStoreEntities" 
connectionString="Server=[myserver];Database=MusicStore;
User Id=[myusername];Password=[mypassword];" providerName="Npgsql" />
</connectionStrings>

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.

b) Update the entityFramework element as follow

<entityFramework>
<!--<defaultConnectionFactory
type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
<parameters>
<parameter value="v11.0" />
</parameters>
</defaultConnectionFactory>-->
<defaultConnectionFactory type="Npgsql.NpgsqlFactory, Npgsql" />
<providers>
<provider invariantName="System.Data.SqlClient"
type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
<provider invariantName="Npgsql"
type="Npgsql.NpgsqlServices, Npgsql.EntityFramework" />
</providers>
</entityFramework>

c) Add the system.data element as follow.

<system.data>
<DbProviderFactories>
<add name="Npgsql Data Provider" invariant="Npgsql"
support="FF" description=".Net Framework Data Provider for Postgresql"
type="Npgsql.NpgsqlFactory, Npgsql" />
</DbProviderFactories>
</system.data>

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: Disable migration
Comment out the following line in the global.asax.cs file. Otherwise you will get error since Npgsql does not support migration and database creation.

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

Run

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

Download

A working demo is available at GitHub

Update (2015-12-20)

The original post was written almost two years ago. The MvcMusicStore was based on ASPNET MVC 3 and ASPNET Membership. There are some clarifications and update I would like to make:

  • The original MvcMusicStore uses two databases: one for the store, another for the membership. The blog post only covers converting the store database to PostgreSQL.

  • The original MvcMusicStore uses ASPNET Membership, you probably want to use the new ASPNET Identity instead. (“The ASP.NET Identity system is designed to replace the previous ASP.NET Membership and Simple Membership systems”).

  • I have created a storage provider for ASPNET Identity using PostgresSQL & EntityFramework 6 a few weeks ago. It supports the new columns in ASPNET Identity 2.0.  A working demo and source is available on
    GitHub: PostgreSQL.AspNet.Identity.EntityFramework

  • In the process of creating PostgreSQL.AspNet.Identity.EntityFramework, I found that Npgsql now has better Entity Framework 6 support. It can now auto create the database. The GitHub repo has a demo that take advantage of the auto database creation.

  • There is a new MvcMusicStore project on GitHub that’s targeting ASPNET 5 (vNext). It uses the ASPNET Identity and Entity Framework 7. Note: EntityFramework 7 currently only has provider for  SQL Server, SQLite, and InMemory provider. Npgsql also has an EF7 experimental provider.

Advertisements

10 thoughts on “Using PostgreSQL with Entity Framework in ASP.NET MVC

  1. The MVCMusicStore project uses two database. One is the MVCMusicStore. The conversion to postgresql is covered in the blog post. The other is the ASPNETDB which AspNet Identity depends on. It appears you are running into a problem with this. To use ASPNET identity with postgresql, you can take a look at this: https://github.com/danellos/AspNet.Identity.PostgreSQL%5B^]
    Note: I took a quick look at the source. It’s using ado.net with npgsql not EF.

  2. Hello,
    I’m on a traineeship and i have to create a web site using ASP.NET MVC 4 with postgresql..I managed the connection to postgres using your demo(Thanks a lot). but I don’t seem to fin a way to use mvc membership with postgres, can you help me with that please ??!
    Best regards

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