How to design mssql db for storing name/value objects of unknown value type (EF core 5 as ORM)?

Question

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 7-Sep-21 0:32am

Member 14637786

Updated 7-Sep-21 1:02am

Add a Solution

Comments

Richard MacCutchan 7-Sep-21 6:48am

Why would you use nvarchar to store numeric values?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2021-09-07T01:02:00

Solution 1

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");
}

Posted 7-Sep-21 1:02am

Richard Deeming

Comments

[no name] 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

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.

[no name] 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.