Exception when using an Any/All filter query on collection of enums field

[rich0726]

I get the following exception when running an OData filter query against a field in my model that is a collection of enum values.

System.InvalidOperationException: The LINQ expression 'DbSet() .Where(p => p.myAppliances .Any(enum => (int)enum == 61))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

Assemblies affected

OData 7.8.2 EFCore 5.0.4

Reproduce steps

I have an entity called Property (think real estate property). It has a field defined as an ICollection of enum called Appliances. That is persisted as a comma-delimited string in SQL server. I run the following query:

http://localhost:5000/odata/property?$filter=myAppliances/any(enum: enum eq enums.Appliances'Washer')

and it errors out.

Expected result

I'd expect this to not throw the exception.

Actual result

It throws the exception in my OData controller at the point where I try to apply the ODataQueryOptions to my context.

Additional detail

I've tried a few things:

• querying for the property data first and then using a opts.ApplyTo on the resulting data set.
• first casting Property results to AsEnumerable before then casting to AsQueryable
• the reverse, getting my AsQueryable and then casting to AsEnumerable

[Sreejithpin]

HI, Can you please share your model and a repro for this issue and we can take a look, thanks

[rich0726]

Yes I can. Give me a couple of days to put together a lightweight POC app that showcases the issue, and I'll update at that point.

[rich0726]

OK, here you go:

https://github.com/rich0726/PersonSportPOC

You can create the DB using code-first migrations...it's just a single table called People with four fields: PK int PersonID, string FirstName, string LastName, and string Sports. Alternatively, I've included a SQL .bak (PeopleSports.bak) file that you can restore from.

To replicate the issue, run the API locally, and hit the following URL:

http://localhost:50553/odata/people?$filter=Sports/any(enum:enum eq PeopleSportsSandbox.Sport'Soccer')

Thanks!

[rich0726]

any updates on this issue?