Raw SQL in Postgres with EntityFramework Core

Question

Say I have a customer table and would like to make a query with raw SQL. The below code does not work:

List<Customer> customers = _db.Customer.FromSql("SELECT * FROM Customer").ToList();

It fails with the error code

'42P01: relation "customer" does not exist'

Answer 1

In certain situations Postgres will produce case sensitive table names. This can happen if you use helper programs to create the database, like SQL Manager. This affects how you have to reference the table. You need to add quotes around the table names like so: "Customer".

You may need to include the schema as well. This should work:

List<Customer> customers = _db.Customer.FromSql("SELECT * FROM \"public\".\"Customer\"").ToList();

Assuming your schema name is "public". Otherwise insert your schema name instead.