Hello,
How do you approach typical scenario of custom values (key/value pairs) for an entity? The example would be something like this:
class Person
{
int Id {get;set;}
string Name{get;set;}
IEnumerable<CustomProperty> CustomProperties {get;set;}
}
class CustomProperty
{
int Id {get;set;}
string Name{get;set;}
string Value{get;set;}
}
The problem is that Value needs to be of different types so e.g custom property called Color would have a string value of e.g. "Red" while Age would have to be treated as a number (even though it's stored as nvarchar). How would you approach this with a requirement to be able to filter and order over custom properties (multiple at once also with OR/AND) when querying Persons so I'd like to e.g. query over Persons of Age greater than 10 while Value is stored as nvarchar so casting is needed for comparing as numbers instead of strings.
The implementation is based on EF Core 5 Code First and C#.
What I have tried:
Possible ways I've come up with:
1) Building query with casting/converting and adding PropertyType property to the CustomProperty entity to declare what type is stored. This would store everything in a single table CustomProperties. That's tricky but doable, the hard part would be filtering and ordering over properties that need to be cast first to 'real' types instead of nvarchar and create a query that's executed on SQL fully instead of partially on SQL and partially locally.
2) Creating a separate table per a custom property type so creating tables: StringProperties, IntegerProperties, DateTimeProperties which would simply be {string Name, int Number}, {string Name, string Value}, {string Name, DateTime Timestamp} etc. And all these would have a relationship to Person entity.
3) Other options?
How do you typically approach such dynamic collection of properties in EF Core and MSSQL? Obviously the properties are unknown and the solution needs to cover adding whatever property of whatever value, also multiple values of the same type but different names e.g. Age, Weight. And in the end, as mentioned, filtering and ordering over these properties with complex filters filter1 AND filter2 OR filter3 ORDER BY order1 THEN BY order2