Trying to connect to PostgreSQL with Entity Framework via Npgsql and C#

Question

So, I'm new to databases and only have done a little with PostgreSQL before. I have C# (visual studio 2012) and have downloaded the Entity framework (6). I have also downloaded the latest Npgsql data connection driver and used this tutorial:

Using Entity Framework 6 with Npgsql 2.1.0

Now I have of course installed PostgreSQL and referenced the two dll's in my C# project:

npgsql.dll and npgsql.entityframework.dll

Now I need to know how to add, view, delete stuff via C# but I can't find any material on using C#, Npgsql and Entity Framework together.

With no material on using Npqsql and Entity Framework 6, I have instead found this tutorial for version 5:

Create Entity Data Model in Entity Framework 5.0

However, under choose your data connection, when I add a new connection, there is no option to use Npqsql. I really am confused and have been trying to control PostgreSQL via C# for about a week now, I have tried almost every link I can find on Google and am close to giving up. A lot of tutorials show how to drive PostgreSQL directly via Npgsql without using Entity Framework, but I really wanted to use the Entity Framework because I've been told it makes manipulating the database much easier.

Answer 1

I recommend not using Entity Framework with anything else other than SQLSERVER. And then LINQ to SQL may be better. lightspeed was recommended to me as a better product. I personally have not used it.

Answer 2

you can follow the tutorial in this link in-order to use PostgreSQL under C# and .net framework http://www.codeproject.com/Articles/30989/Using-PostgreSQL-in-your-C-NET-application-An-intr

for managing the database side you can use aquafold (in my opinion one of the best database management for PostgreSQL check the following link http://www.aquafold.com/), PGAdmin http://www.pgadmin.org/ or EMS http://www.sqlmanager.net/ . those database management program will help you a lot if you want to make the management of the database using GUI (easier to use than terminal screen)

Answer 3

I solved this issue using a non-free ORM http://www.devart.com/dotconnect/postgresql/articles/tutorial_ef.html

Answer 4

U have to use command line if you want to do it in database first approach and if you want to do as code first then make sure you install all the necessary packages this is how your csporj should look after all package installation

    <Project Sdk="Microsoft.NET.Sdk.Web">
      <PropertyGroup Label="Globals">
    <SccProjectName>Svn</SccProjectName>
    <SccProvider>SubversionScc</SccProvider>
    <SccAuxPath>Svn</SccAuxPath>
    <SccLocalPath>Svn</SccLocalPath>
    <TypeScriptToolsVersion>2.3</TypeScriptToolsVersion>
  </PropertyGroup>
 <PropertyGroup>
    <TargetFramework>netcoreapp2.0</TargetFramework>
    <!--<PackageTargetFallback>$(PackageTargetFallback);portable-net45+win8+wp8+wpa81;</PackageTargetFallback>-->
    <UserSecretsId>99526e66-801d-4816-b800-279edbf6d971</UserSecretsId>
  </PropertyGroup>

  <PropertyGroup Condition="'$(Configuration)|$(Platform)'=='Debug|AnyCPU'">
    <PlatformTarget>AnyCPU</PlatformTarget>
  </PropertyGroup>
   <PropertyGroup Condition="'$(Configuration)|$(Platform)'=='Release|AnyCPU'">
    <PlatformTarget>x64</PlatformTarget>
  </PropertyGroup>
   <ItemGroup>
    <PackageReference Include="BCrypt.Net-Core" Version="1.4.0" />
    <PackageReference Include="Microsoft.ApplicationInsights.AspNetCore" Version="2.1.1" />
    <PackageReference Include="Microsoft.AspNetCore" Version="2.0.1" />
    <PackageReference Include="Microsoft.AspNetCore.Authentication.Cookies" Version="2.0.1" />
    <PackageReference Include="Microsoft.AspNetCore.Cors" Version="2.0.1" />
    <PackageReference Include="Microsoft.AspNetCore.Identity" Version="2.0.1" />
    <PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="2.0.1" />
    <PackageReference Include="Microsoft.AspNetCore.Mvc" Version="2.0.1" />
    <PackageReference Include="Microsoft.AspNetCore.ResponseCompression" Version="2.0.1" />
    <PackageReference Include="Microsoft.AspNetCore.Server.IISIntegration" Version="2.0.1" />
    <PackageReference Include="Microsoft.AspNetCore.Session" Version="2.0.1" />
    <PackageReference Include="Microsoft.AspNetCore.StaticFiles" Version="2.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer.Design" Version="1.1.5" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.1" />
    <PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="2.0.0" />
    <PackageReference Include="Microsoft.VisualStudio.Web.BrowserLink" Version="2.0.1" />
    <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.0.1" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.0.0" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.Design" Version="1.1.1" />
    <PackageReference Include="System.Xml.XmlDocument" Version="4.3.0" />
  </ItemGroup>

  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="1.0.1" />
<DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.0.0" />
  </ItemGroup>



  <ItemGroup>
    <Folder Include="bin\Debug\netcoreapp1.1\" />
    <Folder Include="Data\" />
    <Folder Include="Views\Temp\" />
    <Folder Include="wwwroot\css\" />
    <Folder Include="wwwroot\images\" />
  </ItemGroup>

  <ItemGroup>

    <None Include="Views\Shared\Error.cshtml" />
    <None Include="Views\Shared\_Layout.cshtml" />

    <None Include="Views\_ViewImports.cshtml" />
    <None Include="Views\_ViewStart.cshtml" />
  </ItemGroup>

</Project>