Skip to main content

Using Entity Framework 6 with Npgsql 2.1.0

UPDATE (2014-05-19): Marek Beneš noticed a problem in the default connection factory config. It is fixed now. Thanks, Marek!

UPDATE (2014-02-20): I created a new post explaining how to get Npgsql 2.1.0.

Although this post is about EF 6, I'd like to talk about our current situation to support both EF 6 and EF4.x which explain why there are some subtle changes between EF 4.x and EF 6.x App.config settings. 

Support for EF versions 4.x and 6.x


Sometime after we started to work on Npgsql 2.1.0, we started to add code to support EF6 and decided to reorganize our Entity Framework support code. Shay created a pull request to organize this change and isolate the EF code out of core Npgsql code. The result was the creation of two separated assemblies:

  • Npgsql.EntityFramework.dll for EF6 and above;
  • Npgsql.EntityFrameworkLegacy.dll for EF4.x.

Only when using Npgsql with EF6 you will need to reference Npgsql.EntityFramework.dll assembly. This is needed because the EF system has to find it in order to use it.

When using EF 4.x, you won't need to reference Npgsql.EntityFrameworkLegacy.dll. Npgsql will load it using reflection. You just need to make sure the assembly is located at the same place as Npgsql.dll. With this approach, using EF 4.x with 2.1.0 will be the same way as using with previous versions of Npgsql.

In order to facilitate how to configure Npgsql 2.1.0 to work with EF6, I'm pasting* below an App.config template which you can use as a starting point(1).

Configuration settings for EF 6


<xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework,
 Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <entityFramework>
    <providers>
      <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, Npgsql.EntityFramework"></provider>
    </providers>
    <defaultConnectionFactory type="Npgsql.NpgsqlConnectionFactory, Npgsql" />
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant="Npgsql" />
      <add name="Npgsql Data Provider" invariant="Npgsql" support="FF" description=".Net Framework Data Provider for Postgresql" 
 type="Npgsql.NpgsqlFactory, Npgsql" />
    </DbProviderFactories>
  </system.data>
</configuration>

*Sorry for the bad formatting :(

The most important change from previous configuration files is the provider entry which had its type changed from Npgsql.NpgsqlFactory to Npgsql.NpgsqlServices as well as the location of this type which used to be in the Npgsql assembly and now it is located at the Npgsql.EntityFramework assembly.

With those changes, you will be able to use Npgsql 2.1.0 with EF6 without any problem.

Let me know in the comments if you have any problem with those directions.

In a later post I'll show a step by step on how to use Npgsql 2.1.0 with EF 6 and some exciting features we are working on: Support for EFMigration and Database creation and Returning DB generated values on insert for EF

---------------------------------

1. Another option to have those configurations set up is to install the Nuget Npgsql.EntityFramework package. Shay added a script which configures the App.config file and add the needed entries in order to make it work with Npgsql.

Comments

Anonymous said…
thanx , it is a good development
Piotr Trychta said…
This comment has been removed by the author.
Piotr Trychta said…
SSL no work
Protocol=3;SSL=true;SslMode=Require;

An unhandled exception of type 'System.Data.Entity.Core.ProviderIncompatibleException' occurred in EntityFramework.dll

Additional information: An error occurred while getting provider information from the database. This can be caused by Entity Framework using an incorrect connection string. Check the inner exceptions for details and ensure that the connection string is correct.

InnerException:
{"The provider did not return a ProviderManifestToken string."}
{"Failed to establish a connection to 'my host...'."}
Hi, Piotr Trychta!

Can you report this error in our issues section on github? This way we can track this it. Thanks in advance.
Antônio Neto said…
Aqui está dando esse erro:


Additional information: Failed to set Database.DefaultConnectionFactory to an instance of the 'Npgsql.NpgsqlFactory, Npgsql' type as specified in the application configuration. See inner exception for details.
Hello, Antônio.

Can you post the inner exception? This would give us more information about the problem you are having.

Thanks in advance.
alexacron said…
at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, StackCrawlMark& stackMark)
at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
at System.Activator.CreateInstance(Type type, Object[] args)
at System.Data.Entity.Internal.AppConfig.<.ctor>b__3()
at System.Lazy`1.CreateValue()
at System.Lazy`1.LazyInitValue()
at System.Data.Entity.Internal.AppConfig.TryGetDefaultConnectionFactory()
at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.GetServiceFactory(Type type, String name)
at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.<>c__DisplayClass1.b__0(Tuple`2 t)
at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
at System.Data.Entity.Infrastructure.DependencyResolution.AppConfigDependencyResolver.GetService(Type type, Object key)
at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.<>c__DisplayClass3.b__0(IDbDependencyResolver r)
at System.Linq.Enumerable.WhereSelectArrayIterator`2.MoveNext()
at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
at System.Data.Entity.Infrastructure.DependencyResolution.ResolverChain.GetService(Type type, Object key)
at System.Data.Entity.Infrastructure.DependencyResolution.CompositeResolver`2.GetService(Type type, Object key)
at System.Data.Entity.Infrastructure.DependencyResolution.DbDependencyResolverExtensions.GetService[T](IDbDependencyResolver resolver)
at System.Data.Entity.Internal.LazyInternalConnection.Initialize()
at System.Data.Entity.Internal.LazyInternalConnection.get_ProviderName()
at System.Data.Entity.Internal.LazyInternalContext.get_ProviderName()
at System.Data.Entity.Internal.DefaultModelCacheKeyFactory.Create(DbContext context)
at System.Data.Entity.Internal.LazyInternalContext.InitializeContext()
at System.Data.Entity.Internal.InternalContext.Initialize()
at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)
at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()
at System.Data.Entity.Internal.Linq.InternalSet`1.get_InternalContext()
at System.Data.Entity.Infrastructure.DbQuery`1.System.Linq.IQueryable.get_Provider()
at System.Linq.Queryable.Select[TSource,TResult](IQueryable`1 source, Expression`1 selector)
alexacron said…
The comment above was the inner details of the exception
"Failed to set Database.DefaultConnectionFactory to an instance of the 'Npgsql.NpgsqlFactory, Npgsql' "

As I checked in the source it has aprivate constructor, which is good, but I don't have a clue why is it called
Marek Beneš said…
This comment has been removed by the author.
Marek Beneš said…
Hi,
you have bug in provided config file.
In the defaultConnectionFactory element, there shouldn't be Npgsql.NpgsqlFactory but Npgsql.NpgsqlConnectionFactory. This change resolves problem mentioned above.
Td said…
This comment has been removed by the author.
Td said…
Just want to share how I've wasted few hours of my life...

First I've installed from nuget:
1) Entity framework 6.1.1
2) Npgsql 2.1.3
3) Npgsql for Entity framework 2.1.3

Then I've tried to use app.config provided in the post. It didn't work for me. I was trying to fix it for few hours without any luck.

Later I just restored type in defaultConnectionFactory element, which previous commenter recommended to change. After that it worked fine!

Just in case, providing my element:
defaultConnectionFactory type="Npgsql.NpgsqlFactory, Npgsql"
Hi, Td! Thanks for your comment.

I updated the original post with Merek's comments. Did you try this most updated version or a previous version from this article?

ab.normal said…
This comment has been removed by the author.
ab.normal said…
Hello, I'm trying to get this thing to work, but I'm having some problems. The error i get is:

Failed to set Database.DefaultConnectionFactory to an instance of the 'Npgsql.NpgsqlConnectionFactory, Npgsql'

I'm using Npgsql 2.1.3

My config file looks like the one posted above.

I would appreciate any help!

Thanks!

Alberto Ramos said…
Hello. I follow all steps here

https://github.com/npgsql/Npgsql/wiki/Visual-Studio-Design-Time-Support---DDEX-Provider

But still in step 8 doesn't show
[PostgreSQL Database] at Change Data Source.

What is the step making that option available?
otr214426 said…
INTERNATIONAL CONCEPT OF WORK FROM HOME
Work from home theory is fast gaining popularity because of the freedom and flexibility that comes with it. Since one is not bound by fixed working hours, they can schedule their work at the time when they feel most productive and convenient to them. Women & Men benefit a lot from this concept of work since they can balance their home and work perfectly. People mostly find that in this situation, their productivity is higher and stress levels lower. Those who like isolation and a tranquil work environment also tend to prefer this way of working. Today, with the kind of communication networks available, millions of people worldwide are considering this option.

Women & Men who want to be independent but cannot afford to leave their responsibilities at home aside will benefit a lot from this concept of work. It makes it easier to maintain a healthy balance between home and work. The family doesn't get neglected and you can get your work done too. You can thus effectively juggle home responsibilities with your career. Working from home is definitely a viable option but it also needs a lot of hard work and discipline. You have to make a time schedule for yourself and stick to it. There will be a time frame of course for any job you take up and you have to fulfill that project within that time frame.

There are many things that can be done working from home. A few of them is listed below that will give you a general idea about the benefits of this concept.

Baby-sitting
This is the most common and highly preferred job that Women & Men like doing. Since in today's competitive world both the parents have to work they need a secure place to leave behind their children who will take care of them and parents can also relax without being worried all the time. In this job you don't require any degree or qualifications. You only have to know how to take care of children. Parents are happy to pay handsome salary and you can also earn a lot without putting too much of an effort.

Nursery
For those who have a garden or an open space at your disposal and are also interested in gardening can go for this method of earning money. If given proper time and efforts nursery business can flourish very well and you will earn handsomely. But just as all jobs establishing it will be a bit difficult but the end results are outstanding.

Freelance
Freelance can be in different wings. Either you can be a freelance reporter or a freelance photographer. You can also do designing or be in the advertising field doing project on your own. Being independent and working independently will depend on your field of work and the availability of its worth in the market. If you like doing jewellery designing you can do that at home totally independently. You can also work on freelancing as a marketing executive working from home. Wanna know more, email us on workfromhome.otr214426@gmail.com and we will send you information on how you can actually work as a marketing freelancer.


Internet related work
This is a very vast field and here sky is the limit. All you need is a computer and Internet facility. Whatever field you are into work at home is perfect match in the software field. You can match your time according to your convenience and complete whatever projects you get. To learn more about how to work from home, contact us today on workfromhome.otr214426@gmail.comand our team will get you started on some excellent work from home projects.


Diet food
Since now a days Women & Men are more conscious of the food that they eat hence they prefer to have homemade low cal food and if you can start supplying low cal food to various offices then it will be a very good source of income and not too much of efforts. You can hire a few ladies who will help you out and this can be a good business.

Thus think over this concept and go ahead.
Dave Paterson said…
This comment has been removed by the author.
Dave Paterson said…
Awsum piece of coding, just got my first code first database on PostgresQL :)

I did however have to correct the line:

from

defaultConnectionFactory type="Npgsql.NpgsqlConnectionFactory, Npgsql"

to

defaultConnectionFactory type="Npgsql.NpgsqlConnectionFactory, Npgsql.EntityFramework"

This is simple once you realise the class has moved assembly.
Natalie said…
this is very useful informative sharing thanks for this ....
free download | free software download
Carlos Amaral said…
Olá Francisco.
Estou querendo para com Java e tentando entrar no mundo dotNet.
Tenho que começar um projeto, e o BD será Postgres.
Tentei seguir vários tutoriais aqui, mas não consigo fazer funcionar.

Ambiente : Windows 7 64, VS 2013 Community, Postgres 9.3.5

Pelo NuGet, instalei o Npgsql.EntityFramework, que já baixou todas as dependências (Npsql e EntityFramework 6.0.0.0).
Criei um aplicação console e estou tentando acessar um BD e uma tabela existentes mas da retorna o seguinte erro :
An unhandled exception of type 'System.ArgAn unhandled exception of type 'System.ArgumentException' occurred in System.Data.dll

Additional information: O formato da cadeia de inicialização não está de acordo com a especificação iniciada no índice 0.

Meu App.config está assim :






















alguma idéia do que pode estar errado
costlules said…
you dont need to know all this !
all you need to do with us is to click on links and we will pay you for this !
http://www.paiddailytoclick.com/
José de Paz said…
Hola, gracias Francisco Figueiredo por esta excelente herramienta.

Estoy utilizando lo siguiente:

Postgresql Server 9.0.18
Npgsql DDEX (2.2.5)
Visual Studio Prof 2013 Sp4


Tengo la siguiente función:


CREATE OR REPLACE FUNCTION "uspLogin"(
pass character varying,
users character varying)
RETURNS integer AS
$BODY$
DECLARE RETORNO integer;
BEGIN
SELECT COUNT(*) INTO RETORNO FROM "USUARIO" WHERE IDUSER=$2 and CLAUSR=$1;
RETURN RETORNO;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION "uspLogin"(character varying, character varying)
OWNER TO postgres;


Y cuando realizo el mapeo desde Visual Studio, la generación de las tablas se realiza de forma exitosa, pero para las funciones me muestra el siguiente mensaje:


Error 6046: Unable to generate function import return type of the store function 'uspLogin'. The store function will be ignored and the function import will not be generated.


Quisiera conocer la forma de realizar la importación de forma correcta.

Saludos y gracias
Jan Rou said…
Dear Francisco Jr.,


What do I not understand?
I have installed the Nuget packages Npgsql, Entity Framework 6.1.3 and Npgsql Entity Framework to my project in the free VS2015 Community edition. I add ADO.Net to the project. First step is to choose DB first. In second step I can't make a connection to the postgress SQL server only mS SQL servers. What am I doing wrong?

Hope someone can help


Regards
Jan Rou
Derek Harmon said…
Jan Rou,

It sounds like you may not have edited your "app.config" to match what is in the blog article, and removed the LocalDb default connection factory for SQL Server.

Thereafter, from contributions by Marek Benes and Dave Paterson in the comments thread, you need to change the defaultConnectionFactory/@type attribute.

For the Npgsql v3.21 bits on an EF6 project, I had to change mine to

<defaultConnectionFactory type="Npgsql.NpgsqlConnectionFactory, EntityFramework6.Npgsql" ...

Regards,
Derek Harmon
Cara Derek e Francisco Jr, no meu caso, consigo adicionar as classe normalmente, mas quando vou "ADD();" algo no Banco ele me retorna um erro.

o que devo estar fazendo de errado ?

Obs. EF6

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!