Why Entity Framework Core ORM generating wrong query for PostgreSQL?

Question

I am writing a web application. My application information is as follows:

Language: C#
Database: PostgreSQL
ORM: Entity Framework Core
Framework: NET 6
Application type: Web API

In my queries to other tables ORM works great, even with JSONB format. However I got an exception when I made a query to the table [WareHouse]

My query code with LINQ is:

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

Object Mapping with [WareHouse] table is:

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

    }

Object Mapping with [Localization] table is:

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

Object Mapping with [LocalizationTranslation] table is:

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

    }

When I execute the above command line, I get the following exception:

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

The above exception says I don't have a [LocalizationID] column in the table. However, my object mapping currently has no description for this column.

Ways I have tried or tested:

Retrieves the query statement that EF generates, it has the following content:

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

It's generating wrong?

I removed the following command line from Object mapping [WareHouse] and it queried the data. However I need to use this attribute

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

I have a table [Localization] where the rows can be the parent of another row.

Where does my problem happen? How to fix it?

Please help me.

Thank you!

@SvyatoslavDanyliv it is saying [LocalizationID] column is missing. However in my object mapping [WareHouse] there is no this column. So why is it asking in a [LocalizationID] column in the [WareHouse] table? — Amino Trần
– Amino Trần, Commented Dec 31, 2021 at 18:24

Amino Trần · Accepted Answer · 2022-01-01 14:15:30Z

0

It seems that the object mapping converter according to the attribute of the properties has a problem.(Entity Framework Core or Npgsql Entity Framework Core Provider) Instead of defining as JSONB type in object mapping, I used Fluent API. It worked. It's hard to understand. :(

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

answered Jan 1, 2022 at 14:15

Amino Trần

591 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why Entity Framework Core ORM generating wrong query for PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related