C# LINQ for Npgsql

Question

I have been searching the internet extensively and have found no clear path for using LINQ queries on Npgsql Objects. I want to get away from using the laborious:

using (var connection = new NpgsqlConnection(ConnectionString))
        {
            using (var adapter = new NpgsqlDataAdapter(query, connection))
            {
                connection.Open();
                var command = adapter.SelectCommand;
                command.Parameters.Add(param1);
                command.Parameters.Add(param2);
                var dataSet = new DataSet();
                adapter.Fill(dataSet);
                //... Do something with the dataset
            }
        }

I would love it if I could do something LINQ-esque like:

var pendingBalances = from c in customers
                      where c.Balance > 100
                      select new { c.FirstName, c.LastName };

Not only is it easier/shorter to write, it is way easier to read for debugging and helping future developers who have to read it. Please tell me this is possible? If so, what object do I use in the place of "customers" to run the query against? It needs to be some sort of DbContext right? Thanks for the help!

Answer 1

You should use EF Core or EF 6 ORM that have Npgsql providers. Here's a link