Errors on mapping enum types to SQL and storing into database

Open amyboose opened this issue 1 year ago • 0 comments

Example 1:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Npgsql;
using System;
using System.Linq;
using System.Threading.Tasks;

namespace PrimitiveTypes;
public class Program
{
    public static async Task Main(params string[] args)
    {
        MyContext context = new();
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        SimpleModel simpleModel = new SimpleModel()
        {
            ServiceLevel = ServiceLevel.Standart,
            Service = new Service()
            {
                ServiceLevel = ServiceLevel.Standart,
            }
        };

        context.Set<SimpleModel>().Add(simpleModel);
        context.SaveChanges();

        var model = context.Set<SimpleModel>().FirstOrDefault(x => x.Service.ServiceLevel == ServiceLevel.Standart);
    }
}


public class SimpleModel
{
    public int Id { get; set; }
    public ServiceLevel ServiceLevel { get; set; }
    public Service Service { get; set; }
}

public class Service
{
    public ServiceLevel ServiceLevel { get; set; }
}

public enum ServiceLevel
{
    Econom,
    Standart
}

public class MyContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        NpgsqlDataSourceBuilder dataSourceBuilder = new(@"Host=localhost;Port=7435;Database=testdb;Username=admin;Password=testpass");

        dataSourceBuilder
            .MapEnum<ServiceLevel>();

        NpgsqlDataSource dataSource = dataSourceBuilder.Build();

        optionsBuilder
            .UseNpgsql(dataSource)
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .HasPostgresEnum<ServiceLevel>();

        modelBuilder.Entity<SimpleModel>(builder =>
        {
            builder
                .OwnsOne(p => p.Service, b =>
                {
                    b.ToJson();
                    b.Property(p => p.ServiceLevel);
                });
        });
    }
}

An error:

An exception occurred while iterating over the results of a query for context type 'PrimitiveTypes.MyContext'.
      Npgsql.PostgresException (0x80004005): 22P02: invalid input value for enum service_level: "1"
         at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
         at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlDataReader.NextResult()
         at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
         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__21_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()
        Exception data:
          Severity: ERROR
          SqlState: 22P02
          MessageText: invalid input value for enum service_level: "1"
          File: enum.c
          Line: 128
          Routine: enum_in

A request:

SELECT s."Id", s."ServiceLevel", s."Service"
      FROM "SimpleModel" AS s
      WHERE (CAST(s."Service" ->> 'ServiceLevel' AS service_level)) = 'standart'::service_level
      LIMIT 1

A value in json column:

{"ServiceLevel": 1}

As you can see here is integer format.

Example 2:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace PrimitiveTypes;
public class Program
{
    public static async Task Main(params string[] args)
    {
        MyContext context = new();
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        SimpleModel simpleModel = new SimpleModel()
        {
            ServiceLevel = ServiceLevel.Standart,
            Services = 
            [ 
                new Service()
                {
                    ServiceLevels = [ ServiceLevel.Standart ],
                }
            ]
        };

        context.Set<SimpleModel>().Add(simpleModel);
        context.SaveChanges();

        var model = context.Set<SimpleModel>().FirstOrDefault(x => x.Services.Any(x => x.ServiceLevels.Any(y => y == ServiceLevel.Standart)));
    }
}


public class SimpleModel
{
    public int Id { get; set; }
    public ServiceLevel ServiceLevel { get; set; }
    public List<Service> Services { get; set; }
}

public class Service
{
    public List<ServiceLevel> ServiceLevels { get; set; }
}

public enum ServiceLevel
{
    Econom,
    Standart
}

public class MyContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        NpgsqlDataSourceBuilder dataSourceBuilder = new(@"Host=localhost;Port=7435;Database=testdb;Username=admin;Password=testpass");

        dataSourceBuilder
            .MapEnum<ServiceLevel>();

        NpgsqlDataSource dataSource = dataSourceBuilder.Build();

        optionsBuilder
            .UseNpgsql(dataSource)
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .HasPostgresEnum<ServiceLevel>();

        modelBuilder.Entity<SimpleModel>(builder =>
        {
            builder
                .OwnsMany(p => p.Services, b =>
                {
                    b.ToJson();
                });
        });
    }
}

An error:

An exception occurred while iterating over the results of a query for context type 'PrimitiveTypes.MyContext'.
      Npgsql.PostgresException (0x80004005): 22P02: invalid input value for enum service_level: "Standart"
         at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
         at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlDataReader.NextResult()
         at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
         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__21_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()
        Exception data:
          Severity: ERROR
          SqlState: 22P02
          MessageText: invalid input value for enum service_level: "Standart"
          File: enum.c
          Line: 128
          Routine: enum_in

A request:

SELECT s."Id", s."ServiceLevel", s."Services"
      FROM "SimpleModel" AS s
      WHERE EXISTS (
          SELECT 1
          FROM ROWS FROM (jsonb_to_recordset(s."Services") AS ("ServiceLevels" service_level[])) WITH ORDINALITY AS s0
          WHERE EXISTS (
              SELECT 1
              FROM unnest(s0."ServiceLevels") AS j(value)
              WHERE j.value = 'standart'::service_level))
      LIMIT 1

A value in json column:

[{"ServiceLevels": ["Standart"]}]

As you can see here is enum format.

EF Core version: 8.0.7 Database provider: Npgsql.EntityFrameworkCore.PostgreSQL 8.0.4 Target framework: .NET 8.0 Operating system: Windows 11 IDE: e.g. Visual Studio 2022 17.10.2

Jul 17 '24 16:07 amyboose