Using EFCore with Postgres

If you are wanting to work with EF Core with Postgres, you may run into a problem where the casing of your tables may cause issues for running SQL queries against the data. In this post, we’ll walk through using the Clean Architecture demo and setting it up for Postgres. We will show you the painful problem we ran into and how we fixed it.

If you aren’t familiar with the casing names, we cover naming conventions in this blog post

Prerequisites

For this demo, we are running:

• .NET 7
• Postgres 15

Install the Ardalis/CleanArchitecture Template

If you don’t have the template installed, you can install it via the dotnet CLI with the following command:

dotnet new -i Ardalis.CleanArchitecture.Template

Create a new project

Once the template is installed, you can create a solution using this template. This command will create a solution folder with the scaffolding for Clean Architecture. Do not use dashes in your solution folder name.

dotnet new clean-arch -o Your.Application

Configure Postgres with EF Core

As the Clean Architecture template uses EF Core, you need to add a reference to a library that brings together EF Core and Postgres. Npgsql is the Postgres driver for .NET. There is a second package for connecting EF core to Postgres. In the Infrastructure project, add Nuget references to Npgsql and Npgsql.EntityFrameworkCore.PostgreSQL.

You also need to update StartupSetup.cs. Change from UseSqlite to UseNpgsql. Your code will look like this:

 services.AddDbContext<AppDbContext>(options =>
          options.UseNpgsql(connectionString));

Update connection string handling

In the Web project, you’ll need to add a connection string to appsettings.json with a Postgres connection string. Make note of the key that you use for your connection string.

Our connection string section in appsettings.json looks like this:

"ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\v11.0;Database=cleanarchitecture;Trusted_Connection=True;MultipleActiveResultSets=true",
    "SqliteConnection": "Data Source=database.sqlite",
    "PostgresConnection": "Server=localhost;Database=postgres;Port=5432;User Id=postgres;Password=demo@secret123;Ssl Mode=Require"
  }

Once the connection string is added to app settings, navigate to Program.cs. On the line that starts string? connectionString, change "SqliteConnection" to the key of your connection string.

Run the application

At this point, you can run the Web project. It will add the data to the database via the call to SeedData.Initialize() in Program.cs.

If you want to try things out, check out the Swagger for the endpoints. If you run the GET endpoint for Contributors, you should get responses for 2 contributors - Ardalis and Snowfrog.

Look at the tables in database tools

You might use Pgadmin, DataGrip, Azure Data Studio with the Postgres extension, or some other tool to view the data and query it directly via SQL. Notice that the Clean Architecture demo created three tables:

• Contributors
• Projects
• ToDoItems

Query the data

While in your data tool, try running the following SQL query:

SELECT * FROM Contributors;

You will see that this returns the following error:

ERROR: relation "contributors" does not exist

Wait! Why is “contributors” in lowercase?

It’s a Postgres gotcha - unless things are in quotes, Postgres will lowercase the query.

What does that mean? What do we have to do to get that query to work?

Try this query:

SELECT * FROM "Contributors";

Notice that this works. If we want only the name field, that query would be:

SELECT "Name" FROM "Contributors";

Our demo has small tables. But what if you were working with a table with many fields and needed to escape those columns? What if your business analysts had to do that while querying the database with their tools such as Power BI? That gets to be painful quickly.

Let’s get rid of these tables and show you how to create the tables with lowercased naming conventions. Run these SQL statements to reset your database:

DROP TABLE "ToDoItems";
DROP TABLE "Projects";
DROP TABLE "Contributors";

Make life easier with EFCore.NamingConventions

It would be annoying to have to write queries with quotes around the table names and field names. Thankfully, the EFCore.NamingConventions package has the ability to use snake casing or lowercasing without us having to do a lot of rewrite.

1. Add a reference to the EFCore.NamingConventions package in the Infrastructure project.
2. Apply the casing statement to StartupSetup.cs.

If you want to use snake casing, your configuration will look like this:

 services.AddDbContext<AppDbContext>(options =>
          options.UseNpgsql(connectionString).UseSnakeCaseNamingConvention());

If you want lowercasing, your configuration will look like this:

 services.AddDbContext<AppDbContext>(options =>
          options.UseNpgsql(connectionString).UseLowerCaseNamingConvention());

Rerun the application

Try running the GET endpoint for Contributors and notice the same results.

Now, look at the database within your database tools. You should see that the table names are lowercased.

If you drill down into the columns for the contributors table, notice that the columns are also lowercased.

Conclusion

By default, when you use EFCore with Postgres, it will use the casing of your objects. However, this can make it painful for those who have to run SQL queries against the data, as they would have to use quotes around table names and field names. Bring in EFCore.NamingConventions to use a lowercased-friendly naming convention to help make it easier for others to query the data with SQL.