How to solve the error "errorMissingColumn" on a snakecase id column in entity framework 5?

Question

I am building an API which is accessing a PostgreSQL database using DotNet 5.0, JsonAPIDotNetCore and EntityFramework. I have generated my Models and my DbContext with dotnet-ef as I am doing database-first (there will be changes on the database in the near future). When I launch the API and I am trying to access a resource, the API respond an error 500 with this error in the debug console:

Exception data:
    Severity: ERROR
    SqlState: 42703
    MessageText: column j.id doesn't exist
    Position: 56
    File: d:\pginstaller_13.auto\postgres.windows-x64\src\backend\parser\parse_relation.c
    Line: 3514
    Routine: errorMissingColumn

Database

The Table Jobs I'm trying to access:

Column	Type	Collationnement	NULL-able	Default
id_job	bigint		not null	generated always as identity
inserted_date	timestamp with time zone		not null
path	ltree		not null
name	character varying(260)		not null

Index :

"Jobs_pkey" PRIMARY KEY, btree (id_job)

Referenced by :

TABLE ""Versions"" CONSTRAINT "Versions_id_job_fkey" FOREIGN KEY (id_job) REFERENCES "Jobs"(id_job) NOT VALID

Notice, I am using snake_case as proven to be a postgresql naming convention

Model

Then I use the command documented by the Npgsql EF Core provider which generate my DbContext. Here is the model with annotations:

[DisplayName("job")]
[Table("Jobs")]
public partial class Job : Identifiable
{
    public Job()
    {
        Versions = new HashSet<Version>();
    }

    [Attr(PublicName = "id-job")]
    public long IdJob { get; set; }

    [Attr(PublicName = "inserted-date")]
    public DateTime InsertedDate { get; set; }

    [Attr(PublicName = "path")]
    public string Path { get; set; }

    [Attr(PublicName = "name")]
    public string Name { get; set; }

    [Attr(PublicName = "versions")]
    [ForeignKey("id_version")]
    public virtual ICollection<Version> Versions { get; set; }
}

DbContext

And here an extract of the model builder (using Fluent API):

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasPostgresExtension("adminpack")
                    .HasPostgresExtension("ltree")
                    .HasAnnotation("Relational:Collation", "French_France.1252");

        modelBuilder.Entity<Job>(entity =>
            {
                entity.HasKey(e => e.IdJob)
                      .HasName("Jobs_pkey");

                entity.Property(e => e.IdJob)
                      .HasColumnName("id_job")
                      .UseIdentityAlwaysColumn();

                entity.Property(e => e.InsertedDate)
                      .HasColumnType("timestamp with time zone")
                      .HasColumnName("inserted_date");

                entity.Property(e => e.Name)
                      .IsRequired()
                      .HasMaxLength(260)
                      .HasColumnName("name");

                entity.Property(e => e.Path)
                      .IsRequired()
                      .HasColumnType("ltree")
                      .HasColumnName("path");
            });
        
        [...]
        
        OnModelCreatingPartial(modelBuilder);
    }

Services Configuration

Finally, in the services configuration, I have added the DbContext with the connection string and the naming convention as proposed by the Npgsql EF Core provider documentation:

services.AddDbContext<MyDatabaseDbContext>(options =>
    options.UseNpgsql(Configuration.GetConnectionString("MyConnectionString"))
                                   .UseSnakeCaseNamingConvention());

CsProj

Here is the csproj file :

<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="EFCore.NamingConventions" Version="5.0.2" />
    <PackageReference Include="EntityFramework" Version="6.4.4" />
    <PackageReference Include="JsonApiDotNetCore" Version="4.2.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.8">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="5.0.8">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.3" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="5.0.7" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="5.6.3" />
  </ItemGroup>

</Project>

After some experimentations, it seems that Entity Framework parse a snake_case column name "id_something" or "something_id" as "id". Is this a normal behavior for Entity Framework ? How can I change it without compromising on the database naming convention ?

Answer 1

As supposed by Steve Py (Thank you very much), the problem come from JsonApiDotNetCore on the id serializing.

Models

In my Job Model I need to change the name of the ressource Id (here, JobId), for Id which is a property of Identifiable to override. You need to specify the Type of the Id property to the generic interface Identifiable<TId>. So I change those parts of the model:

public partial class Job : Identifiable
{

    [...]

    [Attr(PublicName = "id-job")]
    public long IdJob { get; set; }

    [...]

}

for this:

public partial class Job : Identifiable<long>
{

    [...]

    [Attr(PublicName = "id-job")]
    public override long Id { get; set; }

    [...]

}

I had to rename all occurrences of IdJob in the DbContext.

Controller

Finally, I set my controller, specifying the type of the resource identifier for JsonApiController<TResource, TId> and IResourceService<TResource, TId> as follow:

namespace TalendExplorerAPI.Controllers
{
    public class JobController : JsonApiController<Job, long>
    {

        public JobController(IJsonApiOptions options, ILoggerFactory loggerFactory,
            IResourceService<Job, long> resourceService)
            : base(options, loggerFactory, resourceService)
        {
        }
    

    }
}

If you are using VSCode, you might have two compiler errors on your controller. If this is the case, just reboot the IDE.