为什么实体框架核心ORM为PostgreSQL生成错误的查询？

Question

我正在写一个web应用程序。我的申请资料如下：

PostgreSQL

• ORM：

• 语言:C#

• 数据库:C#实体框架Core

• Framework: NET 6

• 应用程序类型: Web

在我对其他表的查询中，ORM工作得很好，即使使用JSONB格式也是如此。但是，当我对表WareHouse进行查询时，出现了一个异常。

我使用LINQ的查询代码是：

List<WareHouse> wareHouses = db.WareHouses.AsNoTracking()
                                .Where(x => x.SellerID == dataFuture_Product.SellerID)
                                .ToList();

使用WareHouse表的对象映射是：

public class WareHouse
    {
        [Key]
        public Guid ID { get; set; }
        public Guid SellerID { get; set; }

        public string Name { get; set; }
        public string Code { get; set; }

        
        public string GoogleMapEmbedUrl { get; set; }
        
        public string PhoneNumber { get; set; }
        public string Fax { get; set; }

        public string Facebook { get; set; }
        public string Instagram { get; set; }
        public string Zalo { get; set; }
        public string Email { get; set; }

        public bool isActive { get; set; }

        
        public DateTime DateCreate { get; set; }
        public DateTime DateModified { get; set; }
        public bool isDelete { get; set; }

        [Column("Localization", TypeName = "jsonb")]
        public Localization Localization { get; set; }
        public string SpecificAddress { get; set; }

        [NotMapped]
        public WareHouse_Product WareHouse_Product { get; set; }

    }

使用Localization表的对象映射是：

public class Localization
    {
        [Key]
        public Guid ID { get; set; }


        public string Code { get; set; }

        public Guid? ParentID { get; set; }
        public int Level { get; set; }

        public DateTime DateCreate { get; set; }
        public DateTime DateModify { get; set; }
        public bool isDelete { get; set; }

        [NotMapped]
        public Localization localization { get; set; }

        [NotMapped]
        public List<LocalizationTranslation> LocalizationTranslations { get; set; }
    }

使用LocalizationTranslation表的对象映射是：

public class LocalizationTranslation
    {
        [Key]
        public Guid ID { get; set; }
        public Guid LocalizationID { get; set; }
        public Guid LanguageID { get; set; }

        public string Name { get; set; }

        public string Title { get; set; }

    }

当我执行上面的命令行时，我会得到以下异常：

Npgsql.PostgresException (0x80004005): 42703: column w.LocalizationID does not exist

POSITION: 132
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|213_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at ProductController.cs:line 213
  Exception data:
    Severity: ERROR
    SqlState: 42703
    MessageText: column w.LocalizationID does not exist
    Hint: Perhaps you meant to reference the column "w.Localization".
    Position: 132
    File: d:\pginstaller_13.auto\postgres.windows-x64\src\backend\parser\parse_relation.c
    Line: 3513
    Routine: errorMissingColumn

上面的例外情况表明，表中没有LocalizationID列。但是，我的对象映射目前没有对该列的描述。

我试过或测试过的方法：

1. 检索EF生成的查询语句，它具有以下内容：

 @__8__locals2_dataFuture_Product_SellerID_0='e816c2e4-98ad-42ee-9b4a-aa37217aa564'
SELECT w."ID", w."Code", w."DateCreate", w."DateModified", w."Email", w."Facebook", w."Fax", w."GoogleMapEmbedUrl", w."Instagram", w."LocalizationID", w."Name", w."PhoneNumber", w."SellerID", w."SpecificAddress", w."Zalo", w."isActive", w."isDelete"
FROM "WareHouse" AS w
WHERE w."SellerID" = @__8__locals2_dataFuture_Product_SellerID_0

--它产生错误了吗？

1. I从对象映射WareHouse中删除以下命令行，并对数据进行查询。但是，我需要使用这个属性

。

[Column("Localization", TypeName = "jsonb")]
        public Localization Localization { get; set; }

我有一个表Localization，其中行可以是另一行的父行。

我的问题出在哪里？怎么修呢？

请帮帮我。

谢谢!

Answer 1

基于属性属性的对象映射转换器似乎存在一个问题：(或Npgsql实体框架核心提供者)在对象映射中没有定义为JSONB类型，而是使用了Fluent API。啊，真灵。很难理解。:(

            modelBuilder.Entity<WareHouse>()
                .ToTable("WareHouse");
            modelBuilder.Entity<WareHouse>()
                .Property(x => x.Localization)
                .HasColumnName("Localization")
                .HasColumnType("jsonb");