Skip to main content

Npgsql Code First Entity Framework 4.3.1 Sample



After reading the excellent article about entity framework on Postgresql by Brice Lambson, I decided to write this post to document my experience playing with Entity Framework 4.3.1 and Npgsql. This post will be an adaptation of the Code First To a New Database walkthrough in order to make it work with Npgsql. 

First Steps

You should follow the first 4 steps of Code First To a New Database. Go ahead, I''l wait for you.

Next steps

Here is where the adaptation of the walkthrough begins. As Brice noted in his post, Npgsql currently doesn't support database creation. ( I'm working on that and hope to get news about it soon.) So, for while, you have to create the database manually.

Those are the steps you have to do to create the database and the model:

First, run this command in the terminal to create the database (or you can use pgAdmin if you prefer a GUI):

> createdb ef_code_first_sample 

After that, you have to run the following commands inside the database you have just created (to simplify permissions, remember to run this script connected as the same user who is specified in your connection string):

create table "Blog" ("BlogId" serial, "Name" varchar(255));
create table "Post" ("PostId" serial, "Title" varchar(255), "Content" varchar(8000), "BlogId" int);

And here comes the first trick you have to use when working with EF and Npgsql: the table names as well as column names need to be double quoted

Entity Framework generates code with table and column names double quoted and, to Postgresql, using double quotes means you want to preserve the casing of the names. So you need to create the tables with the correct case or else, Postgresql will complain it can't find your tables.

With the database and tables created, let's make some more configuration before we can run the sample.

Entity Framework installation

Unfortunately Npgsql doesn't support EF 5. Currently it supports 4.3.1 and there is a pull request by Pēteris Ņikiforovs to add support for EF 6. Yeah, Npgsql will have support for latest EF version soon!

You will need to install the 4.3.1 version of EF. According to EF Nuget project page, this is done with the following command:

PM> Install-Package EntityFramework -Version 4.3.1

This is needed because if you don't specify the 4.3.1 version, Nuget will install the latest version which isn't supported by Npgsql yet.

Npgsql installation

If you don't have Npgsql installed already, you should install Npgsql from Nuget too:

PM> Install-Package Npgsql

And then you should configure Npgsql in your App.config to be found by the DbProviderFactory API: 

<system.data>
  <DbProviderFactories>
    <add name="Npgsql Data Provider"
          invariant="Npgsql"
          description="Data Provider for PostgreSQL"
          type="Npgsql.NpgsqlFactory, Npgsql" />
  </DbProviderFactories>
</system.data>

and configure your connection string in the same App.config file:

<connectionStrings>
      <add name="BloggingContext"
           providerName="Npgsql"
           connectionString="server=10.0.2.2;userid=npgsql_tests;password=npgsql_tests;database=ef_code_first_sample"/>
    </connectionStrings>

Running the code

Now it's time to run the code. If you have everything configured and hit Ctrl+F5 you should get the code running and will be greeted with the Blog name question.

Unfortunately after answering the question and pressing enter, an exception will be thrown:

Unhandled Exception: System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---
> System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> Npgsql.NpgsqlException: ERROR: 3F000: schema "dbo" does not exist

This error occurs because by default, Entity Framework uses the schema dbo and Postgresql uses the schema public. This is the message you get in the Postgresql log:


INSERT INTO "dbo"."Blogs"("Name") VALUES (E'test');SELECT currval(pg_get_serial_sequence('"dbo"."Blogs"', 'BlogId')) AS "BlogId"

ERROR: schema "dbo" does not exist at character 13

As pointed out by Brice in his answer to a user question about this error, you have to tell Entity Framework to use a different schema. This is done by using Data Annotations and adding a Table attribute to the classes of the model:

[Table("Blog", Schema = "public")]
public class Blog

and

[Table("Post", Schema = "public")]
public class Post

To use those attributes, you have to import the Data Annotations namespace:

using System.ComponentModel.DataAnnotations;

More information about that can be found in the Code First To a New Database article linked at the beginning of this post.

That's it! After making those changes, you should now get the data correctly inserted in the database and everything should work ok:

LOG: statement: INSERT INTO "public"."Blog"("Name") VALUES (E'test');SELECT currval(pg_get_serial_sequence('"public"."Blog"', 'BlogId')) AS "BlogId"


I hope you enjoyed this post and could get started to Entity Framework and Npgsql. Please, let me know what you think in your comments. 

I'd like to thank Brice Lambson for his excellent article and the Microsoft Entity Framework team for their Code First To a New Database walkthrough and all the EF stuff.

Comments

Michael Randall said…
Thank you for this post.

You have me on the right track, but I am still receiving this error message:

ERROR: 3F000: schema "dbo" does not exist

I added [Table("RuleSet", Schema = "public")] to my model class.

And voila, if I mouse over "db.RuleSets" in the following code, I can see that "dbo." has been changed to "public." before the tablename in the query.

using (var db = new PostgresContext())
{
var data = (from a in db.RuleSets
where a.Name == ruleSetInfo.Name
select a).SingleOrDefault();

more code...
}

However, even though it looks like the right query is being sent to the database I still get the dbo error. How is this possible?
This is very strange.

Are you able to get the query which generates this error?

EF framework generates two queries when starting the application which use the dbo schema. But they don't prevent your application from working.

If you are running your code under vs.net, the debugger will still stop you about this exception but you can safely continue your app.

I'll have to investigate more how to change the first queries. They are about migration which Npgsql still doesn't support.

I hope it helps.
Michael Randall said…
I can get a query, and that query has "public" instead of "dbo" and it runs perfectly in pgAdmin.

The app throws the same exception whether I am in debug or running it straight.

Thank you for any further assistance you can provide. If I need to put money in a paypal account for your help, I will be happy to.

Hi, Michael.

Is it possible for you to send me your project so I can give it a try?

You don't need to send all your entities, just the one which is giving you problems. I'll try to reproduce the error here and see what is happening.
Daniel Summers said…
Francisco,

First - Awesome! I found this, and have now gotten EF Code First implemented in a couple of different applications. I'm seeing something strange, though, so I thought I'd ask you about it.

Say I have a class like this:

public class MyTest
{
public int ID { get; set; }
public string Name { get; set; }
}

Then, I have a mapping that assigns ID as the primary key: (omitting some declarations)

this.HasKey(x => x.ID);
this.Property(x => x.ID)
.IsRequired()
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
this.Property(x => x.Name)
.IsRequired()
.HasMaxLength(50);
this.ToTable("MyTest", "theschema");

Then, given this table:

create table theschema."MyTest" (
"ID" serial not null,
"Name" varchar(50) not null,
primary key "ID"
);

When I try to retrieve data from the table, I'll get an error that Extent1."MyTest_ID" does not exist.

Why is it trying to fetch that column when I already have defined the primary key? If I add this as a "dummy" column in the table, everything seems to work correctly. I'm using EF 4.3.1 and Npgsql 2.0.12.1.

Thanks!


Daniel
Daniel Summers said…
I've determined that it's actually using that MyTest_ID column to do joins between tables, even though I have the navigation properties set to the ID property. I tried renaming the ID column to MyTestId, thinking that maybe that I was just being a bit too clever. Well, at the point, Extent1."MyTest_MyTestId" was the column that was not found.

This is not how the documentation seems to describe these links being put together. Any idea why? Since I've determined that it's using them for joins, just adding dummy columns doesn't seem to be a good solution.

I wondered if I could map the ID field with the "MyTest_ID" column. So, when I changed the column in the database, and added

.HasColumnName("MyTest_ID")

to the ID declaration in my previous post, now the column that can't be found is Extent1."MyTest_ID1". Argh!

I'd appreciate any help you'd be able to give. Am I not defining the primary key correctly?
Daniel Summers said…
I just upgraded to EF 6, and I'm still having the same issue. It must be something with the way I have things defined. I'll keep digging; I'm all ears if you have any other ideas.
Hi, Daniel. Sorry for late response.

I'm glad you enjoyed the article.


About your problem, I'll contact Josh Cooley who added the EF support to Npgsql and ask him if he has any idea why this is happening.

When I get any response I'll let you know.
Daniel Summers said…
Thanks! :) No worries on the time; since I've got it using EF6, I'm going through and converting my calls to Async.
Olá gostaria de saber se você tem referencias de post em portugues para utilização e esclarecimento do Npgsql com Entity Framework CodeFirst e aproveitando sua atenção estou com problema na execução de comando no banco de dados PostgreSql com Entity Framework CodeFirst informações da aplicação em que o erro ocorreu estão descritas abaixo.


// c# Entity
[Table(name: "tab_aluno", Schema = "public")]
public class Aluno
{
[Key]
public int id { get; set; }

public string nome { get; set; }

public bool ativo { get; set; }
}

public class BContext : DbContext
{
public BContext()
{

}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
Database.SetInitializer(null);
modelBuilder.Conventions.Remove();
modelBuilder.HasDefaultSchema("public");
}

public DbSet Alunos { get; set; }
}



// Tabela no Banco

CREATE TABLE tab_aluno
(
id serial NOT NULL,
nome text NOT NULL,
ativo boolean NOT NULL DEFAULT false,
CONSTRAINT prk_aluno PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE tab_aluno
OWNER TO postgres;

// Instrução Linq
using (BContext bContext = new BContext())
{
//bool vari = true;

Expression> filtro = m => m.ativo == true; //vari;

var result = bContext.Alunos.Where(filtro).ToList();


Console.WriteLine("Fim da Execução");
Console.ReadKey();
}


// Detalhes do erro

ERRO: 42703: coluna "etrue" não existe

erroSql:
SELECT "Extent1"."id" AS "id","Extent1"."nome" AS "nome","Extent1"."ativo" AS "ativo" FROM "public"."tab_aluno" AS "Extent1" WHERE ETRUE="Extent1"."ativo"

StackTrace:
at Npgsql.NpgsqlState.d__b.MoveNext() in e:\Dna\Desktop\Postgresql 2.0.13.91\Npgsql2.0.13.91.src\src\Npgsql\NpgsqlState.cs:line 882
at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject() in e:\Dna\Desktop\Postgresql 2.0.13.91\Npgsql2.0.13.91.src\src\Npgsql\NpgsqlDataReader.cs:line 1173
at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription() in e:\Dna\Desktop\Postgresql 2.0.13.91\Npgsql2.0.13.91.src\src\Npgsql\NpgsqlDataReader.cs:line 1191
at Npgsql.ForwardsOnlyDataReader.NextResult() in e:\Dna\Desktop\Postgresql

desde já agradeço pela sua atenção.
Olá, Bruno!

Esse é um problema conhecido do Npgsql na versão 2.0.14. Pegue a versão 2.0.14.3 que está com a correção desse erro aplicada.

Se o erro persistir, por favor, entre em contato novamente para que possamos verificar o que está acontecendo. Mas tenho quase 100% de certeza de que essa nova versão irá resolver o problema.

Obrigado por usar o Npgsql!
ronaldo alvaro said…
Ola Francisco, estou com um problema no qual não consigo resolver. Eu utilizo como Model First, ou seja, um modelo de dados já criado no banco de dados. Eu até consigo através do provider(Trial) baixado criar *.edmx. Mas quando executo meu WCF que por sua vez tenta algo como:
"return context.t_log.SingleOrDefault(a => a.tid == tid);"

Não consigo conectar retornando erros como:

The 'server' keyword is not supported.
The 'password' keyword is not supported.
The 'host' keyword is not supported.

Segue meu web.config:


















E os pacotes utilizados:




Hi
I have an issue using npgsql.
I am using EF 6.1.3 , VS 2015 Enterprise Edition, PostGresql.
I have installed npgsql 3.1.7, EntityFramework6.Npgsql v3.1.1 and Npgsql.EF6 v2.0.12-pre4.
But when i create ADO.net Entity Data model, I get this exception.
"Your project references the latest version of entity framework, however an entity framework database provider compatible with this version could not be found for your data connection. If you have already installed a compatible provider, ensure you have rebuilt your project before performing this action. Otherwise exit this wizard, install a compatible provider and rebuilt your project before performing this action."
Any help would be greatly appreciates.
rakhi karampuri said…
I'm followed your Example But Error Occuring "dbo.Employ" Doesn't Exist
I'm Added table name and schema in my class
[Table("Empoy", Schema = "public")]
public class School
{
[Key]
public int Id { get; set; }
public string EmployName{ get; set; }

}

Popular posts from this blog

Using SSL Client Certificates with Npgsql

Hi all!

Recently, Jarrod Kinsley asked on our Forums how to establish an SSL connection. As Laurenz Albe pointed out, normally you just need to change your connection string to put "SSL=True;Sslmode=Require;" in your connection string and "ssl=on" in postgresql.conf and you are ready to go.

The problem was that this works in the general case where you don't have to deal with client certificates and other stuff. Npgsql has a lot of callbacks to help you to validate and talk to the server.

The last callback added to the chain by Frank Bollack was to provide a way to pass client certificates to server.

Later on the thread, Jennifer Marienfeld was also trying to connect and was stuck in the client certificate part. Jennifer eventually got success to establish connection to the server and I decided to create this post to show the code so others can benefit from this.

Here is Jennifer's code so you all can use as a template:



using System;
using System.IO;
using System.Se…

Npgsql Tips: Using " in (...)" queries with parameters list and "any" operator

Hi, all!

We have received some users questions about how to send a list of values to be used in queries using the "in" operator. Something like:

select foo, bar from table where foo in (blah1, blah2, blah3);

Npgsql supports array-like parameter values and the first idea to have this working would try to use it directly:

NpgsqlCommand command = new NpgsqlCommand("select * from tablee where field_serial in (:parameterlist)", conn);

ArrayList l = new ArrayList();
l.Add(5);
l.Add(6);

command.Parameters.Add(new NpgsqlParameter("parameterlist", NpgsqlDbType.Array | NpgsqlDbType.Integer));

command.Parameters[0].Value = l.ToArray();


NpgsqlDataReader dr = command.ExecuteReader();


but unfortunately this won't work as expected. Npgsql will send a query like this:

select * from tablee where field_serial in ((array[5,6])::int4[])

And Postgresql will complain with the following message:

ERROR: operator does not exist: integer …

Npgsql2 housekeeping

Hi all,

After 2 weeks without being able to play very much with computers due to my RSI problems, I'm finally getting back to normal work. Of course, under a much less workload... :)

Today, I did some housekeeping in Npgsql2 project. I fixed Npgsql.build file which wasn't compiling due to some missing files. Now it is compiling very well with NAnt. Just call nant and it will compile a nice assembly Npgsql.dll for you. I also added MonoDevelop project files and also fixed Npgsql2 version in AssemblyInfo.cs file.

That's it!

I hope to be able to release a preview of Npgsql2 really soon. Stay tuned!