How to change how EF Core communicates with postgresql db

Question

I am using ASP.NET Core MVC and postgresql to write an application, however when running this piece of code:

public async Task<IActionResult> Index()
{
    return _context.antrenorler != null 
                 ? View(await _context.antrenorler.ToListAsync()) 
                 : Problem("Entity set 'YazlabDbContext.antrenorler' is null.");
}

Entity Framework Core runs this SQL query:

SELECT a.id, a.adsoyad, a."contactInfo", a.experience, a.specialties
FROM antrenorler AS a

on my database but (pgadmin4) keeps saying that the column a.id doesn't exist. Now I know Entity Framework Core prefers SQL Server but I need to have EF Core run queries in a way that is suitable for PostgreSQL.

Database connection is successful. All I need to do is change the query format but I don't know how.

Edit: Here are the exact error messages

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (469ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT a.id, a.adsoyad, a."contactInfo", a.experience, a.specialties
      FROM antrenorler AS a
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'YAZLAB2.YazlabDbContext'.
      Npgsql.PostgresException (0x80004005): 42703: column a.id does not exist

      POSITION: 8
         at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|223_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
        Exception data:
          Severity: ERROR
          SqlState: 42703
          MessageText: column a.id does not exist
          Position: 8
          File: parse_relation.c
          Line: 3661
          Routine: errorMissingColumn
      Npgsql.PostgresException (0x80004005): 42703: column a.id does not exist

Answer 1

You don't need to alias anything on the database. EF doesn't prefer one database over the others, though if you are using Identity for authentication then the generated schema will use PascalCase. It is the provider, in your case NPGSql to build the SQL for the database to execute from the linq expressions.

By default, the SQL generation will expect that the entity property names match the table column names. If your antrenorler table does not contain an "id" column, and instead use something like antrenorlerId, then you have a couple of choices:

1- Update your entity definition to match the table column:

[Key]
public int antrenorlerId { get; protected set; }

2- Tell EF that it should expect a different column name:

[Key, Column("antrenorler_id")]
public int id { get; protected set; }

With PostgreSQL you often encounter issues with snake_case for property names. This can result in needing to specify the name to use in every property. Alternatively you can intercept the property name resolver that handles the conversion back and forth between properties and database columns to handle snake_case rather than using that naming convention in your entities. An example of this approach: (https://andrewlock.net/customising-asp-net-core-identity-ef-core-naming-conventions-for-postgresql/)

Most of the time when I am working with PostgreSQL I'm just using [Column] attributes as it serves as documentation that is easy to reference when comparing the schema and entities rather than "magic" code performing the translation at runtime.