80644

Entity Framework 6 - Npgsql - connection string error

Question:

A tricky (and frustrating problem) - perhaps you folks may be clever enough to solve it:

<strong>Problem</strong>

I want to be able to read/write to my database using Entity Frameworks. I have got a simple app rails running on Heroku (straightforward scaffold). I want to connect to this database and manipulate records. The good news is that I can successfully connect to that database using npgsql. The bad news is that I cannot do it with Entity Frameworks. This is the error I’m receiving:

<blockquote>

System.Data.Entity.Core.ProviderIncompatibleException: 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. ---> System.Data.Entity.Core.ProviderIncompatibleException: The provider did not return a ProviderManifestToken string. ---> System.IO.FileLoadException: Could not load file or assembly 'Npgsql, Version=3.1.2.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

</blockquote>

Here is the stack trace:

at Npgsql.NpgsqlServices.GetDbProviderManifestToken(DbConnection connection) at System.Data.Entity.Core.Common.DbProviderServices.GetProviderManifestToken(DbConnection connection) --- End of inner exception stack trace --- at System.Data.Entity.Core.Common.DbProviderServices.GetProviderManifestToken(DbConnection connection) at System.Data.Entity.Utilities.DbProviderServicesExtensions.GetProviderManifestTokenChecked(DbProviderServices providerServices, DbConnection connection) --- End of inner exception stack trace --- at System.Data.Entity.Utilities.DbProviderServicesExtensions.GetProviderManifestTokenChecked(DbProviderServices providerServices, DbConnection connection) at System.Data.Entity.Infrastructure.DefaultManifestTokenResolver.<>c__DisplayClass1.<ResolveManifestToken>b__0(Tuple`3 k) at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory) at System.Data.Entity.Infrastructure.DefaultManifestTokenResolver.ResolveManifestToken(DbConnection connection) at System.Data.Entity.Utilities.DbConnectionExtensions.GetProviderInfo(DbConnection connection, DbProviderManifest& providerManifest) at System.Data.Entity.DbModelBuilder.Build(DbConnection providerConnection) at System.Data.Entity.Internal.LazyInternalContext.CreateModel(LazyInternalContext internalContext) at System.Data.Entity.Internal.RetryLazy`2.GetValue(TInput input) 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) at ge_EntityFrameworkTest.Program.<Test>d__4.MoveNext() in c:\Users\Koshy\Documents\Visual Studio 2013\Projects\Practice\ge-EntityFrameworkTest\ge-EntityFrameworkTest\Program.cs:line 118

Here is my connection string:

NpgsqlConnectionStringBuilder sqlBuilder = new NpgsqlConnectionStringBuilder(); sqlBuilder.Username = user; sqlBuilder.Password = password; sqlBuilder.Host = host; sqlBuilder.Port = Int32.Parse(port); sqlBuilder.Database = database; sqlBuilder.Pooling = true; sqlBuilder.UseSslStream = true; sqlBuilder.SslMode = Npgsql.SslMode.Require; sqlBuilder.TrustServerCertificate = true;

Here is my “Hello world” that I am using to connect and read from my database (from the players table). It is successfully printing: “Lionel Messi” on to the console. Great!

#region connectingAndReadingDatabase using (var conn = new NpgsqlConnection(sqlBuilder.ToString())) { conn.Open(); using (var cmd = new NpgsqlCommand()) { cmd.Connection = conn; // Retrieve all rows cmd.CommandText = "SELECT * FROM players"; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine(reader.GetString(1)); } } } } #endregion

The problem is when I try to use Entity Frameworks. It fails massively with a painful error. I am using exactly the same connection string, and cannot for the life of me work out where I’m going wrong. Perhaps you may be able to easily spot the problem?

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Text.RegularExpressions; using System.Threading.Tasks; using System.Net.Http; using Newtonsoft.Json; using Npgsql; using System.Data.Entity; using System.Data.Common; using System.ComponentModel.DataAnnotations.Schema; using System.ComponentModel.DataAnnotations; using System.Configuration; using System.Data.Entity.ModelConfiguration.Conventions; // Here is the code pertaining to my hello world entity framework example: [Table("players", Schema = "public")] public class Player { [Key] [Column("id")] public int id { get; set; } [Column("name")] public string Name { get; set; } [Column("team")] public string Team { get; set; } public Player() { } } class NpgsqlConfiguration : System.Data.Entity.DbConfiguration { public NpgsqlConfiguration() { SetProviderServices ("Npgsql", Npgsql.NpgsqlServices.Instance); SetProviderFactory ("Npgsql", Npgsql.NpgsqlFactory.Instance); SetDefaultConnectionFactory (new Npgsql.NpgsqlConnectionFactory ()); } } [DbConfigurationType(typeof(NpgsqlConfiguration))] public class PlayerContext : DbContext { public PlayerContext(DbConnection connection): base(connection, true) { } public DbSet<Player> Players { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>(); //modelBuilder.Conventions.Add<CascadeDeleteAttributeConvention>(); modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); modelBuilder.HasDefaultSchema("public"); base.OnModelCreating(modelBuilder); } }

And here is my app.config file

<?xml version="1.0" encoding="utf-8"?> <configuration> <configSections> <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 --> <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> <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework"> <parameters> <parameter value="v12.0" /> </parameters> </defaultConnectionFactory> <providers> <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" /> <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, EntityFramework6.Npgsql" /> </providers> </entityFramework> <runtime> <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> <dependentAssembly> <assemblyIdentity name="Npgsql" publicKeyToken="5d8b90d52f46fda7" culture="neutral" /> <bindingRedirect oldVersion="0.0.0.0-3.1.0.0" newVersion="3.1.0.0" /> </dependentAssembly> </assemblyBinding> </runtime> <system.data> <DbProviderFactories> <add name="Npgsql Data Provider" invariant="Npgsql" description="Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql" /> </DbProviderFactories> </system.data> <connectionStrings> <add name="PlayerContext" connectionString="Username=hjanadgkizjmgf;Password=password;Host=ec2-54-235-250-156.compute-1.amazonaws.com;Port=5432;Database=deek4ap6cf2a1;Pooling=true;Use SSL Stream=True;SSL Mode=Require;TrustServerCertificate=True;" providerName="Npgsql" /> </connectionStrings> </configuration>

When I pass in a connection string directly - the same one which worked so well to retrieve records earlier, I get this curious exception:

<blockquote>

“Keyword not supported ‘username’” – obviously referring to the connection string passed in.

</blockquote> using (var db = new PlayerContext(sqlBuilder.ToString())) { // etc }

Also curiously, I receive a warning before compiling:

<blockquote>

“Warning 1 Found conflicts between different versions of the same dependent assembly that could not be resolved. These reference conflicts are listed in the build log when log verbosity is set to detailed. pg-EF-test2” perhaps it’s something to do with Npgsql?

</blockquote>

Any assistance would be much appreciated.

Answer1:

Looks like the "EntityFramework6.Npgsql" nuget package in the current version has incorrectly defined dependencies. It lists "Npgsql (>= 3.1.0)" as a dependency but it actually requires Npgsql in version 3.1.2 or higher.

So the fix is simple - just update the Npgsql package to the latest version. "Update-Package Npgsql" should do the trick.

And as for the context constructor with a string parameter - you got a weird exception because that constructor expects you to pass the <strong>name</strong> of the connection string from your config file. You should use it like this:

using (var db = new PlayerContext("PlayerContext")) { }

Recommend

  • Underlying Provider Failed on Open
  • How to configure EF Code First to not map a specific type?
  • System.NotSupportedException: Unable to determine the provider name for provider factory of type �
  • mvx:Warning: Exception masked ArgumentException: An item with the same key has already been added
  • EF: LINQ - orderby using child collection with condition - ArgumentException
  • How do I create a core data entry with a queryable array property?
  • Grouping results “fairly” using LINQ
  • How to apply paging specifically to a list which is part of a JSON using asp.net OData protocol
  • How do I combine three observables such that
  • Dynamic LINQ, Select function, works on Enumerable, but not Queryable
  • How to Unit Test GetNewValues() which contains EntityFunctions.AddDays function
  • How to apply a filter on LINQtoSQL results?
  • ExpressionTree rewrite - The parameter 'x' is not in scope
  • Take all items in sub-categories using LINQ
  • LINQ GroupBy Count
  • Linq Error \"Could not find an implementation of the query pattern for source type 'Syste
  • ZF2 Doctrine: When to flush ObjectManager
  • Does Microsoft chatbot(Node.js) support multiple language in the single LUIS.AI application?
  • Reusing try catch for wcf call
  • JPA - getting distinct value from one column
  • How can I train an intent in Watson conversation to accept any number?
  • WCF Data Services Entity Framework
  • Modify a Google App Engine entity id?
  • JPA/EclipseLink Returning No Results
  • Can't connect Entity Framework to local SQL Server Express
  • Full 8 bit adder, illogical output
  • JPA CDI Injecting DAO into an Entity
  • Neo4j Cypher query performance optimization
  • How to know which Linq statement produced the SQL on hand during runtime?
  • Symfony2 - Doctrine - no changeset in post update
  • Exception creating JSON with LINQ
  • Doctrine/Symfony entity generator and generating entity from one table
  • Linq Merge lists
  • Why cepheus don't send int without quotes to orion?
  • GAE: Way to get reference to an HttpSession from its ID?
  • Force show.bind execution
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • How do you join a server to an Active Directory (domain)?
  • How does Linux kernel interrupt the application?