时间戳

首页 > 解决方案 > 如何解决实体框架 5 中蛇盒 id 列上的错误“errorMissingColumn”？

c# - 如何解决实体框架 5 中蛇盒 id 列上的错误“errorMissingColumn”？

问题描述

我正在构建一个使用 DotNet 5.0、JsonAPIDotNetCore 和 EntityFramework 访问 PostgreSQL 数据库的 API。我已经用 dotnet-ef 生成了我的模型和我的 DbContext，因为我正在做数据库优先（在不久的将来数据库会发生变化）。当我启动 API 并尝试访问资源时，API 在调试控制台中响应错误 500 并显示此错误：

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

数据库

Jobs我试图访问的表：

柱子 类型 校对 可空的 默认
id_job 大整数 不为空 始终作为身份生成
插入日期 带时区的时间戳 不为空
小路 ltree 不为空
姓名 字符变化（260） 不为空

指数 ：

"Jobs_pkey" PRIMARY KEY, btree (id_job)

引用者：

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

注意，我使用的是蛇形大小写，被证明是一个postgresql 命名约定

模型

然后我使用由 Npgsql EF Core 提供程序记录的命令生成我的DbContext. 这是带有注释的模型：

[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; }
}

数据库上下文

这里是模型构建器的摘录（使用 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);
    }

服务配置

最后，在服务配置中，我添加了 DbContext 以及Npgsql EF Core 提供程序文档中提出的连接字符串和命名约定：

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

CsProj

这是 csproj 文件：

<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>

经过一些实验，似乎 Entity Framework 解析了一个 snake_case 列名"id_something"或. 这是实体框架的正常行为吗？如何在不影响数据库命名约定的情况下更改它？"something_id""id"

标签： c#postgresqlentity-framework.net-corejson-api

解决方案

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.

推荐阅读