Struggling with Enum values and $filter query

[marknextdev]

Our database is MSSQL and we use Microsoft.EntityFrameworkCore 6.0.22, AutoMapper.AspNetCore.OData.EFCore 4.0.1 and Microsoft.AspNetCore.OData 8.2.3. Our entities have properties that are of Enum types, stored in db as either int or string. The DTO classes have properties of the same name, but are always of the type string. When these properties are filtered in OData, the query against the entities is not successful.

Some sample code:

public class Foo
{
    public int Id { get; set; }
    public FooEnum EnumValue { get; set; }
    public FooEnum EnumAsStringInDTO { get; set; }
    public FooEnum EnumAsIntInDTO { get; set; }
}

public class FooDTO
{
    public int Id { get; set; }
    public FooEnum EnumValue { get; set; }
    public string EnumAsStringInDTO { get; set; }
    public int EnumAsIntInDTO { get; set; }
}

When I create a Queryable from a list, it behaves how I would expect it to:

[HttpGet("odata/foos")]
public ActionResult<IQueryable<FooDTO>> GetAllFoos(ODataQueryOptions<FooDTO> odataQuery)
{
    List<Foo> listOfFoos = new List<Foo>()
    {
        new Foo() { Id = 1, EnumValue = FooEnum.Bar, EnumAsStringInDTO = FooEnum.Bar, EnumAsIntInDTO = FooEnum.Bar, },
        new Foo() { Id = 2, EnumValue = FooEnum.Bar2, EnumAsStringInDTO = FooEnum.Bar2, EnumAsIntInDTO = FooEnum.Bar2, },
    };

    IMapper mapper = new MapperConfiguration(opt =>
    {
        opt.CreateMap<Foo, FooDTO>()
            .ForAllMembers(o => o.ExplicitExpansion());

    }).CreateMapper();

    var fooQuery = listOfFoos.AsQueryable().GetQuery(mapper, odataQuery);

    return Ok(fooQuery);
}

These are OK:

GET /odata/foos?$filter=EnumValue eq 'Bar2'

GET /odata/foos?$filter=EnumAsIntInDTO eq 1

And this throws an exception:

GET /odata/foos?#filter=EnumAsStringInDTO eq 'Bar2'

Exception thrown: 'System.InvalidOperationException' in System.Linq.Expressions.dll: 'No coercion operator is defined between types 'FooEnum' and 'System.String'.'

After this I add additional configuration in the mapping profile

opt.CreateMap<Foo, FooDTO>()
    .ForMember(x => x.EnumAsStringInDTO, opt => opt.MapFrom(src => src.EnumAsStringInDTO.ToString()))
    .ForAllMembers(o => o.ExplicitExpansion());

And then the last OData filtered query also succeeds.

However when I apply the same configuration to a DbSet<Foo>, the LINQ query doesn't succeed into translating into SQL.

var fooQuery = fooDbContext.Foos.GetQuery(mapper, odataQuery);

GET /odata/foos?#filter=EnumAsStringInDTO eq 'Bar2'

System.InvalidOperationException: The LINQ expression 'DbSet() .Where(f => f.EnumAsStringInDTO.ToString() == __TypedProperty_0)' could not be translated. Additional information: Translation of method 'object.ToString' failed.

The only way that I managed to have the query correctly translated, is to do a manual mapping:

opt.CreateMap<Foo, FooDTO>()
    .ForMember
    (
        dest => dest.EnumAsStringInDTO,
        opts => opts.MapFrom
        (
            src => src.EnumAsStringInDTO == FooEnum.Bar ? FooEnum.Bar.ToString()
                : src.EnumAsStringInDTO == FooEnum.Bar2 ? FooEnum.Bar2.ToString()
                : ""
        )
    )
    // .ForMember(x => x.EnumAsStringInDTO, opt => opt.MapFrom(src => src.EnumAsStringInDTO.ToString()))
    .ForAllMembers(o => o.ExplicitExpansion());

This is seemingly a common scenario, in which DTOs have string properties that relate to Enum values on the mapped entities. Or in any case it's very common in our application, with dozens of Enums being used in dozens of classes. Having a manual mapping like above would be hard to maintain. What would be the common approach here, or at least one that is better than what I have right now?

[BlaiseD]

I think you should add the type in the query like here.

And when there's a difference between data and DTO then a conversion like this should work.