Monday, June 17, 2013

Initial EF-6 support added to Npgsql

In my last post, I said there is a pull request by Pēteris Ņikiforovs to add support for EF-6 to Npgsql. Yesterday, I merged this pull request to the master branch of Npgsql.

With this merge, Npgsql has officially initial support for EF-6!

How to compile

For now, in order to compile Npgsql to use EF-6, you have to open the solution file NpgsqlEF6.sln. Later, as suggested by Pēteris Ņikiforovs, the idea is that we create a new configuration inside main project solution instead of maintain 2 separated projects.

Another thing you will need to compile Npgsql is the latest release of EntityFramework assembly through NuGet:

PM> Install-Package EntityFramework -Pre

That's it! Now you will be able to play with Npgsql and EF-6. Check out my previous post about how to use Npgsql with EntityFramework.

I'd like to thank Pēteris Ņikiforovs for his patch. And maxbundchen for his patch about Open/Close events needed for EF-6.

Please, give it a try and let me know how it works for you.

42 comments:

Malcolm Jerry Leung said...

When I try to download Npgsql.EF6 2.0.12-pre4 on NuGet, it can not install to my project and the error message is about my target run time is .NET 4.0. Does it only support .Net 4.5 ?

Francisco Figueiredo Jr. said...

The solution Npgsql2010EF6.sln has a target of .Net 4.5. It should work on 4.0 if you change the target though.

Are you getting this error when you try to add the NpgsqlEF62010.csproj project to your project? If so, you should open the project separately and change its target to .net 4.0

I'll fix that in the future when I integrate the Npgsql-EF6 support into the main project file. Sorry for this inconvenience.

I hope it helps.

Malcolm Jerry Leung said...

Thank you for reply.
Should I put the "#define ENTIIES6" in the first line of every file that used "#if ENTITIES6
using System.Data.xxx
..............." etc ?

Francisco Figueiredo Jr. said...

No. You should add this to your project properties. Vs.net will set the define automatically for you when compiling your code. To do that:
Right click your project, Properties -> Build - > Conditional compilation symbols.

I hope it helps.

Malcolm Jerry Leung said...

Hello, how can fix this 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 extend from this class and the 'Instance' member must return the Singleton instance of the provider.

Malcolm Jerry Leung said...

I am sure I have added ENTITIES6 in Conditional compilation symbols..
And compile no problem, but when it runs and try to get data from postgre db, it occur ......'Npgsql.NpgsqlFactory......did not return an object that inherits from 'System.Data.Entity.Core.Common.DbProviderServices'.

清风 said...

Hello,I use ef6 beta1, and use NpgsqlEF6.sln generate Npgsql.dll (2.0.12.91), and perform the following work:

1.GAC
cd C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\NETFX 4.0 Tools
.\gacutil -I "C:\Mono.Security.dll"
.\gacutil -I "C:\Npgsql.dll"
.\gacutil -I "C:\policy.2.0.Npgsql.dll"
.\gacutil -I "C:\EntityFramework.dll"
.\gacutil -I "C:\EntityFramework.SqlServer.dll"

cd C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\Bin\NETFX 4.0 Tools
.\gacutil -I "C:\Mono.Security.dll"
.\gacutil -I "C:\Npgsql.dll"
.\gacutil -I "C:\policy.2.0.Npgsql.dll"
.\gacutil -I "C:\EntityFramework.dll"
.\gacutil -I "C:\EntityFramework.SqlServer.dll"


2.Modify the machine.config
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config

Add



However, the use
C:\Windows\Microsoft.NET\Framework\v4.0.30319\EdmGen.exe
To create solid, always following error:

-------------------------------------------------------------------------------
error 7001: A null was returned after calling the 'GetService' method on a store
provider instance of type 'Npgsql.NpgsqlFactory'. The store provider might not
be functioning correctly.

Generation Complete -- 1 errors, 0 warnings
-------------------------------------------------------------------------------

What should I do?
Thank you very much!

清风 said...

In machine.config,

add name="Npgsql Data Provider" invariant="Npgsql" support="FF" description=".Net Framework Data Provider for PostgreSQL Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.12.91, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"

That error always appears, help! %> _ <%

Malcolm Jerry Leung said...

public object GetService(Type serviceType)
{
#if ENTITIES
if (true || serviceType == typeof(DbProviderServices))
return NpgsqlServices.Instance;
else
#endif
return null;
}
==========================================================================
The parameter must is 'System.Data.Entity.Core.Common.DbProviderServices'.

Francisco Figueiredo Jr. said...

Hi! Could you send me a sample project where you are getting this error? Also, what is the full command you use when running edmgen.exe?

Malcolm Jerry Leung said...

Sorry, my fault, its all OK now, thanks!

Malcolm Jerry Leung said...
This comment has been removed by the author.
Malcolm Jerry Leung said...

We need add

<providers>
<provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, Npgsql" />
</providers>

Francisco Figueiredo Jr. said...

Could you explain the steps to make it work ok so others saying this post can use it as a reference?

Thanks in advance.

Francisco Figueiredo Jr. said...

Great! Thanks!

清风 said...

Hello everyone,
Malcolm Jerry Leung, thank you very much, your method also solves my problem (although edmgen.exe not support EF6, but with npgsql finally able to use the windows inside EF6)
However, new problems come, my project is going to deploy the Linux + Mono 3.0.12,
But in this context the following error message, even though I used the EF6 Mono git source code to compile a npgsql (mono git above EF6 is alpha2), also reported this error
Francisco Figueiredo Jr. Will be able to publish a mono 3.0.x-based version of npgsql for EF6 do? Thank you!

清风 said...

Message:
The requested feature is not implemented.
StackTrace:
at System.Reflection.AssemblyName.ReferenceMatchesDefinition (System.Reflection.AssemblyName reference, System.Reflection.AssemblyName definition) [0x00000] in :0
at System.Data.Entity.Core.Metadata.Edm.DefaultAssemblyResolver.ResolveAssembly (System.Reflection.AssemblyName referenceName) [0x00000] in :0
at System.Data.Entity.Core.Metadata.Edm.DefaultAssemblyResolver.TryResolveAssemblyReference (System.Reflection.AssemblyName refernceName, System.Reflection.Assembly& assembly) [0x00000] in :0
at System.Data.Entity.Core.Metadata.Edm.MetadataArtifactLoaderCompositeResource.ResolveAssemblyName (System.String assemblyName, System.Data.Entity.Core.Metadata.Edm.MetadataArtifactAssemblyResolver resolver) [0x00000] in :0
at System.Data.Entity.Core.Metadata.Edm.MetadataArtifactLoaderCompositeResource.CreateResourceLoader (System.String path, ExtensionCheck extensionCheck, System.String validExtension, ICollection`1 uriRegistry, System.Data.Entity.Core.Metadata.Edm.MetadataArtifactAssemblyResolver resolver) [0x00000] in :0
at System.Data.Entity.Core.Metadata.Edm.MetadataArtifactLoader.Create (System.String path, ExtensionCheck extensionCheck, System.String validExtension, ICollection`1 uriRegistry, System.Data.Entity.Core.Metadata.Edm.MetadataArtifactAssemblyResolver resolver) [0x00000] in :0
at System.Data.Entity.Core.Metadata.Edm.MetadataArtifactLoader.Create (System.String path, ExtensionCheck extensionCheck, System.String validExtension, ICollection`1 uriRegistry) [0x00000] in :0
at System.Data.Entity.Core.Metadata.Edm.MetadataCache.SplitPaths (System.String paths) [0x00000] in :0
at System.Data.Entity.Core.Common.Utils.Memoizer`2+c__AnonStorey14D[System.String,System.Collections.Generic.List`1[System.Data.Entity.Core.Metadata.Edm.MetadataArtifactLoader]].<>m__159 () [0x00000] in :0
at System.Data.Entity.Core.Common.Utils.Memoizer`2+Result[System.String,System.Collections.Generic.List`1[System.Data.Entity.Core.Metadata.Edm.MetadataArtifactLoader]].GetValue () [0x00000] in :0
at System.Data.Entity.Core.Common.Utils.Memoizer`2[System.String,System.Collections.Generic.List`1[System.Data.Entity.Core.Metadata.Edm.MetadataArtifactLoader]].Evaluate (System.String arg) [0x00000] in :0
at System.Data.Entity.Core.Metadata.Edm.MetadataCache.GetOrCreateMetdataArtifactLoader (System.String paths) [0x00000] in :0
at System.Data.Entity.Core.EntityClient.EntityConnection.GetMetadataWorkspace () [0x00000] in :0
at System.Data.Entity.Core.Objects.ObjectContext.RetrieveMetadataWorkspaceFromConnection () [0x00000] in :0
at System.Data.Entity.Core.Objects.ObjectContext..ctor (System.Data.Entity.Core.EntityClient.EntityConnection connection, Boolean isConnectionConstructor, System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory objectQueryExecutionPlanFactory, System.Data.Entity.Core.Common.Internal.Materialization.Translator translator, System.Data.Entity.Core.Query.InternalTrees.ColumnMapFactory columnMapFactory, IDbCommandInterceptor commandInterceptor) [0x00000] in :0
at System.Data.Entity.Core.Objects.ObjectContext..ctor (System.Data.Entity.Core.EntityClient.EntityConnection connection) [0x00000] in :0
at System.Data.Entity.Internal.InternalConnection.CreateObjectContextFromConnectionModel () [0x00000] in :0
at System.Data.Entity.Internal.LazyInternalConnection.CreateObjectContextFromConnectionModel () [0x00000] in :0
at System.Data.Entity.Internal.LazyInternalContext.InitializeContext () [0x00000] in :0
at System.Data.Entity.Internal.InternalContext.Initialize () [0x00000] in :0
at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType (System.Type entityType) [0x00000] in :0
at System.Data.Entity.Internal.Linq.InternalSet`1[PandaSystem.DataBase.Models.OrderManager.t_client].Initialize () [0x00000] in :0

清风 said...

at System.Data.Entity.Internal.Linq.InternalSet`1[PandaSystem.DataBase.Models.OrderManager.t_client].get_InternalContext () [0x00000] in :0
at System.Data.Entity.Infrastructure.DbQuery`1[PandaSystem.DataBase.Models.OrderManager.t_client].System.Linq.IQueryable.get_Provider () [0x00000] in :0
at System.Linq.Queryable.FirstOrDefault[t_client] (IQueryable`1 source) [0x00000] in :0
at PandaSystem.BO.Controllers.OrderManager.ClientAcountAPIController.Login (System.String email, System.String password) [0x00000] in :0

Francisco Figueiredo Jr. said...

Hi, 清风

I will create Npgsql EF6 for Mono 3.x


Do you get this "feature not implemented" error when running edmgen or when running your app in Mono 3.x?

清风 said...

Hello, Francisco Figueiredo Jr,

Error "feature not implemented" in mono 3.0.12 of mvc4 (. Net 4.0) site, and edmgen has not been able to use (at present does not seem to support the ef-6).

GLane said...

when u r going to release 2.0.13 stable release.... because when we try out beta version with visual studio 2012, end-up with errors...we cannot even see npgsql data provider inside providers list....This one is pretty urgent, so Please reply..... Thank you

罗乐 said...

hi Francisco: Im a coder in china, and i want to make our own interface to support EF, but I dont know how. So if there is any suggestion, please let me know. Thanks very much.

boogieman said...

Hello, please explain how to use NpgsqlFactory.Instance.GetService
i got null object when using it with parameter typeof(DbProviderServices).
When i decompile source of NpgsqlFactory i see "if" statement with typeof(DbProviderServices), but it didn't work.
Thanks for answer

boogieman said...
This comment has been removed by the author.
KU said...

I'm actively using Npgsql's EF6 support to realize WPF data binding.

Any chance to have support for DbFunctionCommandTree within NpgsqlServices.TranslateCommandTree?

I have to use traditional id generator. It is built as a PostgreSQL function.

The function new_seq_id:
---
CREATE OR REPLACE FUNCTION new_seq_id()
  RETURNS character varying AS ...
---

The edmx summary:
---
<edmx:Edmx ...>
  <edmx:Runtime>
    <edmx:StorageModels>
      <Schema ...>
        <Function Name="new_seq_id" StoreFunctionName="new_seq_id" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="public" />
      </Schema ...>
    </edmx:StorageModels>
    <edmx:ConceptualModels>
      <Schema ...>
        <EntityContainer ...>
          <FunctionImport Name="new_seq_id" ReturnType="Collection(String)"/>
        </EntityContainer>
      </Schema ...>
    </edmx:ConceptualModels>
    <edmx:Mappings>
      <Mapping Space="C-S" ...>
        <EntityContainerMapping ...>
          <FunctionImportMapping FunctionImportName="new_seq_id" FunctionName="KanauModel.Store.new_seq_id"/>
        </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>
</edmx:Edmx>
---

The class summary:
---
  public partial class KanauEntities : ObjectContext
  {
    public ObjectResult<global::System.String> new_seq_id()
    {
      return base.ExecuteFunction<global::System.String>("new_seq_id");
    }
  }
---

It gets ArgumentException from NpgsqlServices.TranslateCommandTree. (passing DbFunctionCommandTree)

I'll help you what I can do.

thanks
kenji uno

KU said...

I could code my own DbFunctionCommandTree support code. It goes well.

However we may need good edmx generator to ease code generation for StoredProcedures/Functions support.
By the way, I'm using VS 2012 Express, so I cannot try EF designer support. Even if it has good generator support.

EdmGen2 can generate edmx (ssdl,csdl,msl) for older ObjectContext support with Npgsql provider.
It also writes Functions support into ssdl.
But we need to write Functions support into msl and csdl in hand work.
Probably ssdl needs to be tweaked for Parameter type issue.

thanks
kenji uno

KU said...

Hi.

I love Npgsql so much. It is significantly good library :)

I've fixed some problems in msl/ssdl of Npgsql, when I'm writing my edmx generator tool.

Here is link for discussion. I've posted the link for new schema files.

https://entityframework.codeplex.com/discussions/468221

thanks
kenji uno

Francisco Figueiredo Jr. said...

That's great. KU!
Please, send your patches as pull request to our github project: https://github.com/npgsql/Npgsql

I'd be glad to add your patches to our codebase.

Thank you very much for your feedback and support.

KU said...

Hi Francisco Figueiredo Jr.

Thank you.

I'll prepare pull request for you and Npgsql asap, about 2 things: schema file correction, and experimental DbFunctionCommandTree support.

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

Hi, I'm receiving the same error malcolm have been getting
"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've added the provider long before this error occured to meand i am pretty stomped, any chance you could help me figure out what is going on if i'll send you my project? (it's a tiny tiny one)

Rehmat Alam said...

I want to know when I am adding a record to the PostgreSql database through Entity Framework the insert query is adding E' preceding every column values

Piotr Trychta said...

I have npgsql 2.1 nad EF6.0.2. My configuration file is:










For above I have error:
Additional information: The 'Instance' member of the Entity Framework provider type 'Npgsql.NpgsqlFactory, Npgsql, Version=2.1.0.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;

Next I replace NpgsqlFactory to NpgsqlServices and I have:
Additional information: The Entity Framework provider type 'Npgsql.NpgsqlServices, Npgsql' registered in the application config file for the ADO.NET provider with invariant name 'Npgsql' could not be loaded. Make sure that the assembly-qualified name is used and that the assembly is available to the running application.

What is wrong?

Piotr Trychta said...

<entityFramework>
<providers>
<provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, Npgsql" />
</providers>
</entityFramework>
<system.data>
<DbProviderFactories>
<add name="Npgsql Data Provider"
invariant="Npgsql"
description="Data Provider for PostgreSQL"
type="Npgsql.NpgsqlServices, Npgsql" />
</DbProviderFactories>
</system.data>

Advanced ASP.NET Training in Udaipur said...

Hi All,

I am trying to connect PostgreSQL 9.3.2 with EF 6.0.2 using Npgsql 2.x.x.
Please suggest me the compatible versions of EF and Npgsql.dll and the working setting in web.config.

Thanks in advance

Bhupendra

Leonidio Borges said...

Bom dia Francisco, tudo bem?

Estou usando o Npgsql.2.0.14.3 com EF6.0.2 ja coum o pre instalado também.

Não estou conseguindo inserir registros com colunas que tem uma sequence (autoincremento).

Segue os codigos.

#db
CREATE TABLE tb_unidade
(
cod_unidade bigint NOT NULL DEFAULT nextval('sq_tb_unidade'::regclass),
cod_pesquisa character varying(3) NOT NULL,
descricao character varying(20) NOT NULL,
CONSTRAINT tbentries_unidade_pkey PRIMARY KEY (cod_unidade)
)..

#poco
[Table("tb_unidade", Schema = "public")]
public class UnidadeEntidade : IEntidadeBase
{

[Key]
[Column("cod_unidade")]
public Int64 Codigo { get; set; }

[Column("cod_pesquisa")]
[MaxLength(3), MinLength(1)]
public string CodPesquisa { get; set; }

[Column("descricao")]
[Required(ErrorMessage="Descrição não foi informada.")]
public string Descricao { get; set; }

}

#ef

1)
var db = OvniContext.CreateContext();
db.UnidadesContext.Add(item);
db.SaveChanges();

2)
var db = OvniContext.CreateContext();
db.Entry(item).State = System.Data.Entity.EntityState.Added;
db.SaveChanges();


em ambos os casos aparece a exception:

A null store-generated value was returned for a non-nullable member 'Codigo'.

O Erro so ocorre para inserções.

Poderia ajudar?

Leonidio Borges said...

Encontrei outra pessoa com o mesmo problema.

https://groups.google.com/forum/#!topic/npgsql-help/97X1QX5WE5k


Terei que baixar o codigo pra ver :(.

像壞人的好人 said...

Hi, Francisco
I have a question for EF6 and new Npgsql.
I used NuGet to install Npgsql & EF6. Then, I added data object and dbcontext manually. And, It's worked fine.
My question is : How can created data object and object context automatically by edmx ?
or it doesn't work ?

Thank you for answer. :)

Bruno Moreira Mota said...

Olá Francisco. Estou com o seguinte erro:

null store-generated value was returned for a non-nullable member 'id'

Estrutura da tabela.

CREATE TABLE tab_emp
(
id serial NOT NULL,
cnpj text,
ie text,
razao text NOT NULL,
fantasia text NOT NULL,
im text,
obs text,
ativo boolean NOT NULL DEFAULT true,
CONSTRAINT prk_emp PRIMARY KEY (id),
CONSTRAINT unc_emp UNIQUE (razao, cnpj, fantasia)
)
WITH (
OIDS=FALSE
);
ALTER TABLE tab_emp
OWNER TO postgres;

-- Index: ind_emp_id

-- DROP INDEX ind_emp_id;

CREATE UNIQUE INDEX ind_emp_id
ON tab_emp
USING btree
(id);


-- Trigger: trg_emp_logs on tab_emp

-- DROP TRIGGER trg_emp_logs ON tab_emp;

CREATE TRIGGER trg_emp_logs
AFTER INSERT OR UPDATE OR DELETE
ON tab_emp
FOR EACH ROW
EXECUTE PROCEDURE trf_reglogs();
ALTER TABLE tab_emp DISABLE TRIGGER trg_emp_logs;

Possiveis causas do erro:

INSERT INTO "public"."tab_emp"("fantasia","razao","cnpj","ie","im","obs","ativo") VALUES (E'asdfasdfasdfasdfasdfasdfas',E'asdfasdfasdfasdfasdfasfasdfas',E'12.013.121/3321-32',E'32165646546546565',NULL,NULL,cast(TRUE as boolean));

SELECT currval(pg_get_serial_sequence('"public"."tab_emp"', 'id')) AS "id";


Sugestão:
INSERT INTO "public"."tab_emp"("fantasia","razao","cnpj","ie","im","obs","ativo") VALUES (E'asdfasdfasdfasdfasdfasdfas',E'asdfasdfasdfasdfasdfasfasdfas',E'12.013.121/3321-32',E'32165646546546565',NULL,NULL,cast(TRUE as boolean)) RETURN "id";

Existe uma causa especifica para o erro?

Bruno Moreira Mota said...

Errata: trocar RETURN por RETURNING

Bruno Moreira Mota said...

Olá Francisco tudo bem, estou com um erro que talvez possa ser um problema do Npgsql. O cenário é o seguinte, minha entidade possui um campo do tipo interval na entidade o campo é mapeado para o tipo TimeSpan na linguagem c#. Quando o EntityFramework gera um insert ele especifica que o tipo correspondente no banco de dados e do tipo time e não interval.

{"ERRO: 22007: sintaxe de entrada é inválida para tipo time: \"10.11:12:00\""}

INSERT INTO "public"."tab_procedim"("nome","realizado","comentario","previsaoexe","horainicial","horafinal","previsao","dataa","obrig","ativo","procedimgrupo","empsyst","empsystvers","ordem","status","colab","colab_alter","emp") VALUES (E'ASDFASDF',NULL,NULL,TIMESTAMP '2014-05-31T09:00:00.0000000',TIMESTAMP '0001-01-01T00:00:00.0000000',TIMESTAMP '0001-01-01T00:01:00.0000000',TIME '10.11:12:00',TIMESTAMP '0001-01-01T00:00:00.0000000',cast(FALSE as boolean),cast(TRUE as boolean),130,23,0,0,11,3,3,7);
SELECT currval(pg_get_serial_sequence('"public"."tab_procedim"', 'id')) AS "id"

Francisco Figueiredo Jr. said...


Hi, all!

Sorry for late response.

To all who are having problems with EF, please, check this post: http://fxjr.blogspot.com.br/2014/02/using-entity-framework-6-with-npgsql-210.html

You will need to try Npgsql 2.1.x


Bruno and Leonidio, in your case, you will need to use our master branch version. Support for what you are doing will be available in 2.2.x version.

像壞人的好人, Currently support for using edmx isn't very good yet. Sorry for that. I'll create a post talking about the current situation with edmx.

poly islam said...

Thanks a bunch for sharing this with all people you actually recognize what you are talking about! Bookmarked. Kindly also discuss with my website.
Email list providers