# Using EFCore with Postgres > How to get around Postgres' auto lowercasing of queries when using EF Core - URL: https://blog.nimblepros.com/blogs/using-ef-core-with-postgres/ - Author: [Sarah Dutkiewicz](https://blog.nimblepros.com/authors/sadukie/) - Published: 2023-04-14 - Categories: [Software Development](https://blog.nimblepros.com/categories/software-development/) - Publisher: [NimblePros](https://blog.nimblepros.com/) --- 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](../identifying-variable-casing-conventions/) ## 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: ```csharp services.AddDbContext(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: ```json "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 ![View of the tables created by the app - Contributors, Projects, and ToDoItems](https://blog.nimblepros.com/blogs/using-ef-core-with-postgres/postgres-tables.png) ## Query the data While in your data tool, try running the following SQL query: ```sql 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: ```sql SELECT * FROM "Contributors"; ``` Notice that this works. If we want only the name field, that query would be: ```sql 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: ```sql 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](https://github.com/efcore/EFCore.NamingConventions) 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: ```csharp services.AddDbContext(options => options.UseNpgsql(connectionString).UseSnakeCaseNamingConvention()); ``` If you want lowercasing, your configuration will look like this: ```csharp services.AddDbContext(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. ![Postgres tables now lowercased - contributors, projects, and todoitems](https://blog.nimblepros.com/blogs/using-ef-core-with-postgres/postgres-tables-lowercased.png) If you drill down into the columns for the contributors table, notice that the columns are also lowercased. ![contributors table with lowercased id and name columns](https://blog.nimblepros.com/blogs/using-ef-core-with-postgres/postgres-table-lowercased-columns.png) ## 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.