PostgreSQL database connection in asp.net mvc and Entity Framework 6

Question

PostgreSQL database connection in asp.net mvc and Entity Framework 6

asp.net-mvc entity-framework npgsql postgresql visual-studio

Question

I'm attempting to connect to a PostgreSQL database from my ASP.NET MVC site. The format for the view and model work for both SQL Server and MySQL, so I shouldn't have problems there, as far as I can tell.

The application is throwing a System.ArgumentException in the System.Data.dll and returns an error:

Keyword not supported: metadata" "Parameter name: keyword

on the webpage.

That it is using the System.Data.dll seems wrong, but I can't corroborate this.

How can I connect to PostgreSQL in this manner?

I have Nuget installed Npgsql 3.0.5 and EntityFramework6.Npgsql 3.0.5.

Please let me know if I left off vital information.

Relevant web.config information is as follows:

<add name="PostgreSQLConnectionString" 
     connectionString="
     metadata=res://*/Models.Test.csdl|res://*/Models.Test.ssdl|res://*/Models.Test.msl;
     provider=System.Data.SqlClient;
     provider connection string=&quot;
     data source=localhost:5432\;
     initial catalog=Test;
     integrated security=False;
     user id=username;
     password=password;
     multipleactiveresultsets=True;
     App=EntityFramework&quot;" 
     providerName="Npgsql" />

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

<remove invariant="Npgsql" />
<add name="Npgsql - .Net Data Provider for PostgreSQL" 
     invariant="Npgsql" 
     description=".Net Data Provider for PostgreSQL" 
     type="Npgsql.NpgsqlFactory, Npgsql, Version=3.0.5.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />

Kamurai

View more on Stack Overflow

Dennis Sasse · Accepted Answer · 1/28/2018 3:18:29 PM

Adding to above answer from @Ram Pukar: Make sure you define the correct table- and schema-name in your models. The default schema in Postgres is "public", whereas MsSQL (and EF) expects "dbo".

If you are using delimiter-separated words for column names, you can stop EF complaining by adding column aliases to your definiton, e.g.:

 [Table("your_table", Schema = "your_schema")]
    public class YourModelName
    {
        [Key]
        [Column("id_colum_name")]
        public int Id { get; set; }

        [Column("some_string_colum_name")]
        public string SomeString{ get; set; }
    }
 }

PostgreSQL database connection in asp.net mvc and Entity Framework 6

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related