asp.net mvc 和 Entity Framework 6 中的 PostgreSQL 数据库连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36558564/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL database connection in asp.net mvc and Entity Framework 6
提问by Kamurai
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.
我正在尝试从我的 ASP.NET MVC 站点连接到 PostgreSQL 数据库。视图和模型的格式适用于 SQL Server 和 MySQL，所以据我所知，我不应该在那里遇到问题。
The application is throwing a System.ArgumentExceptionin the System.Data.dlland returns an error:
该应用程序被扔System.ArgumentException在System.Data.dll并返回一个错误：
Keyword not supported: metadata" "Parameter name: keyword
不支持关键字：元数据" "参数名称：关键字
on the webpage.
在网页上。
That it is using the System.Data.dllseems wrong, but I can't corroborate this.
它使用的System.Data.dll似乎是错误的，但我无法证实这一点。
How can I connect to PostgreSQL in this manner?
如何以这种方式连接到 PostgreSQL？
I have Nuget installed Npgsql 3.0.5 and EntityFramework6.Npgsql 3.0.5.
我有 Nuget 安装了 Npgsql 3.0.5 和 EntityFramework6.Npgsql 3.0.5。
Please let me know if I left off vital information.
如果我遗漏了重要信息，请告诉我。
Relevant web.configinformation is as follows:
相关web.config信息如下：
<add name="PostgreSQLConnectionString"
connectionString="
metadata=res://*/Models.Test.csdl|res://*/Models.Test.ssdl|res://*/Models.Test.msl;
provider=System.Data.SqlClient;
provider connection string="
data source=localhost:5432\;
initial catalog=Test;
integrated security=False;
user id=username;
password=password;
multipleactiveresultsets=True;
App=EntityFramework""
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" />
回答by Joseph Lee
I use this in the web.config:
我在 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).
只是对NUGet“Npgsql”和“Npgsql for Entity Framework 6”的友好提醒。“Npgsql”和“Npgsql Entity Framework 6”都在 3.0.4 版上（请检查）。
回答by Ram Pukar
Microsoft Visual Studio 2017
微软 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
用户.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>
回答by Dennis Sasse
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".
添加@Ram Pukar 的上述答案：确保在模型中定义正确的表名和模式名。Postgres 中的默认架构是“public”，而 MsSQL（和 EF）需要“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.:
如果您使用分隔符分隔的词作为列名，您可以通过向定义中添加列别名来停止 EF 抱怨，例如：
[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; }
}
}
