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

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" />

I'm pretty sure the provider will be something like Npgsql, not System.Data.SqlClient, and I'm not sure the Postgres driver supports MARS. Here's an example of a Code-first configuration. — StuartLC
– StuartLC, Commented Apr 11, 2016 at 20:31
@StuartLC That a piece of it all, all the provider names are Npgsql. I'm not sure why its references the System.Data.SqlClient at all. — Kamurai
– Kamurai, Commented Apr 11, 2016 at 21:01

Ram Pukar · Accepted Answer · 2017-05-15 11:07:09Z

2

Microsoft Visual Studio 2017

pm> Install-Package Npgsql -Version 2.2.0
pm> Install-Package EntityFramework

Web.config

<connectionStrings>
    <add name="DefaultConnectionString" connectionString="server=localhost;user id=postgres;password=admin;database=api_db" providerName="Npgsql" />
</connectionStrings>
<system.data>
    <DbProviderFactories>
      <add name="Npgsql Data Provider"
           invariant="Npgsql"
           support="FF"
           description=".Net Framework Data Provider for Postgresql"
           type="Npgsql.NpgsqlFactory, Npgsql" />
</DbProviderFactories>
</system.data>

Model

PGDbContext.cs

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;

namespace PgDemo.Models
{
    public class PGDbContext: DbContext
    {
        public PGDbContext() : base(nameOrConnectionString: "DefaultConnectionString") { }
        public virtual DbSet<Users> Usr { get; set; }
    }
}

Users.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;

namespace PgDemo.Models
{
    [Table("users", Schema = "public")]
    public class Users
    {
        [Key]
        public int id { get; set; }
        public string username { get; set; }
        public string userpass { get; set; }
    }
}

Controller

using PgDemo.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace PgDemo.Controllers
{
    public class HomeController : Controller
    {
        PGDbContext _context;
        public HomeController()
        {
            _context = new PGDbContext();
        }
        public ActionResult Index()
        {
            return View(_context.Usr.ToList());
        }
    }
}

Views

@using PgDemo.Models
@model IEnumerable<Users>
<table border="1">
    @foreach (var item in Model)
    {
        <tr>
            <td>@Html.DisplayFor(modelItem => item.id)</td>
            <td>
                @Html.DisplayFor(modelItem => item.username)
            </td>
            <td>@Html.DisplayFor(modelItem => item.userpass)</td>
        </tr>
    }
</table>

edited May 15, 2017 at 11:07

answered May 13, 2017 at 10:54

Ram Pukar

1,63117 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kamurai Over a year ago

JUST saw this. I will try to play with it some today if possible.

Kamurai Over a year ago

Thank you, but I'm still getting "42P01: relation "dbo.PostgreSQLTests" does not exist" as an error. Problem seems to be that its looking for a SQL Server table: the PostgreSQL is formatted "Test"."PostgreSQLTests";

Dennis Sasse · Accepted Answer · 2018-01-28 15:18:29Z

2

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; }
    }
 }

answered Jan 28, 2018 at 15:18

Dennis Sasse

234 bronze badges

1 Comment

Kamurai Over a year ago

Thank you, I'll reinvestigate this when I have the opportunity.

jlee88my · Accepted Answer · 2016-04-11 21:28:52Z

0

I use this in the web.config:

<connectionStrings>
    <add name="MyDbConnection"  connectionString="server=localhost;port=5432;user id=myUser;password=myPassword;database=myDatabaseName"    providerName="Npgsql" />
</connectionStrings>
<system.data>
    <DbProviderFactories>
        <add name="Npgsql Data Provider" invariant="Npgsql" description="Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql" />
    </DbProviderFactories>
</system.data>
<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
        <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
        <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, EntityFramework6.Npgsql" />
    </providers>
</entityFramework>

Just a friendly reminder to NUGet "Npgsql" and "Npgsql for Entity Framework 6". BOTH the "Npgsql" and the "Npgsql Entity Framework 6" are on version 3.0.4 (please check).

answered Apr 11, 2016 at 21:28

jlee88my

3,0531 gold badge23 silver badges28 bronze badges

2 Comments

Kamurai Over a year ago

web.config settings don't seem to make a difference, but I will double check the versions for Npgsql and Npgsql Entity Framework. I think Npgsql is 3.0.5, but I'll make sure. Thank you.

Kamurai Over a year ago

Yes, both were installed using 3.0.5.

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related