Using PostgreSQL with Entity Framework in ASP.NET MVC

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

    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.

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

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

2. Update the entityFramework element as follows:
   XML

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

3. Add the system.data element as follows:
   XML

   <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

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

Finally

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

CodeProject