Click here to Skip to main content
15,445,641 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 7-Sep-21 1:02am
Comments
Richard MacCutchan 7-Sep-21 6:48am    
Why would you use nvarchar to store numeric values?

1 solution

Entity-attribute-value[^] (EAV) models are generally not recommended. However, there are situations where they are unavoidable.

One option you've left off is to store the value in a sql_variant column (assuming all values will be no longer than 8016 bytes):
sql_variant (Transact-SQL) - SQL Server | Microsoft Docs[^]

EF Core has supported mapping object properties to sql_variant columns since 2018. You just need to specify the column type explicitly - for example:
C#
class CustomProperty
{
    string Name { get; set; }
    object Value { get; set; }
}
C#
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.Entity<CustomProperty>.Property(p => p.Value).HasColumnType("sql_variant");
}
 
Share this answer
 
Comments
Member 14637786 7-Sep-21 8:02am    
Does it support filtering over the object Value based on real type? Like CustomProperties.Where( x => (double)x.Value == 20.5)?
Richard Deeming 7-Sep-21 10:20am     CRLF
Just done a quick test, and that doesn't seem to work. You get an "explicit conversion not allowed" exception when you try to run the query. If you need to query by value, then you'd probably need to write a custom SQL query, map a user-defined function, or use a SQL view.
Mycroft Holmes 7-Sep-21 18:23pm    
And this is why the database design is wrong. Each type that you are likely to want to query/filter/search on should have it's own field. Truly custom fields (ie those you cannot identify) or user defined fields are the only ones you should be allowing in this format. Design your database structure correctly, don't try and take shortcuts they will cost you dearly in the future.
Member 14637786 8-Sep-21 5:03am    
Honestly I agree, I am the one who said no to this approach but that's not good enough. I need a decent idea how to make it right. And in my opinion dedicated table per custom type is a better option in terms of clean code, future enhancements and maintenance.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900