42883: operator does not exist: tag_control_type = integer after .net 9 update

[clintsinger]

I have updated from .net 8 to .net 9 and all of the packages that come with it but now am running into an issue about an operator not existing.

If I roll back to .net 8 the error goes away so definitely follows the latest .net updates.

BackgroundService failed
Npgsql.PostgresException (0x80004005): 42883: operator does not exist: tag_control_type = integer

POSITION: 107
   POSITION: 107
    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.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
    at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
    at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
    at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
    at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
    at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
Exception data:
  Severity: ERROR
  SqlState: 42883
  MessageText: operator does not exist: tag_control_type = integer
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 107
  File: parse_oper.c
  Line: 647
  Routine: op_error

Additional logs

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (86ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT t0.id
      FROM (
          SELECT DISTINCT t.id, t.type
          FROM tag_control_exception AS t
          WHERE t.type = 1
      ) AS t0
Microsoft.EntityFrameworkCore.Database.Command: Error: Failed executing DbCommand (86ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT t0.id
FROM (
    SELECT DISTINCT t.id, t.type
    FROM tag_control_exception AS t
    WHERE t.type = 1
) AS t0

The database initialization:

create type tag_control_type as enum(
	'organization',
	'tag'
);

create table tag_control_exception(
	id UUID not null primary key,
	type tag_control_type not null
);

Setting up the data source builder:

var dataSourceBuilder = new NpgsqlDataSourceBuilder(options.ConnectionString);
dataSourceBuilder.MapEnum<TagControlType>("tag_control_type");
...

And the model builder:

internal static ModelBuilder ConfigureTags(this ModelBuilder modelBuilder) {
   modelBuilder.HasPostgresEnum<TagControlType>(null, "tag_control_type");
   ...
}

public enum TagControlType
{
    Tag,
    Organization
}

[Rasmus715]

Can confirm. Updating from 8.0.10 to 9.0.2 broke enum mapping & actions with them for me as well.

UPD: Same behavior running 9.0.0

[overbit]

@Rasmus715 @clintsinger you will need to update the way how you register the mapping
Check out https://www.npgsql.org/efcore/mapping/enum.html?tabs=with-external-datasource%2Cwith-datasource#setting-up-your-enum-with-ef

[clintsinger]

Thanks for pointing that out. I haven't had to look at the documentation for some time so it caught me off guard. An analyzer would be awesome in this situation to notify people of these kinds of changes.

That being said, I have run into another issue. I tried both the "connection string" and "data source" configurations but I'm getting the following error:

System.InvalidOperationException: Sequence contains more than one matching element
         at System.Linq.ThrowHelper.ThrowMoreThanOneMatchException()
         at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Func`2 predicate, Boolean& found)
         at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source, Func`2 predicate)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlTypeMappingSource.FindEnumMapping(RelationalTypeMappingInfo& mappingInfo)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlTypeMappingSource.FindMapping(RelationalTypeMappingInfo& mappingInfo)
         at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMappingSource.<>c.<FindMappingWithConversion>b__8_0(ValueTuple`4 k, RelationalTypeMappingSource self)
         at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd[TArg](TKey key, Func`3 valueFactory, TArg factoryArgument)
         at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMappingSource.FindMappingWithConversion(RelationalTypeMappingInfo mappingInfo, Type providerClrType, ValueConverter customConverter)
         at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMappingSource.FindMapping(MemberInfo member, IModel model, Boolean useAttributes)
         at Microsoft.EntityFrameworkCore.Metadata.Internal.MemberClassifier.IsCandidatePrimitiveProperty(MemberInfo memberInfo, IConventionModel model, Boolean useAttributes, CoreTypeMapping& typeMapping)
         at Microsoft.EntityFrameworkCore.Metadata.Conventions.PropertyDiscoveryConvention.IsCandidatePrimitiveProperty(MemberInfo memberInfo, IConventionTypeBase structuralType, CoreTypeMapping& mapping)
         at Microsoft.EntityFrameworkCore.Metadata.Conventions.PropertyDiscoveryConvention.DiscoverPrimitiveProperties(IConventionTypeBaseBuilder structuralTypeBuilder, IConventionContext context)
         at Microsoft.EntityFrameworkCore.Metadata.Conventions.PropertyDiscoveryConvention.ProcessEntityTypeAdded(IConventionEntityTypeBuilder entityTypeBuilder, IConventionContext`1 context)
         at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnEntityTypeAdded(IConventionEntityTypeBuilder entityTypeBuilder)
         at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.OnEntityTypeAddedNode.Run(ConventionDispatcher dispatcher)
         at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.DelayedConventionScope.Run(ConventionDispatcher dispatcher)
         at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ConventionBatch.Run()
         at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ConventionBatch.Dispose()
         at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelInitialized(IConventionModelBuilder modelBuilder)
         at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelInitialized(IConventionModelBuilder modelBuilder)
         at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.OnModelInitialized(IConventionModelBuilder modelBuilder)
         at Microsoft.EntityFrameworkCore.Metadata.Internal.Model..ctor(ConventionSet conventions, ModelDependencies modelDependencies, ModelConfiguration modelConfiguration)
         at Microsoft.EntityFrameworkCore.ModelBuilder..ctor(ConventionSet conventions, ModelDependencies modelDependencies, ModelConfiguration modelConfiguration)
         at Microsoft.EntityFrameworkCore.ModelConfigurationBuilder.CreateModelBuilder(ModelDependencies modelDependencies)
         at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, ModelDependencies modelDependencies)
         at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, ModelCreationDependencies modelCreationDependencies, Boolean designTime)
         at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel(Boolean designTime)
         at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
         at Microsoft.EntityFrameworkCore.DbContext.get_Model()
         at Microsoft.EntityFrameworkCore.DbContext.DisposeSync(Boolean leaseActive, Boolean contextShouldBeDisposed)
         at Microsoft.EntityFrameworkCore.DbContext.Dispose()
         at 
...

I do have the following which may be the reason for the error but I'm not sure:

services.AddDbContextPool<T>(optionsBuilder);

services.AddPooledDbContextFactory<T>(optionsBuilder);

Where optionsBuilder contains the same configuration for both of these.

This did work in 8.0

[Rasmus715]

@Rasmus715 @clintsinger you will need to update the way how you register the mapping Check out https://www.npgsql.org/efcore/mapping/enum.html?tabs=with-external-datasource%2Cwith-datasource#setting-up-your-enum-with-ef

The database context and enum mapping are registered the next way:

 var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
 dataSourceBuilder.MapEnum<***>();
 ...
 dataSourceBuilder.MapEnum<***>("***");

 var datasource = dataSourceBuilder.Build();

 services.AddDbContext<DbContext>(options => options.UseNpgsql(
     datasource,
     sqlOptions =>
     {
         sqlOptions.EnableRetryOnFailure();
     }));

Results with the same error.
It's possible that I'm facing it because the app is using .NET 8. But this needs to be verified.

[clintsinger]

I have done a bit more experimentation and based on my use case I'm still running into the issue Sequence contains more than one matching element with the following:

services.AddDbContextPool<T>(optionsBuilder);

services.AddPooledDbContextFactory<T>(optionsBuilder);

It seems that whatever is happening under the hood it doesn't like registering two types of db context. Again, this worked fine in 8.0.

I should also point out that I did also have a duplicate MapEnum<...>("type_name") as well but after fixing it I was able to make the error go away when I had a single registration but not when I had both. As an aside, it would be great if when the exception is thrown it actually indicates the name of the item that is duplicate to make it easier to find.

[clintsinger]

I was able to work past my issue with the two contexts by refactoring my project to only use the AddPooledDbContextFactory. It wasn't ideal because I am using the ASP.NET auth libraries which don't work with the context factory so I had to pull out special configuration for them using the AddDbContextPool. The issue still stands that you should be able to register any number of context without causing an enum conflict.

After getting past that I ran into another issue

      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
       ---> Npgsql.PostgresException (0x80004005): 42804: column "condition" is of type inventory_condition but expression is of type integer
      
      POSITION: 196
         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.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
        Exception data:
Exception thrown: 'System.IO.IOException' in System.Net.Sockets.dll
Exception thrown: 'System.IO.IOException' in System.Net.Sockets.dll
          Severity: ERROR
          SqlState: 42804
Exception thrown: 'System.IO.IOException' in System.Net.Sockets.dll
          MessageText: column "condition" is of type inventory_condition but expression is of type integer
          Hint: You will need to rewrite or cast the expression.
          Position: 196
          File: parse_target.c
          Line: 587
          Routine: transformAssignedExpr```

The enum is mapped according to the latest 9.0 guidelines.

services.AddPooledDbContextFactory<T>(contextOptions =>
{
    ... 
    contextOptions.UseNpgsql(options.ConnectionString, builder =>
    {
        builder.UseNodaTime();
        builder.UseNetTopologySuite();
        ...
        builder.MapEnum<InventoryConditionType>("inventory_condition");
        ...
    });
});

I do have other enums that are configured the same way but they aren't throwing the same error.

The only thing I can think of that might "different" is that these entities with the condition enum are the only ones also using UseTpcMappingStrategy.

The enum fields in question are in a base class.

[wojtek-viirtue]

Seeing the same thing. We have 2 contexts (migrating data from legacy service) and the enums are registered properly using the latest pattern via the NpgsqlDataSourceBuilder.

Versions

Microsoft.EntityFrameworkCore - 7.0.20
Npgsql.EntityFrameworkCore.PostgreSQL - 7.0.18

Stack Trace

Npgsql.PostgresException (0x80004005): 42883: operator does not exist: sequence_type = integer

POSITION: 121
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|233_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(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at Viirtue.Invoicing.Data.EntityFramework.Repositories.NextSequenceEfRepository.NextSequence(SequenceType sequenceType, Guid resellerId) in /Users/wojtek/src/viirtue/viibe/libs/dotnet/services/invoicing/Viirtue.Invoicing.Data.EntityFramework/Repositories/NextSequenceEfRepository.cs:line 48
   at Viirtue.Invoicing.Data.EntityFramework.Repositories.NextSequenceEfRepository.NextSequence(SequenceType sequenceType, String resellerId) in /Users/wojtek/src/viirtue/viibe/libs/dotnet/services/invoicing/Viirtue.Invoicing.Data.EntityFramework/Repositories/NextSequenceEfRepository.cs:line 43
   at Viirtue.Invoicing.Core.Invoices.Services.Impl.BillingPeriodInvoiceGeneratorService.GenerateInvoice(GenerateInvoiceArg message) in /Users/wojtek/src/viirtue/viibe/libs/dotnet/services/invoicing/Viirtue.Invoicing.Core/Invoices/Services/Impl/BillingPeriodInvoiceGeneratorService.cs:line 44
   at Viirtue.Invoicing.App.Components.MassTransit.Activities.Invoices.Generate.GenerateInvoiceActivity.Execute(ExecuteContext`1 context) in /Users/wojtek/src/viirtue/viibe/apps/dotnet/invoicing/src/Viirtue.Invoicing.App/Components/MassTransit/Activities/Invoices/Generate/GenerateInvoiceActivity.cs:line 39
  Exception data:
    Severity: ERROR
    SqlState: 42883
    MessageText: operator does not exist: sequence_type = integer
    Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
    Position: 121
    File: parse_oper.c
    Line: 656
    Routine: op_error

Note

I've worked around this in a previous project/service where we were using Dapper and I remember having to doing something like:

SELECT
...
CASE ""Key"" WHEN 'invoice_number' THEN 'InvoiceNumber' 
             WHEN 'order_number' THEN 'OrderNumber' END as ""Key""
...

Once the db value matches the properly cased Enum representation, everything works fine. However, as you can tell from the stack trace, we're using a DbContext and the error is being thrown on the call to SingleOrDefaultAsync so we can't use the same workaround without some rework.

[roji]

There seem to be multiple things being reported in this issue.

First, enum mapping has indeed changed significantly in 9.0, and user code may need to react. Please carefully read the 9.0 release notes to know more. If you still believe you're hitting a provider bug after reading that, then I need a minimal, runnable repro that shows the problem - please open a new issue, as this one has become somewhat unfocused.

Also, there's already #3375 tracking the problem of MapEnum being called multiple times (see #3375 (comment) for the workaround).

[clintsinger]

Yes, this thread has evolved and could be split into additional issues. I'll sum up what has occurred.

1. "operator does not exist"
   This was due to the changes to the way enum configuration worked between 8.0 and 9.0. This was the original issue so
   technically this issue could be closed.

2. "duplicate enum"
   Once the original issue was resolved it led to an issue where multiple DbContext were creating conflicts with
   MapEnum. This is the same issue as Mapping enum issues with multiple DbContext's #3375.

3. "column "condition" is of type..."
   This should be created as a new issue as it appears to follow the UseTpcMappingStrategy. I'll open a new issue for this
   specifically and try to narrow it down with a repro.
   UPDATE: This issue appears to be caused by a bug in my code that wasn't exposed in the .NET8 version but showed up when I moved to .NET9. I had a entity property defined in the base class and in one of the derived classes I initialized the property again but there was a spelling mistake in the mapping. I removed the second initialization of the property since it was already handled in the base class initialization and the problem was resolved.

[roji]

Great, thanks for the breakdown @clintsinger.