ValueConverter for collection of objects to collection of enum

[rich0726]

I have an issue trying to configure a ValueConverter to convert my database object, which represents a many-to-many collection of string objects, to an in-code collection of enums that map to those strings. In short, I'm persisting an open enum in my database so I can track non-standard values (i.e. those outside of the enum definition). I want to be able to use the in-code enum as a filter for my OData service I'm using on top of EF Core.

Example: I have a class called Person. It has an iCollection of Sports. Sport is an open enum, with 10-15 common sports pre-seeded. Person's can play multiple sports, and sports can have multiple people who play it.

When I query the database, I want to use a Value Conversion to convert the collection of Sports objects (which have a key, a numeric ID, and a string representation) to just a collection of the Sports enum I have in code.

I haven't been able to figure out how to create a ValueConverter for this instance. I've found examples for a single enum value, but nothing for a collection.

Thanks in advance for any help!

[ajcvickers]

@rich0726 I don't think this is currently possible, but it depends on how the database table(s) are defined and what you mean exactly by an "open enum". Can you post SQL that shows the table(s) and C# code that shows the open enum?

[maulik-modi]

@rich0726 , Please share your classes and TSQL

[rich0726]

Thanks to both of you for your response. I need to put together a small POC app that showcases the issue. That will be easier for you guys to pick up and try out. Give me a day or two to do so.

[rich0726]

BTW, what I mean by an "open enum" is an enumeration class that has a set of known values, as in "Football, Baseball, Soccer". But, upon new data ingestion, there could be custom values: "Underwater Basket Weaving", "Synchronized Water Dancing", etc. So I can't define it as an enum, since an enumeration is set at runtime. Currently I use a backing table to store all known and custom values, and I dynamically compile a DLL whenever those values change, which is then referenced in my API project.

[rich0726]

I've created a POC and uploaded to GitHub:
https://github.com/rich0726/PeopleSportPOCM2M

You should be able to build the database from a Code-First Migration. If not, I've included a SQL .BAK file in the repo that you should be able to restore from.

You'll see in my DBContext class that I've been trying to tinker with value conversion, unsuccessfully. If you run the project and go to:

http://localhost:50553/odata/people

...you'll see that Sports is now shown, even though I have an "Include" in my controller code. If I run an expand on Sports, I get the object view of the sports in the JSON. I'd ideally like to see a comma-delimited string of Sports instead of the objects, and ideally without having to call an Expand.

Thanks in advance for any guidance!
Rich

[rich0726]

Also, I wouldn't worry about the open enum at this point. I've got that handled (it'll be dynamically compiled from the DB into a DLL that is then referenced in my project...so all values for sports will be valid enum values, at all times)

[ajcvickers]

@rich0726 There's a variety of different code snippets in the project, so it's hard to tell what your intentions are. Do you want to have a table for Sports in the database? If so, value converters are not the way to do this; instead it should be modeled as a relationship in the normal way.

If you don't want a table for Sports, but rather format the sports into a single column in the database and then split them again on the way out, then a value converter can do that.

[rich0726]

Thanks for getting back to me. I'm beginning to realize that going with the relational table to store Sports isn't the way to go for what I want to do. I've got a separate POC that models things the way you describe: a single column in the Person table with a comma delimited list of Sports. I use a value converter to convert this to a list of my enum type.

This works up until the point that I try to filter the Person object based it having an assigned Sports value. It appears that EFCore generates the Linq query incorrectly, and SQL can't process it.

Here is the repo with the simple database field for storing Sports:
https://github.com/rich0726/PersonSportPOC

When I run the following OData query against this API:

/Person?$filter=Sports/Any(enum:enum eq PeopleSportsSandbox'Soccer'),

I get the following error:

InvalidOperationException: The LINQ expression 'DbSet()
.Where(p => p.Sports
.Any(enum => (int)enum == 2))' 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

I've tried to pull the People records into the client before applying the OData query options to it, but it doesn't circumvent the error.

Rich

[ajcvickers]

@rich0726 Querying into the converted value is not yet supported. See #10434.

[rich0726]

Can you suggest an alternative approach?

[rich0726]

Or, going back to the other POC (the one with the M2M suffix), whereby I have Sports as a many-to-many relationship, how would I accomplish this? That was the initial scope of this issue.

[rich0726]

BTW, I used this sample to figure out the converter and comparer code:
https://gregkedzierski.com/essays/enum-collection-serialization-in-dotnet-core-and-entity-framework-core/

[ajcvickers]

@rich0726 If both sides of the relationship are entity types, then this should be a normal EF Core relationship mapping. If this doesn't work, then please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[rich0726]

@ajcvickers I understand what you're saying, but what happens then isn't necessarily what I want. Let's assume Sports and People have navigation properties to each other, and upon pushing a migration to the DB, EFCore does the legwork of creating the relational table between them. That's great, and saves a lot of headaches and SQL work.

However, then Sports are treated as a related entity, not a property of People, which presents three problems:

• I can't filter People based on Sports in my OData API
• In order to view the sports a person plays, I have to call an Expand in my queries, which isn't desirable in this case
• The Sports are treated as a collection of objects, instead of a collection of enums, which also isn't desirable in this case.

I'm probably being naive, but there's gotta be a way to model this correctly...seems like a collection of lookups would be a pretty common thing.

Thanks for any guidance!

[AndriySvyryd]

I think #25163 should allow to do what you are asking for.

[rich0726]

@AndriySvyryd , how so?

[AndriySvyryd]

It would allow to store Sports in its own table and treat it as a collection of enums without value converters.

[rich0726]

Unfortunately this approach has the same drawbacks I mentioned to @ajcvickers above.

I want my data represented (and queryable)like this:

"Sports": [
"Baseball",
"Football"
],

but this method renders it out like this:

"Sports": [
{
"Name": "Baseball",
"Id": 1
},
{
"Name": "Football",
"Id": 2
}

Also, I need to call expand to get the list of Sports to show up, and I can't filter People by the Sports they play. The poster in the other Issue mentions this, too: "All proposed solutions are to serialize the collection but this does not allow for querying it's content easily or to create a class with a single property to represent the new table."

[AndriySvyryd]

While Sports would be stored with an Id column, on the entity it would be represented as

"Sports": [
"Baseball",
"Football"
]

as long as you are ok with the restriction that the values in Sports for a given entity must be unique.

They would be queryable and you wouldn't need to call Expand on it as it would be auto-included.

[rich0726]

@AndriySvyryd , that's exactly what I need. How would I code that in my entity model?

Thanks!

[AndriySvyryd]

You need to wait until #25163 is implemented