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.