Efcore.pg: InvalidCastException when using unmapped parameterized enum

[oliveira-marcello]

The query below to filter by the day of week of a DateTime throws an InvalidCastException.

var dayOfWeek = DayOfWeek.Monday;
var mondayTransactions = await _context.Transactions
    .Where(t => t.Date.DayOfWeek == dayOfWeek)
    .ToListAsync();

And this other query correctly translates to SQL using a hardcoded day of week.

var mondayTransactions = await _context.Transactions
    .Where(t => t.Date.DayOfWeek == DayOfWeek.Monday)
    .ToListAsync();

The exception thrown by the first query:

System.InvalidCastException: Can't write CLR type System.DayOfWeek with handler type Int32Handler
   at lambda_method(Closure , NpgsqlTypeHandler , Object , NpgsqlLengthCache& , NpgsqlParameter )
   at Npgsql.TypeHandling.NpgsqlSimpleTypeHandler`1.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter)
   at Npgsql.NpgsqlParameter.ValidateAndGetLength()
   at Npgsql.NpgsqlCommand.ValidateParameters(ConnectorTypeMapper typeMapper)
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(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.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, 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.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)

Luckily there are only seven days in a week, so I'm working around this issue by using an extension method like this

public static IQueryable<Transaction> FilterByDayOfWeek(this IQueryable<Transaction> transactions, DayOfWeek dayOfWeek)
{
    return dayOfWeek switch
    {
        DayOfWeek.Monday => transactions.Where(t => t.Date.DayOfWeek == DayOfWeek.Monday),
        DayOfWeek.Tuesday => transactions.Where(t => t.Date.DayOfWeek == DayOfWeek.Tuesday),
        ...
    };
}

[roji]

For now, as a workaround, you can cast the parameterized enum to an int before using it in your LINQ query:

c# var dayOfWeek = (int)DayOfWeek.Monday; var mondayTransactions = await _context.Transactions .Where(t => (int)t.Date.DayOfWeek == dayOfWeek) .ToListAsync();

[roji]

The root cause of this is https://github.com/dotnet/efcore/issues/21770

[zakeryooo]

Is there an elegant workaround for this for an array of enum (mapped to text[]), I think I'm hitting the same bug (v5 preview 7);

Region region = Region.US;
var shops = ctx.Shops.Where(s => s.Regions.Contains(region)).ToList();

throws

Can't write CLR type Proj.Model.Region with handler type TextHandler'

If I hardcode the region:

var shops = ctx.Shops.Where(s => s.Regions.Contains(Region.US)).ToList();

It works so I could write a switch statement as a temporary workaround but my Region enum has 100+ values so that wouldn't be great. Any better way to workaround this? Thanks!

[yyjdelete]

@zakeryooo
Not sure if it works, but maybe you can try with the below code as an workaround
var shops = ctx.Shops.Where(s => s.Regions.Contains((Region)(object)region)).ToList();

[zakeryooo]

Thanks for the suggestion @yyjdelete but unfortunately it didn't work, the query goes through with that cast, but it doesn't actually return any results (not sure why):

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (5ms) [Parameters=[@__region_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT s."Id", s."Regions", s."Identifier", s."Name", s."Website"
      FROM "Shops" AS s
      WHERE s."DeliveryRegions" @> ARRAY[@__region_0::text]::text[]

[yyjdelete]

@zakeryooo
Try this?
var shops = ctx.Shops.Where(s => s.Regions.Contains((Region)(object)region.ToString())).ToList();

[zakeryooo]

Thank you @yyjdelete! That works 👍