How to configure DbContext from a NpgSqlConnection

Question

I have a postgres database on a remote machine which has a postgres server, that I connect to using SshClient

using (var client = new SshClient("<public ip>", <port>, "<myuser>", "<mypassword>"))
{
    client.Connect();

    var port = new ForwardedPortLocal("localhost", 5432, "10.0.0.5", 5432);
    client.AddForwardedPort(port);

    port.Start();

    using (var conn = new NpgsqlConnection("Server=localhost;Database=<secret>;Port=5432;User Id=postgres;Password=<secret>;"))
    {
        conn.Open();
    }

    port.Stop();
    client.Disconnect();
}

Can I create a DbContext from the NpgsqlConnection object? I ask because I´d like to run scaffolding and all the other regular stuff, queries, commands etc

Answer 1

Short answer, no. You are mixing data access technologies. The type NpgsqlConnection is used when you are wanting to use the ADO.NET Technology. Entity Framework is another technology you can use for data access, and it uses the type DbContext. You need to decide what technology to use.

My recommendation, if you are wanting to generate the Entities and perform Linq to SQL queries, is to use Entity Framework. Entity Framework provides tools you can use for scaffolding the Database, and documentation for the Entity Framework Core toolset can be found here. After installing the Npgsql.EntityFrameworkCore.PostgreSQL nuget package you can run the following Scaffold command:

Scaffold-DbContext "Host=my_host;Database=my_db;Username=my_user;Password=my_pw" Npgsql.EntityFrameworkCore.PostgreSQL

I hope this helps