October 31, 2012

Entity Framework 5 on PostgreSQL

This walkthrough will get you started with an application that uses the Entity Framework (EF) to read and write data from a PostgreSQL database. It is intended to be similar to the Code First to a New Database walkthrough.

There are currently two PostgreSQL providers for EF that I know of: Npgsql and Devart's dotConnect for PostgreSQL. Devart's provider has a much richer set of features, but it is also a commercial product. In the spirit of FOSS, we will be using the Npgsql provider for this walkthrough. I encourage you to keep the Devart provider in mind, however, if your project requires that extra level of support.

Create the Application

For simplicity, we will be using a Console Application, but the basic steps are the same regardless of project type.
  1. Open Visual Studio
  2. Select File -> New -> Project...
  3. Select Console Application
  4. Name the project
  5. Click OK

Create the Model

For our model, we'll be borrowing pieces from the Chinook Database (a cross-platform, sample database). Specifically, we will be using Artists and Albums.

Add the following two classes to your project.
public class Artist
{
    public Artist()
    {
        Albums = new List<Album>();
    }

    public int ArtistId { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Album> Albums { get; set; }
}

public class Album
{
    public int AlbumId { get; set; }
    public string Title { get; set; }

    public int ArtistId { get; set; }
    public virtual Artist Artist { get; set; }
}

Create a Context

In EF, the context becomes your main entry point into the database. Before we define our context though, we will need to install Entity Framework.
  1. Select Tools -> Library Package Manager -> Package Manager Console
  2. Inside the Package Manager Console (PMC) run Install-Package EntityFramework
Now, add the context class to your project.
class ChinookContext : DbContext
{
    public DbSet<Artist> Artists { get; set; }
    public DbSet<Album> Albums { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Map to the correct Chinook Database tables
        modelBuilder.Entity<Artist>().ToTable("Artist", "public");
        modelBuilder.Entity<Album>().ToTable("Album", "public");

        // Chinook Database for PostgreSQL doesn't auto-increment Ids
        modelBuilder.Conventions
            .Remove<StoreGeneratedIdentityKeyConvention>();
    }
}

Install the Provider

In order to connect to PostgreSQL databases, we will need to install an appropriate ADO.NET and Entity Framework provider. Luckily, the provider we're using is available via NuGet.
  1. Inside PMC, run Install-Package Npgsql
We also need to register the provider. Open App.config, and anywhere inside the configuration element, add the following fragment.
<system.data>
  <DbProviderFactories>
    <add name="Npgsql Data Provider"
          invariant="Npgsql"
          description="Data Provider for PostgreSQL"
          type="Npgsql.NpgsqlFactory, Npgsql" />
  </DbProviderFactories>
</system.data>

Add the Database

Unfortunately, Npgsql does not support creating databases. So instead of letting Code First create our database, we will need to manually add a database to our project. It's a good thing we're using a cross-platform sample database!

When I started writing this, Chinook Database actually wasn't available for PostgreSQL. Fortunately, it's an open source project so I've submitted a pull request that adds support for PostgreSQL. It was released as part of version 1.4.
  1. Download and extract the PostgreSQL version of the Chinook Database
  2. Run CreatePostgreSql.bat
Also, add a connection string to the App.Config that points to the database. Anywhere inside the configuration element, add the following fragment.
<connectionStrings>
  <add name="ChinookContext"
        connectionString=
"Server=localhost;Database=chinook;User Id=postgres;Password=P4ssw0rd;"
        providerName="Npgsql" />
</connectionStrings>

Start Coding

Ok, we should be ready to start coding our application. Let's see what artists exist in the database. Inside Program.cs, add the following to Main.
using (var db = new ChinookContext())
{
    var artists = from a in db.Artists
                  where a.Name.StartsWith("A")
                  orderby a.Name
                  select a;

    foreach (var artist in artists)
    {
        Console.WriteLine(artist.Name);
    }
}
Hmm, it looks like one of my favorite bands is missing. Let's add it.
using (var db= new ChinookContext())
{
    db.Artists.Add(
        new Artist
        {
            ArtistId = 276,
            Name = "Anberlin",
            Albums =
            {
                new Album
                {
                    AlbumId = 348,
                    Title = "Cities"
                },
                new Album
                {
                    AlbumId = 349,
                    Title = "New Surrender"
                }
            }
        });
    db.SaveChanges();
}
We can also update and delete existing data like this.
using (var context = new ChinookContext())
{
    var police = db.Artists.Single(a => a.Name == "The Police");
    police.Name = "Police, The";

    var avril = db.Artists.Single(a => a.Name == "Avril Lavigne");
    context.Artists.Remove(avril);

    db.SaveChanges();
}

Conclusion

Hopefully by now, you have enough information to get started using the Entity Framework with a PostgreSQL database. For many, many more articles on how to use EF, check out our team's official Getting Started page on MSDN.

31 comments :

Mau said...

Hey Brice, thanks for sharing this post.

I followed your steps and ended up with a "schema dbo does not exist" error.

As you advised I created the DB before running the program.

On my infinite wisdom I created a dbo schema and assigned permissions accordingly, on the hope it would work.

But it does not. This is the strack trace, hope you have any clue.

at Npgsql.NpgsqlState.d__a.MoveNext() in C:\projects\Npgsql2\src\Npgsql\NpgsqlState.cs:line 850
at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject() in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:line 1173
at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription() in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:line 1191
at Npgsql.ForwardsOnlyDataReader.NextResult() in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:line 1377
at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean synchOnReadError) in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:line 1040
at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb) in C:\projects\Npgsql2\src\Npgsql\NpgsqlCommand.cs:line 611
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb) in C:\projects\Npgsql2\src\Npgsql\NpgsqlCommand.cs:line 588
at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\Npgsql2\src\Npgsql\NpgsqlCommand.cs:line 538
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

Thanks again.

Brice Lambson said...

@Mau
That stack trace doesn't offer many clues, do you have the output of calling ToString on the exception? This will include any inner exceptions too.

The default schema in PostgreSQL is named "public". You could try mapping each table to that schema instead either by using the Table attribute [Table("MyTable", "public")] on the entity class, or by calling the Fluent API modelBuilder.Entity().ToTable("MyTable", "public") in the override of OnModelCreating on your DbContext-derived class.

Orlando Jr. said...

Hello Brice!
Do what when is needed use a sequence? I'm trying but without success until now. Do you use postgresql professionally with EF Code Fist? How do you control the column increment?
Thanks!

Brice Lambson said...

@Orlando, I do not use PostgreSQL professionally; I am a member of the Entity Framework team. Once you have your sequence set up in the database, you will need to tell EF not to try and insert values into the column. Do this in the OnModelCreating override using the following:

modelBuilder.Entity().Property(e => e.MyProperty).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)

You can also re-enable the StoreGeneratedIdentityKeyConvention convention if all of your integer-based primary key columns have sequences backing them -- that way you won't have to do it for each of these columns.

seba said...

Hey Brice, thanks for this post.

After the dbo schema erros, i create the entire db in the schema dbo. To do that, just insert at the top o the Chinook_PostgreSql.sql file (the run the bat):

/** create schema - begin **/

CREATE SCHEMA dbo
AUTHORIZATION user;

GRANT ALL ON SCHEMA public TO user;
COMMENT ON SCHEMA public
IS 'default EF schema';

SET search_path TO 'dbo';

/** create schema - end **/

After that, starting the aplication, the same throws a NpgsqlException about "dbo.__MigrationHistory table, that not exists".

Any more steps are required to run the sample app?

Thanks for your help.

Steef said...

I tried your example and it works great. If I now want to extend it using the following table:

CREATE TABLE "Person"
(
"PersonId" integer NOT NULL,
"Name" character varying(120),
CONSTRAINT "PK_Person" PRIMARY KEY ("PersonId")
);
ALTER TABLE "Person" OWNER TO postgres;
CREATE INDEX "IFK_PersonPersonId" ON "Person" ("PersonId");

The code I use to access this table is this :
public class Person
{
public Person()
{
Persons = new List();
}

public int PersonId { get; set; }
public string Name { get; set; }
public virtual ICollection Persons { get; set; }
}

As soon as I try to access an item retrieved using a Linq-query I get the following error :

System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> Npgsql.NpgsqlException: ERROR: 42703: column Extent1.Person_PersonId does not exist at Npgsql.NpgsqlState.d__a.MoveNext()

For some reason, the EntityFramework generates a wrong statement but I don't know why.

JJ said...

Hi Brice

Thanks for sharing this post. I could get EF working wit PostGres by following the above steps.

Now I am trying to get custom connection string via code and not via config file. I tried using NpgsqlConnectionStringBuilder.

But I am not able to get through. Can you please share the steps for the same.

Thanks again.

Brad Mathews said...

I too am getting the "schema dbo does not exist" errors. However, I am using a production database so changing my schema name is not an option.

Has anyone gotten this to work with a different schema? And if so, could you throw in me some working sample code?

I see Brice's reply to Mau, but I am still way down the learning curve from understanding how to implement that.

Nguyễn Đình Nhật said...
This comment has been removed by the author.
Anonymous said...

For those experiencing the "dbo" issue like I did, here's the fix.

Add this line to the beginning of "OnModelCreating":
Database.SetInitializer(new Initializer());

Add this class:
public class Initializer : IDatabaseInitializer
{

public void InitializeDatabase(DefaultContext context)
{
if (!context.Database.Exists())
{
context.Database.Create();
Seed(context);
context.SaveChanges();
}
}

private void Seed(DefaultContext context)
{
throw new NotImplementedException();
}
}

Answer came from here: http://stackoverflow.com/questions/11259079/how-can-i-disable-the-use-of-the-migrationhistory-table-in-entity-framework-4

Tonta said...

Is there any possibility how tu enable and run database migrations?
And I have also problem with droping and creating database.
Any advice from pro npgsql user?

Brice Lambson said...

@Tonta, as far as I know, Npgsql has not yet implemented a migrations provider. You might want to look into using DevArt's dotConnect provider.

Anonymous said...

Hi Brice,

Thank you very much for this example, it works great! I just have one question, I'm trying to get the artist's album doing artist.Albums but it throws this exception There is already an open DataReader associated with this Command which must be closed first. Do you have any idea why this happens?

Brice Lambson said...

@Anonymous,

This happens in ADO.NET when you try to use multiple active result sets (or MARS). Some providers support this by enabling it in the connection string; I'm not sure if Npgsql does or not. It happens in EF when you use lazy loading inside of a foreach loop:

// While streaming the results of this query...
foreach (var artist in db.Artists)
{
// ...get results for this query.
var albums = artist.Albums;
}

Francisco Figueiredo Jr. said...

@Anonymous, you can use the Preload Reader=true in your connection string. This way Npgsql will allow multiple resultsets open at the same time.

Francisco Figueiredo Jr. said...

@Brice Lambson, would you mind to point me out where I can find documentation how to add database creation to Npgsql provider?

Thanks in advance.

Brice Lambson said...

@Francisco, To support database creation in a provider, you need to implement DbDatabaseExists, DbCreateDatabase & DbDeleteDatabase on DbProviderServices. To support Code First Migrations, implement the MigrationSqlGenerator class. Our current recommendation is to use the SQL Server Compact provider for reference. The relevant files are SqlCeProviderServices.cs and SqlCeMigrationSqlGenerator.cs. There is also still some useful information on the Entity Framework Sample Provider page. Our team is also always ready to help on the project's discussion forum.

Brice Lambson said...

@Francisco, one other great resource for provider writers that I failed to mention is the EF6 provider model documentation page.

Francisco Figueiredo Jr. said...

@Brice Lambson, thank you very much for your pointers! I'll check out those references and integrate the changes needed in Npgsql.

Alex said...

I am doing database first with postgres and using Npgsql to import my tables into the edmx. However, I was not able to import my views. I get a message that states the view doesn't have a key and cannot be imported. Postgres doesn't allow me to put a key on a view. Is there any work around for importing my views?

sinambela said...

hi...brice...thank u very much for explanation about accessing postgresql using entity framework.

Simple and useful..

Hichem Ben Jeddi said...

Hello
Can you help me please I used Npgsql with entity framework but i have an error and i don't know how can I resolve this problem the error is
ERROR: 42703: column Extent1.ID does not exist

Hichem Ben Jeddi said...

hello
Can you help me please I used Npgsql with entity framwork but i have this error ERROR: 42703: column Extent1.ID does not exist
and i don't know how can i resolve this problem do you have any idea thk very much

Brice Lambson said...

@Hichem Ben Jeddi, You may want to try contacting the Npgsql team.

Homem said...

Hello,
I do this example anda i'm getting the following error:

System.ArgumentException was unhandled by user code
HResult=-2147024809
Message=key=value argument incorrect in ConnectionString
Nome do parâmetro: persist security info
Source=Npgsql
ParamName=persist security info
StackTrace:
em Npgsql.NpgsqlConnectionStringBuilder.GetKey(String key) na C:\projects\Npgsql2\src\Npgsql\NpgsqlConnectionStringBuilder.cs:linha 723
em Npgsql.NpgsqlConnectionStringBuilder.set_Item(String keyword, Object value) na C:\projects\Npgsql2\src\Npgsql\NpgsqlConnectionStringBuilder.cs:linha 800
em System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
InnerException:

הסוחר העובד והלומד said...

Hi,
I know it's an old post but I hope you can help me,
I've tried using your example, did everything to the letter but I receive the following error:
The 'Instance' member of the Entity Framework provider type 'Npgsql.NpgsqlFactory, Npgsql, Version=2.0.12.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7' did not return an object that inherits from 'System.Data.Entity.Core.Common.DbProviderServices'. Entity Framework providers must inherit from this class and the 'Instance' member must return the singleton instance of the provider. This may be because the provider does not support Entity Framework 6 or later;

I know it should support ef6 as per http://fxjr.blogspot.co.il/2013/06/initial-ef-6-support-added-to-npgsql.html
did you happen to make it work or maybe you have any sort of idea what I did wrong?

Ruben said...

Hi Jewish name, instead of Install-Package Npgsql do a -Pre to get a pre-release version. Npgsql has not been updated in more than a year on official channels (only beta channels) so adding changes to the repository did nothing for nuget.

Anonymous said...

I am getting an "ConfigurationErrorsException" with a message 'Unrecognised Element' in this particular line of code
" var artists = from a in Context.Artists where a.Name.StartsWith("A")
orderby a.Name
select a; "

Brice Lambson said...

@Anonymous Check your App/Web.config file -- especially the entityframework section -- to make sure it's correct.

Web development said...

Hi All,

Which Npgsql.dll version is compatible with EF 6.0.2.

If anybody have working example
Please suggest.
Bhupendra

Joe said...

I am getting an exception when trying to use the function db.SaveChanges() and was wondering if there is something that I am missing something

Thanks