CODEWITHSUNDEEP
c#enumsentity-framework-core
napfernandes
napfernandes

Reputation: 1359

EF Core + enum parameter in where query

I'm struggling to add an enum to a where condition I have on my queries.

I have the following definition for my class:

public enum Status
{
  Pending,
  Confirmed
}

public class Deposit
{
  public Status Status { get; set; }
}

Whenever I add a where condition with a raw enum value, like in the below example, it works perfectly:

query.Where(d => d.Status == Status.Completed);

// Generated query:
Executed DbCommand (40ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT d.*
FROM deposit AS d
WHERE d.status = 'Completed'

Whenever I try to add this enum value as a parameter, it fails in the following query:

query.Where(d => d.Status == input.Status);

// Generated query:

Failed executing DbCommand (244ms) [Parameters=[@__searchInput_Status_Value_0='Completed' (Nullable = false)], CommandType='Text', CommandTimeout='30']
SELECT d.*
FROM deposit AS d
WHERE d.status = @__searchInput_Status_Value_0

If I try to convert this enum in my model creation, I keep having the same error, either with string or int conversion:

For string conversions:

modelBuilder.Entity<Deposit>()
  .Property(d => d.Status)
  .HasConversion(new EnumToStringConverter<Status>());

Npgsql.PostgresException (0x80004005): 42883: operator does not exist: enum_deposit_status = text

For int conversions:

modelBuilder.Entity<Deposit>()
  .Property(d => d.Status)
  .HasConversion<int>();

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

Can somebody give some light on it? Thank y'all!! :)

Upvotes: 1

Views: 3711

Answers (2)

Mihai Veres
Mihai Veres

Reputation: 11

In case someone (myself included) runs into problems in the future when working with filtering a Postgres array of enums mapped as strings in EF Core, I'm just gonna make it clear that the workaround above works for arrays too, namely:

This does not work

query.Where(d => d.SupportedDepositTypes.Contains(DepositType.RecurringDeposit));

But this works

var filterValue = DepositType.RecurringDeposit;
query.Where(d => d.SupportedDepositTypes.Contains(filterValue));

Upvotes: 1

Sasha
Sasha

Reputation: 1017

It seems like this is an issue in the PostgreSQL EF Core provider.

The following workaround should solve it, but there are version limitations; See notes below.

Map:

modelBuilder.Entity<Deposit>()
  .Property(d => d.Status)
  .HasConversion(new EnumToStringConverter<Status>());

Code:

var statusFilter = new[] { input.Status };
query.Where(d => statusFilter.Contains(d.Status));

Notes regarding affected versions

The workaround above works for Npgsql.EntityFrameworkCore.PostgreSQL versions 3.0, 5.0 (and hopefully 6.0, did not check it)

Versions 3 and 5 translate the code differently:

  • 3.0 (with EF 3.x)

    info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT d.*
      FROM deposit AS d
      WHERE d.status IN ('Completed')

  • 5.0 (with EF 5.x)

    info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (15ms) [Parameters=[@__statusFilter_0='?' (DbType = Object)], CommandType='Text', CommandTimeout='30']
      SELECT d.*
      FROM deposit AS d
      WHERE b.status = ANY (@__statusFilter_0)

It won't work in 3.1; it fails with pretty much the same error:

  • 3.1 (with EF 3.x) 
    fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (18ms) [Parameters=[@__statusFilter_0='?' (DbType = Object)], CommandType='Text', CommandTimeout='30']
      SELECT d.*
      FROM deposit AS d
      WHERE b.status = ANY (@__statusFilter_0)
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for <...>.
      Npgsql.PostgresException (0x80004005): 42883: operator does not exist: character varying = integer

Upvotes: 5

Related Questions